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 

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: 

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/383c0712-9416-4e23-9f45-d74148202596 

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/e5a72c22-56c9-420a-bf2f-05b834665e41 

About these ads
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s