Check waits stats against the database
use DGI_450
select * from sys.dm_os_waiting_tasks where session_id in (select session_id from sys.dm_exec_requests where database_id = db_id())
Create table msdb.dbo.waiting_stats and Insert Wait stat data in table .
CREATE TABLE [dbo].[waiting_stats](
[timestamp] [datetime] NOT NULL,
[dbid] [int] NULL,
[waiting_task_address] [varbinary](8) NOT NULL,
[session_id] [smallint] NULL,
[exec_context_id] [int] NULL,
[wait_duration_ms] [bigint] NULL,
[wait_type] [nvarchar](60) NULL,
[resource_address] [varbinary](8) NULL,
[blocking_task_address] [varbinary](8) NULL,
[blocking_session_id] [smallint] NULL,
[blocking_exec_context_id] [int] NULL,
[resource_description] [nvarchar](1024) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[OverallWaitsOnServer](
[logtime] [datetime] NULL,
[wait_type] [nvarchar](60) NOT NULL,
[waiting_tasks_count] [bigint] NOT NULL,
[wait_time_ms] [bigint] NOT NULL,
[max_wait_time_ms] [bigint] NOT NULL,
[signal_wait_time_ms] [bigint] NOT NULL
) ON [PRIMARY]
use DGI_450
select getdate()timestamp,* into msdb.dbo.waiting_stats from sys.dm_os_waiting_tasks where session_id in (select session_id from sys.dm_exec_requests where database_id = db_id())
Query Insert the data wait stat and table count every 1 min .
declare @datefilter datetime
set @datefilter = dateadd(hh, 1,getdate())
--print @datefilter
while (getdate() <= @datefilter)
begin
insert into msdb.dbo.T_CTRL_FILE_ERROR_DETAIL_temp select getdate(), count(*) from DGI_450_PROD_R_4_4..T_CTRL_FILE_ERROR_DETAIL with (nolock)
insert into msdb.dbo.waiting_stats select getdate(),* from sys.dm_os_waiting_tasks where session_id in (select session_id from sys.dm_exec_requests where database_id = db_id())
waitfor delay '000:00:56'
end
-----------------------------------------------------------------
Query Insert the data wait stat and table count every 1 min .
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
declare @datefilter datetime, @Counter int
set @Counter = 0
set @datefilter = dateadd(hh, 1,getdate())
if (datepart(hh, getdate()) = 6 and (datepart(mi, getdate()) between 25 and 35))
set @datefilter = dateadd(hh, 2,getdate())
print @datefilter
while (getdate() <= @datefilter)
begin
insert into msdb.dbo.waiting_stats
select getdate(), er.database_id, wt.* from sys.dm_os_waiting_tasks wt
join sys.dm_exec_requests er
on wt.session_id = er.session_id
where wait_duration_ms > 0
and wt.wait_type not in ( 'WAITFOR', 'TRACEWRITE')
if (@Counter%2 = 0)
insert into msdb.dbo.OverallWaitsOnServer
select Getdate(), * from sys.dm_os_wait_stats
where waiting_tasks_count > 0 and wait_time_ms > 0
and max_wait_time_ms > 0 and signal_wait_time_ms > 0
set @Counter = @Counter + 1
waitfor delay '000:00:05'
end
----------------------------------------------------------------------------
This query gives you an idea of what is going on with your server right now..
DECLARE @OpenQueries TABLE (cpu_time INT, logical_reads INT, session_id INT)
INSERT INTO @OpenQueries(cpu_time, logical_reads, session_id)
select r.cpu_time ,r.logical_reads, r.session_id
from sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r
on s.session_id =r.session_id and s.last_request_start_time=r.start_time
where is_user_process = 1
and s.session_id <> @@SPID
waitfor delay '00:00:01'
select substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text) else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text
, r.cpu_time-t.cpu_time as CPUDiff
, r.logical_reads-t.logical_reads as ReadDiff
, r.wait_type
, r.wait_time
, r.last_wait_type
, r.wait_resource
, r.command
, r.database_id
, r.blocking_session_id
, r.granted_query_memory
, r.session_id
, r.reads
, r.writes, r.row_count, s.[host_name]
, s.program_name, s.login_name
from sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r
on s.session_id =r.session_id and s.last_request_start_time=r.start_time
left join @OpenQueries as t on t.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h
where is_user_process = 1
and s.session_id <> @@SPID
order by 3 desc
-------------------------------------------------------------------------------------------------------------------------------------------
SQL Server for the cumulative wait statistics using the sys.dm_os_wait_stats DMV,
This will show the waits grouped together as a percentage of all waits on the system, in decreasing order. The waits to be concerned about (potentially) are those at the top of the list as this represents the majority of where SQL Server is spending it’s time waiting. You can see that a bunch of waits are being filtered out
-------------------------------------------------------------------------------------------------------------------------------------------
WITH [Waits] AS
(SELECT
[wait_type],[wait_time_ms] / 1000.0 AS [WaitS],([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS],[waiting_tasks_count] AS [WaitCount],100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP',
N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH',
N'SLEEP_TASK', N'SLEEP_SYSTEMTASK',
N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN',
N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',
N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE',
N'TRACEWRITE', N'XE_DISPATCHER_WAIT',
N'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER',
N'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP')
)
SELECT
[W1].[wait_type] AS [WaitType], CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S], CAST([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],[W1].[WaitCount] AS [WaitCount],CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S], CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S] FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
[W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
GO
-------------------------------------------------------------------------------------------------------------------
--<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>-- --DMV_All-Stars.sql --http://blogs.msdn.com/jimmymay --Table of Contents --1. expensive queries --2. wait stats --3. virtual file stats (& virtual file latency) --4. plan cache interrogation --5. real-time blockers --<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>-- --Weasel Clause: This script is provided "AS IS" with no warranties, and confers no rights. -- Use of included script samples are subject to the terms specified at -- http://www.microsoft.com/info/cpyright.htm --<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>-- --1. expensive queries --text *and* statement --usage: modify WHERE & ORDER BY clauses to suit circumstances SELECT --TOP 25 -- the following four columns are NULL for ad hoc and prepared batches DB_Name(qp.dbid) as dbname , qp.dbid , qp.objectid , qp.number --, qp.query_plan --the query plan can be *very* useful; enable if desired , qt.text , SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) as statement_text , qs.creation_time , qs.last_execution_time , qs.execution_count , qs.total_worker_time / qs.execution_count as avg_worker_time , qs.total_physical_reads / qs.execution_count as avg_physical_reads , qs.total_logical_reads / qs.execution_count as avg_logical_reads , qs.total_logical_writes / qs.execution_count as avg_logical_writes , qs.total_elapsed_time / qs.execution_count as avg_elapsed_time , qs.total_clr_time / qs.execution_count as avg_clr_time , qs.total_worker_time , qs.last_worker_time , qs.min_worker_time , qs.max_worker_time , qs.total_physical_reads , qs.last_physical_reads , qs.min_physical_reads , qs.max_physical_reads , qs.total_logical_reads , qs.last_logical_reads , qs.min_logical_reads , qs.max_logical_reads , qs.total_logical_writes , qs.last_logical_writes , qs.min_logical_writes , qs.max_logical_writes , qs.total_elapsed_time , qs.last_elapsed_time , qs.min_elapsed_time , qs.max_elapsed_time , qs.total_clr_time , qs.last_clr_time , qs.min_clr_time , qs.max_clr_time --, qs.sql_handle , qs.statement_start_offset , qs.statement_end_offset , qs.plan_generation_num , qp.encrypted , qp.query_plan FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt --WHERE last_execution_time >= '20130264' --ORDER BY qs.execution_count DESC --Frequency ORDER BY qs.total_worker_time DESC --CPU --ORDER BY qs.total_elapsed_time DESC --Durn --ORDER BY qs.total_logical_reads DESC --Reads --ORDER BY qs.total_logical_writes DESC --Writes --ORDER BY qs.total_physical_reads DESC --PhysicalReads --ORDER BY avg_worker_time DESC --AvgCPU --ORDER BY avg_elapsed_time DESC --AvgDurn --ORDER BY avg_logical_reads DESC --AvgReads --ORDER BY avg_logical_writes DESC --AvgWrites --ORDER BY avg_physical_reads DESC --AvgPhysicalReads --sample WHERE clauses --WHERE last_execution_time > '20070507 15:00' --WHERE execution_count = 1 -- WHERE SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1, -- ((CASE statement_end_offset -- WHEN -1 THEN DATALENGTH(qt.text) -- ELSE qs.statement_end_offset END -- - qs.statement_start_offset)/2) + 1) -- LIKE '%MyText%' --<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>-- --2. wait stats --DBCC sqlperf(waitstats) --DBCC sqlperf('sys.dm_os_wait_stats',CLEAR) --re-initialize waitstats SELECT * , (wait_time_ms - signal_wait_time_ms) as resource_wait_time_ms , signal_wait_time_per_wait = CASE WHEN waiting_tasks_count = 0 THEN 0 ELSE (signal_wait_time_ms/waiting_tasks_count) END , resource_wait_time_per_wait = CASE WHEN waiting_tasks_count = 0 THEN 0 ELSE ((wait_time_ms - signal_wait_time_ms)/waiting_tasks_count) END FROM sys.dm_os_wait_stats ORDER BY resource_wait_time_ms DESC --ORDER BY wait_time_ms DESC --ORDER BY signal_wait_time_ms DESC --ORDER BY waiting_tasks_count DESC --ORDER BY max_wait_time_ms DESC --adapted from Paul Randal --Wait statistics, or please tell me where it hurts --http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts ;WITH Waits AS (SELECT wait_type, wait_time_ms / 1000.0 AS WaitS, (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS, signal_wait_time_ms / 1000.0 AS SignalS, waiting_tasks_count AS WaitCount, 100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT' ) ) SELECT TOP 10 W1.wait_type AS WaitType, CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage, CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_Sec, CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_Sec, CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_Sec, W1.WaitCount AS WaitCount, CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_Sec, CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_Sec, CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_Sec FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage HAVING SUM (W2.Percentage) - W1.Percentage < 99.9; -- percentage threshold GO --<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>-- --3. virtual file stats SELECT --virtual file latency vReadLatency = CASE WHEN num_of_reads = 0 THEN 0 ELSE (io_stall_read_ms/num_of_reads) END , vWriteLatency = CASE WHEN num_of_writes = 0 THEN 0 ELSE (io_stall_write_ms/num_of_writes) END , vLatency = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 ELSE (io_stall/(num_of_reads + num_of_writes)) END --avg bytes per IOP , BytesperRead = CASE WHEN num_of_reads = 0 THEN 0 ELSE (num_of_bytes_read/num_of_reads) END , BytesperWrite = CASE WHEN num_of_writes = 0 THEN 0 ELSE (num_of_bytes_written/num_of_writes) END , BytesperTransfer = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 ELSE ((num_of_bytes_read+num_of_bytes_written)/(num_of_reads + num_of_writes)) END , LEFT(mf.physical_name,2) as Drive , DB_NAME(vfs.database_id) as DB --, mf.name AS FileName , vfs.* , mf.physical_name FROM sys.dm_io_virtual_file_stats(NULL,NULL) as vfs JOIN sys.master_files as mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id --WHERE mf.type_desc = 'LOG' -- log files --WHERE DB_NAME(vfs.database_id) IN ('tpcc','tpcc2') ORDER BY vLatency DESC -- ORDER BY vReadLatency DESC -- ORDER BY vWriteLatency DESC --<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>-- --4. plan cache interrogation -- note: sys.dm_exec_cached_plans is diminutive version of syscacheobjects -- no dbid, setopts -- we want reusable code, absence of ad hoc SQL -- we want relatively few rows with low usecounts --2000 SELECT cacheobjtype , objtype , usecounts , pagesused , dbid , sql FROM master.dbo.syscacheobjects WHERE cacheobjtype = 'Compiled Plan' ORDER BY usecounts DESC --ORDER BY sql --2005 SELECT c.cacheobjtype , c.objtype , c.usecounts , c.size_in_bytes , t.dbid , t.text FROM sys.dm_exec_cached_plans as c CROSS APPLY sys.dm_exec_sql_text(plan_handle) as t WHERE c.cacheobjtype = 'Compiled Plan' ORDER BY c.usecounts DESC --ORDER BY t.text --<<<<<<<<<<----------------------------------------------------------------->>>>>>>>>>-- --5. real-time blockers --Report Blocker and Waiter SQL Statements --http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/trans/sql05vb044.mspx?mfr=true -- SQLCAT BPT SELECT t1.resource_type as lock_type , db_name(resource_database_id) as DB , t1.resource_associated_entity_id as blkd_obj , t1.request_mode as lock_req -- lock requested , t1.request_session_id as waiter_sid-- spid of waiter , t2.wait_duration_ms as waittime , (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 --- this is the statement executing right now , 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 JOIN sys.dm_os_waiting_tasks as t2 ON t1.lock_owner_address = t2.resource_address
-------------------------------------------------------------------------------------------------------