Get Top Queries By Average CPU Time SQL Server 2008 R2

Database performance plays significant effect in large user application. We need to examine and identify causes that makes system slow.

One step we can do is to identify query which takes high CPU time. Use transact SQL sys.dm_exec_sql_text in order to get top CPU expensive queries.

Taken from Microsoft site, https://msdn.microsoft.com/en-us/library/ms181929(v=sql.105).aspx, the code using sys.dm_exec_sql_text is below:

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

Above T-SQL gets Top five query by Average CPU Time. Open your SQL Server Management Studio, choose your database and run above query.

After getting those Top queries, you can open SQL Server Database Engine Tuning Advisor to analyze them and apply the Tuning Advisor recommendation to get better performance.
You can take a look at my post about How to use Database Engine Tuning Advisor for your reference.

Regards,
Agung Gugiaji

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s