List of indexes and Parallel queries

Today while being on forum found this information so though to blog for future reference. thanx Uri Dimant for sharing this. 

WITH cte 


SELECT object_name(ic.object_id) as object_name , index_name =,
‘column’ =,
        ‘column usage’ = CASE ic.is_included_column
                    WHEN 0 then ‘KEY’
                    ELSE ‘INCLUDED’
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; 




This entry was posted in Index, Performance Tuning, Personal, What I learned today and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.