asked
Temp db issues and things to keep in mind?
Hi what are the precautions to be taken for temp db to avoid of being it get crashed (how it should be designed or maintained to avoid problems), for instance if have a minimum of 2 TB critical user databases on that instance. what to do if the Temp Db is growing faster like anything, is it better to add a secondary file or is it fine if i increase the file size The problem is, there are lot of long running queries, at the same time can i add secondary files to temp Db or is it good to increase the file size or any other better things to be done.
This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
Its a good idea to split TempDB files in alignment with user databases. Once can calculate the TPS rate for various UDBs to decide how many secondary files needs to be added to TempDB. Storing TempDB log files and data files in different physical drives reduce the contentions. Read more from [here][1] and Let us know if that helps. [1]:
http://technet.microsoft.com/en-us/library/cc966545.aspx
How do you mean "alignment with user databases"? How do you do that with tempdb?
by saying Alignment with user database what I mean was there is no thumb rule as such for the total number of secondary files for TempDB so the entire environment should be studied i.e. how many UDBs are there, how busy they are, workload etc before arriving to a conclusion. Hope this clarifies now?
In general you should have more than one file for tempdb. The current recommendations by the experts vary. Paul Randal of
Sqlskills.com recommends start with approximately 1/4 the number of files for the number of processors you have. You can [read more from Paul here][1]. Bob Ward, a Senior Escalation Engineer with Product Support in Microsoft (translation, he gets all the really hard problems), suggests that servers with up to eight CPUs have eight files and then monitor for latch issues on tempdb, increasing the number of files by four as latch issues appear for systems with more than eight CPUs. You can [read more about that here][2]. I prefer Paul's more conservative approach. I'd have the number of files correspond to 1/4 of the number of CPUs and then monitor your system from there. Getting tempdb onto disks separate from your operating system, SQL Server, other data and log files, and each other, is also a huge win. Also remember that the number of controllers can act as a bottleneck even if you have multiple disks, so you may need more than one disk controller to see even more benefits. For some more info on tempdb, check out Pauls post on [how to monitor tempdb][3]. [1]:
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/ [2]:
https://www.simple-talk.com/sql/database-administration/optimizing-tempdb-configuration-with-sql-server-2012-extended-events/ [3]:
http://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/
16 People are following this question.
