Get Wait Stats

set nocount on;
create table #wt (types varchar(30))

-- Add any wait types you want to ignore here
insert into #wt values('CHECKPOINT_QUEUE')
insert into #wt values('LAZYWRITER_SLEEP')
insert into #wt values('SLEEP_TASK')
insert into #wt values('SQLTRACE_BUFFER_FLUSH')
insert into #wt values('LOGMGR_QUEUE')
insert into #wt values('ONDEMAND_TASK_QUEUE')
insert into #wt values('REQUEST_FOR_DEADLOCK_SEARCH')
insert into #wt values('CLR_AUTO_EVENT')
insert into #wt values('CLR_MANUAL_EVENT')
insert into #wt values('OLEDB')
insert into #wt values('BROKER_EVENTHANDLER')
insert into #wt values('BROKER_TRANSMITTER')
insert into #wt values('BROKER_TASK_STOP')
insert into #wt values('BROKER_RECEIVE_WAITFOR')

select top 10 wait_type,
       cast(wait_time_ms / 1000. as decimal(12, 2)) wait_time_s,
       cast(100. * wait_time_ms /
               (select sum(wait_time_ms)
                from sys.dm_os_wait_stats
                where wait_type not in
                        (select types from #wt)) as decimal(12, 2)) pct,
       case when waiting_tasks_count > 0
            then (wait_time_ms - signal_wait_time_ms) / waiting_tasks_count
            else 0 end avg_wait_ms 
from sys.dm_os_wait_stats 
where wait_type not in (select types from #wt)
order by 3 desc 

drop table #wt

set nocount on;
create table #wt (types varchar(30))

-- Add any wait types you want to ignore here. 'Total' MUST be ignored.
insert into #wt values('Total')
insert into #wt values('LAZYWRITER_SLEEP')
insert into #wt values('SLEEP')
insert into #wt values('OLEDB')
insert into #wt values('RESOURCE_QUEUE')

create table #ws (wait_type varchar(255),
                  requests decimal(20, 0),
                  wait_time_ms decimal(20, 0),
                  signal_wait_time decimal(20, 0))

insert #ws exec ('dbcc sqlperf(waitstats) with no_infomsgs') 

select top 10 wait_type,
       cast(wait_time_ms / 1000. as decimal(12, 2)) wait_time_s,
       cast(100. * wait_time_ms /
               (select sum(wait_time_ms)
                from #ws
                where wait_type not in
                        (select types from #wt)) as decimal(12, 2)) pct, 
       case when requests > 0
            then cast((wait_time_ms - signal_wait_time) / requests as int)
            else 0 end avg_wait_ms 
from #ws 
where wait_type not in (select types from #wt) 
order by 3 desc

drop table #ws 
drop table #wt