February 3, 2009 at 2:05 am
Hi all,
I've been doing some server bench marking on one of our production SQL 2K5 instances, one thing I did notice was the transaction/sec counter (using performance monitor). So I did a little more digging around and found nearly all of the activity for this counter is on the tempdb.
My question is, I assume this is being caused by operations which are occuring in the tempdb such as sorts, orders, temp tables etc. Is this correct? and if so, is this operation worse (in general) than the transaction/sec being done in one of the user db's rather than tempdb?
I know this is a bit of an open ended question, there are no noticable performance issue with the server but i'm trying to be pro-active and anywhere I can increase performance would be a benefit.
Thanks in advance.
February 3, 2009 at 7:43 am
Are you making selection out of the available databases listed on the right hand side when you click on Transactions/sec counter?
MJ
February 3, 2009 at 7:45 am
Hi,
Yes, original I just looked at the total, then to try and find the cause I added the user db's and the temp db.
As mentioned the tempdb accounts for approximately 95% of the total transaction/sec.
Thanks.
February 3, 2009 at 9:04 am
tempdb utilization is a function of the application, in your case application was developed in a way where it does heavy use of features that end up hitting tempdb.
Question for you... is this intense utilization of tempdb creating an I/O bottleneck?
In the affirmative case. A way to improve performance in regards to tempdb is to spread it in as many datafiles -located in different disks - as processors you got in the server. The rationale behind this strategy is that SQL Server would manage work load across all these datafiles then minimizing the chances of seeing I/O bottleneck.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply