SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Monitor SQL Server TempDB usage

07 March,2013 by Jack Vamvas

Large queries spilling into tempdb such as SQL Server – SORT IN TEMPDB and Sort Warnings  and large temporary tables can use Tempdb space very quickly and lead to outages. Use the DMVs : sys.dm_db_file_space_usage, sys.dm_db_session_space_usage, sys.dm_db_task_space_usage

How to Troubleshoot

First Step - Comprehensive Tempdb Sizing exercise. There are many different methods  , such as : a) create a workload b) running large maintenance jobs e.g index rebuild

Second Step - monitoring Tempdb usage. As well as a  SQL Server monitoring alerts solution based on events , such as disk full, there are some useful DMVs  to drill into TempDB usage based on tasks and sessions.

sys.dm_db_file_space_usage -  Lists space usage information for each file in the Tempdb database. Reports on unallocated extent page count.

sys.dm_db_session_space_usage – Broken down by each session. Lists the number of pages allocated and deallocated

sys.dm_db_task_space_usage – Broken down by each task. Lists page allocation and deallocation activity

 

go
select * from  sys.dm_db_file_space_usage 
go 
select * from  sys.dm_db_session_space_usage
go
select * from sys.dm_db_task_space_usage


Read More

SQL Server – List current tempdb statements

SQL Server - TempDB performance and strategy checklist

SQL Server – tempdb and solid state drives

SQL Server - Shrink tempdb without sql server restart

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer