December 16, 2008 at 1:21 pm
I recently had an issue that was caused by a maintenance plan that had been disabled. As a result indexing was not being rebuilt and it became out of date. So soon the queries became very cpu intensive.
My question is that on this box it is basically one application so I was able to focus on that database. However on a system that may be 100's of dbs how can you determine what database or indexes are causing a high cpu load (granted that it is the root cause and not something else). In that situation I would not have known what db to focus on.
Can anyone shed some light on how they determine this (2000 and or 2005)?
December 16, 2008 at 8:21 pm
Usually I capture procedure calls using trace events and then you know which procedures on which databases are running long or using more cpu.
With 2005 & 2008, you can also query the sys.dm_exec_query_stats to see the aggregate statistics of all the queries that are currently in cache. From there you can identify queries that are running slow or using too many resources.
You can capture wait stats to identify what types of waits are occuring and then drill down on where the problems lie.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply