question

Therealist avatar image
Therealist 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.
tempdbissuetempdbfiles
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
0 Likes 0 ·
VishalhSingh avatar image
VishalhSingh answered
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
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
How do you mean "alignment with user databases"? How do you do that with tempdb?
0 Likes 0 ·
DenisT avatar image DenisT commented ·
How the TPS rate may relate to TempDB? Say you have an OLTP system with 10,000 TPS, short, fast, and serial transactions -- how would it affect TempDB? I think you need to consider a workload, the actual queries, and not the TPS. Am I wrong?
0 Likes 0 ·
VishalhSingh avatar image VishalhSingh commented ·
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?
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.