Diagnostic SQL Queries

System Stored Procedures:

EXEC sys.sp_who @loginame = NULL -- sysname
EXEC sys.sp_who @loginame = 'sa' -- sysname

EXEC sys.sp_who2

Current Tasks SnapShot

SELECT SUBSTRING(qt.text, ( er.statement_start_offset / 2 ) + 1,
( ( CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE er.statement_end_offset
END - er.statement_start_offset ) / 2 ) + 1) AS [Query] ,
er.session_id AS [Session Id] ,
er.cpu_time AS [CPU (ms/sec)] ,
db.name AS [Database Name] ,
er.total_elapsed_time AS [Elapsed Time] ,
er.reads AS [Reads] ,
er.writes AS [Writes] ,
er.logical_reads AS [Logical Reads] ,
er.row_count AS [Row Count] ,
mg.granted_memory_kb AS [Allocated Memory] ,
mg.used_memory_kb AS [Used Memory] ,
mg.required_memory_kb AS [Required Memory] ,
master.dbo.fn_varbintohexstr(er.plan_handle) AS [sample_plan_handle] ,
er.statement_start_offset AS [sample_statement_start_offset] ,
er.statement_end_offset AS [sample_statement_end_offset]
FROM sys.dm_exec_requests er
LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg ON er.session_id = mg.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) qt ,
sys.databases db
WHERE db.database_id = er.database_id
AND er.session_id <> @@spid;

SELECT  [oswt].[session_id] ,
    [er].percent_complete,
        [oswt].[exec_context_id] ,
        [ot].[scheduler_id] ,
        [oswt].[wait_duration_ms] ,
        [oswt].[wait_type] ,
        [oswt].[blocking_session_id] ,
        [oswt].[resource_description] ,
        CASE [oswt].[wait_type]
          WHEN N'CXPACKET'
          THEN RIGHT([oswt].[resource_description],
                     CHARINDEX(N'=', REVERSE([oswt].[resource_description]))
                     - 1)
          ELSE NULL
        END AS [Node ID] ,
        [es].[program_name] ,
        [est].text ,
        [er].[database_id] ,
        [eqp].[query_plan] ,
        [er].[cpu_time]
FROM    sys.dm_os_waiting_tasks [oswt]
        INNER JOIN sys.dm_os_tasks [ot] ON [oswt].[waiting_task_address] = [ot].[task_address]
        INNER JOIN sys.dm_exec_sessions [es] ON [oswt].[session_id] = [es].[session_id]
        INNER JOIN sys.dm_exec_requests [er] ON [es].[session_id] = [er].[session_id]
        OUTER APPLY sys.dm_exec_sql_text([er].[sql_handle]) [est]
        OUTER APPLY sys.dm_exec_query_plan([er].[plan_handle]) [eqp]
WHERE   [es].[is_user_process] = 1
ORDER BY [oswt].[session_id] ,
        [oswt].[exec_context_id];

SET TRAN ISOLATION LEVEL READ UNCOMMITTED
-- Who is running what at this instant at this moment
SELECT @@SERVERNAME AS ServerName ,
r.percent_complete [PercentComplete],
st.[text] AS [Command Text] ,
s.host_name ,
r.session_id AS [SessionID] ,
r.[status] ,
DB_NAME(r.database_id) AS [DatabaseName] ,
ISNULL(r.wait_type, N'None') AS [WaitType] ,
r.logical_reads ,
r.cpu_time ,
r.total_elapsed_time
FROM sys.dm_exec_requests AS r WITH ( NOLOCK )
INNER JOIN sys.dm_exec_connections AS c WITH ( NOLOCK ) ON r.session_id = c.session_id
INNER JOIN sys.dm_exec_sessions AS s WITH ( NOLOCK ) ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.session_id > 50
AND r.session_id <> @@SPID -- eliminate current connection
AND s.is_user_process = 1
ORDER BY r.[status];

-- Task Count by OS Scheduler
SELECT scheduler_id ,
current_tasks_count ,
runnable_tasks_count ,
pending_disk_io_count ,
load_factor
FROM sys.dm_os_schedulers WITH ( NOLOCK )
WHERE scheduler_id < 255
AND status ='VISIBLE ONLINE'

SQL Handle to SQL Text

DECLARE @sql_handle VARBINARY(64) ,
    @stmtstart INT ,
    @stmtend INT 
 
SELECT  @stmtstart = 3268 ,
        @stmtend = 5102 ,
        @sql_handle = 0x03000800d8c3dd70e2f83f01e2a300000100000000000000
 
SELECT  SUBSTRING(qt.text, s.statement_start_offset / 2,
                  ( CASE WHEN s.statement_end_offset = -1
                         THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                         ELSE s.statement_end_offset
                    END - s.statement_start_offset ) / 2) AS "SQL statement" ,
        s.statement_start_offset ,
        s.statement_end_offset ,
        batch = qt.text ,
        qt.dbid ,
        qt.objectid ,
        s.execution_count ,
        s.total_worker_time ,
        s.total_elapsed_time ,
        s.total_logical_reads ,
        s.total_physical_reads ,
        s.total_logical_writes
FROM    sys.dm_exec_query_stats s
        CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS qt
WHERE   s.sql_handle = @sql_handle
AND s.statement_start_offset = @stmtstart AND s.statement_end_offset = @stmtend

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

CREATE TABLE #DatabaseSpace
    (
      database_id INT PRIMARY KEY ,
      data_used_size DECIMAL(20, 2) ,
      log_used_size DECIMAL(20, 2)
    );

DECLARE @SQL NVARCHAR(MAX);

SELECT  @SQL = STUFF((SELECT    '
    USE [' + d.name
                                + ']
    INSERT INTO #DatabaseSpace (database_id, data_used_size, log_used_size)
    SELECT
          DB_ID()
        , SUM(CASE WHEN [type] = 0 THEN space_used END)
        , SUM(CASE WHEN [type] = 1 THEN space_used END)
    FROM (
        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
        FROM sys.database_files s
        GROUP BY s.[type]
    ) t;'
                      FROM      sys.databases d
                      WHERE     d.[state] = 0
        FOR          XML PATH('') ,
                         TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');
-- SELECT @SQL

EXEC sys.sp_executesql @SQL;

SELECT  d.database_id ,
        d.name ,
        d.state_desc ,
        d.recovery_model_desc AS RecoveryModel,
        t.total_size TotalDBSize ,
        t.data_size DataSize,
		s.data_used_size  ,
		CONVERT(DECIMAL(18,2) , s.data_used_size /t.data_size)*100 AS PctDataUsed ,
        t.log_size LogSize,
        s.log_used_size ,
		CONVERT(DECIMAL(18,2) , t.log_size /s.log_used_size) AS PctDataUsed ,
        bu.full_last_date ,
        bu.full_size ,
        bu.log_last_date ,
        bu.log_size
FROM    ( SELECT    database_id ,
                    log_size = CAST(SUM(CASE WHEN [type] = 1 THEN size
                                        END) * 8. / 1024 AS DECIMAL(18, 2)) ,
                    data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size
                                         END) * 8. / 1024 AS DECIMAL(18, 2)) ,
                    total_size = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18, 2))
          FROM      sys.master_files
          GROUP BY  database_id
        ) t
        JOIN sys.databases d ON d.database_id = t.database_id
        LEFT JOIN #DatabaseSpace s ON d.database_id = s.database_id
        LEFT JOIN ( SELECT  database_name ,
                            full_last_date = MAX(CASE WHEN [type] = 'D'
                                                      THEN backup_finish_date
                                                 END) ,
                            full_size = MAX(CASE WHEN [type] = 'D'
                                                 THEN backup_size
                                            END) ,
                            log_last_date = MAX(CASE WHEN [type] = 'L'
                                                     THEN backup_finish_date
                                                END) ,
                            log_size = MAX(CASE WHEN [type] = 'L'
                                                THEN backup_size
                                           END)
                    FROM    ( SELECT    s.database_name ,
                                        s.[type] ,
                                        s.backup_finish_date ,
                                        backup_size = CAST(CASE
                                                              WHEN s.backup_size = s.compressed_backup_size
                                                              THEN s.backup_size
                                                              ELSE s.compressed_backup_size
                                                           END / 1048576.0 AS DECIMAL(18,
                                                              2)) ,
                                        RowNum = ROW_NUMBER() OVER ( PARTITION BY s.database_name,
                                                              s.[type] ORDER BY s.backup_finish_date DESC )
                              FROM      msdb.dbo.backupset s
                              WHERE     s.[type] IN ( 'D', 'L' )
                            ) f
                    WHERE   f.RowNum = 1
                    GROUP BY f.database_name
                  ) bu ON d.name = bu.database_name
ORDER BY t.total_size DESC;

-- Drop Temp Table
IF OBJECT_ID('tempdb..#DatabaseSpace') IS NOT NULL
    DROP TABLE #DatabaseSpace;

USE [DatabaseName];
GO

;
WITH    src
          AS ( SELECT   [Object] = o.name ,
                        [Type] = o.type_desc ,
                        [Index] = COALESCE(i.name, '') ,
                        [Index_Type] = i.type_desc ,
                        p.[object_id] ,
                        p.index_id ,
                        au.allocation_unit_id
               FROM     sys.partitions AS p
                        INNER JOIN sys.allocation_units AS au ON p.hobt_id = au.container_id
                        INNER JOIN sys.objects AS o ON p.[object_id] = o.[object_id]
                        INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id]
                                                       AND p.index_id = i.index_id
               WHERE    au.[type] IN ( 1, 2, 3 )
                        AND o.is_ms_shipped = 0
             )
    SELECT  src.[Object] ,
            src.[Type] ,
            src.[Index] ,
            src.Index_Type ,
            buffer_pages = COUNT_BIG(b.page_id) ,
            buffer_mb = COUNT_BIG(b.page_id) / 128
    FROM    src
            INNER JOIN sys.dm_os_buffer_descriptors AS b ON src.allocation_unit_id = b.allocation_unit_id
    WHERE   b.database_id = DB_ID()
    GROUP BY src.[Object] ,
            src.[Type] ,
            src.[Index] ,
            src.Index_Type
    ORDER BY buffer_pages DESC;