Today while being on forum found this information so though to blog for future reference. thanx Uri Dimant for sharing this.
WITH cte
AS
(
SELECT object_name(ic.object_id) as object_name , index_name = i.name,
‘column’ = c.name,
‘column usage’ = CASE ic.is_included_column
WHEN 0 then ‘KEY’
ELSE ‘INCLUDED’
END
FROM sys.index_columns ic JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
JOIN sys.indexes i
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
) SELECT * FROM cte WHERE object_name =’tablename’
——————————–
Below is the query which give information about parallel queries:
–This first thing to check if CPU is at 100% is to look for parallel queries:
– Tasks running in parallel (filtering out MARS requests below):
select * from sys.dm_os_tasks as t
where t.session_id in ( select t1.session_id from sys.dm_os_tasks as t1 group by t1.session_id having count(*) > 1
and min(t1.request_id) = max(t1.request_id));
– Requests running in parallel:
select * from sys.dm_exec_requests as r
join (select t1.session_id, min(t1.request_id)
from sys.dm_os_tasks as t1
group by t1.session_id
having count(*) > 1
and min(t1.request_id) = max(t1.request_id)
) as t(session_id, request_id)
on r.session_id = t.session_id
and r.request_id = t.request_id;
Thanx.
Vinay
Reference:
