Session ID

SELECT  session_id, status, command, blocking_session_id, wait_time as wait_time_in_milli_seconds, SUBSTRING(detail.text,
                  requests.statement_start_offset / 2,
                 ABS ((requests.statement_end_offset - requests.statement_start_offset)) / 2) AS sql_text
, requests.wait_type
, requests.wait_resource
, requests.percent_complete AS percent_complete_for_maintenance_jobs
, cpu_time AS cpu_time_in_milliseconds
, total_elapsed_time AS total_elapsed_time_in_milliseconds
, logical_reads
, row_count
FROM    sys.dm_exec_requests requests
CROSS APPLY sys.dm_exec_sql_text (requests.sql_handle) detail

SQL Server Memory Grants

DMV Query to examine the cached query plans that are taking up memory space

The query below tells us which SQL is using what amount of memory currently. This space is used to for the duration of the query processing only and does not include the space occupied by compiled plans in the cache

SELECT
session_id
, granted_memory_kb
, syst.text AS sql_text
, sysp.query_plan
FROM sys.dm_exec_query_memory_grants sysm
CROSS APPLY sys.dm_exec_sql_text (sysm.sql_handle) syst
CROSS APPLY sys.dm_exec_query_plan (sysm.plan_handle) sysp
ORDER BY 1 DESC

DMV Query to see which queries are waiting for a memory grant

This query tells us which SQLs are waiting for memory space to be granted for processing their hash and sort data

SELECT
session_id
, dop as [degree_of_parallelism]
, request_time
, requested_memory_kb
, query_cost
, timeout_sec
, wait_order
, CASE is_next_candidate WHEN 1 THEN 'Next Candidate' ELSE 'Not the Next Candidate' END AS is_next_candidate
, wait_time_ms
, syst.text AS sql_text
FROM sys.dm_exec_query_memory_grants sysm
CROSS APPLY sys.dm_exec_sql_text (sysm.sql_handle) syst
WHERE wait_time_ms > 0
ORDER BY 1 DESC

Reference: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx?Redirected=true

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