Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have SQL Server 2008 with multiple databases on it. CPU get's up to 100% on that server quite a lot. I would like to figure out which database might be causing it. What is the easies way to do that?

My "magic" idal tool would show me:

  1. CPU, RAM and IO for SQL Server
  2. Drill down and show the same for each DB
  3. Drill down and show what queries causing issues, etc..
share|improve this question
    
Been a while I cannot remember, is there a "Query Analyzer" tool that comes with that version? –  Tim Dec 9 '11 at 16:41
    
Yes, there is Query analyzer. But how do I use it? Is it going to give me breakdown of CPU per database? –  katit Dec 9 '11 at 16:43
    
Query Analyzer allows you to breaks down problem queries to see where they are spending their time...if you know the queries in question. SQL Server 2008 does have an Activity Monitor which will help you determine where the problem is when it is happening. –  Top__Hat Dec 9 '11 at 17:02

2 Answers 2

up vote 0 down vote accepted

I prefer to use information from system view sys.dm_exec_query_stats. Activity monitor is inconvenient for me.

To get information from sys.dm_exec_query_stats I use following script which I found here (unfortunatly it is in Russian). Works for SQL Server 2005+.

Using this script you can filter or group queries by the fields you want and, what is more important, get information about last execution time and object (SP, trigger) to which the query belong (Activity Monitor does not provide this information).

The only disadvantage of the script is that it returns aggregate performance statistics for cached query plans only. If a plan is removed from cache by any reason (shortage of memory, recompilation) you will not see statistics for that query.

set transaction isolation level read uncommitted
select 
    top 100
    creation_time,
    last_execution_time,
    execution_count,
    total_worker_time/1000 as CPU,
    convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
    qs.total_elapsed_time/1000 as TotDuration,
    convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
    total_logical_reads as [Reads],
    total_logical_writes as [Writes],
    total_logical_reads+total_logical_writes as [AggIO],
    convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0))as [AvgIO],
    case 
        when sql_handle IS NULL then ' '
        else(substring(st.text,(qs.statement_start_offset+2)/2,(
            case
                when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
                else qs.statement_end_offset    
            end - qs.statement_start_offset)/2  ))
    end as query_text,
    db_name(st.dbid)as database_name,
    object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as object_name
from sys.dm_exec_query_stats  qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_logical_reads > 0
order by AvgDur desc
share|improve this answer
    
Thank you! Fortunatelly, I have no problems reading russian :) –  katit Dec 11 '11 at 18:35

I think you will find your answer in one of these two posts: 1, and 2.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.