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.
Best Answer
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
andsys.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:You can do a similar thing with
sys.dm_exec_query_stats
if you have a lot of ad hoc queries. For example: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.