January 11, 2012 at 11:59 pm
Hello,
I'm getting 100% disk highest active time for 10 second bursts with gaps of 10 seconds caused by sqlserver.exe and I don't know the steps to determine what is causing the problem. The db is for a .net web application and doesn't happen instantly on app start. If I restart the app it will reoccur after 5 minutes or so and continue indefinitely. Starting at 1,000,000 B/min read and 0 B/min write and when the problems begin moving up to 400,000,000 B/min read and 600,000,000 B/min write. The write is the most bizarre because almost no queries are being run that write to the db.
I've used server profiler and can't find any problem queries.
Is there somewhere I can a breakdown of the B/min read and writes for the db?
January 12, 2012 at 4:54 am
Just noticed it's the tempdb.mdf files with the high read/write not my database file. I'm not creating an temp object that should pose a problem that I know of. Do you know of a way to determine what tempdb is doing to cause the read/writes?
January 12, 2012 at 6:32 am
eli 35692 (1/12/2012)
Just noticed it's the tempdb.mdf files with the high read/write not my database file. I'm not creating an temp object that should pose a problem that I know of. Do you know of a way to determine what tempdb is doing to cause the read/writes?
Hmm,
I'm not sure other then running the profiler against it , or the performance logging from windows, but I'm interested in seeing if there is any other method out there. At any rate maybe something from this MSDN article could help?
The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:
Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.
I was also wondering if your tempdb has autogrowth set to on? Yet that wouldn't seem to make sense from what you've described...
January 12, 2012 at 2:48 pm
I added another mdf file of the same size to the tempdb on another disk and it seems to have fixed the problem. I don't know what the cause was however.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply