Transaction/Sec on tempdb

SQLServerCentral is supported by Red Gate Software Ltd.

Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Transaction/Sec on tempdb

Transaction/Sec on tempdb

Rate Topic

Display Mode

Topic Options

Author

Message

NicHopper

Posted Tuesday, February 03, 2009 2:05 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 2:29 AM
Points: 731, Visits: 797

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.

------------------------------------------------------------
Check out my blog

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #648635

MANU-J.

Posted Tuesday, February 03, 2009 7:43 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:53 PM
Points: 1,441, Visits: 7,341

Are you making selection out of the available databases listed on the right hand side when you click on Transactions/sec counter?

MJ

Post #648851

NicHopper

Posted Tuesday, February 03, 2009 7:45 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 2:29 AM
Points: 731, Visits: 797

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.

------------------------------------------------------------
Check out my blog

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #648854

PaulB-TheOneAndOnly

Posted Tuesday, February 03, 2009 9:04 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:57 PM
Points: 889, Visits: 1,380

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.

Post #648953

« Prev Topic | Next Topic »

Permissions