Sql-server – How to find out what the SqlServer 2005 CPU is mostly working on

performancesql-server-2005

I apologize if this is somewhat of a rookie question.

After looking online I found some articles that explain how to find the worker time per query using sys.dm_exec_query_stats. e.g.

SELECT TOP(10)
creation_time
, last_execution_time
, (total_worker_time+0.0)/1000 AS total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime] , execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_worker_time > 0
ORDER BY total_worker_time DESC

from here.

But according to Microsoft the total_work_time only tells us the amount of time spent after the plan has been generated, not for instance, the amount of time spent building the plan for that query.

Is there a way to determine how much CPU is being spent in building the plans, and better yet, a simple breakdown of percent CPU by task (e.g. building plans, executing queries, indexing).

Thank you in advance.

Best Answer

Ref: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-time-transact-sql

You can use SET STATISTICS TIME ON.

You can get a breakdown of:

SQL Server parse and compile time
SQL Server Execution Times

You can turn this on in Management studio for all queries or per session.

Related Solutions

Sql-server – How to find the VLF count for a SQL 2005 database without running dbcc loginfo

I don't believe there is another way; you could always do something like this:

   CREATE TABLE #dbcc_out 
                      (FileID varchar(3), 
                       FileSize numeric(20,0),
                       StartOffset bigint, 
                       FSeqNo bigint, 
                       Status char(1),
                       Parity varchar(4), 
                       CreateLSN numeric(25,0))

    INSERT INTO #dbcc_out                  EXEC ('DBCC LOGINFO')

    select count(*)  from #dbcc_out

Sql-server – How to find out which statistics are evaluated by the optimizer

I don't know of any exposed or documented way that SQL Server keeps track of this as a separate statistic (no pun intended). But if you monitor your server for expensive queries (long duration, high CPU, high I/O etc) and collect the plans for those queries, you may be able to derive some of that information from the plans you've collected - but keep in mind that not all statistics will be represented (some details on that here). If you know of queries off-hand that are slow and you suspect the problem may be due to specific statistics or missing statistics, you can generate an actual execution plan for that query and inspect the plan - you'll often find evidence such as a missing statistics warning or a huge discrepancy between estimated and actual rows (and the latter is why generating an estimated plan only is kind of worthless).

SentryOne Performance Advisor product is one way to help with the collection of high-impact queries ("Top SQL") and the execution plans associated with them at runtime, since they are collected and saved for you over time. Also Plan Explorer is a free tool that can generate plans or open existing plans you've collected, often highlighting some of these issues much easier than viewing the graphical showplan in Management Studio or parsing the XML yourself.*

Best Answer

Related Solutions

Sql-server – How to find the VLF count for a SQL 2005 database without running dbcc loginfo

Sql-server – How to find out which statistics are evaluated by the optimizer

Related Question