Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Finding tempdb contention Expand / Collapse
Author
Message
Posted Friday, December 6, 2013 6:49 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 16, 2014 12:56 PM
Points: 332, Visits: 868
I am having tempdb issue on my server. Everytime it happens, I need to restart the service. Is there a DMV that shows the query causing contention on tempdb?

Thanks in advance.
Post #1520782
Posted Saturday, December 7, 2013 1:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
Define 'TempDB issue'? Why do you think it's TempDB contention?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1520799
Posted Saturday, December 7, 2013 8:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 16, 2014 12:56 PM
Points: 332, Visits: 868
The tempdb grows. In other words the drive where my tempdb files are becomes full. When I right click on tempdb, it locks up and I am not able to view the properties.
Post #1520817
Posted Saturday, December 7, 2013 1:01 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
That's not contention, that's just queries using a lot of TempDB.

Query the sys.dm_db_session_space_usage to identify sessions using lots of TempDB. You can join it back to sys.dm_exec_sessions to get the sQL hangle. Will need to poll it, it's just current sessions, not history.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1520834
Posted Saturday, December 7, 2013 1:55 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 16, 2014 12:56 PM
Points: 332, Visits: 868
Thanks Gail. So, in my situation if I had used this dmv and figured out which query was using most of tempdb and killed it, I wouldn't have to restart the service? Would adding another tempdb file help for future?
Post #1520842
Posted Saturday, December 7, 2013 2:38 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
Kill the query so that the user gets annoyed and re-runs it?

If you're running out of space for TempDB you need to either change the queries so that they use less TempDB or you need to give TempDB more space.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1520844
Posted Saturday, December 7, 2013 9:17 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 16, 2014 12:56 PM
Points: 332, Visits: 868
Thanks. Which field should I look for heavy usage of tempdb?

user_objects_alloc_page_count?

Is there any threshold to indicate heavy usage?
Post #1520851
Posted Sunday, December 8, 2013 1:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
As with most things, heavy is relative to your app, not an overall distinction.

Look in Books Online for the definitions of the fields, google for any articles or blog posts on the DMV.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1520856
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse