BPSoftware.com

Blocking and locking is part of the normal operation of a database. The sys.SysProcesses System Compatibility View contains information about client and system processes that are running on an instance of SQL Server. The sys.SysProcesses System View can be used to retrieve a list of blocked and blocking processes. The following query can be used to see a list of blocked and blocking users. The query also displays the SQL command being executed which may assist in optimization resulting is less blocking. It is important to remember that blocking is normal and focus should be focused on long blocks/locks.

-- ##############################
-- Get a list of blocking users
-- ##############################
 
SELECT 
	spid
	,nt_username
	,hostname
	,program_name
	,blocked
	,waitresource
	,db_name(dbid) as Db
	,status
	,cmd
	,login_time 
	,datediff(ss, last_batch, getdate()) duration
	,(SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS sql_text
FROM 
	sys.sysprocesses
WHERE 
	(blocked > 0) 
	OR (waitresource != '')
UNION
SELECT 
	spid
	,nt_username
	,hostname
	,program_name
	,blocked
	,waitresource
	,db_name(dbid) as Db
	,status
	,cmd
	,login_time 
	,datediff(ss, last_batch, getdate()) duration
	,(SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS sql_text
FROM 
	sys.sysprocesses
WHERE 
	spid in (SELECT blocked FROM sys.sysprocesses WHERE blocked > 0) 
 
 -- #############################

SQL server Object Views allow for the retrieval of object information within a database. You can query a list of the columns in a table as well as retrieve a list of a table's indexes. Taking this information a bit further you can retrieve a list of the columns contained in an index.

This information is helpful for determining if it is possible to REBUILD a SQL index online (REBUILD WITH ONLINE = ON). Indexes cannot be rebuilt online (fails) if it is an XML index, Spatial index or if it contains Large object data type columns (image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml). I used this query as part of a an index maintenance plan to determine if it an index supported an online rebuild.

-- ##################################################
-- get a list of columns in an index
-- ##################################################    
SELECT
	ao.object_id 
	,ao.name AS [object_name] 
	,i.index_id 
	,i.name AS index_name
	,c.column_id
	,c.name AS column_name
	,c.max_length 
	,c.system_type_id
	,s.name
FROM 
	sys.all_objects ao LEFT JOIN sys.indexes i 
		ON (i.object_id = ao.object_id)
	LEFT JOIN sys.index_columns ic 
		ON (ao.object_id = ic.object_id) 
		AND (i.index_id = ic.index_id)
	LEFT JOIN sys.columns c 
		ON (ic.object_id = c.object_id) 
		AND (ic.column_id = c.column_id)
	LEFT JOIN sys.types s 
		ON (c.system_type_id = s.system_type_id)
WHERE 
	(ao.type = N'U')
 
-- ##################################################

Expanding on yesterday's post on SQL Server Catalog Views, the following example gets a list of columns in a table within SQL Server Database.

-- ##################################################
-- get a list of columns in a table
-- ##################################################    
SELECT 
	ao.object_id
	,ao.name
	,c.column_id
	,c.name
	,c.system_type_id
	,s.name AS type_name
FROM 
	-- sys.tables can be used to retrieve a list of tables of type 'U'
	sys.all_objects ao LEFT JOIN sys.columns c 
		ON (ao.object_id = c.object_id)
	LEFT JOIN sys.types s 
		ON (c.system_type_id = s.system_type_id)
WHERE 
	(ao.type = N'U')

-- ##################################################