8 Comments on “Which dynamic management view should you use?”

1. merica says:

   July 9, 2015 at 5:17 am

   Wouldnt the correcet answer be A? I dont see any index usage in the columns from sys.dm_exec_connections.

   
   
   
   0

   
   
   
   0
2. HR_OS_B says:

   July 12, 2015 at 1:42 pm

   I agree, A should be the correct answer.
   If you cross apply sys.dm_exec_query_optimizer_info with sys.dm_exec_text_query_plan
   you can get execution plan for that query and see the index usage.
   Try this:

   select qs.*, qt.text as sql_command, CAST(qp.query_plan as XML) as query_plan
   from sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) as qt
   cross apply sys.dm_exec_text_query_plan (qs.[plan_handle],default,default) as qp

   
   
   
   0

   
   
   
   0
3. HR_OS_B says:

   July 12, 2015 at 1:44 pm

   UPS, sorry, I mean C should be the correct answer. LOL

   
   
   
   1

   
   
   
   0
4. Modi says:

   August 31, 2015 at 7:22 am

   A is the right answer

   
   
   
   0

   
   
   
   0
5. Dim says:

   October 10, 2015 at 3:51 am

   How you can recognize long running transactions with sys.dm_exec_query_optimizer_info?
   sys.dm_exec_query_optimizer_info is used for different tasks:
   sys.dm_exec_query_optimizer_info

   C should be the correct answer.

   
   
   
   1

   
   
   
   0
6. ryahan says:

   January 13, 2016 at 2:41 pm

   Answer is C
   the sys.dm_Exec_query_stats with the sys_DM_Exec_SQL_Text

   
   
   
   1

   
   
   
   0
7. Skippo says:

   April 14, 2016 at 11:49 pm

   TOP I/O Queries:

   SELECT TOP 100
   (total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
   (total_logical_reads + total_logical_writes) AS total_IO,
   qs.execution_count AS execution_count,
   SUBSTRING (qt.text,qs.statement_start_offset/2,
   (CASE WHEN qs.statement_end_offset = -1
   THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
   ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) AS indivudual_query, o.name AS object_name,
   DB_NAME(qt.dbid) AS database_name
   FROM sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
   LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
   where qt.dbid = DB_ID()
   ORDER BY average_IO DESC;

   
   
   
   1

   
   
   
   0
8. Vladimir says:

   August 17, 2016 at 4:58 am

   C

   
   
   
   1

   
   
   
   0