SQL Server Temp DB Tuning

SQL Server Temp DB Tuning

In ILooKIX, but not necessarily in any other Sqlserver application base, it will increase efficiency of the Database engine to create multiple Tempdb files for Sqlserver to use, based on multicore computer systems. Think of tempdb just as that, temporary working space but only for sqlserver. By default ONE will exist just like a pagefile, but speed can be enhanced by simply modifying the volume to match the disk core counts. Disregard any directions on this matter other than this document which is for KIX ONLY, not for other database uses.

The computation of HOW MANY tempdb’s you need to create, is a simple math based direction. Making sure ALL virtualization is set to true in the machine bios, the perfmon tool in windows 7 will then tell you the core count windows has seen on the machine. Assuming hyperthreading is on, you use one number, but if it is off, you use another. ONLY testing will tell you if there is an advantage to hyperthreading, and generally no advantage is seen in KIX at all with it set to true. But the math is still simple.

You start with the number of tempdb’s needed. To compute that do this:
Take the core count hyperthreading on, reduce it 50%, reduce it by a constant of 1. Example, a 4 core cpu with hyper on has 8 cores. 8 divided by 50% = 4. 4‐1 = 3, so you would create 3 tempdb’s on disk (1 is reduced because sqlserver always has 1 claimed tempdb file), then multiply it by 50% and round up. This comes out to 3x 50% = 1.5 , rounded to 2 temp db’s.

With hyperthreading OFF, this machine would have 4 cores – 1 = 3 tempdbs need to exist, but you simply half that number and then round up. So here, a typical 4 core processor – 1 = 3 X 50% = 1.5, rounded up equals 2 tempdb’s.
You start the process by going to sql server management studio…

Read the white paper here