February 1, 2022 at 5:50 pm
Hello,
So i am using this query below:
SELECT TOP(10) @@SERVERNAME SERVERNAME,qs.execution_count AS [Execution_Count],
(qs.total_logical_reads)*8/1024.0 AS [Total_Logical_Reads_MB],
(qs.total_logical_reads/qs.execution_count)*8/1024.0 AS [Avg_Logical_Reads_MB],
(qs.total_worker_time)/1000.0 AS [Total_Worker_Time_ms],
(qs.total_worker_time/qs.execution_count)/1000.0 AS [Avg_Worker_Time_ms],
(qs.total_elapsed_time)/1000.0 AS [Total_Elapsed_Time_ms],
(qs.total_elapsed_time/qs.execution_count)/1000.0 AS [Avg_Elapsed_Time_ms],
qs.creation_time AS [Creation_Time]
,coalesce('['+DB_NAME(t.dbid) + N'].['
+ OBJECT_SCHEMA_NAME(t.objectid, t.dbid) + N'].['
+ OBJECT_NAME(t.objectid, t.dbid)+']'
, '<Adhoc Batch>') as FQObjectName
, case when sql_handle IS NULL
then ''
else REPLACE(REPLACE((substring(t.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(varchar(MAX),t.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ) ),N'''',N''''''),'""','')
end AS [Complete_Query_Text]---, qp.query_plan AS [Query Plan]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE t.dbid = DB_ID()
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);-- frequently ran query
-- ORDER BY [Total Logical Reads (MB)] DESC OPTION (RECOMPILE);-- High Disk Reading query
-- ORDER BY [Avg Worker Time (ms)] DESC OPTION (RECOMPILE);-- High CPU query
-- ORDER BY [Avg Elapsed Time (ms)] DESC OPTION (RECOMPILE);-- Long Running query
from url: https://blog.sqlauthority.com/2021/03/17/sql-server-list-expensive-queries-updated-march-2021/
however one of the columns I want to get is the query plan, so I can look it up later and see if maybe the query plan changed or not... however when I try and insert it into the column QueryPlan which is XML, it comes blank in the table I try to save...
any ideas or maybe convert it to nvarchar?
February 1, 2022 at 7:19 pm
sorry, i found the issue, However now the problem comes up where it tries to insert but due to it as xml, within the XML there is some databases that have a dash "-" or hyphen in it... any idea around that?
thanks
February 1, 2022 at 7:37 pm
You can also get the query plan as text
SELECT TOP(10)
d.name AS DatabaseName,
dest.text AS SQL_Text,
deqs.last_execution_time,
deqs.creation_time,
deqs.last_elapsed_time,
deqs.last_logical_reads,
deqs.last_logical_writes,
deqs.last_physical_reads,
deqs.last_ideal_grant_kb,
deqs.last_rows,
deqs.last_worker_time AS last_cpu_time,
deqs.execution_count,
deqs.total_worker_time AS total_cpu_time,
deqs.max_worker_time AS max_cpu_time,
deqs.total_elapsed_time,
deqs.max_elapsed_time,
deqs.total_logical_reads,
deqs.max_logical_reads,
deqs.total_physical_reads,
deqs.max_physical_reads,
deqp.query_plan,
detqp.query_plan AS query_plan_text,
decp.cacheobjtype,
decp.objtype,
decp.size_in_bytes,
CURRENT_TIMESTAMP DateStamp
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) dest
CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle,deqs.statement_start_offset,deqs.statement_end_offset) detqp
OUTER APPLY sys.dm_exec_query_plan(deqs.plan_handle) deqp
INNER JOIN sys.databases d
ON dest.dbid = d.database_id
LEFT JOIN sys.dm_exec_cached_plans decp
ON decp.plan_handle = deqs.plan_handle
ORDER BY deqs.last_execution_time DESC
February 1, 2022 at 8:03 pm
YOU SIR! are a genius thank you 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply