SQL Server Performance Queries

AllLocksInDatabase.sql

SELECT 
     SessionID = s.Session_id,
     resource_type,   
     DatabaseName = DB_NAME(resource_database_id),
     request_mode,
     request_type,
     login_time,
     host_name,
     program_name,
     client_interface_name,
     login_name,
     nt_domain,
     nt_user_name,
     s.status,
     last_request_start_time,
     last_request_end_time,
     s.logical_reads,
     s.reads,
     request_status,
     request_owner_type,
     objectid,
     dbid,
     a.number,
     a.encrypted ,
     a.blocking_session_id,
     a.text       
 FROM   
     sys.dm_tran_locks l
     JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
     LEFT JOIN   
     (
         SELECT  *
         FROM    sys.dm_exec_requests r
         CROSS APPLY sys.dm_exec_sql_text(sql_handle)
     ) a ON s.session_id = a.session_id
 WHERE  
     s.session_id > 50
	 AND resource_database_id= DB_ID()

DatabaseCPUUsage.sql

WITH DB_CPU_Stats		
AS		
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], 		
  SUM(total_worker_time) AS [CPU_Time_Ms]		
 FROM sys.dm_exec_query_stats AS qs		
 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 		
              FROM sys.dm_exec_plan_attributes(qs.plan_handle)		
              WHERE attribute = N'dbid') AS F_DB		
 GROUP BY DatabaseID)		
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],		
       DatabaseName, [CPU_Time_Ms], 		
       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) 		
       OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]		
FROM DB_CPU_Stats		
WHERE DatabaseID > 4 -- system databases		
AND DatabaseID <> 32767 -- ResourceDB		
ORDER BY row_num OPTION (RECOMPILE);		

ListProcesses.sql

SELECT P.spid,
       RIGHT(CONVERT(VARCHAR, DATEADD(MS, DATEDIFF(MS, P.last_batch, GETDATE()), '1900-01-01'), 121), 12) AS Duration,
       P.program_name,
       P.hostname,
       P.loginame,
       P.status,
       DB_NAME(P.dbid)                                                                      AS DatabaseName,
       P.blocked,
       P.waittime,
       P.lastwaittype,
       P.waitresource,
       P.cpu,
       P.physical_io,
       P.memusage,
       T.text AS Sql
FROM dbo.sysprocesses P
CROSS APPLY sys.dm_exec_sql_text(P.sql_handle) T
WHERE P.spid > 50 AND
      P.status NOT IN ('background', 'sleeping') AND
      P.cmd NOT IN ('AWAITING COMMAND', 'MIRROR HANDLER', 'LAZY WRITER', 'CHECKPOINT SLEEP', 'RA MANAGER')
ORDER BY Duration DESC

ResourceLocksInDatabase.sql

SELECT 
  resource_type, 
  resource_associated_entity_id,
  request_status, 
  request_mode,
  request_session_id,
  CASE s.transaction_isolation_level 
    WHEN 0 THEN 'Unspecified' 
    WHEN 1 THEN 'ReadUncommitted' 
    WHEN 2 THEN 'ReadCommitted' 
    WHEN 3 THEN 'Repeatable' 
    WHEN 4 THEN 'Serializable' 
    WHEN 5 THEN 'Snapshot' 
  END AS TransactionIsolationLevel,
  resource_description, 
  o.object_id, 
  OBJECT_SCHEMA_NAME(o.object_id),
  o.name, 
  o.type_desc
FROM 
  sys.dm_tran_locks l, 
  sys.objects o,
  sys.dm_exec_sessions s
WHERE 
    l.resource_associated_entity_id = o.object_id AND
    --resource_database_id = DB_ID() AND
	s.session_id = request_session_id

SizeOfTables.sql

SELECT t.name                                                                                     AS TableName,
       s.name                                                                                     AS SchemaName,
       p.rows,
       SUM(a.total_pages) * 8                                                                     AS TotalSpaceKB,
       CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2))                     AS TotalSpaceMB,
       SUM(a.used_pages) * 8                                                                      AS UsedSpaceKB,
       CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2))                      AS UsedSpaceMB,
       (SUM(a.total_pages) - SUM(a.used_pages)) * 8                                               AS UnusedSpaceKB,
       CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.object_id > 255
GROUP BY t.name, s.name, p.[rows]
ORDER BY TotalSpaceMB DESC, t.name

Top100QueryInCPUCOst.sql

SELECT TOP 100	
 [Average CPU used] = total_worker_time / qs.execution_count,	
 [Total CPU used] = total_worker_time,	
	
 [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 	
         (CASE WHEN qs.statement_end_offset = -1 	
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 	
          ELSE qs.statement_end_offset END - 	
qs.statement_start_offset)/2)	
,[Parent Query] = qt.text	
,DatabaseName = DB_NAME(qt.dbid)	
FROM sys.dm_exec_query_stats qs	
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt	
ORDER BY [Average CPU used] DESC;	

Top20ExpensiveQueryPlans.sql

SELECT TOP 20		
    qs.sql_handle,		
    qs.execution_count,		
    qs.total_worker_time AS Total_CPU,		
    total_CPU_inSeconds = --Converted from microseconds		
        qs.total_worker_time/1000000,		
    average_CPU_inSeconds = --Converted from microseconds		
        (qs.total_worker_time/1000000) / qs.execution_count,		
    qs.total_elapsed_time,		
    total_elapsed_time_inSeconds = --Converted from microseconds		
        qs.total_elapsed_time/1000000,		
   st.text,		
   qp.query_plan		
from		
    sys.dm_exec_query_stats as qs		
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st		
    cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp		
ORDER BY qs.total_worker_time desc