Clever Geek Handbook

TempDB performance scan; do we have to reboot?

A little background: We have 17 different TempDB database files and 6 TempDB log files on the server. They are distributed on different drives, but are hosted on 2 drive enclosures.

I see disk IO response times exceeding the recommended limits. Typically, you want your disks to respond in 5-10ms and nothing to exceed 200ms. Observed occasional spikes up to 800ms in TempDB files, but only on one drive array.

Suggested solution: Restart the SQL server. While the SQL server is off, reboot the storage array that hosts most of the TempDB files. Also, while SQL is down, retry the network connection to bypass the network switch, trying to eliminate any source of slowness on the hardware.

Is this a good idea or a shot in the dark? Any ideas? Thanks in advance.

+3


source to share


1 answer


17? Who came up with this number? Read this and this - very few scenarios where> 8 files will help, especially if you only have 2 base arrays / controllers. Some suggestions:

  • Use an even number of files. Most people start with 4 or 8 and only exceed that when they have proven they still have competition (and also know that their basic I / O can handle more files and scale them up, in some cases it won't have an effect, or the exact opposite effect - a different drive letter does not necessarily mean better I / O transfer).
  • Make sure all data files are the same size and have the same startup settings. Having 17 files with different sizes and autodestruct settings will win the round - in many cases only one file will be used due to the way SQL Server performs proportional filling. And having a strange number just seems ... well, strange to me.
  • Get rid of your 5 extra log files. They are completely useless .
  • Use trace flag 1117 to ensure that all data files are growing at the same time and (due to 2.) the same rate. Note that this trace flag applies to all databases, not just tempdb. More information here .
  • You might also consider trace flag 1118 to change the selection, but read this first .
  • Make sure instant file initialization is enabled so that the file does not need to be zeroed out when it expands.
  • Preconfigure tempdb files so they don't grow during your day to day activities. Don't compress tempdb files because they suddenly got big - it's just a rinse and repeat operation, as if they get this big one time they'll get that big again. It doesn't look like you can rent the extracted space in the meantime.
  • If possible, do it DBCC CHECKDB

    elsewhere. If you run regularly CHECKDB

    , yay! Pat yourself on the back. However, tempdb may be affected - please see this article on optimizing this operation and remove it from your instance if possible.
  • Finally, confirm what kind of competition you see. You say tempdb performance is scanned, but how? How do you measure it? Some info on determining the exact nature of tempdb bottlenecks here and here and here and here and here .


Do you think I'm using tempdb explicitly (fewer #temp tables, @table variables, and static cursors - or cursors in general)? Are you using RCSI local variables or MARS or LOB?

+9


source






More articles:


All Articles