0

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.

0

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.

enter image description here

2
  • Thank you @SqlWorldWide. Do you know if there's a way to enable this for all queries instead of one?
    – user420667
    Jun 22 '17 at 17:56
  • 1
    Updated my answer based on your comment. Jun 22 '17 at 20:44
0

Once you execute the query, process goes through a couple phases :

In the first phase it checks whether your code is correct

Second phase it binds the tables and columns you have provided. Gives you an error of non existing table/column if it fails

In the third phase query is passed to a query optimizer which (assuming you`re not using Query Store feature) checks if it has cached plan, and if statistics are up to date. Depending on result, it can either create a new plan, or use existing one. It can even create a trivial plan, if query is simple enough but that is not a concern right now.

Query optimizer decision making can hardly have any on your query execution because it has certain amount of time to create a plan no matter how complicated the query is, that is why it can create a non optimal plan for non-optimized queries and outdated statistics.

What you can do is use this query to get most expensive queries, and how many times certain query has executed. If number of executions_count of concerning query is not increasing it means it creates a new a plan for it(which mostly happens with ad-hoc queries). Also pay attention on last_logical_reads,elapsed_time,last_rows if it makes sense to have etc 100000 logical reads for result set of 10 rows, in that case you would need to optimize the query, same can be applied for last_elapsed time, if its too long, and you`re having low num of logical reads and rows, check if it contains some sort of scalar function, use UDF instead, and so on.

SELECT TOP 10
execution_count ,
total_worker_time / execution_count AS [Avg CPU Time] ,last_worker_time ,max_dop,last_logical_reads,last_elapsed_time,last_rows,
CASE WHEN deqs.statement_start_offset = 0
AND deqs.statement_end_offset = -1
THEN '-- see objectText column--'
ELSE '-- query --' + CHAR(13) + CHAR(10)
+ SUBSTRING(execText.text, deqs.statement_start_offset / 2,
( ( CASE WHEN deqs.statement_end_offset = -1
THEN DATALENGTH(execText.text)
ELSE deqs.statement_end_offset
END ) - deqs.statement_start_offset ) / 2)
END AS queryText
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
ORDER BY deqs.total_worker_time DESC ;  

In summary, try to avoid ad-hoc queries as much as possible, using stored procedures or indexed_views.

Hope it helps!

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.