TempDB Monitoring and Troubleshooting: Out of Space

What are DPA's capabilities for monitoring and troubleshooting when [tempdb] runs out of space?

Can DPA...

Identify SPIDs/transactions consuming the most space in [tempdb]?
Identify related SQL statements of SPIDs/transactions?
Identify [tempdb] file space usage (size, allocated size, unallocated size, version store size, user objects size, internal object size, mixed extent size, etc.)
Identify SPIDs with open transactions?

The above information is readily available in various DMVs and I could roll my own solution to get it, if necessary. But I don't want to reinvent the wheel if DPA can get some or all of the above information.

I would kindly ask: DPA solutions only, please.

-Dave

Find more posts tagged with

sql server db

TempDB

sql server dba

dpa sql server

Accepted answers

All comments

athornbury

Dave-

I don't think that DPA has any immediately built-in capabilities to this effect, but what about Custom Metrics and Alerts?

Based on the following information:

https://www.mssqltips.com/sqlservertip/4356/track-sql-server-tempdb-space-usage/

https://blogs.msdn.microsoft.com/sqlserverfaq/2010/10/13/troubleshooting-tempdb-growth-due-to-version-store-usage/

Custom Metric:

Identify [tempdb] file space usage (size, allocated size, unallocated size, version store size, user objects size, internal object size, mixed extent size, etc.)
- I created a series of single value custom metrics on the aggregated sum value of each of the values returned from sys.dm_db_file_space_usage
- I also created two additional single value rate custom metrics from sys.dm_os_performance_counters for the 'Version Generation rate (KB/s)' and 'Version Cleanup rate (KB/s)' counters
- I am debating about adding metrics about temporary object usage, reuse, etc. but haven't decided on it yet
- I created a Metrics category, tempdb, and these custom metrics now appear on the Resource tab for each monitored instance
- See the screenshot below for an example of the results

Custom Alerts:

Identify SPIDs/transactions consuming the most space in [tempdb]?
- I think this item could be configured as Custom Alerts and as a "Custom SQL Alert - Single Numeric Return" alert.
- With the first link above, use or customize one of the queries provided to return two columns, session_id and maybe total allocation, where total allocation is greater than 0
- Set the desired threshold levels for when the level is info, low, medium, high and alert the appropriate group or contact accordingly
- If any one of the values is within those established thresholds then an alert will be sent

Here are some possible options for the remaining two:

Identify related SQL statements of SPIDs/transactions?
- Drill into the Trends or Current tab, you should be able to related activity to specific session ids

Identify SPIDs with open transactions?
- Not so much DPA, but Paul Randal and Adam Machanic both provide useful options:

https://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/

http://whoisactive.com/downloads/

DPA - Custom Metrics - tempdb.jpg

athornbury

I did have to overcome an issue with changes to the DMV sys.m_db_file_space_usage from 2005 to 2008 R2 and 2012 to 2016. Yes, sadly I still have one, solitary 2005 instance to monitor!

- 2012 and newer currently have two additional columns - total_page_count and allocated_extent_page_count - that 2005 to 2008 R2 do not

- However, these two values can be calculated from the remaining columns that are common across all versions from 2005 to at least 2016.

- This has been verified successfully on 2005, 2008 R2, 2014, and 2016. I do not currently have any instances of 2008 or 2012, but it is likely that it will work on these as well.

For total_page_count I used the following TSQL:

----BEGIN CODE BLOCK----

USE tempdb;



/* Setup variables */

DECLARE

@version AS NUMERIC(4,2)

, @stmt AS NVARCHAR(1000);



/* Accommodate SQL Server 2005 which does not allow in-line assignment of values when declaring variables */

SELECT

@version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)), 4) AS NUMERIC(4,2))

, @stmt = NULL;



IF (@version >= 12)

BEGIN

SET @stmt = N'SELECT

SUM(ddfsu.total_page_count) / 128

FROM

sys.dm_db_file_space_usage AS ddfsu;'

END

ELSE IF (@version >= 9 AND @version < 12)

BEGIN

SET @stmt = N'SELECT

SUM(ddfsu.unallocated_extent_page_count + ddfsu.version_store_reserved_page_count + ddfsu.user_object_reserved_page_count + ddfsu.internal_object_reserved_page_count + ddfsu.mixed_extent_page_count) / 128

FROM

sys.dm_db_file_space_usage AS ddfsu;'

END



EXEC sys.sp_executesql @stmt = @stmt;



----END CODE BLOCK----

For allocated_extent_page_count I used the following TSQL:

----BEGIN CODE BLOCK----

USE tempdb;

/* Setup variables */

USE tempdb;

/* Setup variables */

DECLARE

@version AS NUMERIC(4,2)

, @stmt AS NVARCHAR(1000);

/* Accommodate SQL Server 2005 which does not allow in-line assignment of values when declaring variables */

SELECT

@version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)), 4) AS NUMERIC(4,2))

, @stmt = NULL;

IF (@version >= 12)

BEGIN

SET @stmt = N'SELECT

SUM(ddfsu.allocated_extent_page_count) / 128

FROM

sys.dm_db_file_space_usage AS ddfsu;'

END

ELSE IF (@version >= 9 AND @version < 12)

BEGIN

SET @stmt = N'SELECT

SUM(ddfsu.version_store_reserved_page_count + ddfsu.user_object_reserved_page_count + ddfsu.internal_object_reserved_page_count + ddfsu.mixed_extent_page_count) / 128

FROM

sys.dm_db_file_space_usage AS ddfsu;'

END

EXEC sys.sp_executesql @stmt = @stmt;

----END CODE BLOCK----

jsql

Can I tack on some questions here? Let me know if you prefer a separate thread.

I see a screenshot above with tempdb info but I don't find it in my DPA. Any clue on how to get this info?

I'm having some issues with tempdb on a server in which tempdb keeps growing. I used some queries online that I found to find the largest consumer of tempdb by spid. We restarted the app service related to that spid but had the same issue the next morning. I restarted a second app service (for the next highest tempdb consuming process) hoping to get the right process this time. Now the first app has rapidly growing tempdb use again.

I'm finding really inconsistent behaviour with the scripts I find online to monitor tempdb use. The scripts from these all report zero rows:

https://blog.sqlauthority.com/2015/01/23/sql-server-who-is-consuming-my-tempdb-now/

https://thesqldude.com/2012/05/15/monitoring-tempdb-space-usage-and-scripts-for-finding-queries-which-are-using-excessive-tempdb-space/

ON SSMS reports Disk Space, it shows 99% free, even right before we killed the process(es).

The only useful scripts I can find are here, which report on tempdb LOG space usage. I'm using this to get the spid of the heaviest process in order to find what I can kill or restart.

https://thesqldude.com/2012/05/15/monitoring-tempdb-space-usage-and-scripts-for-finding-queries-which-are-using-excessive-tempdb-space/

THANKS!

athornbury1

jsql-

sorry for the long delay, I was not actively monitoring thwack.

The tempdb stuff I created is a set of Custom Metrics; it was not built-in to DPA at the time. I’m no longer at a company that uses DPA but a colleague indicated that the newest version of DPA may have taken these ideas and incorporated it into the product.