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