SQL Server DBA - Most useful script

----prep-----------------------
declare @tsql nvarchar(max)
declare @dbname sysname
declare @latencyThreshold int
declare @oldDate datetime
declare @ignoreheap int
declare @modcounter int
declare @tablename sysname
declare @inclSysTables int
declare @ProductVersion nvarchar(20)
declare @memThreshold int
declare @DrivePctThreshold numeric (16,2)
declare @ProductLevel table (partkey int identity(1,1),parts int)
declare @runMemoryCheck bit,@runBlockingCheck bit, @runParrallelCheck bit, @runErrorLogCheck bit,
  @runDiskSpaceCheck bit, @runStatsLastUpdatedCheck bit, @runStatsModCounterCheck bit,
  @runIndexFragCheck bit, @runCPUCheck bit, @runAvgCPUCheck bit, 
  @runAvgIOCheck bit, @runAvgLogicalIOReadCheck bit,@runAvgLogicalIOWriteCheck bit,
  @runWaitTypesCheck bit, @runLatencyCheck bit, @runLongRunQueryCheck bit,
  @runOpenTran1Check bit, @runOpenTran2Check bit, @runOpenTran3Check bit, @runLockCheck bit,
  @runCurRunnableTopQueriesByCPU bit

--select @runMemoryCheck = 1,@runBlockingCheck = 1, @runParrallelCheck = 1, @runErrorLogCheck = 1,
--  @runDiskSpaceCheck = 1, @runStatsLastUpdatedCheck = 1, @runStatsModCounterCheck = 1,
--  @runIndexFragCheck = 1, @runCPUCheck = 1, @runAvgCPUCheck = 1, 
--  @runAvgIOCheck = 1, @runAvgLogicalIOReadCheck = 1,@runAvgLogicalIOWriteCheck =1,
--  @runWaitTypesCheck = 1, @runLatencyCheck = 1, @runLongRunQueryCheck = 1,
--  @runOpenTran1Check = 1, @runOpenTran2Check = 1, @runOpenTran3Check = 1, @runLockCheck = 1,
--  @runCurRunnableTopQueriesByCPU = 1

select @runMemoryCheck = 1,@runBlockingCheck = 1, @runParrallelCheck = 1, @runErrorLogCheck = 0,
  @runDiskSpaceCheck = 0, @runStatsLastUpdatedCheck = 0, @runStatsModCounterCheck = 0,
  @runIndexFragCheck = 0, @runCPUCheck = 0, @runAVGCPUCheck = 0, 
  @runAvgIOCheck = 0, @runAvgLogicalIOReadCheck = 0,@runAvgLogicalIOWriteCheck =0,
  @runWaitTypesCheck = 0, @runLatencyCheck = 0, @runLongRunQueryCheck = 0,
  @runOpenTran1Check = 0, @runOpenTran2Check = 0, @runOpenTran3Check = 0, @runLockCheck = 0,
  @runCurRunnableTopQueriesByCPU = 0


DECLARE
 @CPU_BUSY int
 , @IDLE int, @CPUBusyPct decimal(16,4), @CPUThreshold int

set @memThreshold = 1000.00 --- mb when get warning
set @DrivePctThreshold = 10.00 --- percent free on drive threshold
set @CPUThreshold = 90.00 -- Percentage cpu usage
set @latencyThreshold = 1 --- used for latency check  returns where latency column value greater than this number 
set @dbname = null -- 'master'  ---- can be null or specify database name (for stats check)
set @oldDate = dateadd(day, -0, getdate())
set @ignoreheap = 1 -- ignore heap  -- forstats check 1
set @modcounter = 10000  -- for stats check Total number of modifications for the leading statistics column (the column on which the histogram is built) since the last time statistics were updated
set @tablename = null --- name of specific table  -- for stats check
set @inclSysTables = 0 --- 0 for no, 1 for yes  -- for stats check
select @ProductVersion= cast(serverproperty('ProductVersion') as nvarchar(20))
 
declare @StringList VARCHAR(MAX),  @Delimiter CHAR(1)

set @StringList = @ProductVersion
set @Delimiter = '.'

DECLARE @XML xml
SET @XML = '<root><csv>'+replace(@StringList,@Delimiter,'</csv><csv>')+
   '</csv></root>'
insert into @ProductLevel
(parts) 
SELECT cast(rtrim(ltrim(replace(Word.value('.','nvarchar(128)'),char(10),'')))  as int)
  AS ListMember 
FROM @XML.nodes('/root/csv') AS WordList(Word)

-------------------------------
--- check 0 - memlor
-------------------------------
if @runMemoryCheck = 1
begin
 if exists(
 select 1 from @ProductLevel PL2k12
 where (partkey = 1 and parts >=11)
 union all
 select 1 from @ProductLevel t1
 --cross apply @ProductLevel t2
 --cross apply @ProductLevel t3
 where (t1.partkey = 1 and t1.parts >=10)
 --and t2.partkey = 2 and t2.parts >= 50
 --and t3.partkey = 3 and t3.parts >=4000
 )
 begin
  select 'Memory Check' as CheckDesc,
   case when (available_physical_memory_kb/1024.0) < @memThreshold then 
    'Warning - Low Memory'
   else
    'None'
   end as Warning,
   total_physical_memory_kb /1024.0 as TotalPhysicalMB,
   (total_physical_memory_kb /1024.0)/1024.0 as TotalPhysicalGB,
   available_physical_memory_kb/1024.0 as AvailablePhysicalMB,
   (available_physical_memory_kb/1024.0) /1024.0 as AvailablePhysicalGB,
   total_physical_memory_kb /1024.0 -
   available_physical_memory_kb/1024.0 as usedMB ,
   (total_physical_memory_kb /1024.0 -
   available_physical_memory_kb/1024.0) /1024.0 as usedGB
    from sys.dm_os_sys_memory
 end
 else
 begin
  select 'Memory Check' as CheckDesc,
   'Not Supported' as Warning,
   0.00 TotalPhysicalMB,
   0.00 as TotalPhysicalGB,
   0.00 as AvailablePhysicalMB,
   0.00 as AvailablePhysicalGB,
   0.00 as usedMB ,
   0.00 as usedGB
 end
end
-------------------------------
--- check 1 -> blocking
-------------------------------
if @runBlockingCheck = 1
begin
 select 'Blocking Processes Check' as CheckDesc,
 DB_NAME(sp1.dbid) as DBName,
 sp2.spid,st2.text as blockedtext,sp1.spid,
 st.text,
 SUBSTRING(st.text, (sp1.stmt_start/2)+1, 
   ((CASE sp1.stmt_end
     WHEN -1 THEN DATALENGTH(st.text)
    ELSE sp1.stmt_end
    END - sp1.stmt_start)/2) + 1) AS statement_text,
  sp1.spid,sp1.lastwaittype, sp1.waitresource,sp1.dbid,
 sp1.loginame
 ,(select s_eqp.query_plan from sys.dm_exec_requests [s_er]  with(nolock)
  OUTER APPLY 
  sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]  
 where s_er.session_id = sp1.spid) as BlockingPlan
 ,(select s_eqp2.query_plan from sys.dm_exec_requests [s_er2]  with(nolock)
  OUTER APPLY 
  sys.dm_exec_query_plan ([s_er2].[plan_handle]) AS [s_eqp2]  
 where s_er2.session_id = sp2.spid) as BlockedPlan
 --,(select [query_plan] AS [Last Plan]  from sys.dm_exec_query_plan ([st2].[plan_handle]) AS [s_eqp] )
 from master..sysprocesses sp1 with(nolock)
  inner join master..sysprocesses sp2  with(nolock)
   on sp2.blocked = sp1.spid
  and sp1.spid <> sp2.spid
 CROSS APPLY sys.dm_exec_sql_text(sp1.sql_handle) AS st  
 CROSS APPLY sys.dm_exec_sql_text(sp2.sql_handle) AS st2 
 where sp1.blocked = 0
end
-------------------------------
--- check 2 -> Parrallelism
-------------------------------
if @runParrallelCheck = 1
begin
 select 'Parrallel Processes Check' as CheckDesc,
 db_name(sp1.dbid) as DBName,
 (select COUNT(spid) from master..sysprocesses  with(nolock) where spid = sp1.spid) as SPIDCount,
 st.text,
 SUBSTRING(st.text, (sp1.stmt_start/2)+1, 
   ((CASE sp1.stmt_end
     WHEN -1 THEN DATALENGTH(st.text)
    ELSE sp1.stmt_end
    END - sp1.stmt_start)/2) + 1) AS statement_text,
  sp1.spid,sp1.lastwaittype, sp1.waitresource,sp1.dbid,
 sp1.loginame
 ,(select s_eqp.query_plan from sys.dm_exec_requests [s_er]  with(nolock)
  OUTER APPLY 
  sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]  
 where s_er.session_id = sp1.spid) as ParrallelQueryPlan
 from master..sysprocesses sp1  with(nolock)
 CROSS APPLY sys.dm_exec_sql_text(sp1.sql_handle) AS st  
 where exists (select spid, COUNT(spid) as c from master..sysprocesses  with(nolock) where spid = sp1.spid
   group by spid having COUNT(spid) > 1)
end
-------------------------------
--- check 3 - errors in log
-------------------------------
if @runErrorLogCheck = 1
begin
  
 declare @errLog table (LogDate datetime, ProcessInfo nvarchar(max), ErrorLogRawText nvarchar(max))

 insert into @errLog
 execute xp_readerrorlog

 select 'ErrorLog Check'  as CheckDesc, * from @errLog where ErrorLogRawText like '%error%' and ErrorLogRawText not like 'Logging SQL Server messages in file%'
 and ErrorLogRawText not like 'The error log has been reinitialized.%'
end
-------------------------------
--- check 4 - disk space
-------------------------------
if @runDiskSpaceCheck = 1
begin
 if OBJECT_ID('tempdb..#DriveSpace') is not null
 begin
  drop table #DriveSpace
 end
 if OBJECT_ID('tempdb..#DriveInfo') is not null
 begin
  drop table #DriveInfo
 end
 DECLARE 
  @FSOobjResult INT
  ,@DriveResult INT
  ,@objFSO INT
  ,@Drv INT
  ,@cDrive VARCHAR(13)
  ,@Size VARCHAR(50)
  ,@Free VARCHAR(50)
  ,@Label VARCHAR(50)
  ,@DriveLetter CHAR(1)

 CREATE TABLE #DriveInfo
 (
  DriveLetter CHAR(1)
  ,VolumeName VARCHAR(50)
  ,TotalSpace BIGINT
  ,FreeSpace BIGINT 
 )
 CREATE TABLE #DriveSpace  
 (
  DriveLetter CHAR(1) NOT NULL
  ,FreeSpace VARCHAR(10) NOT NULL
 )

 INSERT INTO #DriveSpace
    EXEC master..xp_FixedDrives

 
 while exists (select top 1 1 from #DriveSpace)
 begin
  set @DriveLetter = (select top 1 DriveLetter from #DriveSpace)
  SET @cDrive = 'GetDrive("' + @DriveLetter + '")' 
    EXEC @FSOobjResult = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT 
    
    IF @FSOobjResult = 0
     BEGIN
      EXEC @DriveResult = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT 
      IF @DriveResult = 0  
       BEGIN
        EXEC sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT
        EXEC sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT 
        EXEC sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT  

        SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )
        SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )
        
        INSERT INTO #DriveInfo
        VALUES (@DriveLetter, @Label, @Size, @Free)

        EXEC sp_OADestroy @Drv 
       END
      ELSE
       BEGIN
        PRINT 'Drive info not available for Drive ' + @DriveLetter
        EXEC sp_OADestroy @Drv 
       END
      EXEC sp_OADestroy @objFSO 
     END
    ELSE
     BEGIN
      PRINT 'File System Object info not available for Drive ' + @DriveLetter
      EXEC sp_OADestroy @objFSO 
     END
  delete from #DriveSpace where DriveLetter = @DriveLetter
 end

 SELECT 'Drive Space Check' as CheckDesc,
  UPPER(@@SERVERNAME) AS ServerName
  ,VolumeName
  ,DriveLetter
  ,FreeSpace AS [FreeSpaceMB]
  ,(TotalSpace - FreeSpace) AS [UsedSpaceMB]
  ,TotalSpace AS [TotalSpaceMB]
  ,((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [DrivePercentageFree]
  ,@DrivePctThreshold as DrivePctThreshold
  ,GETDATE() AS [DateTimeStamp_DTML]
  ,GETUTCDATE() AS [DateTimeStamp_DTMZ]
 FROM #DriveInfo
 where ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) < @DrivePctThreshold
 ORDER BY [DriveLetter] ASC  

 drop table #DriveSpace
 drop table #DriveInfo
end
-------------------------------
--- check 5.1 - statistics last update check 1
-------------------------------
if @runStatsLastUpdatedCheck = 1
begin
 ----declare @dbname sysname
 ----declare @oldDate datetime
 ----declare @ignoreheap int

 ----set @dbname = null -- 'master'  ---- can be null or specify database name
 ----set @oldDate = dateadd(day, -1, getdate())
 ----set @ignoreheap = 1 -- ignore heap

 if OBJECT_ID('tempdb..#temp') is not null
 begin
  drop table #temp
 end

 Create Table #temp
 (
  databasename sysname,
  tablename sysname,
  indexname sysname,
  indexdesc nvarchar(100),
  statsupdate datetime
 )   
 Exec sp_msforeachdb '
 Use [?];
 Insert Into #temp (databasename,tablename, indexname, indexdesc, statsupdate)
  select distinct ''[?]'',t.name as tablename
  ,isnull(i.name, ''Heap'') as indexname, i.type_desc as indexdesc, 
  stats_date(i.object_id,i.index_id) as statsupdate
  from [?].sys.indexes i  with(nolock)
  inner join [?].sys.tables t  with(nolock)
   on i.object_id = t.object_id
  inner join [?].sys.partitions p  with(nolock)
   on i.object_id = p.object_id
   and p.rows > 0;
 '

 Select 'Stats Last Update Check' as CheckDesc, replace(replace(databasename, '[', ''), ']', ''), 
 tablename, indexname, indexdesc, statsupdate,
 replace(replace(databasename, '[', ''), ']', '') + '..sp_updatestats ' as ResolutionTip1,
 ' use ' + replace(replace(databasename, '[', ''), ']', '') + '; UPDATE STATISTICS ' + tablename   as ResolutionTip2
 From #temp
 where replace(replace(databasename, '[', ''), ']', '') = isnull(@dbname, replace(replace(databasename, '[', ''), ']', ''))
 and isnull(statsupdate, '1/1/1999') <= isnull(@oldDate, '1/1/1999')
 and( @ignoreheap = 0 or (@ignoreheap = 1 and indexname <> 'Heap'))
 order by replace(replace(databasename, '[', ''), ']', ''), statsupdate desc

 drop table #temp
end

-------------------------------
--- check 5.2 - statistics check 2
---- date/time and num of modifications
-------------------------------
if @runStatsModCounterCheck = 1
begin
  
 --declare @dbname2 sysname
 --declare @modcounter int
 --declare @oldDate2 datetime
 --declare @tablename sysname
 --declare @inclSysTables int

 --set @dbname2 = null -- 'master'  ---- can be null or specify database name
 --set @modcounter = 10000  -- Total number of modifications for the leading statistics column (the column on which the histogram is built) since the last time statistics were updated
 --set @oldDate2 = dateadd(day, -0, getdate())  --- find indexes updated previous to this time
 --set @tablename = null --- name of specific table
 --set @inclSysTables = 0 --- 0 for no, 1 for yes


 if OBJECT_ID('tempdb..#temp2') is not null
 begin
  drop table #temp2
 end

 Create Table #temp2
 (
  databasename sysname,
  tablename sysname,
  tabletype varchar(5),
  statsname sysname,
  statsid int,
  lastupdated datetime,
  modification_counter bigint
 )   

 if exists(
 select 1 from @ProductLevel PL2k12
 where (partkey = 1 and parts >=11)
 union all
 select 1 from @ProductLevel t1
 cross apply @ProductLevel t2
 cross apply @ProductLevel t3
 where (t1.partkey = 1 and t1.parts >=10)
 and t2.partkey = 2 and t2.parts >= 50
 and t3.partkey = 3 and t3.parts >=4000
 )
 begin
  Exec sp_msforeachdb '
  Use [?];
  Insert Into #temp2 (databasename,tablename, tabletype,statsname, statsid, lastupdated, modification_counter)
   select distinct ''[?]'',obj.name, obj.type,stat.name, stat.stats_id, last_updated, modification_counter
  FROM [?].sys.objects AS obj  with(nolock)
  JOIN [?].sys.stats stat  with(nolock)
   ON stat.object_id = obj.object_id
  CROSS APPLY [?].sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp 
  order by modification_counter desc;
  '
 end
 else
 begin
  Insert Into #temp2 (databasename,tablename, tabletype,statsname, statsid, lastupdated, modification_counter)
   select distinct 'NotSupported','NotSupported', '','',0,'1/1/1999',99999999
 end

 Select 'Stats ModCounter Check' as CheckDesc, replace(replace(databasename, '[', ''), ']', ''), 
 tablename, tabletype,statsname, statsid,modification_counter, lastupdated,
 replace(replace(databasename, '[', ''), ']', '') + '..sp_updatestats ' as ResolutionTip1,
 ' use ' + replace(replace(databasename, '[', ''), ']', '') + '; UPDATE STATISTICS ' + tablename   as ResolutionTip2
 From #temp2
 where replace(replace(databasename, '[', ''), ']', '') = isnull(@dbname, replace(replace(databasename, '[', ''), ']', ''))
 and isnull(lastupdated, '1/1/1999') <= isnull(@oldDate, '1/1/1999')
 and modification_counter >= @modcounter
 and tablename = isnull(@tablename, tablename)
 and (@inclSysTables = 1 or (@inclSysTables = 0 and tabletype <> 'S'))
 order by replace(replace(databasename, '[', ''), ']', ''), lastupdated desc

 drop table #temp2
end
-------------------------------
--- check 6 - index fragmentation
-------------------------------
if @runIndexFragCheck = 1
begin
 select 'Index Fragmentation Check' as CheckDesc, 'Still need to write this part' as Notes
end
-------------------------------
--- check 7 - CPU
-------------------------------
if @runCPUCheck = 1
begin

 SELECT
  @CPU_BUSY = @@CPU_BUSY
  , @IDLE = @@IDLE
  
 WAITFOR DELAY '000:00:01'
  
 set @CPUBusyPct = (
 SELECT
  (@@CPU_BUSY - @CPU_BUSY)/((@@IDLE - @IDLE + @@CPU_BUSY - @CPU_BUSY) *
  1.00) *100)-- AS CPUBusyPct

 if @CPUBusyPct > @CPUThreshold--90.00
 begin
  select 'CPU Check' as CheckDesc, 'CPU Usage High' as CPUResponse, @CPUThreshold as Threshold, @CPUBusyPct as CPU
 end
 else
 begin
  select 'CPU Check' as CheckDesc, 'CPU Usage Ok' as CPUResponse, @CPUThreshold as Threshold, @CPUBusyPct as CPU
 end
end
-------------------------------
--- check 8 - logical IO
-------------------------------
if @runAvgIOCheck = 1
begin
 set @tsql = N'begin try
 select top 100 ''Top 100 Avg IO Check UserDB(s)'' as CheckDesc
 ,  rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ) as row_no
 ,       (rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ))%2 as l1
 ,       creation_time
 ,       last_execution_time
 ,       (total_worker_time+0.0)/1000 as total_worker_time
 ,       (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
 ,       total_logical_reads as [LogicalReads]
 ,       total_logical_writes as [LogicalWrites]
 ,       (total_logical_reads+0.0)/execution_count as [AvgLogicalReads]
 ,       (total_logical_writes+0.0)/execution_count as [AvgLogicalWrites]
 ,       execution_count
 ,       total_logical_reads+total_logical_writes as [AggIO]
 ,       (total_logical_reads+total_logical_writes+0.0)/execution_count as [AvgIO]
 ,       case when sql_handle IS NULL
     then '' ''
     else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1        then len(convert(nvarchar(MAX),st.text))*2      else qs.statement_end_offset    end - qs.statement_start_offset) /2  ))
   end as query_text 
 ,       db_name(st.dbid) as database_name
 ,       st.objectid as object_id
 from sys.dm_exec_query_stats  qs
 cross apply sys.dm_exec_sql_text(sql_handle) st
 where (total_logical_reads+total_logical_writes ) > 0
 and st.dbid > 4
 '
 if @dbname is not null
 begin
  set @tsql = @tsql + ' and db_name(st.dbid) = ''' + @dbname + ''''
 end
 set @tsql = @tsql +  '
 order by [AvgIO] desc
 end try
 begin catch
 select -100 AS row_no
 ,       1 AS l1, 1 AS creation_time, 1 AS last_execution_time,  1 AS total_worker_time,  1 AS AvgCPUTime, 1 AS logicalReads,  1 AS LogicalWrites
 ,       ERROR_NUMBER() AS execution_count
 ,       ERROR_SEVERITY() AS AggIO
 ,       ERROR_STATE() AS AvgIO
 ,       ERROR_MESSAGE() AS query_text 
 end catch'
 exec sp_executesql @stmt=@tsql,@params=N''
end
-------------------------------
--- check 9 - logical IO reads and writes
-------------------------------
if @runAvgLogicalIOReadCheck = 1
begin
 set @tsql = N'begin try
 select top 100 ''Top 100 Avg Logical IO Reads UserDB(s)'' as CheckDesc
 ,  rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ) as row_no
 ,       (rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ))%2 as l1
 ,       creation_time
 ,       last_execution_time
 ,       (total_worker_time+0.0)/1000 as total_worker_time
 ,       (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
 ,       total_logical_reads as [LogicalReads]
 ,       total_logical_writes as [LogicalWrites]
 ,       (total_logical_reads+0.0)/execution_count as [AvgLogicalReads]
 ,       (total_logical_writes+0.0)/execution_count as [AvgLogicalWrites]
 ,       execution_count
 ,       total_logical_reads+total_logical_writes as [AggIO]
 ,       (total_logical_reads+total_logical_writes+0.0)/execution_count as [AvgIO]
 ,       case when sql_handle IS NULL
     then '' ''
     else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1        then len(convert(nvarchar(MAX),st.text))*2      else qs.statement_end_offset    end - qs.statement_start_offset) /2  ))
   end as query_text 
 ,       db_name(st.dbid) as database_name
 ,       st.objectid as object_id
 from sys.dm_exec_query_stats  qs
 cross apply sys.dm_exec_sql_text(sql_handle) st
 where (total_logical_reads+total_logical_writes ) > 0
 and st.dbid > 4
 '
 if @dbname is not null
 begin
  set @tsql = @tsql + ' and db_name(st.dbid) = ''' + @dbname + ''''
 end
 set @tsql = @tsql +  '
 order by [AvgLogicalReads] desc
 end try
 begin catch
 select -100 AS row_no
 ,       1 AS l1, 1 AS creation_time, 1 AS last_execution_time,  1 AS total_worker_time,  1 AS AvgCPUTime, 1 AS logicalReads,  1 AS LogicalWrites
 ,       ERROR_NUMBER() AS execution_count
 ,       ERROR_SEVERITY() AS AggIO
 ,       ERROR_STATE() AS AvgIO
 ,       ERROR_MESSAGE() AS query_text 
 end catch'
 exec sp_executesql @stmt=@tsql,@params=N''
end
if @runAvgLogicalIOWriteCheck = 1
begin
 set @tsql = N'begin try
 select top 100 ''Top 100 Avg Logical IO Writes UserDB(s)'' as CheckDesc
 ,  rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ) as row_no
 ,       (rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ))%2 as l1
 ,       creation_time
 ,       last_execution_time
 ,       (total_worker_time+0.0)/1000 as total_worker_time
 ,       (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
 ,       total_logical_reads as [LogicalReads]
 ,       total_logical_writes as [LogicalWrites]
 ,       (total_logical_reads+0.0)/execution_count as [AvgLogicalReads]
 ,       (total_logical_writes+0.0)/execution_count as [AvgLogicalWrites]
 ,       execution_count
 ,       total_logical_reads+total_logical_writes as [AggIO]
 ,       (total_logical_reads+total_logical_writes+0.0)/execution_count as [AvgIO]
 ,       case when sql_handle IS NULL
     then '' ''
     else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1        then len(convert(nvarchar(MAX),st.text))*2      else qs.statement_end_offset    end - qs.statement_start_offset) /2  ))
   end as query_text 
 ,       db_name(st.dbid) as database_name
 ,       st.objectid as object_id
 from sys.dm_exec_query_stats  qs
 cross apply sys.dm_exec_sql_text(sql_handle) st
 where (total_logical_reads+total_logical_writes ) > 0
 and st.dbid > 4
 '
 if @dbname is not null
 begin
  set @tsql = @tsql + ' and db_name(st.dbid) = ''' + @dbname + ''''
 end
 set @tsql = @tsql +  '
 order by [AvgLogicalWrites] desc
 end try
 begin catch
 select -100 AS row_no
 ,       1 AS l1, 1 AS creation_time, 1 AS last_execution_time,  1 AS total_worker_time,  1 AS AvgCPUTime, 1 AS logicalReads,  1 AS LogicalWrites
 ,       ERROR_NUMBER() AS execution_count
 ,       ERROR_SEVERITY() AS AggIO
 ,       ERROR_STATE() AS AvgIO
 ,       ERROR_MESSAGE() AS query_text 
 end catch'
 exec sp_executesql @stmt=@tsql,@params=N''
end
-------------------------------
--- check 10 - wait types
-------------------------------
if @runWaitTypesCheck = 1
begin
 --------------------------------------------------------------------------------------------
 --- paul randall's query
 ----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'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 'Wait Types Check' as CheckDesc,
   [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 

end
-------------------------------
--- check 11 - latency
-------------------------------
if @runLatencyCheck = 1
begin

 --------------------------------------------------------------------------------------------------------
 --- Paul Randal's script
 --- http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/
 --------------------------------------------------------------------------------------------------------
 SELECT 'Latency Check' as CheckDesc,
  --virtual file latency 
  [ReadLatency] = 
   CASE WHEN [num_of_reads] = 0 
    THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END, 
  [WriteLatency] = 
   CASE WHEN [num_of_writes] = 0 
    THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END, 
  [Latency] = 
   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 
  [AvgBPerRead] = 
   CASE WHEN [num_of_reads] = 0 
    THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END, 
  [AvgBPerWrite] = 
   CASE WHEN [io_stall_write_ms] = 0 
    THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END, 
  [AvgBPerTransfer] = 
   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], 
   --[vfs].*, 
  [mf].[physical_name] 
  FROM 
   sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]   
  JOIN sys.master_files AS [mf]   with(nolock)
    ON [vfs].[database_id] = [mf].[database_id] 
  AND [vfs].[file_id] = [mf].[file_id] 
 -- WHERE [vfs].[file_id] = 2 -- log files 
 where  DB_NAME ([vfs].[database_id]) = isnull(@dbname, DB_NAME ([vfs].[database_id]))
 and CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) 
    THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END > @latencyThreshold
  ORDER BY [Latency] DESC 
 -- ORDER BY [ReadLatency] DESC 
 --ORDER BY [WriteLatency] DESC; 
end
-------------------------------
--- check 12 - long running queries
-------------------------------
if @runLongRunQueryCheck = 1
begin
 select 
 TOP 50 'Long Running Query Check' as CheckDesc,
  SUBSTRING(t.text, (s.statement_start_offset/2)+1, 
    ((CASE s.statement_end_offset
      WHEN -1 THEN DATALENGTH(t.text)
     ELSE s.statement_end_offset
     END - s.statement_start_offset)/2) + 1) AS statement_text,
 s.max_elapsed_time AS MaxElapsedTime,
  --s.max_rows,
  p.query_plan,OBJECT_NAME(p.objectid) as objectname
 FROM sys.dm_exec_query_stats s
 CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
 OUTER APPLY 
   sys.dm_exec_query_plan (s.[plan_handle]) AS p
 ORDER BY
 s.max_elapsed_time DESC
end
-------------------------------
--- check 13 - open transactions
-------------------------------
if @runOpenTran1Check = 1
begin
 ----- version 1
 select 'Open Transaction 1 Check' as CheckDesc,
 db_name(sp1.dbid) as DBName,
 st.text,
 SUBSTRING(st.text, (sp1.stmt_start/2)+1, 
   ((CASE sp1.stmt_end
     WHEN -1 THEN DATALENGTH(st.text)
    ELSE sp1.stmt_end
    END - sp1.stmt_start)/2) + 1) AS statement_text,
  sp1.spid,sp1.lastwaittype, sp1.waitresource,sp1.dbid,
 sp1.loginame, sp1.open_tran, sp1.waittime, sp1.waitresource, 
 sp1.lastwaittype, sp1.login_time,sp1.last_batch
 from master..sysprocesses sp1  with(nolock)
 CROSS APPLY sys.dm_exec_sql_text(sp1.sql_handle) AS st  
 where open_tran > 0
 and db_name(sp1.dbid) = isnull(@dbname,db_name(sp1.dbid))
end
----- version 2
if @runOpenTran2Check = 1
begin
 select 'Open Transaction 2 Check', 'Still need to write this part for dbcc opentran' as Notes --- dbcc opentran
end

----- version 3
if @runOpenTran3Check = 1
begin

 --  from paul randal 
 ---http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/

 SELECT 'Open Transaction 3 Check' as CheckDesc,
  [s_tst].[session_id], 
  s_es.host_name,
  [s_es].[login_name] AS [Login Name], 
  DB_NAME (s_tdt.database_id) AS [Database], 
  [s_tdt].[database_transaction_begin_time] AS [Begin Time], 
  [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes], 
 [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd], 
  [s_est].text AS [Last T-SQL Text], 
 [s_eqp].[query_plan] AS [Last Plan] 
 FROM 
 sys.dm_tran_database_transactions [s_tdt]  with(nolock)
 JOIN 
 sys.dm_tran_session_transactions [s_tst]  with(nolock)
 ON 
 [s_tst].[transaction_id] = [s_tdt].[transaction_id] 
 JOIN 
 sys.[dm_exec_sessions] [s_es]  with(nolock)
 ON 
 [s_es].[session_id] = [s_tst].[session_id] 
 JOIN 
 sys.dm_exec_connections [s_ec]  with(nolock)
 ON 
  [s_ec].[session_id] = [s_tst].[session_id] 
 LEFT OUTER JOIN 
 sys.dm_exec_requests [s_er]  with(nolock)
 ON 
  [s_er].[session_id] = [s_tst].[session_id] 
 CROSS APPLY 
 sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]  
  OUTER APPLY 
  sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]  
 where
   DB_NAME (s_tdt.database_id) = isnull(@dbname,DB_NAME (s_tdt.database_id))
  ORDER BY [s_tst].[session_id],
  [Begin Time] ASC 
end
-------------------------------
----- check 14 locks ----------
-------------------------------
if @runLockCheck = 1 
begin

 select request_session_id,db_name(resource_database_id), 
  case request_mode
  when 'S' then 'Shared'
  when 'U' then 'Update'
  when 'X' then 'Exclusive'
  when 'IS' then 'Intent Shared'
  when 'IX' then 'Intent Exclusive'
  when 'SIX' then 'Shared with Intent Exclusive'
  when 'IU' then 'Intent Update'
  when 'SIU' then 'Shared intent Update'
  when 'UIX' then 'Update Intent Exclusive'
  when 'Sch-M' then 'Schema Modification'
  when 'Sch-S' then 'Schema Stability'
  when 'BU' then 'Bulk Update'
  else request_mode end as TypeofLock,
  request_type,request_status,
  case resource_type
  when 'database' then 'Lock at database level'
  when 'object' then OBJECT_NAME(resource_associated_entity_id, resource_database_id)
  else resource_type
  end as TheResource,
  resource_subtype,
  Request_Owner_Type
 from sys.dm_tran_locks with(nolock)
 where db_name(resource_database_id) = ISNULL(@dbname, db_name(resource_database_id))
 order by request_session_id
 --order by db_name(resource_database_id),request_session_id

end
-----------------------------------------------
----- check 15 top 100 avg cpu check ----------
-----------------------------------------------
if @runAVGCPUCheck = 1 
begin
 set @tsql = N'begin try
 select top 100 ''Top 100 Avg CPU Check For UserDB(s)'' as CheckDesc
 ,  rank() over(order by (total_worker_time+0.0)/execution_count desc,sql_handle,statement_start_offset ) as row_no
 ,       (rank() over(order by (total_worker_time+0.0)/execution_count desc,sql_handle,statement_start_offset ))%2 as l1
 ,       creation_time
 ,       last_execution_time 
 ,       (total_worker_time+0.0)/1000 as total_worker_time
 ,       (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
 ,       total_logical_reads as [LogicalReads]
 ,       total_logical_writes as [LogicalWrites]
 ,       execution_count
 ,       total_logical_reads+total_logical_writes as [AggIO]
 ,       (total_logical_reads+total_logical_writes)/(execution_count+0.0) as [AvgIO]
 ,       case when sql_handle IS NULL
     then '' ''
     else ( substring(st.text,(qs.statement_start_offset+2)/2,       (case when qs.statement_end_offset = -1         then len(convert(nvarchar(MAX),st.text))*2      else qs.statement_end_offset    end - qs.statement_start_offset) /2  ) )
   end as query_text 
 ,       db_name(st.dbid) as db_name
 ,       st.objectid as object_id
 from sys.dm_exec_query_stats  qs
 cross apply sys.dm_exec_sql_text(sql_handle) st
 where total_worker_time  > 0 
 and st.dbid > 4 '
 if @dbname is not null
 begin
  set @tsql = @tsql + ' and db_name(st.dbid) = ''' + @dbname + ''''
 end
 set @tsql = @tsql +  ' order by [AvgCPUTime] desc
 end try
 begin catch
 select -100 as row_no
 ,       1 as l1, 1 as create_time,1 as last_execution_time,1 as total_worker_time,1 as AvgCPUTime,1 as LogicalReads,1 as LogicalWrites
 ,       ERROR_NUMBER() as execution_count
 ,       ERROR_SEVERITY() as AggIO
 ,       ERROR_STATE() as AvgIO
 ,       ERROR_MESSAGE() as query_text
 ,       0 as db_name
 ,       0 as object_name
 end catch'
 exec sp_executesql @stmt=@tsql,@params=N''
end

-----------------------------------------------
----- check 16 runnable top queries ----------
-----------------------------------------------
if @runCurRunnableTopQueriesByCPU = 1 
begin
 select 'Runnable Queries by CPU' as CheckDesc,
 sp1.spid,
 DB_NAME(sp1.dbid) as DatabaseName,
 st.text,
 SUBSTRING(st.text, (sp1.stmt_start/2)+1, 
   ((CASE sp1.stmt_end
     WHEN -1 THEN DATALENGTH(st.text)
    ELSE sp1.stmt_end
    END - sp1.stmt_start)/2) + 1) AS statement_text,
  sp1.spid,sp1.lastwaittype, sp1.waitresource,sp1.dbid,
 sp1.loginame
 from master..sysprocesses sp1
 CROSS APPLY sys.dm_exec_sql_text(sp1.sql_handle) AS st
 where sp1.status = 'runnable'
 and sp1.spid <> @@spid
 and DB_NAME(sp1.dbid) = isnull(@dbname, DB_NAME(sp1.dbid))
 order by cpu desc
end