SQL Server Performance Tuning Queries

check_for_blocking.sql

--Using sys.dm_exec_requests to find a blocking chain.....
--Here's a query that dumps the output of dm_exec_requests into a temporary table, then uses the XML PATH to transform the blocked_by list into a comma-delimited list in the row 
--of the blocker.  The  sort order is the length of this comma-delimited list, putting the cause of a pileup right at the top.
--Doing this in a temp table was necessary because the blocking_session_id changes all the time.  So keep in mind that the output of this query is several ms behind "live".  But if you 
--run it a few times, if there is an excessive blocker, the offending spid should be hanging out at the top and easy to see.

create table #ExecRequests (
id int IDENTITY(1,1) PRIMARY KEY
,session_id smallint not null
,request_id int
,start_time datetime
,status nvarchar(60)
,command nvarchar(32)
,sql_handle varbinary(64)
,statement_start_offset int
,statement_end_offset int
,plan_handle varbinary (64)
,database_id smallint
,user_id int
,blocking_session_id smallint
,wait_type nvarchar (120)
,wait_time int
,cpu_time int
,tot_time int
,reads bigint
,writes bigint
,logical_reads bigint
,[host_name] nvarchar(256)
,[program_name] nvarchar(256)
,blocking_these varchar(1000) null)

insert into #ExecRequests 

(session_id,request_id,start_time,status,command,sql_handle,statement_start_offset,statement_end_offset,plan_handle,database_id,user_id,
blocking_session_id,wait_type,wait_time,cpu_time,tot_time,reads,writes,logical_reads,host_name, program_name)

select 
r.session_id,request_id,start_time,r.status,command,sql_handle,statement_start_offset,statement_end_offset,plan_handle,r.database_id,user_id,
blocking_session_id,wait_type,wait_time,r.cpu_time,r.total_elapsed_time,r.reads,r.writes,r.logical_reads,s.host_name,s.program_name
from sys.dm_exec_requests r
left outer join sys.dm_exec_sessions s on r.session_id = s.session_id
where 1=1
and r.session_id > 35 --retrieve only user spids
and r.session_id <> @@SPID --ignore myself

update #ExecRequests set blocking_these = (select isnull(convert(varchar(5), er.session_id),'') + ', '
from #ExecRequests er
where er.blocking_session_id = isnull(#ExecRequests.session_id ,0)
and er.blocking_session_id <> 0
FOR XML PATH('')
)

select
r.session_id, r.host_name, r.program_name, r.status
, r.blocking_these
, 'LEN(Blocking)' = LEN(r.blocking_these)
, blocked_by = r.blocking_session_id
, r.tot_time
, DBName = db_name(r.database_id), r.command, r.wait_type, r.tot_time, r.wait_time, r.cpu_time, r.reads, r.writes, r.logical_reads
, [text] = est.[text]
, offsettext = CASE WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN null
ELSE
SUBSTRING (est.[text], r.statement_start_offset/2 + 1,
CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text]))
ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1
END)
END
, r.statement_start_offset, r.statement_end_offset
from #ExecRequests r
outer apply sys.dm_exec_sql_text (r.sql_handle) est
order by LEN(r.blocking_these) desc, r.session_id asc
go
drop table #ExecRequests

check_memory_pressure.sql

-- use dbname
SELECT mg.session_id, db_name(r.database_id), mg.requested_memory_kb, mg.granted_memory_kb, mg.request_time, mg.grant_time
  , r.wait_time, r.wait_type, mg.queue_id, mg.wait_order
  FROM sys.dm_exec_query_memory_grants mg
  INNER JOIN sys.dm_exec_requests r
  ON mg.session_id=r.session_id
  ORDER BY mg.granted_memory_kb DESC
  , mg.queue_id
  , mg.wait_order;

  SELECT @@SERVERNAME AS [Server Name], [object_name], instance_name, cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);

execution_plan.sql

SET SHOWPLAN_ALL ON;
GO
-- INSERT YOUR QUERY HERE
GO
SET SHOWPLAN_ALL OFF;

sql_server_running_queries.sql

SELECT p.spid, db_name(p.dbid), p.program_name, p.hostname, p.loginame, mg.requested_memory_kb,
       mg.granted_memory_kb, p.cpu, p.status,
       right(convert(varchar, 
             dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 
             121), 12) as 'batch_duration'
FROM master.dbo.sysprocesses AS p
  LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg ON p.spid = mg.session_id
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 granted_memory_kb DESC

-- DISPLAY Overall SQL Server Memory Usage

SELECT (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
       (locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
       (total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
       process_physical_memory_low, process_virtual_memory_low
FROM sys. dm_os_process_memory