|
Select '1. CPU Bottlenecks'
--Top queries that useed up most of the CPUs
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
Select '1.1 Excessive Query Compilation and Optimization'
Select 'Identify ad hoc queries and candidate for parameterizing?'
select q.query_hash,
q.number_of_entries,
t.text as sample_query,
p.query_plan as sample_plan
from (select top 20 query_hash,
count(*) as number_of_entries,
min(sql_handle) as sample_sql_handle,
min(plan_handle) as sample_plan_handle
from sys.dm_exec_query_stats
group by query_hash
having count(*) > 1
order by count(*) desc) as q
cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as t
cross apply sys.dm_exec_query_plan(q.sample_plan_handle) as p
select 'This query returns a count of the number of distinct query plans for a given query_hash value'
select q.query_hash,
q.number_of_entries,
q.distinct_plans,
t.text as sample_query,
p.query_plan as sample_plan
from (select top 20 query_hash,
count(*) as number_of_entries,
count(distinct query_plan_hash) as distinct_plans,
min(sql_handle) as sample_sql_handle,
min(plan_handle) as sample_plan_handle
from sys.dm_exec_query_stats
group by query_hash
having count(*) > 1
order by count(*) desc) as q
cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as t
cross apply sys.dm_exec_query_plan(q.sample_plan_handle) as p
go
--ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED
select '1.2 Unnecessary Recompilation'
--Take two snapshots of this DMV to see how much time is spent optimizing in the given time period.
select * from sys.dm_exec_query_optimizer_info
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc
select '1.3 Inefficient Query Plan'
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 20
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
Select *
from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where dbid = db_id('SFSLearner')
Select *
from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where dbid = db_id()
Select 'Consider plan freeze'
--DECLARE @plan_handle varbinary(64);
---- Extract the query's plan_handle.
--SELECT @plan_handle = plan_handle FROM sys.dm_exec_query_stats AS qs
--CROSS APPLY sys.dm_exec_sql_text(sql_handle)
--WHERE text LIKE N'Some query matching criteria%';
--EXECUTE sp_create_plan_guide_from_handle
-- @name = N'Sample_PG1',
-- @plan_handle = @plan_handle,
-- @statement_start_offset = NULL;
--GO
select '1.4 Intraquery Parallelism'
Select 'Determine whether any active requests are running in parallel for a given session'
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0
Select 'Find query plans that can run in parallel'
select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0
Select 'Find parallel queries by searching all queries that use more CPU time than the elapsed duration.'
select
qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.text
from
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where
qs.total_worker_time > qs.total_elapsed_time
select '1.5 Poor Cursor Usage'
Select 'Search for API cursor'
select
cur.*
from
sys.dm_exec_connections con
cross apply sys.dm_exec_cursors(con.session_id) as cur
where
cur.fetch_buffer_size = 1 --API cursor (Transact-SQL cursors always have a fetch buffer of 1)
and cur.properties LIKE 'API%'
Select '2. Memory Bottlenecks'
Select '2.1 External Physical Memory Pressure'
Select 'Find out how much memory SQL Server has allocated through the AWE mechanism.'
select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks
select
*
From sys.dm_os_process_memory
Select 'Detect external memory pressure throu sys.dm_os_sys_memory'
select 'Look for System_Memory_State_Desc = Available physical memory is low'
select
total_physical_memory_kb / 1024 as phys_mem_mb,
available_physical_memory_kb / 1024 as avail_phys_mem_mb,
system_cache_kb /1024 as sys_cache_mb,
(kernel_paged_pool_kb + kernel_nonpaged_pool_kb) / 1024
as kernel_pool_mb,
total_page_file_kb / 1024 as total_page_file_mb,
available_page_file_kb / 1024 as available_page_file_mb,
system_memory_state_desc
from sys.dm_os_sys_memory
Select '2.2 External Virtual Memory Pressure'
Select '2.3 Internal Physical Memory Pressure'
Select 'Check the amount of memory that is consumed through the multipage allocator.'
select sum(multi_pages_kb) from sys.dm_os_memory_clerks
Select 'If you are seeing large amounts of memory allocated through the multipage allocator, check the server configuration and try to identify the components that consume most of the memory by using the previous SELECT statement.'
select
type, sum(multi_pages_kb) as [KB]
from
sys.dm_os_memory_clerks
where
multi_pages_kb != 0
group by type
order by 2 desc
Select 'Amount of memory consumed by components outside the bBuffer pool'
-- note that we exclude single_pages_kb as they come from BPool
-- BPool is accounted for by the next query
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb) as [Overall used w/o BPool, Kb]
from
sys.dm_os_memory_clerks
where
type <> 'MEMORYCLERK_SQLBUFFERPOOL'
Select 'Amount of memory consumed by BPool'
-- note that currenlty only BPool uses AWE
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb) as [Used by BPool with AWE, Kb]
from
sys.dm_os_memory_clerks
where
type = 'MEMORYCLERK_SQLBUFFERPOOL'
Select 'Detailed information for each component can be obtained as follows. (This includes memory allocated from both within and outside of the buffer pool.)'
Begin
declare @total_alloc bigint
declare @tab table (
type nvarchar(128) collate database_default
,allocated bigint
,virtual_res bigint
,virtual_com bigint
,awe bigint
,shared_res bigint
,shared_com bigint
,topFive nvarchar(128)
,grand_total bigint
);
-- note that this total excludes buffer pool committed memory as because it represents the largest consumer, which is normal
select
@total_alloc =
sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)
from
sys.dm_os_memory_clerks
print
'Total allocated (including from bBuffer pPool): '
+ CAST(@total_alloc as varchar(10)) + ' Kb'
insert into @tab
select
type
,sum(single_pages_kb + multi_pages_kb) as allocated
,sum(virtual_memory_reserved_kb) as vertual_res
,sum(virtual_memory_committed_kb) as virtual_com
,sum(awe_allocated_kb) as awe
,sum(shared_memory_reserved_kb) as shared_res
,sum(shared_memory_committed_kb) as shared_com
,case when (
(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb))/(@total_alloc + 0.0)) >= 0.05
then type
else 'Other'
end as topFive
,(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)) as grand_total
from
sys.dm_os_memory_clerks
group by type
order by (sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <>
'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) +
shared_memory_committed_kb)) desc
select * from @tab
End
Select 'Determine the top ten consumers of the buffer pool pages (via a single-page allocator)'
select
top 10 type,
sum(single_pages_kb) as [SPA Mem, Kb]
from
sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb) desc
Select '2.4 Caches and Memory Pressure'
Select 'The internal clock hand controls the size of a cache relative to other caches. It starts moving when the framework predicts that the cache is about to reach its cap. '
Select 'The external clock hand starts to move when SQL Server as a whole gets into memory pressure. Movement of the external clock hand can be due external as well as internal memory pressure.'
select *
from
sys.dm_os_memory_cache_clock_hands
where
rounds_count > 0
and removed_all_rounds_count > 0
Select 'Additional information about the caches'
select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on (cc.cache_address = ch.cache_address)
/*
--uncomment this block to have the information only for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc
Select '2.5 Ring Buffers'
Select 'Each ring buffer keeps a record of the last number of notifications of a certain kind.'
select
ring_buffer_type
, count(*) as [Event count]
from sys.dm_os_ring_buffers
group by ring_buffer_type
order by ring_buffer_type
Select 'See overall state of the server as far as there are SystemHealth records present in this ring buffer.'
Begin
-- to correlate events, convert timestamps into time
-- note that the RDTSC counter IS affected by variable clock speeds of the CPU
declare @ts_now bigint
select @ts_now = ms_ticks from sys.dm_os_sys_info
-- "decompose" the records
select record_id,
dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime
,SQLProcessUtilization
,SystemIdle
,100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
,UserModeTime
,KernelModeTime
,PageFaults
,WorkingSetDelta
,MemoryUtilPct
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,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/UserModeTime)[1]', 'int') as UserModeTime,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/KernelModeTime)[1]', 'int') as KernelModeTime,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/PageFaults)[1]', 'int') as PageFaults,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/WorkingSetDelta)[1]', 'int') as WorkingSetDelta,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization)[1]', 'int') as MemoryUtilPct,
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
End
Select '2.5 Internal Virtual Memory Pressure'
Select 'Virtual address space summary view'
-- generates a list of SQL Server regions
-- showing number of reserved and free regions of a given size
/*
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 0 ELSE 1
END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- combine all allocation according with allocation base, don't take into account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes)) AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- we shouldn't be grouping allocations with zero allocation base
--- just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes),
region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size
Select 'Available memory in all free regions'
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB]
FROM VASummary
WHERE Free <> 0
Select 'Get size of largest availble region. If the largest available region is smaller than 4 MB, your system is likely to be experiencing VAS pressure'
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
Drop VIEW VASummary;
*/
Select '3. I/O Bottlenecks'
Select '3.1 Latch waits'
Select wait_type,
waiting_tasks_count,
wait_time_ms,
signal_wait_time_ms --The time between I/O completions until the time the worker is actually scheduled is accounted under the signal_wait_time_ms column.
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%'
order by wait_type
Select 'Find currently pending I/O requests.'
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL) VFS,
sys.dm_io_pending_io_requests as PIOR
where VFS.file_handle = PIOR.io_handle
select '3. 2 Examine the queries that generate the most I/Os'
SELECT TOP 10
(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,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
(SELECT query_plan from sys.dm_exec_query_plan(plan_handle)) as query_plan
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes)/execution_count DESC
Select 'Consider Data Compression to reduce I/O'
Select '4. TempDB Bottlenecks'
Select '4.1 User Object'
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 = DB_ID('tempdb')
Select 'Troubleshooting TempDB Space Issues'
/*Begin
DECLARE userobj_cursor CURSOR FOR
select
S.name + '.' + O.name
from sys.objects O, sys.schemas S
where object_id > 100
and type_desc = 'USER_TABLE'
and O.schema_id = S.schema_id
open userobj_cursor
declare @name varchar(256)
fetch userobj_cursor into @name
while (@@FETCH_STATUS = 0)
begin
exec sp_spaceused @objname = @name
fetch userobj_cursor into @name
end
close userobj_cursor
End */
Select '4.2 Version Store'
Select 'Identify longest running transactions that depend on the versions in the version store'
select top 2
transaction_id,
transaction_sequence_num,
elapsed_time_seconds
from sys.dm_tran_active_snapshot_database_transactions
order by elapsed_time_seconds DESC
Select '4.3 Internal Objects'
select
session_id,
internal_objects_alloc_page_count,
internal_objects_dealloc_page_count
from sys.dm_db_session_space_usage
order by internal_objects_alloc_page_count DESC
Select 'Top user sessions that are allocating internal objects, including currently active tasks.'
SELECT
t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as
deallocated
from sys.dm_db_session_space_usage as t1,
(select session_id,
sum(internal_objects_alloc_page_count)
as task_alloc,
sum (internal_objects_dealloc_page_count) as
task_dealloc
from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id and t1.session_id >50
order by allocated DESC
Select 'Find out which Transact-SQL statement it is and its query plan for a more detailed analysis.'
select
t1.session_id,
t1.request_id,
t1.task_alloc,
t1.task_dealloc,
t2.sql_handle,
t2.statement_start_offset,
t2.statement_end_offset,
t2.plan_handle
from (Select session_id,
request_id,
sum(internal_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count) as task_dealloc
from sys.dm_db_task_space_usage
group by session_id, request_id) as t1,
sys.dm_exec_requests as t2
where t1.session_id = t2.session_id and
(t1.request_id = t2.request_id)
order by t1.task_alloc DESC
Select 'Excessive DDL and Allocation Operations'
--Begin
-- -- get the current timestamp
-- declare @now datetime
-- select @now = getdate()
-- -- insert data into a table for later analysis
-- insert into analysis..waiting_tasks
-- select
-- session_id,
-- wait_duration_ms,
-- resource_description,
-- @now
-- from sys.dm_os_waiting_tasks
-- where wait_type like 'PAGE%LATCH_%' and
-- resource_description like '2:%'
--End
Select '5. Slow-Running Queries'
Select '5.1 Blocking'
select
request_session_id as spid,
resource_type as rt,
resource_database_id as rdb,
(case resource_type
WHEN 'OBJECT' then object_name(resource_associated_entity_id)
WHEN 'DATABASE' then ' '
ELSE (select object_name(object_id)
from sys.partitions
where hobt_id=resource_associated_entity_id)
END) as objname,
resource_description as rd,
request_mode as rm,
request_status as rs
from sys.dm_tran_locks
Select '5.1.1 Locking Granularity and Lock Escalation'
Select '5.1.2 Identifying Long Blocks'
Select 'Find transaction at head of a blocking chain, their input buffers and the type of blocking locks they hold'
Begin
declare @blocker_spid smallint
declare @i_buff_string char(30)
set nocount on
if exists (select * from tempdb.dbo.sysobjects where name like '%#blk%')
drop table #blk
select spid, blocked, hostname=substring (hostname, 1, 10),progname=substring(program_name, 1, 10), cmd=substring(cmd, 1, 10), status, physical_io, waittype
into #blk
from master..sysprocesses (nolock)
where blocked != 0
delete from #blk
where blocked in (select spid from #blk)
select 'Blocking spid' = spid, --loginame=substring(suser_name(suid),1,10),
hostname=substring (hostname, 1, 10), progname=substring(program_name, 1,10),
cmd=substring(cmd, 1, 10), status, physical_io, waittype
from master..sysprocesses (nolock)
where spid in
(select blocked from #blk)
declare blk_cursor CURSOR FOR SELECT blocked from #blk
open blk_cursor
fetch next from blk_cursor into @blocker_spid
while (@@fetch_status <> -1)
begin
select @i_buff_string = ('dbcc inputbuffer (' + convert(char(6),@blocker_spid) +')')
select 'Below is input buffer for this blocking spid: ', @blocker_spid
exec (@i_buff_string)
fetch next from blk_cursor into @blocker_spid
end
deallocate blk_cursor
drop table #blk
End
select '5.1.3 Blocking per Object with sys.dm_db_index_operational_stats'
--Select * from sys.dm_db_index_operational_stats( ... )
select '5.1.4 Overall Performance Effect of Blocking Using Waits'
select top 10 *
from sys.dm_os_wait_stats
order by wait_time_ms desc
select '5.2 Monitoring Index Usage'
select object_id, index_id, user_seeks, user_scans, user_lookups
from sys.dm_db_index_usage_stats
order by object_id, index_id
Select '5.2.1 Indexes of a specific table that has not been used since the last start of SQL Server'
select i.name
from sys.indexes i
where i.object_id=object_id('ApplicationVersion')
and i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = DB_ID() )
Select '5.2.2 All indexes that haven’t been used yet in a specific database'
select object_name(object_id), i.name
from sys.indexes i
where i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = DB_ID() )
order by object_name(object_id) asc
Select '5.2.3 Find unsed index during current session for tables with data'
Select row_number() Over (order by S.name, object_name(IUS.object_id),I.name) as UnusedIndex
,S.name as SchemaName
,object_name(IUS.object_id) as TableName
,I.name as IndexName
,I.index_id
From sys.dm_db_index_usage_stats IUS
Inner Join sys.indexes I
on IUS.object_id = I.object_id And I.index_id = IUS.index_id
Inner Join sys.objects O
on I.object_id = O.object_id
Inner Join sys.schemas S
on O.schema_id = S.schema_id
Inner Join sys.partitions P
on P.object_id = IUS.object_id And P.index_id = IUS.index_id
Where IUS.database_id = DB_ID()
and OBJECTPROPERTY(IUS.object_id, 'IsUserTable') = 1
and I.type_desc = 'nonclustered'
and I.is_primary_key = 0
and I.is_unique_constraint = 0
and P.rows > 1000
|
