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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy