Friday 24 May 2013

SQL Server : Find SPID which is the root of blocking chain

It is very common to deal with blocking scenario and there may be a blocking chain means one SPID is blocking second one and second one is blocking third one and so on so it's really difficult to track the root of blocking chain by simply running sp_who2 or from activity monitor if there are number of SPIDs which are involved so here I wish to share some T-SQL which will help you to find out the root SPID and all associated details with that SPID in order to enable one to further troubleshoot and make prompt decision:

1) To start , one can simply run the below T-SQL but it will only be helpful If there are few connections or only one SPID is blocking another otherwise it will be really difficult to check all SPID involved manually :

select blocked,* from sysprocesses where blocked<>0

2) In case there is a blocking chain means one SPID is blocking second one and second one is blocking third one so it's really difficult to track the root of blocking chain if there are number of SPID involved in it so here is the T-SQL which will help to find the root of this blocking chain. Output if this T-SQL will return SPID that is part of blocking chain but it's request_status is 'GRANT'. If the lock is already acquired then it will be in GRANT status otherwise it will be in WAIT status.

SELECT
distinct(l.request_SESSION_Id)

FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description

OR
(l.resource_description IS NULL
AND l1.resource_description IS NULL))
and (l.request_status)='GRANT' and l.request_SESSION_Id not in (SELECT
distinct(l.request_SESSION_Id)
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description

OR
(l.resource_description IS NULL
AND l1.resource_description IS NULL))
and (l.request_status)='WAIT')
order by (l.request_SESSION_Id)

3) T-SQL to get all the details like login , database name and underlying query which is associated with particular SPID :


SELECT
sp.spid, sp.[status], sp.loginame,
sp.hostname, sp.[program_name],
sp.blocked, sp.open_tran,
dbname=db_name(sp.[dbid]), sp.cmd,
sp.waittype, sp.waittime, sp.last_batch, st.[text]
FROM master.dbo.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text (sp.[sql_handle]) st
WHERE spid = ? -- Please specify the login which is output of first query


Hope this will help you..Thanks/


Brgds,



Chhavinath Mishra

Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)

Wednesday 22 May 2013

SQL Server : Get information about SQL Server instance like Version, Edition, Service pack , Collation and much more

In order to get information about SQL Server instance like Version, Edition, Service pack, Collation and much more , we have very useful System function SERVERPROPERTY. We can use the below mentioned T-SQL to get all these information:


SELECT 'ComputerName', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
UNION ALL
SELECT 'Edition', SERVERPROPERTY('Edition')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
UNION ALL
SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
UNION ALL
SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
UNION ALL
SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
UNION ALL
SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
UNION ALL
SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
UNION ALL
SELECT 'Complete_InstanceName', SERVERPROPERTY('ServerName')
UNION ALL
SELECT 'Collation', SERVERPROPERTY('Collation')



Brgds,



Chhavinath Mishra
Database Administrator
Microsoft Certified IT Professional (MCITP)