 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008TEMPDB Performance
All, I am still trying to find some script or set of actions that will prove the best practice of having 1 data file for each processor on a multi processor box. Any help or direction is greatly appreciated! David Hay What about this one? Linchi > All, > [quoted text clipped - 3 lines] > > David Hay Am I missing something??? What about trying: http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx http://blogs.msdn.com/weix/archive/2005/09/13/464907.aspx http://msdn.microsoft.com/en-us/library/ms175527.aspx John > What about this one? > [quoted text clipped - 7 lines] >> >> David Hay Sorry, I should have noted I am working with SQL2000. Thanks! Most of what is in the link John supplied about Tempdb is still valid for 2000 even though the article was written for 2005. But here is the original KB dealing with the tempdb contention issues. http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B328551&Product=sql2k Please note that adding multiple files and even the trace flags will not improve performance if you don't have contention there to begin with.  SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Sorry, I should have noted I am working with SQL2000. > > Thanks! Also note that you can actually get much WORSE performance if you have, say, a single drive that you are putting multiple files on! the head thrashing that results will be unkind to IO throughput. SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net > Most of what is in the link John supplied about Tempdb is still valid for > 2000 even though the article was written for 2005. But here is the [quoted text clipped - 7 lines] >> >> Thanks! Hmmm... This is an interesting question. I'm not sure to be honest. If there is head thrashing, one would think there'll be head thrashing even with a single file??? Linchi > Also note that you can actually get much WORSE performance if you have, say, > a single drive that you are putting multiple files on! the head thrashing [quoted text clipped - 11 lines] > >> > >> Thanks! >Hmmm... This is an interesting question. I'm not sure to be honest. If there >is head thrashing, one would think there'll be head thrashing even with a >single file??? Could be less thrashing, but more waiting, depending on scenario. The danger is that more concurrency produces relatively more thrashing! J. I had a client that created a 16 file tempdb on a 2 drive raid1 set. Dropping back to (IIRC) a single file improved performance of their typical workload between 10 and 15%. Note that they wrote a fairly large amount of data to tempdb for work tables, etc, but they did NOT have large amounts of create/drops (i.e. no SGAM issues). Also note that a conforming benchmark was not done before/after - just timing metrics of report runs which clearly could have been affected by other loads on server. SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net > Hmmm... This is an interesting question. I'm not sure to be honest. If > there [quoted text clipped - 22 lines] >> >> >> >> Thanks! Thanks for all the input. There are some pieces that do extensive create/drops in TempDB, then I have the business objects users who are doing alot of sorting/grouping etc which also takes place in tempdb. I do see the pagelatch_up waits Andrew mentioned. I might just have to do my best to convince them to let me try it if I cannot replicate the production volume/configuration. Once again, many thanks to all! David Hay >Also note that you can actually get much WORSE performance if you have, say, >a single drive that you are putting multiple files on! the head thrashing >that results will be unkind to IO throughput. A very interesting point - not mentioned in any of the Microsoft white papers I've seen! Let's see, do we benefit by reducing contention at the cost of more head movement? I suppose you can find scenarios that are better one way or the other. J. Andrew, Thanks for the link. Based on what I am seeing I do have some contention issues, but before I can implement a change, I need to prove it in a dev/qa environment before I can push it to production no matter what "best proactices" says, or other peoples observations. I need to be able to prove it on one of our boxes. My issue is that our Dev and QA environments do not have the same set up, much less the volume that the production server has. That is why I am looking for some kind of script that I can run against either configuration and show those results as proof that the concept works. Thanks again! David Hay > Andrew, > [quoted text clipped - 11 lines] > > David Hay Hi If you don't have something like LoadRunner or Rational Performance Tester or if you Web Application Visual Studio Team System, you will be able to put different loads on the system and monitor the changes. Of course over use of tempdb by your application could actually be the issue rather than how they system is configured. If you only want to prove the discs are slow then look at SQLIOStress or the newer SQLIOSim. http://support.microsoft.com/kb/231619 John If you don't have a test env that will stress the db similar to the prod then it will be hard to prove that the changes will actually make a difference. But if you can prove you have contention issues in tempdb then that should be enough. Do you have pagelatch_UP waits? If you do that is a very good sign you have contention and the additional files and or trace flags should help. In my experience with lots of contention this can make a dramatic difference. There is an extremely low if any risk you will see any decrease in performance even if this wasn't the issue. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Andrew, > [quoted text clipped - 11 lines] > > David Hay Linchi, There was no link... Thanks! David Hay Not the first time this happened, but I meant to include this; http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx > What about this one? > [quoted text clipped - 7 lines] > > > > David Hay |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.