Brent Ozar's SQL Check Scripts - Memory/CPU/IO Concerns?

Question

How careful do I need to be using Brent Ozar's free SQL health check scripts on a SQL Server 2016 production machine that's very full and very busy already? Has anyone run into memory, CPU or I/O issues running any of the following? Thanks for any feedback.

• sp_Blitz
• sp_BlitzCache
• sp_BlitzFirst
• sp_BlitzIndex
• sp_BlitzLock
• sp_BlitzQueryStore
• sp_BlitzWho
• sp_WhoIsActive (Adam Machanic)

Answer 1

I would not be without it. We have Redgate and I use Brents tools more often.

Our database is 3 Tb and has approx. 1500 queries per second and have never experienced any problems.

Made a custom sp_BlitzWho_Ext which relys on the sp_BlitzWho.

It takes the result of sp_BlitzWho. Puts it in a temp table so I can add a filter. Also cursors are identified and converted so you can see the actual query.

The SQL for dropping the plan_cache of each of them are there - so whenever we get a dumb plan its easy to find and drop the right one.

It now only takes 5 seconds to identify the culprit and get the plan dropped.

First I created a Function to get the query text from the Cursor

create function [dbo].[CursorQuery]
(
    @session_id int
) returns nvarchar(255)
as
begin
    declare @ret nvarchar(255) = null

    SELECT @ret = t.text
    FROM sys.dm_exec_cursors (@session_id) c
    LEFT JOIN sys.dm_exec_sessions AS es ON c.session_id = es.session_id
    CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t

    return @ret 
end

Then the stored procedure

CREATE PROCEDURE [dbo].[sp_BlitzWho_Ext]
AS
BEGIN
    CREATE TABLE #BlitzWhoResult
    (
        [run_date] VARCHAR(255),
        [elapsed_time] [varchar](41) NULL,
        [session_id] [smallint] NOT NULL,
        [database_name] [nvarchar](128) NULL,
        [query_text] [nvarchar](max) NULL,
        [query_plan] [xml] NULL,
        [live_query_plan] [xml] NULL,
        [query_cost] [float] NULL,
        [status] [nvarchar](30) NOT NULL,
        [wait_info] [nvarchar](max) NULL,
        [top_session_waits] [nvarchar](max) NULL,
        [blocking_session_id] [smallint] NULL,
        [open_transaction_count] [int] NULL,
        [is_implicit_transaction] [int] NOT NULL,
        [nt_domain] [nvarchar](128) NULL,
        [host_name] [nvarchar](128) NULL,
        [login_name] [nvarchar](128) NOT NULL,
        [nt_user_name] [nvarchar](128) NULL,
        [program_name] [nvarchar](128) NULL,
        [fix_parameter_sniffing] [nvarchar](150) NULL,
        [client_interface_name] [nvarchar](32) NULL,
        [login_time] [datetime] NOT NULL,
        [start_time] [datetime] NULL,
        [request_time] [datetime] NULL,
        [request_cpu_time] [int] NULL,
        [request_logical_reads] [bigint] NULL,
        [request_writes] [bigint] NULL,
        [request_physical_reads] [bigint] NULL,
        [session_cpu] [int] NOT NULL,
        [session_logical_reads] [bigint] NOT NULL,
        [session_physical_reads] [bigint] NOT NULL,
        [session_writes] [bigint] NOT NULL,
        [tempdb_allocations_mb] [decimal](38, 2) NULL,
        [memory_usage] [int] NOT NULL,
        [estimated_completion_time] [bigint] NULL,
        [percent_complete] [real] NULL,
        [deadlock_priority] [int] NULL,
        [transaction_isolation_level] [varchar](33) NOT NULL,
        [degree_of_parallelism] [smallint] NULL,
        [last_dop] [bigint] NULL,
        [min_dop] [bigint] NULL,
        [max_dop] [bigint] NULL,
        [last_grant_kb] [bigint] NULL,
        [min_grant_kb] [bigint] NULL,
        [max_grant_kb] [bigint] NULL,
        [last_used_grant_kb] [bigint] NULL,
        [min_used_grant_kb] [bigint] NULL,
        [max_used_grant_kb] [bigint] NULL,
        [last_ideal_grant_kb] [bigint] NULL,
        [min_ideal_grant_kb] [bigint] NULL,
        [max_ideal_grant_kb] [bigint] NULL,
        [last_reserved_threads] [bigint] NULL,
        [min_reserved_threads] [bigint] NULL,
        [max_reserved_threads] [bigint] NULL,
        [last_used_threads] [bigint] NULL,
        [min_used_threads] [bigint] NULL,
        [max_used_threads] [bigint] NULL,
        [grant_time] [varchar](20) NULL,
        [requested_memory_kb] [bigint] NULL,
        [grant_memory_kb] [bigint] NULL,
        [is_request_granted] [varchar](39) NOT NULL,
        [required_memory_kb] [bigint] NULL,
        [query_memory_grant_used_memory_kb] [bigint] NULL,
        [ideal_memory_kb] [bigint] NULL,
        [is_small] [bit] NULL,
        [timeout_sec] [int] NULL,
        [resource_semaphore_id] [smallint] NULL,
        [wait_order] [varchar](20) NULL,
        [wait_time_ms] [varchar](20) NULL,
        [next_candidate_for_memory_grant] [varchar](3) NOT NULL,
        [target_memory_kb] [bigint] NULL,
        [max_target_memory_kb] [varchar](30) NULL,
        [total_memory_kb] [bigint] NULL,
        [available_memory_kb] [bigint] NULL,
        [granted_memory_kb] [bigint] NULL,
        [query_resource_semaphore_used_memory_kb] [bigint] NULL,
        [grantee_count] [int] NULL,
        [waiter_count] [int] NULL,
        [timeout_error_count] [bigint] NULL,
        [forced_grant_count] [varchar](30) NULL,
        [workload_group_name] [sysname] NULL,
        [resource_pool_name] [sysname] NULL,
        [context_info] [varchar](128) NULL,
        [query_hash] [binary](8) NULL,
        [query_plan_hash] [binary](8) NULL,
        [sql_handle] [varbinary] (64) NULL,
        [plan_handle] [varbinary] (64) NULL,
        [statement_start_offset] INT NULL,
        [statement_end_offset] INT NULL
    )

    INSERT INTO #BlitzWhoResult
    EXEC  [dbo].[sp_BlitzWho] @ShowSleepingSPIDs = 1, @ExpertMode = 1,    @MinElapsedSeconds = 1

    SELECT [program_name],
    [session_id], 
    CASE WHEN [query_text] LIKE 'FETCH API_CURSOR%' 
        THEN CursorQuery(session_id) 
        ELSE query_text 
        END AS [query_text],
    [query_plan], 
    [fix_parameter_sniffing] AS [drop_cached_plan_sql], 
    [status], 
    [wait_info], 
    [login_name]
    from #BlitzWhoResult
    where database_name = 'PUT_YOUR_DATABASENAME_HERE' -- and... add more filters?

    DROP TABLE #BlitzWhoResult
END

So.. I really enjoy Brents tool as well as his videos and sense of humour