You need to identify which long running transactions use an index.
Which dynamic management view should you use?
A.
sys.dm_exec_query_optimizer_info
B.
sys.dm_exec_connections
C.
sys.dm_exec_query_stats
D.
sys.dm_exec_sessions
Which dynamic management view should you use?
You need to identify which long running transactions use an index.
Which dynamic management view should you use?
Wouldnt the correcet answer be A? I dont see any index usage in the columns from sys.dm_exec_connections.
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
UPS, sorry, I mean C should be the correct answer. LOL
A is the right answer
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.
Answer is C
the sys.dm_Exec_query_stats with the sys_DM_Exec_SQL_Text
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;
C