Tuesday, December 27, 2011

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) /) )
        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

Friday, December 16, 2011

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



Monday, December 5, 2011

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 >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

Friday, November 25, 2011

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




Thursday, November 3, 2011

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