Finding tempdb contention

Question

Finding tempdb contention

SQL_Surfer

SSCrazy Eights

Points: 8316
More actions
December 6, 2013 at 6:49 pm

#150001

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Define 'TempDB issue'? Why do you think it's TempDB contention?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

SQL_Surfer SSCrazy Eights Points: 8316 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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, MVP, M.Sc (Comp Sci)
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

SQL_Surfer SSCrazy Eights Points: 8316 More actions · Answer 4

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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, MVP, M.Sc (Comp Sci)
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

SQL_Surfer SSCrazy Eights Points: 8316 More actions · Answer 6

Thanks. Which field should I look for heavy usage of tempdb?

user_objects_alloc_page_count?

Is there any threshold to indicate heavy usage?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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, MVP, M.Sc (Comp Sci)
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