'In SQL Server, is there a measure of query cost that is useable outside the context of the batch?

SQL Server will tell you the % cost of a query, relative to others in the batch, but this doesn't tell me much. Is there a measure of query cost that lets me compare all queries in an app, outside the context of the batch ? I could put the query of interest in a batch with a reference query, but this seems needlessly kludgy?

sql-server


Solution 1:[1]

I may be not understanding the question, but I often look at all queries run against a DB (or the 'app' in your example) using a query like this:

Select 
     st.[text] AS [Query Text],          
     wt.last_execution_time AS [Last Execution Time],
     wt.execution_count AS [Execution Count],
     wt.total_worker_time/1000000 AS [Total CPU Time(second)],
     wt.total_worker_time/wt.execution_count/1000 AS [Average CPU Time(milisecond)],
     qp.query_plan,
     DB_NAME(st.dbid) AS [Database Name]
from 
    (select top 50 
          qs.last_execution_time,
          qs.execution_count,
   qs.plan_handle, 
          qs.total_worker_time
    from sys.dm_exec_query_stats qs
    order by qs.total_worker_time desc) wt
cross apply sys.dm_exec_sql_text(plan_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
order by wt.total_worker_time desc

Am I misunderstanding your question? I didn't mean to post this as an 'Answer' but my query was too long for a comment. :-)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 DanielG

Related Questions