Understanding and Monitoring Tempdb

The slides and scripts for my presentation are available here:

http://community.idera.com/wp-content/uploads/2015/06/Understanding-and-Monitoring-Tempdb-Geek-Sync.zip

---

? Vicky Harp
Idera Staff / Product Manager

Thanks to everyone who attended! I’d like to answer the questions I received during the webcast:

1. How to check no. of logical cpus?

As I promised during the presentation, here is the SQL to check: select cpu_count from sys.dm_os_sys_info

2. Can you mention any maintenance activities that could cause TempDB contention (i.e. index rebuilds, etc…) & how to avoid it?

Certainly maintenance activities like index rebuilds and DBCC CheckDB can make heavy use of tempdb. You may want to make sure that your maintenance activities do not overlap in a way that hammers tempdb: for example, if you have large data movement operations occurring in one database at the same time as an index rebuild in another database, while you’re running integrity checks on a third, you could be putting a lot of pressure on tempdb (and the buffer cache, and the CPU, and plenty of other parts of SQL Server)

3. Should you be running shrink operations on tempdb??

For the longest time the advice has been “absolutely not” because of a past issue with this causing corruption. However, recent releases of SQL Server no longer have this issue, so running a shrink operation as a one off solution to a one time problem would probably be ok. But I would not recommend it as a regular activity – if your tempdb is regularly growing quite large, go ahead and set the size accordingly and avoid autogrow. You can read more about this from Paul Randal here

4. GREAT JOB on this presentation, Vicky!

Thank you very much!

5. So.. recommended number of TempDBs? Our storage (backend) is a SAN, EqualLogics and/or EMC. What about placement? WIth a SAN, blackbox, it doesn’t matter ..

You get 1 tempdb file per SQL Server instance – and this is something I should probably clarify up front. What you can configure is the placement of the files and their count – what I refer to in the presentation as file topology. You will benefit from keeping tempdb on relatively fast storage and you’ll want to set the number of data files according to the number of processors and the amount of tempdb contention you’re experiencing. I’d recommend downloading my slides to take a look at the formula mentioned there, or download this whitepaper for a bit more information.

6. I heard that sorts can also put pressure on temp db

Absolutely, sorts, spills, worktables, temporary tables – there are a lot of operations which can put pressure on tempdb. If I had even more time for the presentation it would be great to go over some of the query tuning aspects of tempdb. Perhaps a future topic!

7. Data is out of sync and could have queries taht result in different results when using snapshot isolation

Yes, snapshot isolation should be chosen carefully as it absolutely has impacts on your resultsets. I am fond of Kendra Little’s poster (available here) to explain some of the implications of the different isolation levels.

8. On one server, tempdb is regularly +90% full, all of which is internal objects, with 2 system SPIDs maintaining those objects. One SPID is running the command BRKR EVENT HNDLR, one is BRKR TASK. How can I determine what’s causing this, and how to get them to yield their space?

That command is associated with Service Broker, and I am not ashamed to admit that I am not very fluent in Service Broker performance tuning. However, this MSDN thread seems to touch upon the issue and may help.

---

? Vicky Harp
Idera Staff / Product Manager