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