I want to apply the Table valued function sys.dm_exec_sql_text()
for [sql_handle] in the below query.
SELECT sql_handle
FROM sys.dm_exec_query_stats A
To me the below 2 queries not working
Select *,sys.dm_exec_sql_text(A.sql_handle)
from sys.dm_exec_query_stats A
Error:
Msg 4121, Level 16, State 1, Line 1
Cannot find either column "sys" or the user-defined function or aggregate "sys.dm_exec_sql_text", or the name is ambiguous.
Select *
from sys.dm_exec_query_stats A,
sys.dm_exec_sql_text(A.sql_handle)
Error:
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "A.sql_handle" could not be bound.
“ - [Narrator] Dynamic Management Views, or DMVs, return information about the state of the SQL Server instance. This information is useful for diagnosing problems and tuning performance. Dynamic Management Views come in two scopes: server and database.
Analysis Services Dynamic Management Views (DMVs) are queries that return information about model objects, server operations, and server health. The query, based on SQL, is an interface to schema rowsets.
System Dynamic Management Views Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
DMV's are views, DMF's are functions. Dynamic Management Views and Functions were introduced in SQL 2005 and allow you to look at information that is stored in the Query Cache, query plans, CLR Data, Extended Events, Resource Governor info, etc.
you can CROSS APPLY :
...
from sys.dm_exec_query_stats A
CROSS APPLY sys.dm_exec_sql_text(A.sql_handle)
like the example from microsoft:
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With