MS SQL Server performance analysis

save the following scripts into one and run it during performance issue, you will have an overall view of the server wait events, cpu load and what sql is running.
1. to clear dm_os_wait_stats

  DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR)

2. use the following query to get the delta of 2 seconds of os wait statistics.

select wait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms into #originalwaitstatsnapshot from sys.dm_os_wait_stats
 waitfor delay '00:00:02'
 select wait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms into #latestwaitstatsnapshot from sys.dm_os_wait_stats
 select l.wait_type,(l.wait_time_ms-o.wait_time_ms) accum_wait_ms from #originalwaitstatsnapshot o inner join #latestwaitstatsnapshot l on o.wait_type=l.wait_type
 where l.wait_time_ms > o.wait_time_ms order by accum_wait_ms desc

3. Query to find out which statement is running (stmt_end = -1), and cpu status (is_idle =0, runnable_tasks_count>0)

select top 8 last_batch, stmt_end,spid,blocked,waittype,waittime,lastwaittype,dbid,hostname,program_name,cmd,nt_username,sql_handle,stmt_start
 from sys.sysprocesses order by stmt_end,last_batch desc

 select t1.runnable_tasks_count,t1.is_idle, t1.load_factor,t1.pending_disk_io_count,t1.active_workers_count,t1.work_queue_count,t1.*
 from sys.dm_os_schedulers t1

4. query to find out the sql statement that is in runnable, suspended or running status.
select text,* from sys.dm_exec_requests cross apply sys.dm_exec_sql_text(sql_handle) where sql_handle is not null

5  to find out what query is running now and waiting for what?

select w.session_id,w.wait_duration_ms,w.wait_type,w.blocking_session_id,w.resource_description,s.program_name,t.text,t.dbid,s.cpu_time,s.memory_usage
from sys.dm_os_waiting_tasks w
inner join sys.dm_exec_sessions s on w.session_id=s.session_id
inner join sys.dm_exec_requests r on s.session_id=r.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) t
where s.is_user_process=1


!!! to create an automatic statistics collection, follow this procedure

CREATE TABLE [dbo].[DM_OS_WAIT_STATS](
[snapshot_id] [int] not null,
[timeofsnapshot] [datetime] NOT 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]

GO

create index snapshotid_idx on  [dbo].[DM_OS_WAIT_STATS] (snapshot_id)

 create procedure collect_dmoswaitstats as
insert into DM_OS_WAIT_STATS select (select isnull(max(snapshot_id)+1,1) from [dbo].[DM_OS_WAIT_STATS]) snapshot_id,
getdate() timeofsnapshot, wait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms
from sys.dm_os_wait_stats
go

create procedure compare_dmoswaitstas @begin_snapshotid int, @end_snapshotid int
as
 select l.wait_type,(l.wait_time_ms-o.wait_time_ms) accum_wait_ms from dm_os_wait_stats o
 inner join dm_os_wait_stats l on o.wait_type=l.wait_type
 where o.snapshot_id=@begin_snapshotid and l.snapshot_id=@end_snapshotid
 and l.wait_time_ms > o.wait_time_ms
 order by accum_wait_ms desc
 go

schedule to run every 10 seconds of collect_dmoswaitstats, do not forget to purge the table weekly.
delete from dm_os_wait_stats where timeofsnapshot < getdate()-7

to find the snapshot_id of the period:

select snapshot_id,min(timeofsnapshot) mintime,max(timeofsnapshot) maxtime from dm_os_wait_stats group by snapshot_id order by snapshot_id

to compare the wait statistics betweeen snapshots:

 exec compare_dmoswaitstas 1,2   -- replace 1,2 with the snapshot_id that cover the performance issue period.

to see the variation of statistics:

select wait_type,max(delta_ms),min(delta_ms),(max(delta_ms)-min(delta_ms))/min(delta_ms) p1 from (select s.snapshot_id,s.timeofsnapshot,s.wait_type,s.wait_time_ms-f.wait_time_ms delta_ms from dm_os_wait_stats f, dm_os_wait_stats s
where s.snapshot_id=f.snapshot_id+1 and s.wait_type=f.wait_type and s.wait_time_ms>f.wait_time_ms) a group by wait_type order by p1 desc

to generate data for pivotal chart:
select s.snapshot_id,s.timeofsnapshot,s.wait_type,s.wait_time_ms-f.wait_time_ms delta_ms from dm_os_wait_stats f, dm_os_wait_stats s
where s.snapshot_id=f.snapshot_id+1 and s.wait_type=f.wait_type and s.wait_time_ms>f.wait_time_ms
order by 1