roy ashbrook

Great useful base diag scripts! Snipped from http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!893.entry 

-- SQL Server 2005 Emergency Diagnostic and Performance Queries
-- Glenn Berry 3-17-2008
      

-- Step 1 - Check Task Manager. Are all CPUs above 90-95% for an extended period of time?
-- If yes, run HIGH CPU queries below:

-- Step 2 - Check Performance Monitor
-- SQL Server Buffer Manager: Buffer Cache Hit Ratio and Page Life Expectancy
-- SQL Server Memory Manager: Memory Grants Pending and Memory Grants Pending
-- Physical Disk: Avg disk sec/Read and Avg disk sec/Write

-- Step 3 - Check for locking, blocking and missing indexes
-- Run the BLOCKING queries below: 

-- Step 4 - Is the transaction log full?
-- Run the TRANSACTION LOG FULL queries below:

-- Step 5 - Check for IO Problems
-- Run the IO ISSUES queries below



    -- HIGH CPU *******
    -- Get the most CPU intensive queries
    SET NOCOUNT ON;

    DECLARE @SpID smallint
    DECLARE spID_Cursor CURSOR
    FAST_FORWARD FOR
    
    SELECT TOP 25 spid
    FROM master..sysprocesses
    WHERE status = 'runnable'
    AND spid > 50   -- Eliminate system SPIDs
    AND spid <> @@SPID
    ORDER BY CPU DESC

    OPEN spID_Cursor

    FETCH NEXT FROM spID_Cursor
    INTO @spID
        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'Spid #:' + STR(@spID)
            EXEC ('DBCC INPUTBUFFER (' + @spID + ')')

               FETCH NEXT FROM spID_Cursor
            INTO @spID
        END

    -- Close and deallocate the cursor
    CLOSE spID_Cursor
    DEALLOCATE spID_Cursor


      -- HIGH CPU *******
      -- Isolate top waits for server instance
      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 LIKE '%SLEEP%'
      )
      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 < 90 -- percentage threshold
      ORDER BY W1.rn;

      -- HIGH CPU *******
      -- Total waits are wait_time_ms (high signal waits indicates CPU pressure)
      SELECT '%signal (cpu) waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)),
           '%resource waits'= CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
      FROM sys.dm_os_wait_stats;

      -- HIGH CPU *******
      -- Check SQL Server Schedulers to see if they are waiting on CPU (runnable tasks above 10 is very bad)
      SELECT scheduler_id, current_tasks_count, runnable_tasks_count
      FROM sys.dm_os_schedulers
      WHERE scheduler_id < 255

      -- HIGH CPU *******
      --  Who is running what at this instant 
      SELECT st.text AS [Command text], login_time, [host_name], 
      [program_name], sys.dm_exec_requests.session_id, client_net_address,
      sys.dm_exec_requests.status, command, db_name(database_id) AS DatabaseName
      FROM sys.dm_exec_requests 
      INNER JOIN sys.dm_exec_connections 
      ON sys.dm_exec_requests.session_id = sys.dm_exec_connections.session_id
      INNER JOIN sys.dm_exec_sessions 
      ON sys.dm_exec_sessions.session_id = sys.dm_exec_requests.session_id
      CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
      WHERE sys.dm_exec_requests.session_id >= 51
      AND sys.dm_exec_requests.session_id <> @@spid
      ORDER BY sys.dm_exec_requests.status

      -- HIGH CPU *******
      -- Get a snapshot of current activity
      SELECT LTRIM (st.text) AS 'Command Text',[host_name], der.session_id AS 'SPID',
      der.status, db_name(database_id) AS DatabaseName, ISNULL(der.wait_type, 'None')AS 'Wait Type', der.logical_reads 
      FROM sys.dm_exec_requests AS der
      INNER JOIN sys.dm_exec_connections AS dexc
      ON der.session_id = dexc.session_id
      INNER JOIN sys.dm_exec_sessions AS dexs
      ON dexs.session_id = der.session_id
      CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
      WHERE der.session_id >= 51
      AND der.session_id <> @@spid
      ORDER BY der.status

      -- BLOCKING ************
      -- Detect blocking
      SELECT blocked_query.session_id AS blocked_session_id,
      blocking_query.session_id AS blocking_session_id,
      sql_text.text AS blocked_text, sql_btext.text AS blocking_text, waits.wait_type AS blocking_resource
      FROM sys.dm_exec_requests AS blocked_query
      INNER JOIN sys.dm_exec_requests AS blocking_query 
      ON blocked_query.blocking_session_id = blocking_query.session_id
      CROSS APPLY
      (SELECT * FROM sys.dm_exec_sql_text(blocking_query.sql_handle)
      ) sql_btext
      CROSS APPLY
      (SELECT * FROM sys.dm_exec_sql_text(blocked_query.sql_handle)
      ) sql_text
      INNER JOIN sys.dm_os_waiting_tasks AS waits 
      ON waits.session_id = blocking_query.session_id

      -- BLOCKING ************
      -- Index Contention
      SELECT dbid=database_id, objectname=object_name(s.object_id),
      indexname=i.name, i.index_id, row_lock_count, row_lock_wait_count,
      [block %]= CAST (100.0 * row_lock_wait_count / (1 + row_lock_count) AS NUMERIC(15,2)),
      row_lock_wait_in_ms,
      [avg row lock waits in ms]= CAST (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) AS NUMERIC(15,2))
      FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) AS s
      INNER JOIN sys.indexes AS i
      ON i.object_id = s.object_id
      WHERE objectproperty(s.object_id,'IsUserTable') = 1
      AND i.index_id = s.index_id
      ORDER BY row_lock_wait_count DESC

      -- TRANSACTION LOG FULL *****
      -- Find the log reuse description for the transaction log
      SELECT name, database_id, log_reuse_wait_desc 
      FROM sys.databases

      -- TRANSACTION LOG FULL *****
      -- Individual File Size query
      SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',
      size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB'--, *
      FROM sys.database_files;

      -- IO ISSUES *****************
      -- Analyze DB IO, ranked by IO Stall %
      WITH DBIO AS
      (
        SELECT
            DB_NAME(IVFS.database_id) AS db,
            CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
            SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
            SUM(IVFS.io_stall) AS io_stall
        FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
            JOIN sys.master_files AS MF
              ON IVFS.database_id = MF.database_id
              AND IVFS.file_id = MF.file_id
        GROUP BY DB_NAME(IVFS.database_id), MF.type
      )
      SELECT db, file_type, 
        CAST(1. * io / (1024 * 1024) AS DECIMAL(12, 2)) AS io_mb,
        CAST(io_stall / 1000. AS DECIMAL(12, 2)) AS io_stall_s,
        CAST(100. * io_stall / SUM(io_stall) OVER()
               AS DECIMAL(10, 2)) AS io_stall_pct,
        ROW_NUMBER() OVER(ORDER BY io_stall DESC) AS rn
      FROM DBIO
      ORDER BY io_stall DESC;

      -- HIGH CPU ************
      -- Get Top 100 executed SP's ordered by execution count
      SELECT TOP 100 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',  
      qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',
      qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
      qs.total_worker_time AS 'TotalWorkerTime',
      qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
      qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
      DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
      FROM sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
      WHERE qt.dbid = db_id() -- Filter by current database
      ORDER BY qs.execution_count DESC

      -- HIGH CPU *************
      -- Get Top 20 executed SP's ordered by total worker time (CPU pressure)
      SELECT TOP 20 qt.text AS 'SP Name', qs.total_worker_time AS 'TotalWorkerTime', 
      qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
      qs.execution_count AS 'Execution Count', 
      ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second',
      ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime', 
      qs.max_logical_reads, qs.max_logical_writes, 
      DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
      FROM sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
      WHERE qt.dbid = db_id() -- Filter by current database
      ORDER BY qs.total_worker_time DESC