notes_sql.txt

--top 10 most expecive queries

SELECT TOP 10
 total_worker_time ,
 execution_count ,
 total_worker_time / execution_count AS [Avg CPU Time] ,
 deqs.total_physical_reads,
 deqs.total_logical_reads,
 deqs.total_logical_writes,
 deqs.total_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 , deqs.plan_handle
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 ;


SELECT * FROM sys.dm_exec_query_plan (0x06000500BAFB891110F924330500000001000000000000000000000000000000000000000000000000000000);