1

We have a SQL Server with 15 databases on it. This is used by many users in each database simultaneously. Recently we started getting high CPU alerts from this server. When the alert comes, I can trace to the exact user and query.

We cannot turn on SQL Server Profiler on this server. I like to know if we can get information about historical expensive queries run in any of the databases (causing high CPU) using any script. for example - I need most expensive queries in last 4 hrs along with database name and username.

migrated from stackoverflow.com Oct 25 '13 at 14:04

This question came from our site for professional and enthusiast programmers.

7

No, SQL Server does not track individual occurrences of queries, for the same reason you don't want to run profiler: tracing constantly can have a substantial performance impact on the server, and the last thing Microsoft wants to do is to turn on high-cost diagnostics most people will rarely or never use. Profiler is the absolute last tool you want to use for this anyway, as it is the worst offender. A server-side trace is much lighter weight, but you should also look into extended events and perhaps even 3rd party monitoring tools - I work for a vendor of one such tool and would be happy to give you guidance there.

In the meantime, you can track expensive queries in aggregate using DMVs like sys.dm_exec_procedure_stats and sys.dm_exec_query_stats. Here's a procedure from Glenn Berry's Diagnostic Queries that gets the top 25 CPU-consuming stored procedures for a specific database:

SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], 
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

You can do a similar thing with sys.dm_exec_query_stats if you have a lot of ad hoc queries. For example:

SELECT TOP(25) SUBSTRING(t.text, (qs.statement_start_offset/2)+1, 
    ((CASE qs.statement_end_offset
      WHEN -1 THEN DATALENGTH(t.text)
     ELSE qs.statement_end_offset
     END - qs.statement_start_offset)/2) + 1) AS statement_text,
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Second,qs.creation_time,GETDATE()),0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
ORDER BY AvgWorkerTime DESC OPTION (RECOMPILE);

In fact a similar example can be found in the documentation.

Note that these DMVs do not persist data beyond a service restart and some other events, so you might not have the history you expect.

  • This is to get currently running queries, I like to check about historical queries about 4-5 hrs gao without using profiler. – Amit Arora Oct 25 '13 at 15:12
  • 2
    @Amit no, this does not get currently running queries. In fact I don't think it will even include current queries because it can't aggregate duration, CPU cost etc. for queries that are still running. Did you try it? If you only want results for queries that ran between 4 and 5 hours ago, then no, you can't do that unless you were collecting that information already. As my answer already states, SQL Server does not track this information by default. – Aaron Bertrand Oct 25 '13 at 15:14
  • 2
    @AmitArora just so you know, any solution to look at currently running queries would leverage the sys.dm_exec_sessions and sys.dm_exec_requests DMVs – swasheck Oct 25 '13 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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