SQL to check the queries waittype

SELECT  req.session_id

       ,blocking_session_id

       ,ses.host_name

       ,DB_NAME(req.database_id) AS DB_NAME

       ,ses.login_name

       ,req.status

       ,req.command

       ,req.start_time

       ,req.cpu_time

       ,req.total_elapsed_time / 1000.0 AS total_elapsed_time

       ,req.command

       ,req.wait_type

       ,sqltext.text

FROM    sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

JOIN    sys.dm_exec_sessions ses

        ON ses.session_id = req.session_id

WHERE req.wait_type IS NOT NULL

Find the sp cache

SELECT cp.objtype AS ObjectType,OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,cp.usecounts AS ExecutionCount,st.TEXT AS QueryText,qp.query_plan AS QueryPlanFROM sys.dm_exec_cached_plans AS cpCROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qpCROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st--WHERE OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName'

CX Packet Diagnosis

What is CX packet waittype.

When query uses the parallel threads .i.e available cores assign the threads and execute them. Under these scenarios coordinator wait for the query to be completed.

If another same query executes and uses the same parallel threads and the wait is known as CX packet wait-type.

E.g We can inspect the execution plan and how many rows are distributed in the threads . If it's not equal then one threads will complete quickly those has minimum pages of rows to read and another thread still needs to process due to high pages reads ....so the coordinator wait for another thread to be completed.

To find the wait-type

Declare @spid int

SELECT session_id ,status ,blocking_session_id
    ,wait_type ,wait_time ,wait_resource
    ,transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended' and session_id=@spid;
GO

To find the coordinator thread and it's associated parallel threads

SELECT
     task_state,  ---wait-type
    exec_context_id, ---coordinator
     parent_task_address, --parent task o\null means coordinator
    task_address ---associated thread work resource address
  FROM sys.dm_os_tasks
where session_id =@spid

--To detect further status of coordinator wait-type and it's threads

select wait_type,exec_context_id,blocking_exec_context_id,waiting_task_address,blocking_task_address
from sys.dm_os_waiting_tasks

-- To detect the query which coordinator scheduler_id is running or which core is executing ....

select scheduler_id from sys.dm_exec_requeusts.

This information is useful if more than one request processed on the same scheduler_id or core then the remaining request waiting for the coordinator to be completed or wait for the particular core to be available.

What is sleep task

To get number of rows written in each page for the particluar table

SELECT record_count/page_count as Avgrecords  FROM sys.dm_db_index_physical_stats
    (DB_ID(N'dbname'), OBJECT_ID(N'dbo.tablename'), NULL, NULL , 'DETAILED')
where index_level=0

--To get the details of CX packet as it sense how many pages waited in the sleep task . i.e if the query were used in intensive CPU and the same thread used for another request.

The quantum wait-type for each pages to be scheduled in the thread may be 4 ms can be found on sys.dm_os_sys_info  . if you have the total value of waiting_tasks_counts then divide by number of threads used in the particular query and multiply the rows result based on the above value produced by sys.dm_db_index_physical_stats.

SELECT
wait_type, waiting_tasks_count
, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats

Capture the running sql block for the particular spid

declare

    @spid int

,   @stmt_start int

,   @stmt_end int

,   @sql_handle binary(20)

set @spid = 63    -- Fill this in

select  top 1

    @sql_handle = sql_handle

,   @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end

,   @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end

from    master.dbo.sysprocesses

where   spid = @spid

order by ecid

SELECT

    SUBSTRING(  text,

            COALESCE(NULLIF(@stmt_start, 0), 1),

            CASE @stmt_end

                WHEN -1

                    THEN DATALENGTH(text)

                ELSE

                    (@stmt_end - @stmt_start)

                END

        )

FROM ::fn_get_sql(@sql_handle)

Unused index List Automatic report

Setup Unusedindex Job ALert

USE [DBA]

GO

/****** Object:  StoredProcedure [dbo].[P_unusedindexlist]    Script Date: 23/02/2016 13:19:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--P_unusedindexlist '10','KCRCSQL411I03\I03'

--select @@Servername

CREATE procedure [dbo].[P_unusedindexlist]

(

@dayscount int,

@datasource sysname

)

As begin

Declare @restrarttime datetime

Declare @days int

--Declare @dayscount int =10

SELECT @restrarttime= sqlserver_start_time FROM sys.dm_os_sys_info

SELECT @days=DATEDIFF(day, @restrarttime, getdate())

if @days>= @dayscount

Begin

-- Create the temp table to store the unused indexes

IF OBJECT_ID('TEMPDB..#UnusedIndexes') IS NOT NULL

DROP TABLE #UnusedIndexes

CREATE TABLE #UnusedIndexes

(

DBNAME VARCHAR(500)

,OBJECTNAME VARCHAR(500)

,INDEXNAME VARCHAR(500)

,USER_SEEKS bigint

,USER_SCANS bigint

, USER_LOOKUPS bigint

,USER_UPDATES bigint

)

-- Load the unused indexes into the temp table

EXEC sp_MSForEachDB 'USE [?];

INSERT INTO #UnusedIndexes

SELECT  DISTINCT

        db_NAME() AS DBNAME

        ,OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME]

        ,I.[NAME] AS [INDEX NAME]

        ,S.USER_SEEKS

        ,S.USER_SCANS

        ,S.USER_LOOKUPS

        ,S.USER_UPDATES

              FROM    sys.dm_db_index_usage_stats AS S

         INNER JOIN sys.indexes AS I

         ON I.[OBJECT_ID] = S.[OBJECT_ID]

         AND I.INDEX_ID = S.INDEX_ID

               inner join sys.objects o

               ON I.object_id = o.object_id

WHERE    OBJECTPROPERTY(S.[OBJECT_ID],''IsUserTable'') = 1

and I.index_id<>1 --exclude primary key

and S.user_seeks=0 -- no index seek performed by the user

and S.USER_SCANS=0

and S.USER_LOOKUPS=0

and S.USER_UPDATES>=0 --index maintained during updates

and I.name is not null

       AND o.is_ms_shipped = 0 -- exclude system objects

       AND o.type NOT IN(''F'', ''UQ'') -- exclude the foreign keys and unique contraints

'

DECLARE @bodyMsg nvarchar(max)

DECLARE @subject nvarchar(max)

DECLARE @tableHTML nvarchar(max)

Declare @srvname sysname

select @srvname=srvname from sys.sysservers where datasource=@datasource

SET @subject = 'Unused indexes'+' '+'executed in'+' '+@srvname

SET @tableHTML =

N''+   

N'

Unused Index List

' +

N'' +

N'

DBNAME

OBJECTNAME

INDEXNAME

USER_SEEKS

USER_SCANS

USER_LOOKUPS

USER_UPDATES

' +

CAST ( (

SELECT

td = CAST(DBNAME AS VARCHAR(max)),'',

td = CAST(OBJECTNAME AS VARCHAR(max)),'',

td = CAST(INDEXNAME AS VARCHAR(max)),'',

td = CAST(USER_SEEKS AS VARCHAR(100)),'',

td = CAST(USER_SCANS AS VARCHAR(100)),'',

td = CAST(USER_LOOKUPS AS VARCHAR(100)),'',

td = CAST(USER_UPDATES AS VARCHAR(100))

FROM #UnusedIndexes

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'

'

EXEC msdb.dbo.sp_send_dbmail 

@profile_name='xxxx',

@recipients='xxxx',

@subject = @subject,

@body = @tableHTML,

@body_format = 'HTML' ;

drop table #UnusedIndexes

End

Else

print 'Server was not restarted older than 1 month'

End

GO

Find out the disabled indexes 

SELECT i.name AS Index_Name, i.index_id, i.type_desc, s.name AS 'Schema_Name', o.name AS Table_Name

FROM sys.indexes i

JOIN sys.objects o on o.object_id = i.object_id

JOIN sys.schemas s on s.schema_id = o.schema_id

WHERE i.is_disabled = 1

ORDER BY

i.name

GO

Find out the unused indexes 

SELECT  DISTINCT

        db_NAME() AS DBNAME

        ,OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME]

        ,I.[NAME] AS [INDEX NAME]

        ,S.USER_SEEKS

        ,S.USER_SCANS

        ,S.USER_LOOKUPS

        ,S.USER_UPDATES

              FROM    sys.dm_db_index_usage_stats AS S

         INNER JOIN sys.indexes AS I

         ON I.[OBJECT_ID] = S.[OBJECT_ID]

         AND I.INDEX_ID = S.INDEX_ID

               inner join sys.objects o

               ON I.object_id = o.object_id

WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

and I.index_id<>1 --exclude primary key

and S.user_seeks=0 -- no index seek performed by the user

and S.USER_SCANS=0

and S.USER_LOOKUPS=0

and S.USER_UPDATES>=0 --index maintained during updates

and I.name is not null

       AND o.is_ms_shipped = 0 -- exclude system objects

       --AND o.type NOT IN('F', 'UQ') -- exclude the foreign keys and unique contraints

           and OBJECT_NAME(s.[OBJECT_ID]) in ('tblSt')

go

          SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],

       I.[NAME] AS [INDEX NAME],

       A.LEAF_INSERT_COUNT,

       A.LEAF_UPDATE_COUNT,

       A.LEAF_DELETE_COUNT

FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A

       INNER JOIN SYS.INDEXES AS I

         ON I.[OBJECT_ID] = A.[OBJECT_ID]

            AND I.INDEX_ID = A.INDEX_ID

WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

and OBJECT_NAME(A.[OBJECT_ID])='tblSto'

Blocking Alert -Setup

USE [DBA]

GO

/****** Object:  StoredProcedure [dbo].[BlockingAlert]    Script Date: 18/02/2016 13:09:57 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--select * from BlockingMonitoring

--USE [DBA]

--GO

--select @@SERVERNAME

--[BlockingAlert] 'KCRCSTVSQL410\I01'

ALTER procedure [dbo].[BlockingAlert]

(

@datasource sysname

)

As begin

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BlockingMonitoring]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[BlockingMonitoring](

       [WaitInSeconds] [bigint] NULL,

       [BlockingSessionId] [int] NULL,

       [BlockingUser] [nvarchar](128) NOT NULL,

       [BlockingSQL] [nvarchar](max) NULL,

       [WhyBlocked] [nvarchar](60) NULL,

       [BlockedSessionId] [smallint] NULL,

       [BlockedUser] [nvarchar](128) NULL,

       [BlockedSQL] [nvarchar](max) NULL,

       [DatabaseName] [nvarchar](128) NULL,

       [Programname] [nvarchar](32) NULL,

       [datereceived] date null

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

END

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BlockingAnalysis]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[BlockingAnalysis](

       [WaitInSeconds] [bigint] NULL,

       [BlockingSessionId] [int] NULL,

       [BlockingUser] [nvarchar](128) NOT NULL,

       [BlockingSQL] [nvarchar](max) NULL,

       [WhyBlocked] [nvarchar](60) NULL,

       [BlockedSessionId] [smallint] NULL,

       [BlockedUser] [nvarchar](128) NULL,

       [BlockedSQL] [nvarchar](max) NULL,

       [DatabaseName] [nvarchar](128) NULL,

       [Programname] [nvarchar](32) NULL,

       [datereceived] date null

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

END

SELECT

Waits.wait_duration_ms / 1000 AS WaitInSeconds,

Blocking.session_id as BlockingSessionId

, Sess.login_name AS BlockingUser

, BlockingSQL.text AS BlockingSQL

, Waits.wait_type WhyBlocked

, Blocked.session_id AS BlockedSessionId

, USER_NAME(Blocked.user_id) AS BlockedUser

, BlockedSQL.text AS BlockedSQL

, DB_NAME(Blocked.database_id) AS DatabaseName

,Sess.client_interface_name as Programname

into #blockingAnalysis

FROM sys.dm_exec_connections AS Blocking

INNER JOIN sys.dm_exec_requests AS Blocked

ON Blocking.session_id = Blocked.blocking_session_id

INNER JOIN sys.dm_os_waiting_tasks AS Waits

ON Blocked.session_id = Waits.session_id

RIGHT OUTER JOIN sys.dm_exec_sessions Sess

ON Blocking.session_id = sess.session_id

CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)

AS BlockingSQL

CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL

ORDER BY BlockingSessionId, BlockedSessionId

select * from #blockingAnalysis

-- is currently any blocking ...if it's no then proceed below step

if @@rowcount=0

Begin

select 'Spid' +' '+cast(B.BlockedSessionId as varchar)+' '+'released by'+' '+cast(B.BlockingSessionId as varchar) as BlockingStatus,B.*

into #blockingrelease

 from [BlockingMonitoring] as B

Left outer join #blockingAnalysis as BA

on BA.BlockingSessionId=B.BlockingSessionId and BA.BlockedSessionId=B.BlockedSessionId

where BA.BlockingSessionId is null and BA.BlockedSessionId is null and B.WaitInSeconds>=120

--is it latest blocking resolved and released ? if it's yes then proceed the below step

if @@rowcount>0

Begin

DECLARE @bodyMsg nvarchar(max)

DECLARE @subject nvarchar(max)

DECLARE @tableHTML nvarchar(max)

Declare @srvname sysname

select @srvname=srvname from sys.sysservers where datasource=@datasource

SET @subject = 'Blocking automatically resolved' +'  '+ 'in' + '  ' + @srvname

--set @bodyMsg='Please contact DBA team to resolve this Blocking issue'

SET @tableHTML =

N''+   

N'

Blocking Alert

' +

N'' +

N'

BlockingStatus

WaitInSeconds     

BlockingSessionId 

BlockingUser       

BlockingSQL              

WhyBlocked               

BlockedSessionId  

BlockedUser              

BlockedSQL               

DatabaseName       

Programname              

' +

CAST ( (

SELECT td = CAST(BlockingStatus AS VARCHAR(100)),'',

td = CAST(WaitInSeconds AS VARCHAR(100)),'',

td = CAST(BlockingSessionId AS VARCHAR(100)),'',

td = CAST(BlockingUser AS VARCHAR(100)),'',

td = CAST(BlockingSQL AS VARCHAR(100)),'',

td = CAST(WhyBlocked AS VARCHAR(100)),'',

td = CAST(BlockedSessionId AS VARCHAR(100)),'',

td = CAST(BlockedUser AS VARCHAR(100)),'',

td = CAST(BlockedSQL AS VARCHAR(100)),'',

td = CAST(DatabaseName AS VARCHAR(100)),'',

td = CAST(Programname AS VARCHAR(100))

FROM #blockingrelease

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'

'

+     

N'

Blocking Status - Resolved Automatically

'

EXEC msdb.dbo.sp_send_dbmail

@profile_name='db_profile name',

@recipients='xxxxxxx',

--@copy_recipients ='xxxxxx',

@subject = @subject1,

@body = @tableHTML1,

@body_format = 'HTML' ;

Delete from  [BlockingMonitoring] output DELETED.* INTO [BlockingAnalysis] where [WaitInSeconds]>=120

Drop table #blockingrelease

End

else

print 'validation check 2 : No Release Email Alert'

End

Else

print 'validation check 1 :No Release Email Alert'

drop table #blockingAnalysis

--for releasing the spid and Analysis purpose

INSERT INTO [dbo].[BlockingMonitoring]

           ([WaitInSeconds]

           ,[BlockingSessionId]

           ,[BlockingUser]

           ,[BlockingSQL]

           ,[WhyBlocked]

           ,[BlockedSessionId]

           ,[BlockedUser]

           ,[BlockedSQL]

           ,[DatabaseName]

           ,[Programname]

                 ,[datereceived])

SELECT

Waits.wait_duration_ms / 1000 AS WaitInSeconds,

Blocking.session_id as BlockingSessionId

, Sess.login_name AS BlockingUser

, BlockingSQL.text AS BlockingSQL

, Waits.wait_type WhyBlocked

, Blocked.session_id AS BlockedSessionId

, USER_NAME(Blocked.user_id) AS BlockedUser

, BlockedSQL.text AS BlockedSQL

, DB_NAME(Blocked.database_id) AS DatabaseName

,Sess.client_interface_name as Programname

,getdate() as datereceived

FROM sys.dm_exec_connections AS Blocking

INNER JOIN sys.dm_exec_requests AS Blocked

ON Blocking.session_id = Blocked.blocking_session_id

INNER JOIN sys.dm_os_waiting_tasks AS Waits

ON Blocked.session_id = Waits.session_id

RIGHT OUTER JOIN sys.dm_exec_sessions Sess

ON Blocking.session_id = sess.session_id

CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)

AS BlockingSQL

CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL

ORDER BY BlockingSessionId, BlockedSessionId

SELECT

Waits.wait_duration_ms / 1000 AS WaitInSeconds,

Blocking.session_id as BlockingSessionId

, Sess.login_name AS BlockingUser

, BlockingSQL.text AS BlockingSQL

, Waits.wait_type WhyBlocked

, Blocked.session_id AS BlockedSessionId

, USER_NAME(Blocked.user_id) AS BlockedUser

, BlockedSQL.text AS BlockedSQL

, DB_NAME(Blocked.database_id) AS DatabaseName

,Sess.client_interface_name as Programname

into #backupmonitoring

FROM sys.dm_exec_connections AS Blocking

INNER JOIN sys.dm_exec_requests AS Blocked

ON Blocking.session_id = Blocked.blocking_session_id

INNER JOIN sys.dm_os_waiting_tasks AS Waits

ON Blocked.session_id = Waits.session_id

RIGHT OUTER JOIN sys.dm_exec_sessions Sess

ON Blocking.session_id = sess.session_id

CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)

AS BlockingSQL

CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL

ORDER BY BlockingSessionId, BlockedSessionId

select WaitInSeconds from #backupmonitoring where WaitInSeconds>=120

if @@ROWCOUNT>0

begin

DECLARE @bodyMsg1 nvarchar(max)

DECLARE @subject1 nvarchar(max)

DECLARE @tableHTML1 nvarchar(max)

Declare @srvname1 sysname

select @srvname1=srvname from sys.sysservers where datasource=@datasource

SET @subject1 = 'Blocking Alert' +'  '+ 'in' + '  ' + @srvname1

SET @tableHTML1 =

N''+   

N'

Blocking Alert

' +

N'' +

N'

WaitInSeconds     

BlockingSessionId 

BlockingUser       

BlockingSQL              

WhyBlocked               

BlockedSessionId  

BlockedUser              

BlockedSQL               

DatabaseName       

Programname              

' +

CAST ( (

SELECT td = CAST(WaitInSeconds AS VARCHAR(100)),'',

td = CAST(BlockingSessionId AS VARCHAR(100)),'',

td = CAST(BlockingUser AS VARCHAR(100)),'',

td = CAST(BlockingSQL AS VARCHAR(100)),'',

td = CAST(WhyBlocked AS VARCHAR(100)),'',

td = CAST(BlockedSessionId AS VARCHAR(100)),'',

td = CAST(BlockedUser AS VARCHAR(100)),'',

td = CAST(BlockedSQL AS VARCHAR(100)),'',

td = CAST(DatabaseName AS VARCHAR(100)),'',

td = CAST(Programname AS VARCHAR(100))

FROM #backupmonitoring

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'

'

+     

N'

Please contact DBA team to resolve this Blocking issue

'

EXEC msdb.dbo.sp_send_dbmail

@profile_name='db_profile name',

@recipients='xxxxxxx',

--@copy_recipients ='xxxxxx',

@subject = @subject1,

@body = @tableHTML1,

@body_format = 'HTML' ;

End

else

print 'No Blocking Alert Email'

Drop table #backupmonitoring

End