Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

dynamic view management query

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.
like image 481
HHH Avatar asked Aug 20 '14 12:08

HHH


People also ask

What is dynamic management view in SQL?

“ - [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.

What is a DMV query?

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.

For what purpose do you use dynamic management views?

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.

What is the difference between DMV and DMF in SQL Server?

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.


1 Answers

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;
like image 76
JoseTeixeira Avatar answered Oct 30 '22 22:10

JoseTeixeira