-- CPU
select 
    scheduler_id,
    current_tasks_count,
    runnable_tasks_count
from 
    sys.dm_os_schedulers
where 
    scheduler_id < 255
go

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
go

-- recompile
select 
    spid,
    StartTime,
    Textdata,
    EventSubclass,
    ObjectID,
    DatabaseID,
    SQLHandle 
from 
    fn_trace_gettable ( 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_42.trc' , 1)
where 
    EventClass in (37,75,166)

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

-- inefficient query
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 50 
        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

-- intra-query parallelism
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

-- find query plans that may 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

-- duration of a query is longer than the amount of CPU time
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

-- SQL Trace - look for the following signs of parallel queries, which could be either 
-- statements or batches that have CPU time greater than the duration.
select 	EventClass, 
		TextData 
from	::fn_trace_gettable('c:\temp\high_cpu_trace.trc', default)
where	EventClass in (10, 12)	-- RPC:Completed, SQL:BatchCompleted
and		CPU > Duration/1000	-- CPU is in milliseconds, Duration in microseconds  

-- look for showplan parallelism operators
select 
    EventClass, 
    TextData 
from 
    ::fn_trace_gettable('c:\temp\high_cpu_trace.trc', default)
where 
    TextData LIKE '%Parallelism%'

-- poor cursor usage
-- 
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 
    and cur.properties LIKE 'API%'	-- API cursor (TSQL cursors always have fetch buffer of 1)

-- memory
-- 

-- AWE allocated
--
select 
    sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb] 
from 
    sys.dm_os_memory_clerks

-- amount of mem allocated though multipage allocator interface
select	sum(multi_pages_kb) as total_mem_allocated_in_kb  
from	sys.dm_os_memory_clerks

-- how memory is distributed
select 
    type, sum(multi_pages_kb) as mem_allocated_in_kb 
from 
    sys.dm_os_memory_clerks 
where 
    multi_pages_kb != 0 
group by type
              
-- amount of memory consumed by components outside the Buffer 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'

-- 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'

-- detail memory per object
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 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 Buffer Pool): ' 
    + 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
go

-- top 10 consumers of memory from BPool
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

-- caches and memory pressure
select *
from 
    sys.dm_os_memory_cache_clock_hands
where 
    rounds_count > 0
    and removed_all_rounds_count > 0

-- size of cache
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

-- ring buffers
select	record 
from	sys.dm_os_ring_buffers 
where	ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

select	x.value('(//Notification)[1]', 'varchar(max)') as [Type],
		x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
		x.value('(//AvailablePhysicalMemory)[1]', 'int') as [Avail Phys Mem, Kb],
		x.value('(//AvailableVirtualAddressSpace)[1]', 'int') as [Avail VAS, Kb]
from	(select cast(record as xml) from sys.dm_os_ring_buffers 
			where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') as R(x)
order by [Time Stamp] desc

-- ring buffer OOM
select	record 
from	sys.dm_os_ring_buffers 
where	ring_buffer_type = 'RING_BUFFER_OOM'

select	x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
		x.value('(//Notification)[1]', 'varchar(100)') as [Last Notification]
from	(select cast(record as xml) 
			from sys.dm_os_ring_buffers 
			where ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER') as R(x)
order by [Time Stamp] desc

--
select	record 
from	sys.dm_os_ring_buffers 
where	ring_buffer_type = 'RING_BUFFER_BUFFER_POOL'

-- internal virtual memory pressure

-- 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
go

select * from VASummary
go

-- available memory in all free regions
SELECT	SUM(Size*Free)/1024 AS [Total avail mem, KB] 
FROM	VASummary 
WHERE	Free <> 0

-- get size of largest availble region
SELECT	CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB] 
FROM	VASummary 
WHERE	Free <> 0

-- I/O

-- I/O latch wait statistics
select	wait_type, 
        waiting_tasks_count, 
        wait_time_ms
from	sys.dm_os_wait_stats  
where	wait_type like 'PAGEIOLATCH%'  
order by wait_type

-- 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)t1,
        sys.dm_io_pending_io_requests as t2
where	t1.file_handle = t2.io_handle

-- top 5 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, 
		sql_handle, 
		plan_handle
from	sys.dm_exec_query_stats  
order by (total_logical_reads + total_logical_writes) Desc

-- get query text
select	substring(
			text, 
			(<statement_start_offset>/2), 
			(<statement_end_offset> - <statement_start_offset>) / 2)
 
from	sys.dm_exec_sql_text(sql_handle)                

-- get plan
select	* 
from	sys.dm_exec_query_plan(plan_handle) 
    
-- tempdb space
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

-- enumerate all the tempdb objects
DECLARE userobj_cursor CURSOR FOR 
select 
     sys.schemas.name + '.' + sys.objects.name 
from sys.objects, sys.schemas
where object_id > 100 and 
      type_desc = 'USER_TABLE'and 
      sys.objects.schema_id = sys.schemas.schema_id
go

open userobj_cursor
go

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

-- longest running transaction depeding on 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

-- top sessions allocating 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

-- top sessions 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

-- get TSQL and query plan
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

-- excessive DDL and allocation operations

-- 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:%’
