Simple query to find the blocking and blocked SQLs

This query relies on sys.dm_exec_requests, sys.dm_exec_sql_text and sys.processes DMV, function and catalog view. I out this together quickly so had to overlook the fact that sys.processes may be deprecated in the future. I will try to re-write this with the more current DMVs and functions

SELECT
  session_id
, blocking_session_id
, st.text AS blocked_sql
, st2.text AS blocking_sql
FROM sys.dm_exec_requests dmer
CROSS APPLY sys.dm_exec_sql_text (dmer.sql_handle) st
INNER JOIN sys.sysprocesses sp
ON dmer.blocking_session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text (sp.sql_handle) st2

Script to see all the processes that are waiting for a lock and their SQL statements

This script below will list out all the sessions that are waiting to acquire a lock. The 'WAIT' where clause parameter will filter out other locks such as those that are in 'GRANT' mode

SELECT
resource_type
, DB_NAME (resource_database_id) as database_name
, request_mode
, request_type
, request_status
, request_session_id
, request_owner_type
, dmet.text
FROM sys.dm_tran_locks dtl
INNER JOIN sys.dm_exec_connections dmec
ON dtl.request_session_id = dmec.session_id
CROSS APPLY sys.dm_exec_sql_text (dmec.most_recent_sql_handle) dmet
WHERE request_status = 'WAIT'

Simple script to change all databases to 'muti user'

DECLARE mode_change CURSOR
FOR

SELECT [name]
FROM sys.databases
WHERE user_access_desc = 'SINGLE_USER'

DECLARE @dbname VARCHAR (200)
DECLARE @sql VARCHAR (MAX)

OPEN mode_change
FETCH NEXT FROM mode_change INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

SET @sql = 'ALTER DATABASE ['+@dbname+'] SET MULTI_USER'

PRINT (@sql)

FETCH NEXT FROM mode_change INTO @dbname

END



CLOSE mode_change
DEALLOCATE mode_change

Examining the procedure cache for queries and execution plans

This query below gives the list of cached plans and their corresponding SQL statements. The usecount in this case is greater than one but it can be changed depending on the need. On clicking the hyper link in the query_plan column, SSMS will display the execution plan. The query skips system databases along with Distribution and RS databases

Note: The cache gets cleared when SQL Server is re-started or when DBCC FREEPROCCACHE is run. This will reset the procedure cahe


SELECT DB_NAME(dmqp.[dbid]) AS [database] , dmep.usecounts , dmep.size_in_bytes/1024 AS size_in_KB , dmep.cacheobjtype , dmep.objtype , dmst.text , dmqp.query_plan FROM sys.dm_exec_cached_plans dmep CROSS APPLY sys.dm_exec_query_plan(plan_handle) dmqp CROSS APPLY sys.dm_exec_sql_text (plan_handle) dmst WHERE dmqp.[dbid] > 4 AND DB_NAME(dmqp.dbid) NOT IN ('distribution', 'reportserver', 'reportservertempdb') AND dmep.usecounts > 1 ORDER BY dmep.objtype

SELECT DB_NAME(dmqp.[dbid]) AS [database]
, dmep.usecounts
, dmep.size_in_bytes/1024 AS size_in_KB
, dmep.cacheobjtype
, dmep.objtype
, dmst.text
, dmqp.query_plan
FROM sys.dm_exec_cached_plans dmep
CROSS APPLY sys.dm_exec_query_plan(plan_handle) dmqp
CROSS APPLY sys.dm_exec_sql_text (plan_handle) dmst
WHERE dmqp.[dbid] > 4
AND DB_NAME(dmqp.dbid) NOT IN ('distribution', 'reportserver', 'reportservertempdb')
AND dmep.usecounts > 1
ORDER BY dmep.objtype

Average Space Used in Pages

This script will give the average space used in percentage for indexes and heaps. The lesser the space used is, the more overheard in reading them from the disks. SQL Server will read pages that are partially empty and thus more round trips would be needed to the disk sub system

By re-building the indexes with a higher fill factor, the I/O system reads can be reduced. This is especially true for tables whose data gets added sequentially as fragmentation will not be much of a concern in such tables

SELECT DB_NAME(database_id) AS database_name
, so.[name] AS table_name
, dmv.index_id
, si.[name]
, dmv.index_type_desc
, dmv.alloc_unit_type_desc
, dmv.index_depth
, dmv.index_level
, avg_fragmentation_in_percent
, avg_page_space_used_in_percent
, page_count

FROM sys.dm_db_index_physical_stats (DB_ID(DB_NAME()), NULL, NULL, NULL, 'DETAILED') dmv
INNER JOIN sysobjects so
ON dmv.[object_id] = so.id
INNER JOIN sys.indexes si
ON
dmv.object_id = si.object_id
AND dmv.index_id = si.index_id
ORDER BY table_name, si.name

Look for the 'avg_page_space_used_in_percent' column in the above query's output

Simple SQL query to find the size of tables in a database

SELECT DISTINCT
st.name
, SUM (total_pages)*8.0/1024.0 AS [Size in MB]
FROM sys.partitions sp
INNER JOIN sys.tables st
ON sp.object_id = st.object_id
INNER JOIN sys.allocation_units sa
ON sp.partition_id = sa.container_id
GROUP BY st.name

Stored Procedure Execution Count and Average Time

The following query gives the number of times a stored procedure has been executed since it was last compiled. The time duration is in microseconds and the query below converts them to seconds. It is interesting to note that if you run a stored procedure WITH RECOMPILE, the execution count doesn't go up for that execution but the the previous count remains unchanged






SELECT OBJECT_NAME (object_id, database_id) AS stored_proc_name, s.execution_count, CONVERT (DECIMAL (20, 2), (total_elapsed_time)/1000000) total_elapsed_time_in_secs, CONVERT (DECIMAL (20, 2), (last_elapsed_time/1000000)) last_elapsed_time_in_secs, CONVERT (DECIMAL (20, 2), (min_elapsed_time/1000000)) min_elapsed_time_in_secs, CONVERT (DECIMAL (20, 2), (max_elapsed_time/1000000)) max_elapsed_time_in_secs, CONVERT (DECIMAL (20, 2), (((total_elapsed_time/execution_count)/1000000))) AS avg_elapsed_time_in_secs
FROM sys.dm_exec_procedure_stats s
CROSS APPLY sys.dm_exec_sql_text (s.sql_handle) s1
WHERE database_id = 7
AND [type] = 'P'

Reducing the VLF count for a SQL Server database

Reducing the number of VLFs in the log files of a database

Having many hundreds or thousands of VLFs in a database's log files increases the recovery time of it. It also is an indication that the log file has grown too frequently by a small amount. I will try to explain how to reduce the number of VLFs and set the growth pattern of the log file to a suitable size

Step 1: Find out how many VLFs are in the log files

Run DBCC LOGINFO under the concerned database's context

The row count of the output indicates the number of VLFs

Step 2: Backup the transaction log

Backup the transaction log to clear it of all the committed transactions. It best to make sure that this manual log backup joins the chain of automated log backups to keep it intact. It's a good idea to simply run the automated job for log backups provided there aren't too many other databases that are part of the job and it is ok to do so

Step 3: Shrink the log file

Shrink the log file using DBCC SHRINK FILE (logical file name, TargetSize)

The target size can be accessed by running DBCC SQLPERF ('logspace'). The log space used and log size should give a rough estimation of the how much space can be gained

If the DBCC SHRINKFILE does not shrink the log file enough, backup the log file again to increase the number of inactive VLFs and run DBCC SHRINKFILE again

Step 4

Re-size the log file and change its auto growth factor

Run the following with a suitable initial size, max size and growth factor

Preferably, set the auto growth factor to 1 GB and thus reducing the number of VLFs created when the file grows

Moving a table to a different file group (clustered index move)

Sometimes it becomes necessary to move tables to a different file group. This may be necessary if disk space runs out and a new one is added. In order to use the newly available space, a new file group needs to be created with ndf files assigned to it. Database objects can then be moved to this new file group residing on the newly available space. 

Non clustered indexes and clustered indexes can be moved to the new file group The process of moving a Non clustered index is fairly simple with the CREATE INDEX statement used in conjunction with the DROP EXISTING clause 

Clustered indexes however in many cases are created along with the primary key constraint. In such a case, the clustered index cannot be dropped and re-created without dropping the constraint first 

 Below is an example of a simple table created with a CLUSTERED PRIMARY KEY

CREATE TABLE Index_Drop_Test
( c1 INT NOT NULL
,c2 INT
CONSTRAINT PK_Primary PRIMARY KEY CLUSTERED
)

When we attempt to move this table to another file group by dropping and re-creating the clustered index, the following error is encountered

 SQL Statement to drop and re-create clustered index (not the right approach to use DROP INDEX)

DROP INDEX PK_Primary
ON dbo.Index_Drop_Test

Error Message

Msg 3723, Level 16, State 4, Line 1 An explicit DROP INDEX is not allowed on index 'dbo.Index_Drop_Test.PK_Primary'. It is being used for PRIMARY KEY constraint enforcement. 

Correct Approach

1) Drop the constraint

ALTER TABLE Index_Drop_Test
DROP CONSTRAINT PK_Primary

2) Re-create the constraint on the secondary file group


ALTER TABLE Index_Drop_Test
ADD CONSTRAINT PK_Primary PRIMARY KEY CLUSTERED (c1)
ON SECONDARY