Category Archives: Tableau

How to get the date of creation and size of current temporary objects from tempdb

The query below will return the temporary objects that currently exist in the tempdb of an instance.

It’s a good query if you’re experiencing issues with the tempdb and want to see what objects are taking up space or if temp tables are surviving longer than you might expect them to.

Recently I found that because queries were running directly from Tableau and the connection was being held open temp tables were not being removed. All the temp tables existing at the same time was taking up several GBs of space needlessly.

-- Get current temporary objects date of creation and size 
SELECT DISTINCT obj.name AS ObjectName
	,obj.type_desc AS ObjectType
	,obj.object_id AS ObjectId
	,obj.principal_id AS PrincipalId
	,obj.schema_id AS SchemaId
	,obj.parent_object_id AS ParentId
	,stat.row_count AS RowCountStat
	,stat.used_page_count * 8 AS UsedSizeKB
	,stat.reserved_page_count * 8 AS RevervedSizeKB
	,obj.create_date AS CreatedDate
	,obj.modify_date AS ModifiedDate
FROM tempdb.sys.partitions AS part WITH (NOLOCK)
INNER JOIN tempdb.sys.dm_db_partition_stats AS stat WITH (NOLOCK) ON part.partition_id = stat.partition_id
	AND part.partition_number = stat.partition_number
INNER JOIN tempdb.sys.tables AS tbl WITH (NOLOCK) ON stat.object_id = tbl.object_id
LEFT JOIN tempdb.sys.objects AS obj WITH (NOLOCK) ON tbl.name = obj.name
ORDER BY CreatedDate ASC;

How to get tableau data to refresh automatically within the internet browser

This tutorial describes how to embed a Tableau view, i.e. a published sheet or dashboard etc., into your web page and set the view to refresh every 30 seconds.

Step 1. Open note pad and save the code below into it calling the file template.html

Step 2. Copy the URL

  • Publish the Tableau view and copy the URL from the share button

Or

  • Copy the URL if you have already published the report

Note: If a hash symbol (#) and number, or a “:iid=<n>” appear at the end of the URL, do not include those characters. For example, in the following URL, you would not copy the #3 characters:

http://server-name.com/views/AutoRefreshExample/AutoRefreshExample#3

Step 3. Replace the following in the template file with your URL and change the title to something more appropriate than Auto Refresh Example.

http://server-name.com/views/AutoRefreshExample/AutoRefreshExample

Note: You can change the rate of refresh by changing the content=”30″ value to however many seconds you need. Also you probably don’t want purple as your background color unless you’re Prince so go ahead and change that too.¬†background-color: #452775

<!--Template-->
<!DOCTYPE html PUBLIC "-//W3C//DTDXHTML 1.0 Transitional//EN" <html lang="en-US" xml:lang="en-US"> 
<head> 
<title>Auto Refresh Example</title> 
<meta http-equiv="refresh" content="30">
</head> 
<style>
body {background-color: #452775}
</style>
<body> 
 src="http://server-name.com/views/AutoRefreshExample/AutoRefreshExample?:embed=yes&:refresh=yes" 
width="100%" 
height="100%"
align="middle"
frameborder="0"  
marginwidth="0" 
marginheight="0"
scrolling="no"
> 

 

Your browser does not support iframes.

 

 
 
</body> 
</html>