Temp DB - Optimal Settings

Question

Post reply

Temp DB - Optimal Settings

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
July 29, 2010 at 10:03 pm

#148281

I am back here again to get suggestions of optimal setting for temp db, i know there are tons of topics on tempdb but would like confirm before i do it. We are having some performance issues and tempdb is the heart of all the processes which we run. Data and log file are on the same drive. I am planning to do the following.
i) Split data files into number of processors with equal size and autogrowth on (some say not to keep on autogrowth ,some say yes) please clarify
ii) Can someone please tell me more about spindles ,arrays? How many spindles are suggested to have on each LUN for tempdb?
thanks
Make sure you have pre-sized TempDB - when you restart the service it will revert to it's initial size.
Make sure you have Instant File Initialization turned on. If you presized this to something enormous it will take forever for the file to be zeroed out.
Make sure nothing else reads or writes from that/those disk(s).
Take a Baseline before you make any changes so you can see the impact of any change
Use the fastest disks you can get your hands on.
Read the suggestions from SQLCAT and see if they are suitable for your environment.

Rich

Hope this helps,
Rich

[p]
[/p]

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

can you clarify the issues , so that people can go for precise and close solution

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

application is running very slow...there are no blockings or dead locks....i know some changes can be made to sql query and i do see some i/o issues.thanks

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 3

There are lots of opinions on how TempDB should be set. Some people show metrics on how much quicker it is when split across multiple files whilst others have shown that in their environment 1 file is the fastest configuration.

Without knowing how your disks are configures it would be very difficult to know what to suggest, so I am going to play it safe and give some general advice: