Here is list of Performance, Tuning, Monitoring, Analysis and Configuration SQL that help SQL SERVER DBA.
-- HIGH CPU *******
-- Who is running what at this instant
SELECT st.text AS [Command
text], login_time, [host_name],
[program_name],
sys.dm_exec_requests.session_id, client_net_address,
sys.dm_exec_requests.status,
command, db_name(database_id) AS DatabaseName
FROM sys.dm_exec_requests
INNER JOIN
sys.dm_exec_connections
ON
sys.dm_exec_requests.session_id = sys.dm_exec_connections.session_id
INNER JOIN sys.dm_exec_sessions
ON
sys.dm_exec_sessions.session_id = sys.dm_exec_requests.session_id
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS st
WHERE
sys.dm_exec_requests.session_id >= 51
AND
sys.dm_exec_requests.session_id <> @@spid
ORDER BY sys.dm_exec_requests.status
--show cursor
select c.session_id, c.properties, c.reads, c.writes, c.creation_time,
is_open,
substring(qt.text,c.statement_start_offset/2,
(case when c.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else c.statement_end_offset end - c.statement_start_offset)/2)
as cursor_text
from sys.dm_exec_cursors (0) c
cross apply sys.dm_exec_sql_text(c.sql_handle) qt
where is_open=0
sp_configure
--kill 160
--This example returns information about cursors that have been open on
the server longer than the specified time of xx min.
SELECT creation_time, cursor_id, name, c.session_id, login_name
FROM sys.dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE DATEDIFF(mi, c.creation_time, GETDATE()) > 1;
GO
--space allocation information for the files associated with tempdb
SELECT
SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage
Select *
from sys.dm_os_performance_counters
WHERE OBJECT_NAME='MSSQL$INSTANCENAME:Buffer Manager'
DBCC memorystatus
SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files
--pages and memory
SELECT SUM(internal_object_reserved_page_count) AS [internal object
pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object
space in MB]
FROM sys.dm_db_file_space_usage;
-- top 10 count
SELECT top 10 (total_logical_reads/execution_count),
(total_logical_writes/execution_count),
(total_physical_reads/execution_count),
Execution_count,st.[text],
qp.query_plan, sql_handle,
plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY (total_logical_reads + total_logical_writes) Desc
-- text of query
SELECT text FROM sys.dm_exec_sql_text
(0x02000000A8CDFB26610FE0912D15DE9B57520961A0577BB9)
--showplan
SELECT *
FROM sys.dm_exec_query_plan
(0x06000700E4CA3D174043B0AA030000000000000000000000)
-- identify latches wait in tempdb
SELECT *
FROM sys.sysprocesses
WHERE lastwaittype like 'PAGE%LATCH_%' AND waitresource like '2:%'
SELECT session_id, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type like 'PAGE%LATCH_%' AND resource_description like '7:%'
sp_who2
DBCC PAGE (7,3,23918903)
--Once you know the page number, find the type of the page and the
object it belongs to.
--If you see the contention in PFS, GAM or SGAM pages, it implies
contention in allocation structures.
SELECT P.object_id, object_name(P.object_id) as object_name,
P.index_id, BD.page_type
FROM sys.dm_os_buffer_descriptors BD,
sys.allocation_units A,
sys.partitions P
WHERE BD.allocation_unit_id =
A.allocation_unit_id and
A.container_id =
P.partition_id
--You can use the following DMV query to find currently pending I/O
requests. You can execute this query periodically to check the health of I/O
subsystem
and to isolate physical disk(s) that are involved in the I/O
bottlenecks.
SELECT
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
FROM
sys.dm_io_virtual_file_stats(NULL, NULL)t1
INNER JOIN
sys.dm_io_pending_io_requests as t2
ON t1.file_handle =
t2.io_handle
--show datafile name and number
SELECT mf.physical_name,* FROM sys.dm_io_virtual_file_stats(NULL, NULL)
vfs, sys.master_files mf
where mf.database_id=vfs.database_id and mf.file_id=vfs.file_id and
vfs.database_id=7
--If you find, that you regularly have a high number of pending I/O
requests, you can use the following query to determine which SQL batches use
the most
I/O.
SELECT TOP 5
(total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_phys_reads,
Execution_count,
statement_start_offset as
stmt_start_offset,
st.[text],
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY
(total_logical_reads +
total_logical_writes) Desc
--If you are troubleshooting blocked I/O realtime, you can use
dm_tran_locks and dm_waiting_tasks to determine what sql is blocking and what
is waiting.
SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
s.creation_time AS LogCreatedOn
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO
SELECT
t1.resource_type,
'database'=db_name(resource_database_id),
'blk object' =
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
FROM
sys.dm_tran_locks as t1
INNER JOIN
sys.dm_os_waiting_tasks as t2
ON
t1.lock_owner_address =
t2.resource_address and
t1.request_session_id =
t1.request_session_id
--The last thing to check when analyzing I/O is the utlization of
tempDB. You can use
dm_db_file_space_usage to determine the amount of space that is being
used in tempdb by user_objects, internal_objects and the version_store.
SELECT
SUM
(user_object_reserved_page_count)*8 as user_objects_kb,
SUM
(internal_object_reserved_page_count)*8 as internal_objects_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM
(unallocated_extent_page_count)*8 as freespace_kb
FROM sys.dm_db_file_space_usage
WHERE database_id = 2
--fast forward cursor
--DBCC SHOWCONTIG (TableName)
--sp_spaceused TableName
--sp_help TableName
SET IO_STATISTICS ON
select *
from sys.dm_exec_cursors (232) c
cross apply sys.dm_exec_sql_text(c.sql_handle) qt
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS
[Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)]
FROM sys.dm_os_sys_info
-- Clear Wait Stats
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
-- Isolate top waits for server instance since last restart or
statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms /
SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY
wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN(
'SLEEP_TASK', 'BROKER_TASK_STOP',
'SQLTRACE_BUFFER_FLUSH',
'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
'LAZYWRITER_SLEEP')) -- filter
out additional irrelevant waits
SELECT W1.wait_type,
CAST(W1.wait_time_s AS
DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2))
AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12,
2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold
-- Signal Waits for instance
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS
NUMERIC(20,2)) AS [%signal (cpu) waits],
CAST(100.0 *
SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
AS [%resource waits]
FROM sys.dm_os_wait_stats;
-- Get CPU Utilization History (SQL 2005 Only)
DECLARE @ts_now bigint;
SELECT @ts_now = cpu_ticks / CONVERT(float, cpu_ticks_in_ms) FROM
sys.dm_os_sys_info;
SELECT TOP(30) SQLProcessUtilization AS [SQL Server Process CPU
Utilization],
SystemIdle AS
[System Idle Process],
100 - SystemIdle -
SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 *
(@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
SELECT
record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]',
'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int')
AS
[SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp],
CONVERT(xml, record) AS [record]
FROM
sys.dm_os_ring_buffers
WHERE ring_buffer_type
= N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE
'%<SystemHealth>%') AS x
) AS y
ORDER BY record_id DESC;
-- Signal Waits above 10-15% is usually a sign of CPU pressure
-- Page Life Expectancy (PLE) value for default instance
SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$INSTANCENAME:Buffer Manager' -- Modify this
if you have named instances
'SQLServer:Buffer Manager'
AND counter_name = 'Page life expectancy';
-- PLE is a good measurement of memory pressure
-- Higher PLE is better. Below 300 is generally bad.
-- Watch the trend, not the absolute value
dbcc showfilestats
-- Individual File Sizes and space available for current database
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128
AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS
[Available Space In MB]
FROM sys.database_files;
--DBCC SHRINKFILE(N'SYSlog',1)
-- Cached SP's By Execution Count (SQL 2005)
SELECT TOP(25) qt.[text] AS [SP Name], qs.execution_count AS [Execution
Count],
qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS
[Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.total_worker_time AS [TotalWorkerTime],
qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS [Age in Cache]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE qt.[dbid] = DB_ID() -- Filter by current database
ORDER BY qs.execution_count DESC;
-- Cached SP's By Worker Time (SQL 2005) Worker time relates to CPU cost
SELECT TOP(25) qt.[text] AS [SP Name], qs.total_worker_time AS
[TotalWorkerTime],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.execution_count AS [Execution Count],
ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()),
0) AS [Calls/Second],
ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS [AvgElapsedTime],
qs.max_logical_reads, qs.max_logical_writes,
DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE qt.[dbid] = DB_ID() -- Filter by current database
ORDER BY qs.total_worker_time DESC;
-- Cached SP's By Logical Reads (SQL 2005) Logical reads relate to
memory pressure
SELECT TOP(25) qt.[text] AS [SP Name], total_logical_reads,
qs.execution_count AS [Execution Count],
total_logical_reads/qs.execution_count AS [AvgLogicalReads],
qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS
[Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.total_worker_time AS [TotalWorkerTime],
qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
qs.total_logical_writes,
qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,
DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE qt.[dbid] = DB_ID() -- Filter by current database
ORDER BY total_logical_reads DESC;
-- Possible Bad Indexes (writes > reads)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index
Name], i.index_id,
user_updates AS [Total
Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks
+ user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;
-- Missing Indexes for entire instance by Index Advantage
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS
[index_advantage], migs.last_user_seek,
mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost,
migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC;
--CHECKPOINT 10
--DBCC DROPCLEANBUFFERS
--DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
-- Breaks down buffers used by current database by object (table, index)
in the buffer cache
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName], p.[object_id],
p.index_id, COUNT(*)/128 AS [buffer size(MB)], COUNT(*) AS [buffer_count]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100
GROUP BY p.[object_id], p.index_id
ORDER BY buffer_count DESC;
-- Tells you what tables and indexes are using the most memory in the
buffer cache
SELECT * FROM sys.dm_os_sys_info
select * from sys.dm_os_threads
-- Detect blocking (run multiple times)
SELECT t1.resource_type AS [lock type],DB_NAME(resource_database_id) AS
[database],
t1.resource_associated_entity_id AS [blk object],t1.request_mode AS
[lock req],
---
lock requested
t1.request_session_id AS [waiter sid], t2.wait_duration_ms AS [wait
time], -- spid of waiter
(SELECT [text] FROM sys.dm_exec_requests AS r -- get sql for
waiter
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle])
WHERE r.session_id = t1.request_session_id) AS [waiter_batch],
(SELECT SUBSTRING(qt.[text],r.statement_start_offset/2,
(CASE WHEN
r.statement_end_offset = -1
THEN
LEN(CONVERT(nvarchar(max), qt.[text])) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt
WHERE r.session_id = t1.request_session_id) AS [waiter_stmt], -- statement blocked
t2.blocking_session_id AS [blocker sid], -- spid of blocker
(SELECT [text] FROM sys.sysprocesses AS p -- get sql for blocker
CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle])
WHERE p.spid = t2.blocking_session_id) AS [blocker_stmt]
FROM sys.dm_tran_locks AS t1
INNER JOIN sys.dm_os_waiting_tasks AS t2
ON t1.lock_owner_address = t2.resource_address;
-- HIGH CPU *******
-- Get the most CPU intensive
queries
SET NOCOUNT ON;
DECLARE @spID smallint
DECLARE spID_Cursor CURSOR
FAST_FORWARD FOR
SELECT TOP 25 spid
FROM master..sysprocesses
WHERE status = 'runnable'
AND spid > 50 -- Eliminate system SPIDs
AND spid <> @@SPID
ORDER BY CPU DESC
OPEN spID_Cursor
FETCH NEXT FROM spID_Cursor
INTO @spID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Spid #:' +
STR(@spID)
EXEC ('DBCC
INPUTBUFFER (' + @spID + ')')
FETCH NEXT FROM
spID_Cursor
INTO @spID
END
-- Close and deallocate the
cursor
CLOSE spID_Cursor
DEALLOCATE spID_Cursor
-- HIGH CPU *******
-- Isolate top waits for
server instance
--DBCC SQLPERF(WAITSTATS, CLEAR)
WITH Waits AS
(
SELECT
wait_type,
wait_time_ms / 1000.
AS wait_time_s,
100. * wait_time_ms /
SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER()
OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE
'%SLEEP%'
)
SELECT
W1.wait_type,
CAST(W1.wait_time_s AS
DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12,
2)) AS pct,
CAST(SUM(W2.pct) AS
DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn,
W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct
< 90 -- percentage threshold
ORDER BY W1.rn;
--exec sp_configure 'default trace enabled',0
--reconfigure
select * from sys.dm_os_waiting_tasks
where wait_type = 'SQLTRACE_BUFFER_FLUSH'
--CHECKPOINT
--DBCC TRACEON(3502, -1) --turn on checkpoint logging
--DBCC TRACEOFF(3502, -1) --turn off checkpoint logging
--EXEC xp_readerrorlog --read the log
--sp_configure
-- HIGH CPU *******
-- Total waits are
wait_time_ms (high signal waits indicates CPU pressure)
SELECT '%signal (cpu) waits'
= CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)),
'%resource waits'=
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS
NUMERIC(20,2))
FROM sys.dm_os_wait_stats;
-- HIGH CPU *******
-- Check SQL Server
Schedulers to see if they are waiting on CPU (runnable tasks above 10 is very
bad)
SELECT scheduler_id,
current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
--Analyzing SQL SERVER 2005 LONG RUNNING QUERIES
select name,Page_verify_option_desc from sys.databases
SELECT r.session_id as sid,
s.HOST_NAME, r.status,
r.wait_time,wait_type,r.logical_reads as logical_r,
r.wait_resource,SUBSTRING(qt.text,(r.statement_start_offset/2) +1,
(CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),
qt.text)) * 2 ELSE r.statement_end_offset END -r.statement_start_offset)/2)
AS stmt_executing,r.blocking_session_id as
blk_sid,r.cpu_time,r.total_elapsed_time as tol_elp_tm,r.reads,r.writes,
r.plan_handle,s.host_process_id,s.PROGRAM_NAME FROM sys.dm_exec_requests
r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, sys.dm_exec_sessions
s
WHERE r.session_id > 50 and r.session_id=s.session_id and
r.session_id <> @@spid ORDER BY sid
desc --r.session_id, s.host_name, s.program_name, r.status
-- Get a snapshot of current activity
SELECT LTRIM (st.text) AS
'Command Text',[host_name], der.session_id AS 'SPID',
der.status,
db_name(database_id) AS DatabaseName, ISNULL(der.wait_type, 'None')AS 'Wait
Type', der.logical_reads
FROM sys.dm_exec_requests AS
der
INNER JOIN sys.dm_exec_connections
AS dexc
ON der.session_id =
dexc.session_id
INNER JOIN
sys.dm_exec_sessions AS dexs
ON dexs.session_id =
der.session_id
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS st
WHERE der.session_id >=
51
AND der.session_id <>
@@spid
ORDER BY der.logical_reads
desc
--kill 73
sp_who2 160
dbcc traceon (3604)
DBCC PAGE (7,4,1560834)
dbcc traceoff (3604)
SELECT OBJECT_NAME (1566172875)
DBCC EXTENTINFO(0, TABLENAME, -1)
-- the formatting seems to be working for me, and this is so useful!
/* The following query automates the process of translating the page in
a waitresource in sysprocesses (of the form dbid:fileid:page) into the object
name
containing that page. */
SET NOCOUNT ON declare @dbid int, @fileid int, @pageid int, @spid int,
@sql varchar(128)
--set your spid of interest here:
set @spid = 197
select @dbid = substring(waitresource, 1, charindex (':', waitresource)
- 1),
@fileid =
substring(waitresource, charindex( ':', waitresource) + 1,
charindex(':', waitresource,
charindex(':', waitresource) + 1) -
charindex(':',waitresource)
- 1 ),
@pageid =
substring(waitresource, charindex(':', waitresource,
charindex(':', waitresource,
charindex(':', waitresource) + 1)) + 1,
len(waitresource) -
(charindex(':', waitresource,
charindex(':', waitresource,
charindex(':', waitresource) + 1)) + 1) )
from master..sysprocesses where spid = @spid and waitresource like
'%:%:%'
set @sql = 'dbcc page (' + convert(varchar,@dbid) + ',' +
convert(varchar,@fileid) + ','
+ convert(varchar,@pageid) + ') with
no_infomsgs, tableresults'
if exists (select 1 from tempdb..sysobjects where xtype = 'U' and name
like
'#pageinfo%')
drop table #pageinfo
create table #pageinfo ( ParentObject varchar(128), Object varchar(128),
Field varchar(128), Value
varchar(128) )
dbcc traceon (3604) with no_infomsgs
insert into #pageinfo
(ParentObject, Object, Field, Value)
exec (@sql) select object_name(Value) as 'waitresource object name' from
#pageinfo where Field =
'm_objId' dbcc traceoff (3604) with no_infomsgs
-- BLOCKING ************
-- Detect blocking
SELECT
blocked_query.session_id AS blocked_session_id,
blocking_query.session_id AS
blocking_session_id,
sql_text.text AS
blocked_text, sql_btext.text AS blocking_text, waits.wait_type AS
blocking_resource
FROM sys.dm_exec_requests AS
blocked_query
INNER JOIN
sys.dm_exec_requests AS blocking_query
ON
blocked_query.blocking_session_id = blocking_query.session_id
CROSS APPLY
(SELECT * FROM
sys.dm_exec_sql_text(blocking_query.sql_handle)
) sql_btext
CROSS APPLY
(SELECT * FROM
sys.dm_exec_sql_text(blocked_query.sql_handle)
) sql_text
INNER JOIN
sys.dm_os_waiting_tasks AS waits
ON waits.session_id =
blocking_query.session_id
-- BLOCKING ************
-- Index Contention
SELECT dbid=database_id,
objectname=object_name(s.object_id),
indexname=i.name,
i.index_id, row_lock_count, row_lock_wait_count,
[block %]= CAST (100.0 *
row_lock_wait_count / (1 + row_lock_count) AS NUMERIC(15,2)),
row_lock_wait_in_ms,
[avg row lock waits in ms]=
CAST (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) AS NUMERIC(15,2))
FROM
sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) AS s
INNER JOIN sys.indexes AS i
ON i.object_id = s.object_id
WHERE
objectproperty(s.object_id,'IsUserTable') = 1
AND i.index_id = s.index_id
ORDER BY row_lock_wait_count
DESC
-- TRANSACTION LOG FULL
*****
-- Find the log reuse
description for the transaction log
SELECT name, database_id,
log_reuse_wait_desc
FROM sys.databases
-- TRANSACTION LOG FULL *****
-- IO ISSUES
*****************
-- Analyze DB IO, ranked by
IO Stall %
WITH DBIO AS
(
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1
THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS
io_stall
FROM
sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files
AS MF
ON IVFS.database_id
= MF.database_id
AND IVFS.file_id =
MF.file_id
GROUP BY
DB_NAME(IVFS.database_id), MF.type
)
SELECT db, file_type,
CAST(1. * io / (1024 *
1024) AS DECIMAL(12, 2)) AS io_mb,
CAST(io_stall / 1000. AS
DECIMAL(12, 2)) AS io_stall_s,
CAST(100. * io_stall /
SUM(io_stall) OVER()
AS DECIMAL(10, 2))
AS io_stall_pct,
ROW_NUMBER() OVER(ORDER BY
io_stall DESC) AS rn
FROM DBIO
ORDER BY io_stall DESC;
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'DBNAME'), 1);
--kill 105
-- Get Top 100 executed SP's ordered by execution count
SELECT TOP 100 qt.text AS 'SP Name',
qs.execution_count AS 'Execution Count',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS
'Calls/Second',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS
'TotalWorkerTime',
qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
qs.max_logical_reads,
qs.max_logical_writes, qs.total_physical_reads,
DATEDIFF(Minute,
qs.creation_time, GetDate()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats
AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() --
Filter by current database
ORDER BY qs.execution_count
DESC
-- HIGH CPU *************
-- Get Top 20 executed SP's
ordered by total worker time (CPU pressure)
SELECT TOP 20 qt.text AS 'SP
Name', qs.total_worker_time AS 'TotalWorkerTime',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.execution_count AS
'Execution Count',
ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()),
0) AS 'Calls/Second',
ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',
qs.max_logical_reads,
qs.max_logical_writes,
DATEDIFF(Minute, qs.creation_time,
GetDate()) AS 'Age in Cache'
FROM sys.dm_exec_query_stats
AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() --
Filter by current database
ORDER BY qs.total_worker_time
DESC
-- find plan for sql
select qs.execution_count,
t.text,
p.query_plan,
qs.creation_time,
qs.last_execution_time,
qs.last_elapsed_time,
qs.min_elapsed_time ,
qs.max_elapsed_time ,
qs.total_elapsed_time
/qs.execution_count as 'Avg Execution_time',
qs.plan_handle,
qs.sql_handle
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) t
cross apply sys.dm_exec_query_plan(qs.plan_handle) p
where t.text like '%TABLENAME%'
-- check duplicate plan exist
select t.text from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) t
cross apply sys.dm_exec_query_plan(qs.plan_handle) p
group by t.text
having COUNT(t.text) >1
--show cached plans
SELECT [cp].[refcounts]
, [cp].[usecounts]
, [cp].[objtype]
, [st].[dbid]
, [st].[objectid]
, [st].[text]
, [qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp ;
-- query demonstrates how the dynamic management view returns
fragmentation information:
SELECT database_id,object_id, index_id, index_type_desc, index_depth,
index_level, avg_fragmentation_in_percent, fragment_count,
avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats
(DB_ID(N'DBNAME'), OBJECT_ID(N'TableName'), NULL, NULL , NULL);
--UPDATE STATISTICS TableName
--For PeopleSoft applications, the value for
avg_fragmentation_in_percent should ideally be as close to zero as possible for
--maximum performance. However, values from 0 percent through 10 percent
may be acceptable.
select spid,physical_io fio
into #f
from master..sysprocesses where spid > 50
waitfor delay '00:00:02'
select s.spid,fio,physical_io,physical_io-fio used
from master..sysprocesses s join #f on s.spid = #f.spid
where s.spid > 50
order by physical_io - fio desc
drop table #f
select * from sys.dm_os_tasks
select * from sys.dm_os_schedulers
select * from sys.dm_os_waiting_tasks
select * from sys.dm_os_threads
select * from sys.dm_os_workers
select * from sys.dm_os_sys_info
select * from sys.dm_exec_connections where auth_scheme='NTLM'
select * from sys.tcp_endpoints
SELECT * FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type =
'RING_BUFFER_MEMORY_BROKER';
SELECT type, /*sum(multi_pages_kb) ,*/ *
FROM sys.dm_os_memory_clerks
WHERE multi_pages_kb != 0
SELECT type, sum(multi_pages_kb)
FROM sys.dm_os_memory_clerks
WHERE multi_pages_kb != 0
GROUP BY type;
SELECT type, sum(multi_pages_kb)
FROM sys.dm_os_memory_cache_counters
WHERE multi_pages_kb != 0
GROUP BY type;
