Get Top queries by CPU Time

This script will help you to identify the TOP X heaviest statements executed on the SQL Server since the last time the service was started. The result set returns CPU Times, Logical reads and writes, as well as I/O stats.


It is very useful when you need to start to troubleshoot a performance issue.

USE master

GO

IF (object_id( 'tempdb..#TMP_T1' ) IS NOT NULL) DROP TABLE #TMP_T1 ;

select top 10 rank() over(order by total_worker_time desc,sql_handle,statement_start_offset) as row_no

,       (rank() over(order by total_worker_time 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 DBName

,       st.objectid

,             cast(NULL as varchar(255)) as ObjectName

INTO #TMP_T1

from sys.dm_exec_query_stats  qs

cross apply sys.dm_exec_sql_text(sql_handle) st

where total_worker_time >0

order by total_worker_time  desc

GO

DECLARE curObj CURSOR

       FOR Select Distinct DBName, objectid from #TMP_T1

Declare @Cmd varchar(max)

Declare @DBName sysname

Declare @OBJID int

OPEN curObj

WHILE (1=1)

BEGIN

       FETCH NEXT FROM curObj INTO  @DBName, @OBJID

       IF (@@fetch_status <> 0)

              break

       Set @Cmd = 'Update T set ObjectName = O.name from #TMP_T1 T inner join '+@DBName + '.dbo.sysobjects O (nolock) on O.ID = T.objectid where t.objectid = '+cast(@OBJID as varchar(15))

       --print @Cmd

       exec ( @Cmd )

END

CLOSE curObj

DEALLOCATE curObj

go

select * from #TMP_T1

GO

Find missing indexes

This script will help you to identify all the missing indexes indexes detected by the database engine since the last time the service was started. it provides the equality and inequality columns for the proposed index as well as the included ones.

select

-- mid.database_id,

-- mid.object_id,

mid.equality_columns,

mid.inequality_columns,

mid.included_columns,

mid.statement,

migs.unique_compiles,

migs.user_seeks,

-- migs.user_scans,

migs.last_user_seek,

datediff(mi,migs.last_user_seek,getdate()) as [Min_since_last_U_Seek], --Amount of minutes since the last user seek

--migs.last_user_scan,

round(migs.avg_total_user_cost,2) as avg_total_user_cost,

migs.avg_user_impact,

round(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans),0) as SortOrd --Calculated value to measure the missing index

--select mid.*, migs.*

from sys.dm_db_missing_index_details mid (nolock)

join sys.dm_db_missing_index_groups mig(nolock)

on mid.index_handle = mig.index_handle

join sys.dm_db_missing_index_group_stats migs(nolock)

on mig.index_group_handle = migs.group_handle

--where mid.statement like '%My_Database%'

order by migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

--migs.user_seeks desc,  mid.statement

go

Script to fix Orphaned users after restoring (SQL 2005 , 2008 , 2008 R2)

This is a very useful script I've been using to troubleshoot and fix Orphaned users in sql after restoring databases to a testing environment.


I do restore my production databases to a test server on a daily basis so at the end of the process I run this script. It remaps the orphan database users to the sql logins (the sql logins must be created beforehand) 

use master

go

IF (object_id( 'tempdb..##TMP_ORPHANED_USERS' ) IS NOT NULL) DROP TABLE ##TMP_ORPHANED_USERS ;

CREATE TABLE ##TMP_ORPHANED_USERS(

ServerName sysname

,DatabaseName sysname

,UserName varchar(500)

,UserType varchar(500)

,DefaultSchemaName varchar(500)

,ResolvedID varchar(500)

)

DECLARE @db sysname,

  @cmd varchar(4000)

SET NOCOUNT ON

DECLARE C_databases CURSOR FOR

SELECT name FROM sys.databases (NOLOCK)

WHERE database_id >4 and is_read_only = 0

order by name

OPEN C_Databases

FETCH NEXT FROM C_Databases

INTO @db

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @cmd = 'USE ['+@db+'];INSERT INTO ##TMP_ORPHANED_USERS select @@servername,db_name(),[name],type_desc,Default_Schema_Name,SUSER_SNAME(SID) from sys.database_principals

where type_desc IN (''SQL_USER'',''WINDOWS_USER'',''WINDOWS_GROUP'') and

principal_id >4 and

SID not in (select SID from sys.server_principals)

and [name] <> ''guest'''

 --PRINT @cmd

--PRINT ''

EXEC (@cmd)

FETCH NEXT FROM C_Databases

INTO @db

END

CLOSE C_Databases

DEALLOCATE C_Databases

select * from ##TMP_ORPHANED_USERS

GO

DECLARE @db sysname,

 @user sysname,

  @defaultschema varchar(500),

  @cmd varchar(4000)

SET NOCOUNT ON

DECLARE C_OrpUsers CURSOR FOR

SELECT DatabaseName,UserName,DefaultSchemaName FROM ##TMP_ORPHANED_USERS (NOLOCK)

OPEN C_OrpUsers

FETCH NEXT FROM C_OrpUsers

INTO @db,@user,@defaultschema

WHILE @@FETCH_STATUS = 0

BEGIN

--SELECT @cmd = 'USE '+@db+';IF EXISTS (select * from sys.schemas where name = '''+@user+''')DROP SCHEMA ['+@user+'];DROP USER ['+@user+'];'

 SELECT @cmd = 'USE '+@db+';ALTER USER ['+@user+'] WITH DEFAULT_SCHEMA =['+@defaultschema+'],LOGIN = ['+@user+']'

 BEGIN TRY

  PRINT @cmd

  EXEC (@cmd)

END TRY

BEGIN CATCH

  SELECT

   @db as DatabaseName

   ,@User as UserName

   ,ERROR_NUMBER() AS ErrorNumber

   ,ERROR_SEVERITY() AS ErrorSeverity

   ,ERROR_STATE() AS ErrorState

   ,ERROR_PROCEDURE() AS ErrorProcedure

   ,ERROR_LINE() AS ErrorLine

   ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

FETCH NEXT FROM C_OrpUsers

INTO @db,@user,@defaultschema

END

CLOSE C_OrpUsers

DEALLOCATE C_OrpUsers

IF (object_id( 'tempdb..##TMP_ORPHANED_USERS' ) IS NOT NULL) DROP TABLE ##TMP_ORPHANED_USERS ; 

GO

SQL Waits Stats

Waits are represented by SQL Server wait statistics. SQL Server tracks wait information any time that a user connection or session_id is waiting. This wait information is summarized and categorized across all connections so that a performance profile can be obtained for a given work load. Therefore, SQL Server wait types identify and categorize user (or thread) waits from an application workload or user perspective. 


Queues measure system resources and utilization.  The queues part of performance is represented by Performance Monitor objects and counters and other sources of information. Performance Monitor counters measure various aspects of performance such as transfer rates for disks or the processor time consumed.  SQL Server object counters are exposed to Performance Monitor using the dynamic management view (DMV) sys.dm_os_performance_counters. Thus, Performance Monitor counters show performance from a resource point of view.  


Associations or correlations of wait types to performance counters, and interesting performance counter ratios and comparisons round out the picture.  The association of waits and queues allows one to eliminate irrelevant counters insofar as the performance bottleneck is concerned and focus effectively on the problem area.  Comparisons of one counter to another provide perspective in order to draw the right conclusion.  For example, say you encounter 1000 lock waits during a production workload.  Does this cause a serious performance problem? To determine what role these play in overall performance you need to also consider the total number of lock requests (how many locks are requested overall vs. how many result in lock waits), the wait time or duration of the lock waits, and the time span of the test.  If the total number of lock requests was 10 million, perhaps 1000 waits is insignificant.  Further, if the associated lock wait time is 50 seconds overall, and the workload is over 8 hours, this too is insignificant.  On the other hand, if you average a 50 second wait for EACH of the 1000 lock waits, this IS significant.  In sum, associations or correlations allow us to determine relevancy to overall performance.


Application performance can be easily explained by looking at SQL Server waits and System or Resource queues. In SQL Server the dynamic management view (DMV) sys.dm_os_wait_stats provides a valuable source of wait information from an application perspective. The system or resource perspective uses Performance Monitor counters and other data sources to provide a breakdown of system resource usage according to resource queues. Taken together, the value of the application and resource perspectives used by the waits and queues methodology enables us to narrow in on the bottleneck and eliminate the irrelevant performance data.

--Script

-- Isolate top waits for server instance since last restart or statistics clear

-- Clear Wait Stats

-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

SET NOCOUNT ON

select @@servername as servername,*,CASE wait_time_ms WHEN 0 THEN 0 ELSE wait_time_ms/waiting_tasks_count END as wait_time_avg_ms

from

      sys.dm_os_wait_stats (NOLOCK)

where

      wait_type in ('WRITELOG','SOS_SCHEDULER_YIELD','CXPACKET','LOGBUFFER','PAGEIOLATCH_SH','CMEMTHREAD') OR wait_type like 'RESOURCE_SEMAPHORE%' or  wait_type like '%NETWORK%'

ORDER BY waiting_tasks_count desc;

/*

select  text,query_plan,requested_memory_kb,granted_memory_kb,used_memory_kb

from sys.dm_exec_query_memory_grants MG

cross apply sys.dm_exec_sql_text(sql_handle) t

cross apply sys.dm_exec_query_plan(MG.plan_handle)

*/

WITH Waits AS

(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

FROM sys.dm_os_wait_stats

WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'

,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'

,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'

,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'

,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))

SELECT W1.wait_type,

CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1

INNER JOIN Waits AS W2

ON W2.rn <= W1.rn

GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

-- Common Significant Wait types with BOL explanations

-- *** Network Related Waits ***

-- ASYNC_NETWORK_IO        Occurs on network writes when the task is blocked behind the network

-- *** Locking Waits ***

-- LCK_M_IX                Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock

-- LCK_M_IU                Occurs when a task is waiting to acquire an Intent Update (IU) lock

-- LCK_M_S                Occurs when a task is waiting to acquire a Shared lock

-- *** I/O Related Waits ***

-- ASYNC_IO_COMPLETION  Occurs when a task is waiting for I/Os to finish

-- IO_COMPLETION        Occurs while waiting for I/O operations to complete.

--                      This wait type generally represents non-data page I/Os. Data page I/O completion waits appear

--                      as PAGEIOLATCH_* waits

-- PAGEIOLATCH_SH        Occurs when a task is waiting on a latch for a buffer that is in an I/O request.

--                      The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.

-- PAGEIOLATCH_EX        Occurs when a task is waiting on a latch for a buffer that is in an I/O request.

--                      The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.

-- WRITELOG             Occurs while waiting for a log flush to complete.

--                      Common operations that cause log flushes are checkpoints and transaction commits.

-- PAGELATCH_EX            Occurs when a task is waiting on a latch for a buffer that is not in an I/O request.

--                      The latch request is in Exclusive mode.

-- BACKUPIO                Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data

-- *** CPU Related Waits ***

-- SOS_SCHEDULER_YIELD  Occurs when a task voluntarily yields the scheduler for other tasks to execute.

--                      During this wait the task is waiting for its quantum to be renewed.

-- THREADPOOL            Occurs when a task is waiting for a worker to run on.

--                      This can indicate that the maximum worker setting is too low, or that batch executions are taking

--                      unusually long, thus reducing the number of workers available to satisfy other batches.

-- CX_PACKET            Occurs when trying to synchronize the query processor exchange iterator

--                        You may consider lowering the degree of parallelism if contention on this wait type becomes a problem

Get CPU usage history

This script helps you to easily check the CPU usage of your SQL Server. You can get SQL CPU usage , Other processes CPU usage(Non-SQL) and the total usage for the previous 2 hours. Helpful to understand the usage patterns as well as to create a baseline.

-- CPU Usage History

SET NOCOUNT ON

declare @ts_now bigint

--SELECT @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info -- (Use this line if server is SQL 2005)

SELECT @ts_now = ms_ticks from sys.dm_os_sys_info -- (Use this line if server is SQL 2008 or above)

select /* top 1 */ record_id,

dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,

SQLProcessUtilization,

--SystemIdle,

100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization,

SQLProcessUtilization + (100 - SystemIdle - SQLProcessUtilization) as CPU_Usage

from (

select

record.value('(./Record/@id)[1]', 'int') as record_id,

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,

timestamp

from (

select timestamp, convert(xml, record) as record

from sys.dm_os_ring_buffers

where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

and record like '%<SystemHealth>%') as x

) as y

order by record_id desc

GO