Automated Trace job

USE [msdb]
GO

/****** Object:  Job [Automated Trace - 1 Minute (ad-hoc)]    Script Date: 5/28/2014 1:13:23 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [SQL Vision]    Script Date: 5/28/2014 1:13:23 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'SQL Vision' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'SQL Vision'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Automated Trace - 1 Minute (ad-hoc)',
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'SQL Vision',
@owner_login_name=N'sa',
@notify_email_operator_name=N'Team_DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Run Trace]    Script Date: 5/28/2014 1:13:24 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run Trace',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'-- Variables
declare @rc int, @TraceID int, @filename NVARCHAR(256), @maxsize BIGINT, @on BIT
DECLARE @Folder VARCHAR(1000)


-- Check for other running traces
SELECT @rc = COUNT(DISTINCT traceid) FROM ::fn_trace_getinfo(default)
IF (@rc > 1) goto error


-- Create the holding folder
SELECT TOP 1 @Folder = RTRIM(RootFolderPath) FROM SysAdmin.dbo.tbl_AutoTrace
IF RIGHT(@Folder, 1) <> ''\''
SET @Folder = @Folder + ''\''

SELECT @Folder = @Folder +
                @@SERVERNAME + ''\'' +
''output_'' +
CONVERT(VARCHAR(8), GETDATE(), 112) + ''_'' +
LEFT(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), '':'', ''''), 4)

exec master.dbo.xp_create_subdir @Folder

SELECT @filename = @Folder + ''\'' + REPLACE(@@SERVERNAME, ''\'', ''_'') + ''__sp_trace''
SELECT @maxsize=50, @on=1


-- Create a Queue
exec @rc = sp_trace_create @TraceID output, 2, @filename, @maxsize, NULL
if (@rc != 0) goto error


-- Set the events
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 7, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 7, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 10, 2, @on  -- Needed for 2008 RML
exec sp_trace_setevent @TraceID, 12, 2, @on  --  Needed for 2008 RML

--exec sp_trace_setfilter @TraceID, 10, 0, 7, N''SQL Server Profiler - 8deeb35d-8e51-401e-bc3c-2574de5948eb''


-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1


-- Wait 10 min
WAITFOR DELAY ''00:01:00''

-- Stop/Delete the trace

exec sp_trace_setstatus @TraceID, 0
exec sp_trace_setstatus @TraceID, 2

goto finish

error:
select ErrorCode=@rc

finish:',
@database_name=N'SysAdmin',
@output_file_name=N'G:\Traces\1mintrace.log',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Will produce candidate shrink/grow commands based on given

USE tempdb
GO

SET NOCOUNT ON
SET   QUOTED_IDENTIFIER ON

DECLARE @target DECIMAL(5,2)
SET         @target = 20      -- Modify to desired value of free space %

DECLARE @DriveLetter nvarchar(4)
SET @DriveLetter  ='H'


/***************************************************************************
Candidate Commands Utility                     
                                         
Written by: David Paul Giroux                                    
Date: Fall 2008                                                                                      
Purpose: Assist in file size management.  Will produce candidate shrink/grow commands based on given Target                                
Calls: xp_fixeddrives        
Data Modifications: None

User Modifications:
* Modify @target to desired value of free space percent

Known Issue: There is an issue when dealing with smaller values because the
candidate commands use int data type. The candidate command may round to a value equal to the current file size.
Thus a MODIFY FILE statement will fail. Workaround: Add 1 to the candidate command 
                                                     
***************************************************************************/

DECLARE     @cmd varchar(1000)          -- various commands
DECLARE     @counter tinyint            -- Number of databases
DECLARE     @crlf char(2)               -- carriage return line feed
DECLARE     @db sysname                 -- Database Name

SET         @crlf = CHAR(13) + CHAR(10)
SET         @target = (100.0 - @target) * .01

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (
      DBName sysname,
      [FileName] sysname,
      FileType sysname,
      Drive char(1),
      UsedData varchar(25),
      TotalDataSize varchar(25),
      Smallest decimal(10,2)
      )

-- Databases to examine
DECLARE @Databases TABLE  (
      DID tinyint IDENTITY(1,1) primary key,
      db sysname NULL
      )

-- Hold values from xp_fixeddrives
DECLARE @DiskInfo TABLE(
      Drive char(1) primary key,
      MBFree int
      )

-- Gather databases
INSERT @Databases
SELECT      DISTINCT sd.[name]
FROM  sys.master_files mf WITH (NOLOCK)
JOIN  sys.databases sd WITH (NOLOCK)
ON          mf.database_id = sd.database_id
WHERE sd.[state] = 0
AND         sd.is_read_only = 0
AND         sd.is_in_standby = 0
AND         sd.[name] NOT IN ('model', 'tempdb')
AND         mf.[type] = 0
-- to exclude databases that have a full text-catalog offline
AND         sd.database_id NOT IN (
                            SELECT DISTINCT database_id
                            FROM  sys.master_files WITH (NOLOCK)
                            WHERE [state] <> 0)

SET         @counter = SCOPE_IDENTITY()

WHILE @counter > 0
BEGIN
            SELECT      @db = db
            FROM  @Databases
            WHERE DID = @counter   

            SELECT @cmd =

            N'USE [' + @db + N']' +  @crlf +
            N'SET NOCOUNT ON' + @crlf +
            N'SELECT     '+ QUOTENAME(@db, '''') + N',' + @crlf +
            N'[name],' + @crlf +
            N'CASE type ' + @crlf +
            N'     WHEN 0 THEN ''DATA''' + @crlf +
            N'     WHEN 1 THEN ''LOG'''  + @crlf +
            N'     ELSE ''Other''' + @crlf +
            N'END,' + @crlf +
            N'LEFT(physical_name, 1), ' + @crlf +
            N'CAST(FILEPROPERTY ([name], ''SpaceUsed'')/128.0 as varchar(15)),' + @crlf +
            N'CAST([size]/128.0 as varchar(15))' + @crlf +
            N'FROM sys.database_files WITH (NOLOCK)' + @crlf +
            N'WHERE      [state] = 0' + @crlf +
            N'AND        [type] IN (0,1)'

            -- Preliminary results
            INSERT #Results
            (DBName, [FileName], FileType, Drive, UsedData, TotalDataSize)
            EXEC (@cmd)

            SET   @counter = @counter - 1
END

ALTER TABLE #Results
ALTER COLUMN TotalDataSize decimal(10,2)

ALTER TABLE #Results
ALTER COLUMN UsedData decimal(10,2)

UPDATE      #Results
SET         Smallest = UsedData / @target

-- Command determines free space in MB
INSERT INTO @DiskInfo
EXEC master..xp_fixeddrives

---- Final Query
SELECT      upper(DBName) as dbname,
            upper([FileName]) as filename,
            upper(FileType) as filetype,
            upper(r.Drive) as Drive,
            CAST((TotalDataSize)/1024 as decimal(5,2)) AS [Total Size GB],
            CAST((UsedData /1024) as decimal(5,2))  [Used Space GB],
            CAST((TotalDataSize - UsedData)/1024 as decimal(5,2))  [Free Space GB],
            UsedData,
            TotalDataSize - UsedData N'FreeData',
            TotalDataSize,
            CAST(((TotalDataSize - UsedData) / TotalDataSize) * 100 as decimal(5,2)) [%DataFeeSpace],
            d.MBFree N'DiskFreeSpace',
            Smallest N'SmallestForTarget',
            CASE
                    WHEN TotalDataSize > Smallest THEN CAST(TotalDataSize - Smallest as varchar(10)) + N' Decrease'
                    ELSE CAST(Smallest - TotalDataSize as varchar(10)) + N' Increase'
            END N'CandidateResult',
            CASE
                  WHEN Smallest - TotalDataSize > d.MBFree THEN N'Insufficient Disk Space'
                    WHEN TotalDataSize > Smallest
                              THEN N'USE [' + DBName + N'] DBCC SHRINKFILE(' + QUOTENAME([FileName], '''') + N', ' + CAST(CAST(Smallest as int) as varchar(10)) + N')'
                    ELSE  N'ALTER DATABASE [' + DBName + N']' + @crlf +
                                      N'MODIFY FILE (' + @crlf +
                                      N'     NAME = ' + [FileName] + N',' + @crlf +
                                      N'     SIZE = ' + CAST(CAST(Smallest as int) as varchar(10)) + @crlf +
                                      N'     )'
            END N'CandidateCommand'
FROM  #Results r
JOIN  @DiskInfo d
ON          r.Drive = d.Drive
and     r.drive = @DriveLetter
--ORDER BY (TotalDataSize - UsedData) / TotalDataSize
order by CAST((TotalDataSize - UsedData)/1024 as decimal(5,2)) desc

DROP TABLE #Results

Partitioning: Three useful query

http://weblogs.sqlteam.com/dmauri/archive/2006/04/20/9646.aspx

DECLARE @PartTables Table (DBName sysname, TableName sysname, PartitionScheme sysname)


INSERT INTO @PartTables
EXEC sp_msforeachdb '    select DISTINCT ''[?]'' AS DBName, t.name As TableName, s.name AS PartitionScheme
                        from [?].sys.indexes i
                        join [?].sys.partition_schemes s
                            on i.data_space_id = s.data_space_id
                        join [?].sys.tables t
                            on i.object_id = t.object_id'

SELECT * FROM @PartTables


DECLARE @OBJECTNAME sysname = 'XXXXX'

select distinct
   p.object_id,
   index_name = i.name,
   index_type_desc = i.type_desc,
   partition_scheme = ps.name,
   data_space_id = ps.data_space_id,
   function_name = pf.name,
   function_id = ps.function_id
from
   sys.partitions p
inner join
   sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
inner join
   sys.data_spaces ds on i.data_space_id = ds.data_space_id
inner join
   sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
inner join
   sys.partition_functions pf on ps.function_id = pf.function_id
where p.object_id = object_id(@OBJECTNAME)




DECLARE @OBJECTNAME sysname = 'XXXX'


select
   p.object_id,
   p.index_id,
   p.partition_number,
   p.rows,
   index_name = i.name,
   index_type_desc = i.type_desc,
   i.data_space_id,
   pf.function_id,
   pf.type_desc,
   pf.boundary_value_on_right,
   destination_data_space_id = dds.destination_id,
   prv.parameter_id,
   prv.value
from
   sys.partitions p
inner join
   sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
inner join
   sys.data_spaces ds on i.data_space_id = ds.data_space_id
inner join
   sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
inner join
   sys.partition_functions pf on ps.function_id = pf.function_id
inner join
   sys.destination_data_spaces dds on dds.partition_scheme_id = ds.data_space_id and p.partition_number = dds.destination_id
left outer join
   sys.partition_range_values prv on prv.function_id = ps.function_id and p.partition_number = prv.boundary_id
where p.object_id = object_id(@OBJECTNAME)

Collect Lock information

USE [msdb]
GO

/****** Object:  Job [Collect Lock information]    Script Date: 08/16/2012 11:30:41 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 08/16/2012 11:30:41 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Collect Lock information',
        @enabled=0,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa',
        @notify_email_operator_name=N'DBA_ONLY', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [run tsql]    Script Date: 08/16/2012 11:30:42 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'run tsql',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'USE [SysAdmin]
GO

/****** Object:  Table [dbo].[locks]    Script Date: 10/22/2010 11:23:14 ******/
IF  not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[locks]'') AND type in (N''U''))


CREATE TABLE [dbo].[locks](
    [databasename] [nvarchar](128) NULL,
    [resource_type] [nvarchar](60) NOT NULL,
    [requested_object_name] [nvarchar](128) NULL,
    [lock_type] [nvarchar](60) NOT NULL,
    [request_status] [nvarchar](60) NOT NULL,
    [request_session_id] [int] NOT NULL,
    [lock_owner_address] [varbinary](8) NOT NULL,
    [request_owner_id] [bigint] NULL,
    [TimeofOccurence] [datetime] NOT NULL
) ON [PRIMARY]


use master
go

DECLARE @COUNTER int
, @RUN_TIME_MIN int
, @STOPAT int
   
SET @COUNTER=0;


truncate table SysAdmin..locks

WHILE 1=1

BEGIN

with all_locks (databasename,resource_type, requested_object_name,lock_type,request_status,request_session_id,
          lock_owner_address,request_owner_id,TimeofOccurence)
as
(
select Db_name(resource_database_id) databasename,
       resource_type,
       ( case
           when resource_type = ''OBJECT'' then Object_name(resource_associated_entity_id, resource_database_id)
           when resource_type in ( ''DATABASE'', ''FILE'', ''METADATA'' ) then ''N/A''
           when resource_type in ( ''KEY'', ''PAGE'', ''RID'' ) then (select Object_name(object_id)
                                                                from   sys.partitions
                                                                where  hobt_id = resource_associated_entity_id)
           else ''Undefined''
         end )                       as requested_object_name,
       request_mode                  as lock_type,
       request_status,
       request_session_id,
       lock_owner_address,
       request_owner_id              as transaction_id,
       getdate() as TimeofOccurence
from   sys.dm_tran_locks
where  resource_type not in ( ''DATABASE'', ''METADATA'', ''APPLICATION'' )
      
)


insert into sysadmin..locks
select * from all_locks
where lock_type =''X''



SET @COUNTER=@COUNTER+1;
WAITFOR DELAY ''00:00:01'';

IF @COUNTER = -1 BREAK;

END
',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Sp_spaceused

  declare @Space TABLE  (
   tablename    sysname,
   [rows]       bigint,
   reserved_S   varchar(32),
   data_S       varchar(32),
   index_size_S varchar(32),
   unused_S     varchar(32)
   )


  INSERT @Space(tablename,[rows],reserved_s,data_s,index_size_s,unused_s)
  EXEC sp_msforeachtable 'exec sp_spaceused [?]'


select
DB_NAME() as DatabaseName,
tablename    ,
   [rows]       ,
   type_desc,
   cast(replace(reserved_S, 'KB','') as bigint)/1024 as reserved_MB,
    cast(replace(data_S, 'KB', '') as bigint)/1024 as datasize_MB ,
    cast(replace(index_size_S, 'KB', '') as bigint)/1024 as index_size_MB  ,
    cast(replace(unused_S, 'KB', '') as bigint)/1024 as unused_MB
from @Space spa
inner join sys.indexes idx on idx.[object_id]=object_id(spa.tablename) and index_id in (0,1)
where (cast(replace(reserved_S, 'KB', '') as bigint)/1024) > 50000
order by unused_MB desc

Blocking Chains

-- http://thesqlguy.wordpress.com/2010/11/15/sql-2005-blocking-chains-a-friendly-display-using-cte-and-recursion/
--Best to output results to text rather than grid -- SQL 2005 and above only

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#Processes') IS NOT NULL
    DROP TABLE #Processes

DECLARE @results TABLE (
    id INT IDENTITY(1,1),
    DB_Name VARCHAR(500),
    BlockingSPID INT, SPID INT,
    BlockingStatement VARCHAR(MAX),
    RowNo INT, LevelRow INT,
    hostname sysname
)

SELECT
    s.spid,
    BlockingSPID = s.blocked,
    DatabaseName = DB_NAME(s.dbid),
    s.program_name,
    s.loginame,
    ObjectName = OBJECT_NAME(objectid, s.dbid),
    Definition = CAST(text AS VARCHAR(MAX)),
    hostname
INTO #Processes
FROM sys.sysprocesses s
    CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE s.spid > 50;


WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow, DB_Name, Hostname)
AS
(
    SELECT
        s.SPID,
        s.BlockingSPID,
        s.Definition,
        ROW_NUMBER() OVER(ORDER BY s.SPID) AS RowNo,
        0 AS LevelRow,
        s.DatabaseName AS DB_Name,
        s1.hostname
    FROM #Processes s
        INNER JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
    WHERE s.BlockingSPID = 0
        UNION ALL
    SELECT
        r.SPID,
        r.BlockingSPID,
        r.Definition,
        d.RowNo,
        d.LevelRow + 1,
        r.DatabaseName AS DB_Name,
        d.Hostname
    FROM #Processes r
        INNER JOIN Blocking d ON r.BlockingSPID = d.SPID
    WHERE r.BlockingSPID > 0
)
INSERT INTO @results (
    [DB_Name],
    [BlockingSPID],
    [SPID],
    [BlockingStatement],
    [RowNo],
    [LevelRow],
    [Hostname]
)
SELECT
    MIN(DB_NAME) AS DB_Name,
    BlockingSPID,
    SPID,
    BlockingStatement,
    MIN(RowNo),
    LevelRow,
    [Hostname]
FROM Blocking
GROUP BY BlockingSPID, SPID, BlockingStatement, LevelRow,  [Hostname]
ORDER BY MIN(RowNo), LevelRow
 
SELECT
    CASE
        WHEN [BlockingSPID] = 0
        THEN '
****Head of Blocking Chain SPID hostname:' + rtrim(hostname) + ' Spid:' + CAST([SPID] AS VARCHAR(5)) + '...
'
            + 'SPID ' + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
                + '
'
                + '...is blocking the following SPID(s):'
        WHEN [LevelRow] > 1
        THEN '
'
            + SPACE(LevelRow * 5) + 'SPID '
            + CAST(BlockingSPID AS VARCHAR(50))
            + ' is, in turn, blocking the following SPID:
'
            + SPACE((LevelRow + 1) * 5) + 'SPID ' + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')

        ELSE '
'
            + SPACE(LevelRow * 5) + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
                + '
'
    END
FROM @results

IF NOT EXISTS (SELECT TOP 1 * FROM @results)
    PRINT 'No Blocking Chains Were Found'

BEGIN TRY
    DROP TABLE #Processes
END TRY
BEGIN CATCH
END CATCH

dm_exec_query_stats

SELECT TOP 20
        SUBSTRING(text, statement_start_offset/2 + 1,
             (CASE WHEN statement_end_offset = -1
                             THEN LEN(CONVERT(nvarchar(max), text)) * 2
                             ELSE statement_end_offset
                END - statement_start_offset)/2) as SQL,
        db_name(query_text.DBID) as DBNAME, Object_name(query_text.objectid,query_text.DBID) as OBJECTNAME
        ,
        cast(c.query_plan as XML) as queryplan, cast(c.query_plan as XML).exist(
'declare default element namespace ''http://schemas.microsoft.com/sqlserver/2004/07/showplan'';
/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan//MissingIndexes') HASMISSINGINDEX,
        --s.min_worker_time / 1000 AS min_cpu_time_ms,
        --s.max_worker_time / 1000 AS max_cpu_time_ms,
        ((s.total_worker_time * 1.00)/ s.execution_count)/ 1000.00 AS avg_cpu_time_ms,
       
       
        --s.total_elapsed_time / 1000.00 AS total_execution_time_ms,
        --s.min_elapsed_time / 1000.00 AS min_execution_time_ms,
        --s.max_elapsed_time / 1000.00 AS max_execution_time_ms,
          ((s.total_elapsed_time)/ s.execution_count)/ 1000.00 AS avg_execution_time_ms,
       
        --s.total_logical_reads,
        --s.min_logical_reads,
        --s.max_logical_reads,
         ((s.total_logical_reads * 1.00)/ s.execution_count) AS avg_logical_reads,
        
        --s.total_logical_writes,
        --s.min_logical_writes,
        --s.max_logical_writes,
        ((s.total_logical_writes * 1.00)/ s.execution_count) AS avg_logical_writes,
       
       
        --s.total_physical_reads,
        --s.min_physical_reads,
        --s.max_physical_reads,
        ((s.total_physical_reads * 1.00)/ s.execution_count) AS avg_physical_reads,
       
       s.query_hash,
        s.query_plan_hash,
                s.execution_count,
        -- Convert the DMV's ambiguous DATETIME to DATETIMEOFFSET
        TODATETIMEOFFSET (s.creation_time, DATENAME (TZoffset, SYSDATETIMEOFFSET())) AS creation_time,
        s.plan_generation_num,
        TODATETIMEOFFSET (s.last_execution_time, DATENAME (TZoffset, SYSDATETIMEOFFSET())) AS last_execution_time
        --s.total_worker_time / 1000 AS total_cpu_time_ms,
FROM sys.dm_exec_query_stats s
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS query_text
    cross apply sys.dm_exec_text_query_plan(plan_handle, statement_start_offset, statement_end_offset) c
    --where Object_name(query_text.objectid,query_text.DBID) = 'p_Folders_sel_All_Active_with_Hierarchy'
ORDER BY total_worker_time DESC
--ORDER By (s.total_elapsed_time/ s.execution_count) DESC