Physical Operations

/*

Lack of Indexes or memory pressure results in high physical I/O .
To find queries with high physical I/O , run the following script

NOTE:  This may take considerable time , so use TOP operator
to  restrict number of rows

*/

select top 50 sql.text, p.query_plan,qs.execution_count
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(sql_handle) sql
cross apply sys.dm_exec_query_plan(plan_handle) p
where query_plan.exist('
declare default element namespace http://schemas.microsoft.com/sqlserver/2004/07/showplan;
/showplanxml/batchsequence/batch/statements//relop/@physicalop[. = sql:variable("@op")]') = 1
order by execution_count desc

go

I/O related Queries

-----------------------------------------------------------

-- I/O related Queries
----------------------------------------------------------
/*
Log Sapce Information
*/
select rtrim(pc1.instance_name) as [database name]
, pc1.cntr_value/1024.0 as [log size (mb)]
, cast(pc2.cntr_value*100.0/pc1.cntr_value as dec(5,2))
as [log space used (%)]
from sys.dm_os_performance_counters as pc1
join sys.dm_os_performance_counters as pc2
on pc1.instance_name = pc2.instance_name
where pc1.object_name like '%Databases%'
and pc2.object_name like '%Databases%'
and pc1.counter_name = 'Log File(s) Size (KB)'
and pc2.counter_name = 'Log File(s) Used Size (KB)'
and pc1.instance_name not in ('_Total', 'mssqlsystemresource')
and pc1.cntr_value > 0

GO
------------------------------------------------------------------------

/*
The two columns io_stall_read_ms and io_stall_write_ms
represent the time SQL Server waited for Reads and Writes
issued on the file since the start of SQL Server.
poll the command for small duration and then compare it with baseline numbers.
*/
select db_name(database_id) as dbname , file_id,
io_stall_read_ms, io_stall_write_ms
from sys.dm_io_virtual_file_stats(null, null)
order by (io_stall_read_ms+ io_stall_write_ms) desc;
GO
------------------------------------------------------------------------

Utility Queries 01

/*

Idle process with open transaction
*/

select spid,kpid,blocked,status,cmd,open_tran,dbid,
datediff(s,last_batch,getdate()) as idle_secs,waittype,lastwaittype,waitresource,
login_time,last_batch,cpu,physical_io,memusage
from sys.sysprocesses
where status = 'sleeping' and open_tran > 0
---------------------------------------------------------------------------------------------------
 
/*

Transaction Running in the current session
*/
select * fromsys.dm_tran_current_transaction
 
---------------------------------------------------------------------------------------------------

--Number of threads used


select count(*) as 'Number of threads'

from sys.dm_os_waiting_tasks
where wait_type <> 'THREADPOOL';
go
---------------------------------------------------------------------------------------------------
 /*


Check whether a DAC is in use by running
the following query. If there is an active DAC,
the query will return the server process id (spid)
for the DAC; otherwise, it will return no rows.
*/

select t2.session_id
from sys.tcp_endpoints as t1
join sys.dm_exec_sessions as t2
on t1.endpoint_id = t2.endpoint_id
where t1.name='Dedicated Admin Connection';
GO

-------------------------------------------------------------------------
/*
protocol used for the current connection:
*/
select net_transport
from sys.dm_exec_connections
where session_id = @@spid;
GO

-------------------------------------------------------------------------
 

Estimated Time of Completion of Backup/Restore

/*
If you have started backup/restore process, you would like to know,
percentage of completion and approximate time, the process will take to complete

Estimate completion of long-running backup or restore commands.
Use the following code to display the Estimated Time  and Percentage Completion
*/

select r.session_id,r.command,
convert(numeric(6,2),r.percent_complete) as [percent complete],
convert(varchar(20),dateadd(ms,r.estimated_completion_time,getdate()),20) as [eta completion time],
convert(numeric(6,2),r.total_elapsed_time/1000.0/60.0) as [elapsed min],
convert(numeric(6,2),r.estimated_completion_time/1000.0/60.0) as [eta min],
convert(numeric(6,2),r.estimated_completion_time/1000.0/60.0/60.0) as [eta hours],
,convert(varchar(100),(select substring(text,r.statement_start_offset/2,
case
when r.statement_end_offset = -1 then 1000
else (r.statement_end_offset-r.statement_start_offset)/2
end
)
from sys.dm_exec_sql_text(sql_handle)))
from sys.dm_exec_requests r
where command in ('restore database','backup database')
-----------------------------------------------------------------------------------------------------

Memory Related Queries

----------------------------------------------------------------------

-- Memory Related Queries
---------------------------------------------------------------------
/*
dbcc memorystatus
Memory manager = VM Committed + AWE Allocated = Total Physical Memory for the instance
Single Page
Check CACHE_STORE_OBJCP to check how much memory SP,Funtions,Triggers,Views are consuming
Check CACHE_STORE_SQLCP to check how much memory adhoc queries are consuming
Check CACHE_STORE_XPROC to check how much memory xtended SPs are consuming
Check Buffer Pool
Committed - Total Physical Memory Committed
Target - Required
If Target > Committed Memory Pressure
If Target = Committed OK
If Target < Committed Memeory will Shrink
Free - Free Memory Available ( Not Commited /Reserved )
Stolen -- This is memory stolen by SQL Intenal components from Buffer Pool
Check Procedure Cache for
TotalProcs,TotalPages,Inuse pages
*/
dbcc memorystatus
------------------------------------------------------------------
-- Investigate Buffer pool and Procedure Cache Memory allocation
declare @sqlobject varchar(80)
exec @sqlobject = dbo.lib_fn_sqlobject
select object_name,counter_name,instance_name,cntr_value,
(cntr_value*8)/1024 as size_MB
from sys.dm_os_performance_counters a
where
(a.object_name = @sqlobject+'Buffer Manager' and counter_name = 'Database pages')
or (a.object_name = @sqlobject+'Plan Cache' and counter_name = 'cache pages')
---------------------------------------------------------------------

-- Total amount of memory consumed (including AWE) by the buffer pool:
select sum(multi_pages_kb + virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb)/1024.00/1000.00 as [used by bpool, gb]
from sys.dm_os_memory_clerks
where type = 'MEMORYCLERK_SQLBUFFERPOOL';
-----------------------------------------------------------------------------

/*
Internal memory pressure occurs because internal
components have stolen pages from buffer pool,
Identify internal components that are stealing
the most pages from buffer pool
Note: single_pages are allocated from Buffer Pool
multi_pages are allocated from outside buffer pool
*/
select type, sum(single_pages_kb)/1024.00 as stolen_mem_mb
from sys.dm_os_memory_clerks
group by type
order by stolen_mem_mb desc;
---------------------------------------------------------------------

/*
Internal components that have
allocated memory outside of buffer pool by using
multipage allocator
*/
select type, sum(multi_pages_kb)/1024.00 as multi_page_allocated_mb
from sys.dm_os_memory_clerks
where multi_pages_kb != 0
group by type
order by multi_page_allocated_mb desc;
---------------------------------------------------------------------
/*

Total : single_page_allocator + multi_page_allocator
for internal components
*/
select type, sum(single_pages_kb + multi_pages_kb)/1024.00 as total_mem_mb
from sys.dm_os_memory_clerks
group by type
order by total_mem_mb desc;

OR
--see the amount of memory allocated in the multipage units.

select name, type, (single_pages_kb/1024.00) single_mb,
(multi_pages_kb/1024.00) multi_mb,
(single_pages_in_use_kb/1024.00) single_use_mb,
(multi_pages_in_use_kb/1024.00) multi_use_mb
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' OR type = 'CACHESTORE_OBJCP';

---------------------------------------------------------------------

Compilation and Recompilation Information from Performance Counters

First create the following function  which will capture information from the current instance of SQL Server

create function dbo.lib_fn_sqlobject ()

returns varchar(80)
as
begin

 declare @sqlobject varchar(80)
 select @sqlobject = object_name
 from sys.dm_os_performance_counters
 where object_name like '%sql statistics%'
 and counter_name = 'sql compilations/sec'
 set @sqlobject = substring(@sqlobject,1,charindex(':',@sqlobject))
 return @sqlobject
end

/*

Check Number of Compilations and Recompilations / Sec
If % of Compliations and Recompilations compared
to batch requests are high ( > 50 %) , you need to check
queries are properly parameterized.

*/

declare @sqlobject varchar(80)
exec @sqlobject = dbo.lib_fn_sqlobject
select b.counter_name,a.cntr_value,b.cntr_value,b.cntr_value *100.00/a.cntr_value as percentage
from sys.dm_os_performance_counters a,
sys.dm_os_performance_counters b
where a.object_name = @sqlobject+'SQL Statistics'
and a.counter_name in ('Batch Requests/sec')
and b.object_name = @sqlobject + 'SQL Statistics'
and b.counter_name in ('SQL Compilations/sec','SQL Re-Compilations/sec')
----------------------------------------------------------------------

/*

Check Number Safe Auto-Params and Failed Auto-Params /sec
If Failed Auto-Params /sec Percentage returns a high value ,
queries are creating new plans . Try to use sp_executesql and
check the query parameters for high faile auto parameterization.
This will occur mostly for adhoc queries.

You can test whether setting parameterization to 'FORCED' at database level

will improve the performance. Do a test on development server before promoting to production server.
*/

declare @sqlobject varchar(80)
exec @sqlobject = dbo.lib_fn_sqlobject
select b.counter_name,b.cntr_value *100.00/a.cntr_value as percentage
from sys.dm_os_performance_counters a,
sys.dm_os_performance_counters b
where a.object_name = @sqlobject+'SQL Statistics'
and a.counter_name in ('Auto-Param Attempts/sec')
and b.object_name = @sqlobject+'SQL Statistics'
and b.counter_name in ('Failed Auto-Params/sec','Safe Auto-Params/sec')
----------------------------------------------------------------------
/*
Check Plan Cache Hit Ratio for each cache Object type
If  % is less than 80 % investigate further
*/



declare @sqlobject varchar(80)

exec @sqlobject = dbo.lib_fn_sqlobject

select b.instance_name,a.cntr_value base ,b.cntr_value counter,
b.cntr_value *100.00/a.cntr_value cache_hit_ratio
from sys.dm_os_performance_counters a,
sys.dm_os_performance_counters b
where a.object_name = @sqlobject+'Plan Cache'
and a.cntr_type = 1073939712
and b.object_name = @sqlobject+'Plan Cache'
and b.cntr_type = 537003264
and a.instance_name = b.instance_name
and a.cntr_value > 0
----------------------------------------------------------------------

High CPU Usage due to recompilation

/*

High CPU Pressure can also happen due to queries/batches
that are being recompiled frequently
plan_genration_num indicates multiple plans .
If  plan_genration_num  > 0 , then there is recompilation
*/

select top 50 plan_generation_num, execution_count,
db_name(depamain.dbid) as dbname,object_name(depamain.objectid,depamain.dbid) as objname,
(
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
from sys.dm_exec_query_stats
outer apply
(
 select dbid,objectid from

  (
    select attribute,cast(value as int) as attvalue
    from sys.dm_exec_plan_attributes(plan_handle)
    where attribute in ('dbid','objectid')
) as depa
pivot
 (
  max(depa.attvalue) for depa.attribute in ("dbid", "objectid")
 ) as depapvt
) as depamain
where plan_generation_num >1
order by plan_generation_num desc;

----------------------------------------------------------------------

Find the state of the process and count on each scheduler

-- Find the state of the process and count on each scheduler


select count(*) noofrows ,t1.state as processorstate, t2.scheduler_id
from sys.dm_os_workers as t1, sys.dm_os_schedulers as t2
where t1.scheduler_address = t2.scheduler_address
and t2.scheduler_id < 255
group by t2.scheduler_id,t1.state
order by scheduler_id

Brief Description about WAITs

---------------------------------------

-- WAITS
---------------------------------------
/*
Some of the wait types should be ignored which are system wait types
create a view to ignore certain wait types
*/
create view dbo.lib_vw_ignore_waittypes
as
select 'DBMIRRORING_CMD' as wait_type
union all
select 'BROKER_EVENTHANDLER'
union all
select 'BROKER_TRANSMITTER'
union all
select 'BROKER_RECEIVE_WAITFOR'
union all
select 'ONDEMAND_TASK_QUEUE'
union all
select 'REQUEST_FOR_DEADLOCK_SEARCH'
union all
select 'DBMIRROR_EVENTS_QUEUE'
union all
select 'SQLTRACE_BUFFER_FLUSH'
union all
select 'BAD_PAGE_PROCESS'
union all
select 'CHECKPOINT_QUEUE'
union all
select 'DBMIRROR_EVENTS_QUEUE'
union all
select 'LAZYWRITER_SLEEP'
union all
select 'LOGMGR_QUEUE'
union all
select 'KSOURCE_WAKEUP'
go
-------------------------------------------------------------------------
/*
Performance of the query depends on Current Waits
availbale in sys.dm_os_waiting_tasks.
Find waits currently occuring on the system
check wait_duration_ms to identify waits with high wait duration
select * from sys.dm_os_waiting_tasks

Troubleshooting Blocking

1. Identify blocking
2. Cause of blocking
3. Remove the cause of blocking

select * from sys.dm_os_waiting_tasks provides blocking information
Session currently executing is associated with a task

Some systems tasks will not have session_id

1. waiting tasks columns
a. waiting_task_address ( unique internal memory address of the object
representing the task)
b. session_id ( user session associated with the task.
Association between session and task is only for the duration of the task )
c. exec_context_id
2. blocking task information columns
a. blocking_task_address
b. blocking_session_id
c. blocking_exec_context_id

3. wait information

a. wait_type ( current_wait_type for the waiting_task_address)
b. wait_duration_ms ( duration of the current wait)
c. resource_address ( memory address of the resource on which task is waiting)
d. resource_description ( populated only for locks,latch,CXPACKET,THREADPOOL)
*/

SELECT
WT.*
FROM sys.dm_os_waiting_tasks AS WT
where wait_type not in (select wait_type from master.dbo.lib_vw_ignore_waittypes)
/*

1. Check Wait_type column . If this column is repeated for several session_ids
with the same resource_id for long duration, resolve this wait_type

2. Check resource_address. If same resource_address is repeated then there is
a problem with the resource

3. wait_duration_ms provides evidence of high wait time
*/
-------------------------------------------------------------------------------

/*
sys.dm_os_wait_stats -- provides cumulative
wait type statistics from the time of sql server tart
-- You can clear the wait static information .
dbcc sqlperf('sys.dm_os.wait_stats','CLEAR')
-- NOTE: Avoid wait statisitcs clear in production environment
By default all wait types are not listed from sys.dm_os.wait_stats
-- To display all the wait types
dbcc traceon(8001,-1)
select * from sys.dm_os_wait_stats
*/

/*
Compare Signal Waits and Resource Waits
Signal waits are the time spent in the runnable queue waiting for the CPU,
while resource waits are the time spent waiting for the
resource (wait_time_ms - signal_wait_time_ms).
Wait_time_ms represents the total waits.
Check for CPU Issue

If %signal waits is around 25% and above, CPU issue

*/
Select signal_wait_time_ms=sum(signal_wait_time_ms)
,'%signal waits' = cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))
,resource_wait_time_ms=sum(wait_time_ms - signal_wait_time_ms)
,'%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
where wait_type not in (select wait_type from master.dbo.lib_vw_ignore_waittypes)
go

-------------------------------------------------------------------------------------

-- Wait Types with high wait times
select top 10 *,wait_time_ms*1.0000/waiting_tasks_count as avg_wait_ms,
wait_time_ms/1000.00/60.00/24.00 as wait_time_days,
max_wait_time_ms/1000.00/60.00 as max_wait_time_min
from sys.dm_os_wait_stats
where wait_type not in (select wait_type from master.dbo.lib_vw_ignore_waittypes)
order by wait_time_ms desc

-----------------------------------------------------------------------------------
-- Wait Types with high maximum wait time
select top 10 *,
wait_time_ms/1000.00/60.00/24.00 as wait_time_days,
max_wait_time_ms/1000.00/60.00 as max_wait_time_min
from sys.dm_os_wait_stats
where wait_type not in (select wait_type from master.dbo.lib_vw_ignore_waittypes)
order by max_wait_time_ms desc
----------------------------------------------------------------------------
-- Wait Types with high waiting tasks count
select top 10 *, wait_time_ms*1.0000/waiting_tasks_count as avg_wait_ms,
wait_time_ms/1000.00/60.00/24.00 as wait_time_days,
max_wait_time_ms/1000.00/60.00 as max_wait_time_min
from sys.dm_os_wait_stats
where wait_type not in (select wait_type from master.dbo.lib_vw_ignore_waittypes)
order by waiting_tasks_count desc
------------------------------------------------------------------------
-- Wait Types with high average wait time

select top 20 *, wait_time_ms*1.0000/waiting_tasks_count as avg_wait_ms,
wait_time_ms/1000.00/60.00/24.00 as wait_time_days,
max_wait_time_ms/1000.00/60.00 as max_wait_time_min
from sys.dm_os_wait_stats
where wait_type not in (select wait_type from master.dbo.lib_vw_ignore_waittypes)
and waiting_tasks_count > 0
order by avg_wait_ms desc

------------------------------------------------------------------------------

SQL Server 2008 system and memory Information

select
cpu_ticks,ms_ticks,cpu_count,hyperthread_ratio,
cast(physical_memory_in_bytes/1024.00/1024.00/1000 as numeric(8,2)) as physical_memory_GB,
cast(virtual_memory_in_bytes/1024.00/1024.00/1000 as numeric(8,2)) as virtual_memory_GB,
cast(bpool_committed*8/1024.00/1000 as numeric(8,2)) as bpool_committed_GB ,
cast(bpool_commit_target*8/1024.00/1000 as numeric(8,2)) as bpool_commit_target_GB,
cast(bpool_visible*8/1024.00/1000 as numeric(8,2)) as bpool_visible_GB,
cast(stack_size_in_bytes/1024.00 as numeric(8,2)) as stack_size_in_bytes_KB,
os_quantum,os_error_mode,
os_priority_class,max_workers_count,
scheduler_count,scheduler_total_count,
deadlock_monitor_serial_number,
sqlserver_start_time_ms_ticks,
sqlserver_start_time
from sys.dm_os_sys_info

-- Memory Information

select
cast(total_physical_memory_kb/1024.00/1000 as numeric(8,2)) as Phys_Mem_GB,
cast(available_physical_memory_kb/1024.00/1000 as numeric(8,2))as Available_Phys_Mem_GB,
cast(total_page_file_kb/1024.00/1000 as numeric(8,2)) as total_page_file_GB,
cast(available_page_file_kb/1024.00/1000 as numeric(8,2)) as available_page_file_GB,
cast(system_cache_kb/1024.00/1000 as numeric(8,2)) as system_cache_GB,
cast(kernel_paged_pool_kb/1024.00/1000 as numeric(8,2)) as kernel_paged_pool_GB,
cast(kernel_nonpaged_pool_kb/1024.00/1000 as numeric(8,2)) as kernel_nonpaged_pool_GB,
system_high_memory_signal_state,
system_low_memory_signal_state,
system_memory_state_desc
from sys.dm_os_sys_memory

Total amount of memory consumed (including AWE) by the buffer pool:

-- Total amount of memory consumed (including AWE) by the buffer pool:
/*

 Total Amount includes multi page allocator , virtual memory committed,shared memory committed and AWE
*/


SELECT SUM(multi_pages_kb + virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb)/1024.00/1000.00 AS [Used by BPool, GB]
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLBUFFERPOOL';

Time spent by workers in RUNNABLE state

-- Time spent by workers in RUNNABLE state
/*
You can capture  wait times along with signal wait times on an instance of sql server
to investigate   percentage of  CPU waits

*/


create table #tempsignalwait ( wait_time_ms bigint,signal_wait_time_ms bigint, batchid int identity(1,1))
declare @slno int,@counter int
set @slno = 0
set @counter = 3
while @slno < @counter
begin
set @slno = @slno + 1
insert into #tempsignalwait(wait_time_ms,signal_wait_time_ms)
SELECT SUM(wait_time_ms), SUM(signal_wait_time_ms)
FROM sys.dm_os_wait_stats;


waitfor delay '00:01:00' -- wait for a minute

end


select a.batchid,
b.wait_time_ms - a.wait_time_ms as delay_tot_ms_in_onemin,
b.signal_wait_time_ms - a.signal_wait_time_ms as delay_signal_ms_in_onemin,
(b.signal_wait_time_ms - a.signal_wait_time_ms)/(b.wait_time_ms - a.wait_time_ms) * 100.00 as percent_signal_delay
from #tempsignalwait a, #tempsignalwait b
where a.batchid < @counter and b.batchid > 1
and b.batchid = a.batchid + 1
order by a.batchid



drop table #tempsignalwait

Procedure Cache Information with high paln cache size

/*
 To list entries from procedure cache with high cache plan size
 with parameter @noofrounds  = 0 all entries are returned
 with parameter @noofrounds  > 0 returns entries if  particular cache
 is removed from  previous and all round counts which indicates internal procedure cache pressure

Usage : 
exec .sp_lib_CacheInfo  0
exec .sp_lib_CacheInfo  1
*/






CREATE PROCEDURE dbo.sp_lib_CacheInfo @noofrounds int = 0

AS
BEGIN

 SET NOCOUNT ON
 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
 INNER JOIN sys.dm_os_memory_cache_clock_hands ch
 ON (cc.cache_address = ch.cache_address)
-- information only for moving hands caches
WHERE
(
               @noofrounds  = 0 
              OR
              ( @noofrounds  > 0
              AND
              ch.rounds_count > 0
              AND ch.removed_all_rounds_count > 0
              )
)
ORDER BY total_kb DESC

END

GO

Find Top Recompiles

-- To investigate top recompiles , use this Stored Procedure

/*
exec sp_lib_TopRecompiles  10 -- Top 10 Queries having high recompilation
*/


CREATE PROCEDURE dbo.sp_lib_TopRecompiles @CountID INT = 25

AS
BEGIN

SET NOCOUNT ON

SELECT TOP (@CountID), 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
END

GO

List of tables referenced in the view

If you require to obtain list of tables in a view

exec sp_refreshview 'viewname'
exec sp_lib_TablesintheView 'ViewName'


/*

exec dbo.sp_lib_TablesintheView
*/

CREATE Procedure dbo.sp_lib_TablesintheView
@viewname varchar(200) = NULL
AS
SELECT vObj.name AS ViewName, vObj.id AS ViewID, vObj.xtype AS ViewType,
dep.depid AS DependentID, dep.depnumber AS Dependentnumber, tObj.name AS TableName, col.colid AS ColumnID,
col.name AS columnName
FROM sysobjects vObj LEFT OUTER JOIN
sysdepends dep ON vObj.id = dep.id LEFT OUTER JOIN
sysobjects tObj ON dep.depid = tObj.id LEFT OUTER JOIN
syscolumns col ON dep.depnumber = col.colid
AND tObj.id = col.id
WHERE vObj.xtype = 'V' And vObj.category = 0
AND ( ( vObj.name = @viewname ) OR ( @viewname IS NULL) )
ORDER BY vObj.name, tObj.name, col.name



go