Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sp_BlitzCache on Azure SQL DB returns no Results #2022

Closed
iamwyza opened this issue Apr 11, 2019 · 11 comments · Fixed by #2088

Comments

@iamwyza
Copy link

commented Apr 11, 2019

Version of the script
Current version: 7.4 released on Mar 20 2019 12:00AM.

What is the current behavior?
Running exec sp_BlitzCache results in 2 outputs. The first one has no rows, the 2nd one only has 3 rows (2 of which are the static pastetheplan and firstresponderkit rows). The one row with data concerns the number of plans in the cache.

If the current behavior is a bug, please provide the steps to reproduce.
Install sp_BlitzCache on a Azure SQL DB, then run it.

What is the expected behavior?
Output should exist in the first result.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?

Azure SQL Database, Unknown

Output

Here's the output messages of exec sp_BlitzCache:


Checking @MinutesBack validity.
Creating temp tables for results and warnings.
Checking database validity
Checking sort order
Cleaning up old warnings for your SPID
Cleaning up old plans for your SPID
Creating temp tables for internal processing
Checking plan cache age
Setting up variables
Determining SQL Server version.
Creating dynamic SQL based on SQL Server version.
Ignoring system databases by default
Applying chosen sort order
Substituting NULLs for spill columns in older versions of SQL
Adding additional info columns for newer versions of SQL
Substituting NULLs for memory grant columns in older versions of SQL
Substituting NULLs for spill columns in older versions of SQL
Adding SQL to collect trigger stats.
Collecting execution plan information.
Attempting to aggregate stored proc info from separate statements
Computing CPU, duration, read, and write metrics
Update CPU percentage for stored procedures
Gather percentage information from grouped results
Begin XML nodes processing
Gathering high level plan information
Performing query level checks
Performing compile timeout checks
Performing compile memory limit exceeded checks
Gathering trivial plans
Gathering statement costs
Updating statement costs
Checking for plan warnings
Checking for implicit conversion
Performing TVF join check
Checking for table variables
Checking for expensive key lookups
Checking for expensive remote queries
Checking for expensive sorts
No cursor plans found, skipping
Checking for filters that reference scalar UDFs
Checking for wonky Index Spools
Checking for forced serialization
Checking for downlevel cardinality estimators being used on SQL Server 2014.
Gathering additional plan level information
Attempting to get stored procedure name for individual statements
Attempting to get function name for individual statements
Trace flag checks
Checking for MSTVFs
Checking for non-sargable predicates
Getting information about implicit conversions and stored proc parameters
Getting variable info
Getting conversion info
Parsing conversion info
Updating variables for inserted procs
Inserting variables for other procs
Updating procs
Updating SET options
Updating conversion XML
Updating cached parameter XML for stored procs
Updating cached parameter XML for statements
Filling in implicit conversion and cached plan parameter info
Filling in missing index blanks
Setting configuration values
Checking for query level SQL Server issues.
Checking for forced parameterization and cursors.
Populating Warnings column
Populating Warnings column for stored procedures
Checking for plans with >128 levels of nesting
Checking for plans with no warnings
Displaying analysis of plan cache.
Returning ExpertMode = 0
Building query plan summary data.

@BrentOzar

This comment has been minimized.

Copy link
Member

commented Apr 12, 2019

It's working fine here - might be that you don't have anything in the plan cache at the moment, or that you're using a fairly small-sized Azure SQL DB instance that's under performance pressure?

@iamwyza

This comment has been minimized.

Copy link
Author

commented Apr 12, 2019

Definitely have plans in the cache since this line is in the "Plan Cache Information" line of the 2nd output:

You have 10094 total plans in your cache, with 58.00% plans created in the past 24 hours, 35.00% created in the past 4 hours, and 33.00% created in the past 1 hour.

It's definitely not a small DB instance (though there are 500 DBs in the elastic pool. vCore type)

@kculver14

This comment has been minimized.

Copy link

commented Apr 12, 2019

I'm seeing the same behavior. Not a small server, and not currently under performance pressure, 15393 plans in cache, empty results table. We do have high percentages on our query plan creation, with 99% in the past 24 hours, 77% in the past 4, and 72% in the past hour. Could plan cache instability cause this outcome?

@iamwyza

This comment has been minimized.

Copy link
Author

commented Apr 15, 2019

I'm more than happy to provide more diagnostic information if need be.

@BrentOzar

This comment has been minimized.

Copy link
Member

commented Apr 15, 2019

@iamwyza

This comment has been minimized.

Copy link
Author

commented Apr 16, 2019

Checking @MinutesBack validity.
Creating temp tables for results and warnings.
Checking database validity
Checking sort order
Cleaning up old warnings for your SPID
Cleaning up old plans for your SPID
Creating temp tables for internal processing
Checking plan cache age
Setting up variables
Determining SQL Server version.
Creating dynamic SQL based on SQL Server version.
Ignoring system databases by default
Applying chosen sort order
Substituting NULLs for spill columns in older versions of SQL
Adding additional info columns for newer versions of SQL
Substituting NULLs for memory grant columns in older versions of SQL
Substituting NULLs for spill columns in older versions of SQL
Adding SQL to collect trigger stats.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
INSERT INTO ##BlitzCacheProcs (SPID, QueryType, DatabaseName, AverageCPU, TotalCPU, AverageCPUPerMinute, PercentCPUByType, PercentDurationByType,
                    PercentReadsByType, PercentExecutionsByType, AverageDuration, TotalDuration, AverageReads, TotalReads, ExecutionCount,
                    ExecutionsPerMinute, TotalWrites, AverageWrites, PercentWritesByType, WritesPerMinute, PlanCreationTime,
                    LastExecutionTime, StatementStartOffset, StatementEndOffset, MinReturnedRows, MaxReturnedRows, AverageReturnedRows, TotalReturnedRows,
                    LastReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB, MaxUsedGrantKB, PercentMemoryGrantUsed, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, 
					QueryText, QueryPlan, TotalWorkerTimeForType, TotalElapsedTimeForType, TotalReadsForType,
                    TotalExecutionCountForType, TotalWritesForType, SqlHandle, PlanHandle, QueryHash, QueryPlanHash,
                    min_worker_time, max_worker_time, is_parallel, min_elapsed_time, max_elapsed_time, age_minutes, age_minutes_lifetime) 
    SELECT TOP (@Top)
           @@SPID ,
           'Statement' AS QueryType,
           COALESCE(DB_NAME(CAST(pa.value AS INT)), N'-- N/A --') AS DatabaseName,
           (total_worker_time / 1000.0) / execution_count AS AvgCPU ,
           (total_worker_time / 1000.0) AS TotalCPU ,
           CASE WHEN total_worker_time = 0 THEN 0
                WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.creation_time, qs.last_execution_time), 0) = 0 THEN 0
                ELSE CAST((total_worker_time / 1000.0) / COALESCE(age_minutes, DATEDIFF(mi, qs.creation_time, qs.last_execution_time)) AS MONEY)
                END AS AverageCPUPerMinute ,
           CASE WHEN t.t_TotalWorker = 0 THEN 0
                ELSE CAST(ROUND(100.00 * total_worker_time / t.t_TotalWorker, 2) AS MONEY)
                END AS PercentCPUByType,
           CASE WHEN t.t_TotalElapsed = 0 THEN 0
                ELSE CAST(ROUND(100.00 * total_elapsed_time / t.t_TotalElapsed, 2) AS MONEY)
                END AS PercentDurationByType,
           CASE WHEN t.t_TotalReads = 0 THEN 0
                ELSE CAST(ROUND(100.00 * total_logical_reads / t.t_TotalReads, 2) AS MONEY)
                END AS PercentReadsByType,
           CAST(ROUND(100.00 * execution_count / t.t_TotalExecs, 2) AS MONEY) AS PercentExecutionsByType,
           (total_elapsed_time / 1000.0) / execution_count AS AvgDuration ,
           (total_elapsed_time / 1000.0) AS TotalDuration ,
           total_logical_reads / execution_count AS AvgReads ,
           total_logical_reads AS TotalReads ,
           execution_count AS ExecutionCount ,
           CASE WHEN execution_count = 0 THEN 0
                WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.creation_time, qs.last_execution_time), 0) = 0 THEN 0
                ELSE CAST((1.00 * execution_count / COALESCE(age_minutes, DATEDIFF(mi, qs.creation_time, qs.last_execution_time))) AS money)
                END AS ExecutionsPerMinute ,
           total_logical_writes AS TotalWrites ,
           total_logical_writes / execution_count AS AverageWrites ,
           CASE WHEN t.t_TotalWrites = 0 THEN 0
                ELSE CAST(ROUND(100.00 * total_logical_writes / t.t_TotalWrites, 2) AS MONEY)
                END AS PercentWritesByType,
           CASE WHEN total_logical_writes = 0 THEN 0
                WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.creation_time, qs.last_execution_time), 0) = 0 THEN 0
                ELSE CAST((1.00 * total_logical_writes / COALESCE(age_minutes, DATEDIFF(mi, qs.creation_time, qs.last_execution_time), 0)) AS money)
                END AS WritesPerMinute,
           qs.creation_time AS PlanCreationTime,
           qs.last_execution_time AS LastExecutionTime,
           qs.statement_start_offset AS StatementStartOffset,
           qs.statement_end_offset A
S StatementEndOffset, 
           qs.min_rows AS MinReturnedRows,
           qs.max_rows AS MaxReturnedRows,
           CAST(qs.total_rows as MONEY) / execution_count AS AvgReturnedRows,
           qs.total_rows AS TotalReturnedRows,
           qs.last_rows AS LastReturnedRows, 
           NULL AS MinGrantKB,
           NULL AS MaxGrantKB,
           NULL AS MinUsedGrantKB, 
		   NULL AS MaxUsedGrantKB,
		   NULL AS PercentMemoryGrantUsed, 
		   NULL AS AvgMaxMemoryGrant, 
           NULL AS MinSpills,
           NULL AS MaxSpills,
           NULL AS TotalSpills, 
		   NULL AS AvgSpills, 
           SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset
                                                                            WHEN -1 THEN DATALENGTH(st.text)
                                                                            ELSE qs.statement_end_offset
                                                                          END - qs.statement_start_offset ) / 2 ) + 1) AS QueryText ,
           query_plan AS QueryPlan,
           t.t_TotalWorker,
           t.t_TotalElapsed,
           t.t_TotalReads,
           t.t_TotalExecs,
           t.t_TotalWrites,
           qs.sql_handle AS SqlHandle,
           qs.plan_handle AS PlanHandle,
           qs.query_hash AS QueryHash,
           qs.query_plan_hash AS QueryPlanHash,
           qs.min_worker_time / 1000.0,
           qs.max_worker_time / 1000.0,
           CASE WHEN qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)', 'float')  > 0 THEN 1 ELSE 0 END,
           qs.min_elapsed_time / 1000.0,
           qs.max_worker_time  / 1000.0,
           age_minutes,
           age_minutes_lifetime 
FROM   (SELECT TOP (@Top) x.*, xpa.*,
               CAST((CASE WHEN DATEDIFF(mi, creation_time, GETDATE()) > 0 AND execution_count > 1
                          THEN DATEDIFF(mi, creation_time, GETDATE()) 
                          ELSE NULL END) as MONEY) as age_minutes,
               CAST((CASE WHEN DATEDIFF(mi, creation_time, last_execution_time) > 0 AND execution_count > 1
                          THEN DATEDIFF(mi, creation_time, last_execution_time) 
                          ELSE Null END) as MONEY) as age_minutes_lifetime
        FROM   sys.dm_exec_query_stats x
               CROSS APPLY (SELECT * FROM sys.dm_exec_plan_attributes(x.plan_handle) AS ixpa 
                            WHERE ixpa.attribute = 'dbid') AS xpa 
        WHERE  1 = 1 
               AND COALESCE(DB_NAME(CAST(xpa.value AS INT)), '') NOT IN ('master', 'model', 'msdb', 'tempdb', '32767') AND COALESCE(DB_NAME(CAST(xpa.value AS INT)), '') NOT IN (SELECT name FROM sys.databases WHERE is_distributor = 1)
        ORDER BY total_worker_time DESC 
) AS qs 
	   CROSS JOIN(SELECT SUM(execution_count) AS t_TotalExecs,
                         SUM(CAST(total_elapsed_time AS BIGINT) / 1000.0) AS t_TotalElapsed,
                         SUM(CAST(total_worker_time AS BIGINT) / 1000.0) AS t_TotalWorker,
                         SUM(CAST(total_logical_reads AS BIGINT)) AS t_TotalReads,
                         SUM(CAST(total_logical_writes AS BIGINT)) AS t_TotalWrites
                  FROM   sys.dm_exec_query_stats) AS t
       CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
       CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp 
WHERE 1 = 1 
       AND pa.attribute = 'dbid'
ORDER BY total_worker_time DESC OPTION (RECOMPILE) 



SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
INSERT INTO ##BlitzCacheProcs (SPID, QueryType, DatabaseName, AverageCPU, TotalCPU, AverageCPUPerMinute, PercentCPUByType, PercentDurationByType,
                    PercentReadsByType, PercentExecutionsByType, AverageDuration, TotalDuration, AverageReads, TotalReads, ExecutionCount,
 
                   ExecutionsPerMinute, TotalWrites, AverageWrites, PercentWritesByType, WritesPerMinute, PlanCreationTime,
                    LastExecutionTime, StatementStartOffset, StatementEndOffset, MinReturnedRows, MaxReturnedRows, AverageReturnedRows, TotalReturnedRows,
                    LastReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB, MaxUsedGrantKB, PercentMemoryGrantUsed, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, 
					QueryText, QueryPlan, TotalWorkerTimeForType, TotalElapsedTimeForType, TotalReadsForType,
                    TotalExecutionCountForType, TotalWritesForType, SqlHandle, PlanHandle, QueryHash, QueryPlanHash,
                    min_worker_time, max_worker_time, is_parallel, min_elapsed_time, max_elapsed_time, age_minutes, age_minutes_lifetime) 
SELECT TOP (@Top)
       @@SPID ,
       'Procedure or Function: ' 
	   + QUOTENAME(COALESCE(OBJECT_SCHEMA_NAME(qs.object_id, qs.database_id),''))
	   + '.'
	   + QUOTENAME(COALESCE(OBJECT_NAME(qs.object_id, qs.database_id),'')) AS QueryType,
       COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), N'-- N/A --') AS DatabaseName,
       (total_worker_time / 1000.0) / execution_count AS AvgCPU ,
       (total_worker_time / 1000.0) AS TotalCPU ,
       CASE WHEN total_worker_time = 0 THEN 0
            WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0) = 0 THEN 0
            ELSE CAST((total_worker_time / 1000.0) / COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time)) AS MONEY)
            END AS AverageCPUPerMinute ,
       CASE WHEN t.t_TotalWorker = 0 THEN 0
            ELSE CAST(ROUND(100.00 * (total_worker_time / 1000.0) / t.t_TotalWorker, 2) AS MONEY)
            END AS PercentCPUByType,
       CASE WHEN t.t_TotalElapsed = 0 THEN 0
            ELSE CAST(ROUND(100.00 * (total_elapsed_time / 1000.0) / t.t_TotalElapsed, 2) AS MONEY)
            END AS PercentDurationByType,
       CASE WHEN t.t_TotalReads = 0 THEN 0
            ELSE CAST(ROUND(100.00 * total_logical_reads / t.t_TotalReads, 2) AS MONEY)
            END AS PercentReadsByType,
       CASE WHEN t.t_TotalExecs = 0 THEN 0
            ELSE CAST(ROUND(100.00 * execution_count / t.t_TotalExecs, 2) AS MONEY)
            END AS PercentExecutionsByType,
       (total_elapsed_time / 1000.0) / execution_count AS AvgDuration ,
       (total_elapsed_time / 1000.0) AS TotalDuration ,
       total_logical_reads / execution_count AS AvgReads ,
       total_logical_reads AS TotalReads ,
       execution_count AS ExecutionCount ,
       CASE WHEN execution_count = 0 THEN 0
            WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0) = 0 THEN 0
            ELSE CAST((1.00 * execution_count / COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time))) AS money)
            END AS ExecutionsPerMinute ,
       total_logical_writes AS TotalWrites ,
       total_logical_writes / execution_count AS AverageWrites ,
       CASE WHEN t.t_TotalWrites = 0 THEN 0
            ELSE CAST(ROUND(100.00 * total_logical_writes / t.t_TotalWrites, 2) AS MONEY)
            END AS PercentWritesByType,
       CASE WHEN total_logical_writes = 0 THEN 0
            WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0) = 0 THEN 0
            ELSE CAST((1.00 * total_logical_writes / COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0)) AS money)
            END AS WritesPerMinute,
       qs.cached_time AS PlanCreationTime,
       qs.last_execution_time AS LastExecutionTime,
       NULL AS StatementStartOffset,
       NULL AS StatementEndOffset,
       NULL AS MinReturnedRows,
       NULL AS MaxReturnedRows,
       NULL AS AvgReturnedRows,
       NULL AS TotalReturnedRows,
       NULL AS LastReturnedRows,
       NULL AS MinGrantKB,
       NULL AS MaxGrantKB,
       NULL AS MinUsedGrantK
B, 
	   NULL AS MaxUsedGrantKB,
	   NULL AS PercentMemoryGrantUsed, 
	   NULL AS AvgMaxMemoryGrant,
           NULL AS MinSpills,
           NULL AS MaxSpills,
           NULL AS TotalSpills, 
		   NULL AS AvgSpills, st.text AS QueryText ,
       query_plan AS QueryPlan,
       t.t_TotalWorker,
       t.t_TotalElapsed,
       t.t_TotalReads,
       t.t_TotalExecs,
       t.t_TotalWrites,
       qs.sql_handle AS SqlHandle,
       qs.plan_handle AS PlanHandle,
       NULL AS QueryHash,
       NULL AS QueryPlanHash,
       qs.min_worker_time / 1000.0,
       qs.max_worker_time / 1000.0,
       CASE WHEN qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)', 'float')  > 0 THEN 1 ELSE 0 END,
       qs.min_elapsed_time / 1000.0,
       qs.max_elapsed_time / 1000.0,
       age_minutes, 
       age_minutes_lifetime 
FROM   (SELECT TOP (@Top) x.*, xpa.*,
               CAST((CASE WHEN DATEDIFF(mi, cached_time, GETDATE()) > 0 AND execution_count > 1
                          THEN DATEDIFF(mi, cached_time, GETDATE()) 
                          ELSE NULL END) as MONEY) as age_minutes,
               CAST((CASE WHEN DATEDIFF(mi, cached_time, last_execution_time) > 0 AND execution_count > 1
                          THEN DATEDIFF(mi, cached_time, last_execution_time) 
                          ELSE Null END) as MONEY) as age_minutes_lifetime
        FROM   sys.dm_exec_procedure_stats x
               CROSS APPLY (SELECT * FROM sys.dm_exec_plan_attributes(x.plan_handle) AS ixpa 
                            WHERE ixpa.attribute = 'dbid') AS xpa 
        WHERE  1 = 1 
               AND COALESCE(DB_NAME(CAST(xpa.value AS INT)), '') NOT IN ('master', 'model', 'msdb', 'tempdb', '32767') AND COALESCE(DB_NAME(CAST(xpa.value AS INT)), '') NOT IN (SELECT name FROM sys.databases WHERE is_distributor = 1)
        ORDER BY total_worker_time DESC 
) AS qs 
	   CROSS JOIN(SELECT SUM(execution_count) AS t_TotalExecs,
                         SUM(CAST(total_elapsed_time AS BIGINT) / 1000.0) AS t_TotalElapsed,
                         SUM(CAST(total_worker_time AS BIGINT) / 1000.0) AS t_TotalWorker,
                         SUM(CAST(total_logical_reads AS BIGINT)) AS t_TotalReads,
                         SUM(CAST(total_logical_writes AS BIGINT)) AS t_TotalWrites
                  FROM   sys.dm_exec_procedure_stats) AS t
       CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
       CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp 
WHERE 1 = 1 
       AND pa.attribute = 'dbid'
 AND COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), '') NOT IN ('master', 'model', 'msdb', 'tempdb', '32767') AND COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), '') NOT IN (SELECT name FROM sys.databases WHERE is_distributor = 1)
ORDER BY total_worker_time DESC OPTION (RECOMPILE) 



SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
INSERT INTO ##BlitzCacheProcs (SPID, QueryType, DatabaseName, AverageCPU, TotalCPU, AverageCPUPerMinute, PercentCPUByType, PercentDurationByType,
                    PercentReadsByType, PercentExecutionsByType, AverageDuration, TotalDuration, AverageReads, TotalReads, ExecutionCount,
                    ExecutionsPerMinute, TotalWrites, AverageWrites, PercentWritesByType, WritesPerMinute, PlanCreationTime,
                    LastExecutionTime, StatementStartOffset, StatementEndOffset, MinReturnedRows, MaxReturnedRows, AverageReturnedRows, TotalReturnedRows,
                    LastReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB, MaxUsedGrantKB, PercentMemoryGrantUsed, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, 
					QueryText, QueryPlan, TotalWorkerTimeForType, TotalElapsedTimeForType, TotalReadsForType,
                    TotalExecutionCountForType, TotalWritesForType, SqlHandle, PlanHandle, Qu
eryHash, QueryPlanHash,
                    min_worker_time, max_worker_time, is_parallel, min_elapsed_time, max_elapsed_time, age_minutes, age_minutes_lifetime) 
SELECT TOP (@Top)
       @@SPID ,
       'Procedure or Function: ' 
	   + QUOTENAME(COALESCE(OBJECT_SCHEMA_NAME(qs.object_id, qs.database_id),''))
	   + '.'
	   + QUOTENAME(COALESCE(OBJECT_NAME(qs.object_id, qs.database_id),'')) AS QueryType,
       COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), N'-- N/A --') AS DatabaseName,
       (total_worker_time / 1000.0) / execution_count AS AvgCPU ,
       (total_worker_time / 1000.0) AS TotalCPU ,
       CASE WHEN total_worker_time = 0 THEN 0
            WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0) = 0 THEN 0
            ELSE CAST((total_worker_time / 1000.0) / COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time)) AS MONEY)
            END AS AverageCPUPerMinute ,
       CASE WHEN t.t_TotalWorker = 0 THEN 0
            ELSE CAST(ROUND(100.00 * (total_worker_time / 1000.0) / t.t_TotalWorker, 2) AS MONEY)
            END AS PercentCPUByType,
       CASE WHEN t.t_TotalElapsed = 0 THEN 0
            ELSE CAST(ROUND(100.00 * (total_elapsed_time / 1000.0) / t.t_TotalElapsed, 2) AS MONEY)
            END AS PercentDurationByType,
       CASE WHEN t.t_TotalReads = 0 THEN 0
            ELSE CAST(ROUND(100.00 * total_logical_reads / t.t_TotalReads, 2) AS MONEY)
            END AS PercentReadsByType,
       CASE WHEN t.t_TotalExecs = 0 THEN 0
            ELSE CAST(ROUND(100.00 * execution_count / t.t_TotalExecs, 2) AS MONEY)
            END AS PercentExecutionsByType,
       (total_elapsed_time / 1000.0) / execution_count AS AvgDuration ,
       (total_elapsed_time / 1000.0) AS TotalDuration ,
       total_logical_reads / execution_count AS AvgReads ,
       total_logical_reads AS TotalReads ,
       execution_count AS ExecutionCount ,
       CASE WHEN execution_count = 0 THEN 0
            WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0) = 0 THEN 0
            ELSE CAST((1.00 * execution_count / COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time))) AS money)
            END AS ExecutionsPerMinute ,
       total_logical_writes AS TotalWrites ,
       total_logical_writes / execution_count AS AverageWrites ,
       CASE WHEN t.t_TotalWrites = 0 THEN 0
            ELSE CAST(ROUND(100.00 * total_logical_writes / t.t_TotalWrites, 2) AS MONEY)
            END AS PercentWritesByType,
       CASE WHEN total_logical_writes = 0 THEN 0
            WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0) = 0 THEN 0
            ELSE CAST((1.00 * total_logical_writes / COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0)) AS money)
            END AS WritesPerMinute,
       qs.cached_time AS PlanCreationTime,
       qs.last_execution_time AS LastExecutionTime,
       NULL AS StatementStartOffset,
       NULL AS StatementEndOffset,
       NULL AS MinReturnedRows,
       NULL AS MaxReturnedRows,
       NULL AS AvgReturnedRows,
       NULL AS TotalReturnedRows,
       NULL AS LastReturnedRows,
       NULL AS MinGrantKB,
       NULL AS MaxGrantKB,
       NULL AS MinUsedGrantKB, 
	   NULL AS MaxUsedGrantKB,
	   NULL AS PercentMemoryGrantUsed, 
	   NULL AS AvgMaxMemoryGrant,
           NULL AS MinSpills,
           NULL AS MaxSpills,
           NULL AS TotalSpills, 
		   NULL AS AvgSpills, st.text AS QueryText ,
       query_plan AS QueryPlan,
       t.t_TotalWorker,
       t.t_TotalElapsed,
       t.t_TotalReads,
       t.t_TotalExecs,
       t.t_TotalWrites,
       qs.sql_handle AS SqlHandle,
       qs.plan_handle AS PlanHandle,
       NULL AS QueryHash,
       NULL AS QueryPlanHash,
       qs.min_worker_time / 1000.0,
       qs.max_worker_time / 1000.0,
       CASE WHEN qp.query_plan.value('declar
e namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)', 'float')  > 0 THEN 1 ELSE 0 END,
       qs.min_elapsed_time / 1000.0,
       qs.max_elapsed_time / 1000.0,
       age_minutes, 
       age_minutes_lifetime 
FROM   (SELECT TOP (@Top) x.*, xpa.*,
               CAST((CASE WHEN DATEDIFF(mi, cached_time, GETDATE()) > 0 AND execution_count > 1
                          THEN DATEDIFF(mi, cached_time, GETDATE()) 
                          ELSE NULL END) as MONEY) as age_minutes,
               CAST((CASE WHEN DATEDIFF(mi, cached_time, last_execution_time) > 0 AND execution_count > 1
                          THEN DATEDIFF(mi, cached_time, last_execution_time) 
                          ELSE Null END) as MONEY) as age_minutes_lifetime
        FROM   sys.dm_exec_trigger_stats x
               CROSS APPLY (SELECT * FROM sys.dm_exec_plan_attributes(x.plan_handle) AS ixpa 
                            WHERE ixpa.attribute = 'dbid') AS xpa 
        WHERE  1 = 1 
               AND COALESCE(DB_NAME(CAST(xpa.value AS INT)), '') NOT IN ('master', 'model', 'msdb', 'tempdb', '32767') AND COALESCE(DB_NAME(CAST(xpa.value AS INT)), '') NOT IN (SELECT name FROM sys.databases WHERE is_distributor = 1)
        ORDER BY total_worker_time DESC 
) AS qs 
	   CROSS JOIN(SELECT SUM(execution_count) AS t_TotalExecs,
                         SUM(CAST(total_elapsed_time AS BIGINT) / 1000.0) AS t_TotalElapsed,
                         SUM(CAST(total_worker_time AS BIGINT) / 1000.0) AS t_TotalWorker,
                         SUM(CAST(total_logical_reads AS BIGINT)) AS t_TotalReads,
                         SUM(CAST(total_logical_writes AS BIGINT)) AS t_TotalWrites
                  FROM   sys.dm_exec_trigger_stats) AS t
       CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
       CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp 
WHERE 1 = 1 
       AND pa.attribute = 'dbid'
 AND COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), '') NOT IN ('master', 'model', 'msdb', 'tempdb', '32767') AND COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), '') NOT IN (SELECT name FROM sys.databases WHERE is_distributor = 1)
ORDER BY total_worker_time DESC OPTION (RECOMPILE) 



SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
INSERT INTO #p (SqlHandle, TotalCPU, TotalReads, TotalDuration, TotalWrites, ExecutionCount)
SELECT  SqlHandle,
        TotalCPU,
        TotalReads,
        TotalDuration,
        TotalWrites,
        ExecutionCount
FROM    (SELECT  SqlHandle,
                 TotalCPU,
                 TotalReads,
                 TotalDuration,
                 TotalWrites,
                 ExecutionCount,
                 ROW_NUMBER() OVER (PARTITION BY SqlHandle ORDER BY TotalCPU DESC) AS rn
         FROM    ##BlitzCacheProcs
		 WHERE SPID = @@SPID) AS x
WHERE x.rn = 1
OPTION (RECOMPILE);

WITH d AS (
SELECT  SPID,
        ROW_NUMBER() OVER (PARTITION BY SqlHandle, QueryHash ORDER BY TotalCPU DESC) AS rn
FROM    ##BlitzCacheProcs
WHERE SPID = @@SPID
)
DELETE d
WHERE d.rn > 1
AND SPID = @@SPID
OPTION (RECOMPILE);
 
 
 
 
Collecting execution plan information.
Attempting to aggregate stored proc info from separate statements
Computing CPU, duration, read, and write metrics
Update CPU percentage for stored procedures
Gather percentage information from grouped results
Begin XML nodes processing
Gathering high level plan information
Performing query level checks
Performing compile timeout checks
Performing compile memory limit exceeded checks
Gathering trivial plans
Gathering statement costs
Updating statement costs
Checking for plan warnings
Checking for implicit conversion
Performing TVF join check
Checking for table variables
Checking for expensive key lookups
Checking for expensive remote queries
Checking for expensive sorts
No cursor plans found, skipping
Checking for filters that reference scalar UDFs
Checking for wonky Index Spools
Checking for forced serialization
Checking for downlevel cardinality estimators being used on SQL Server 2014.
Gathering additional plan level information
Attempting to get stored procedure name for individual statements
Attempting to get function name for individual statements
Trace flag checks
Checking for MSTVFs
Checking for non-sargable predicates
Getting information about implicit conversions and stored proc parameters
Getting variable info
Getting conversion info
Parsing conversion info
Updating variables for inserted procs
Inserting variables for other procs
Updating procs
Updating SET options
Updating conversion XML
Updating cached parameter XML for stored procs
Updating cached parameter XML for statements
Filling in implicit conversion and cached plan parameter info
Filling in missing index blanks
Setting configuration values
Checking for query level SQL Server issues.
Checking for forced parameterization and cursors.
Populating Warnings column
Populating Warnings column for stored procedures
Checking for plans with >128 levels of nesting
Checking for plans with no warnings
Displaying analysis of plan cache.
Returning ExpertMode = 0

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT  TOP (@Top)  DatabaseName AS [Database],
    QueryPlanCost AS [Cost],
    QueryText AS [Query Text],
    QueryType AS [Query Type],
    Warnings AS [Warnings],
	QueryPlan AS [Query Plan],
	missing_indexes AS [Missing Indexes],
	implicit_conversion_info AS [Implicit Conversion Info],
	cached_execution_parameters AS [Cached Execution Parameters],
    REPLACE(CONVERT(NVARCHAR(30), CAST((ExecutionCount) AS MONEY), 1), N'.00', N'') AS [# Executions],
    REPLACE(CONVERT(NVARCHAR(30), CAST((ExecutionsPerMinute) AS MONEY), 1), N'.00', N'') AS [Executions / Minute],
    REPLACE(CONVERT(NVARCHAR(30), CAST((PercentExecutions) AS MONEY), 1), N'.00', N'') AS [Execution Weight],
    REPLACE(CONVERT(NVARCHAR(30), CAST((TotalCPU) AS MONEY), 1), N'.00', N'') AS [Total CPU (ms)],
    REPLACE(CONVERT(NVARCHAR(30), CAST((AverageCPU) AS MONEY), 1), N'.00', N'') AS [Avg CPU (ms)],
    REPLACE(CONVERT(NVARCHAR(30), CAST((PercentCPU) AS MONEY), 1), N'.00', N'') AS [CPU Weight],
    REPLACE(CONVERT(NVARCHAR(30), CAST((TotalDuration) AS MONEY), 1), N'.00', N'') AS [Total Duration (ms)],
    REPLACE(CONVERT(NVARCHAR(30), CAST((AverageDuration) AS MONEY), 1), N'.00', N'') AS [Avg Duration (ms)],
    REPLACE(CONVERT(NVARCHAR(30), CAST((PercentDuration) AS MONEY), 1), N'.00', N'') AS [Duration Weight],
    REPLACE(CONVERT(NVARCHAR(30), CAST((TotalReads) AS MONEY), 1), N'.00', N'') AS [Total Reads],
    REPLACE(CONVERT(NVARCHAR(30), CAST((AverageReads) AS MONEY), 1), N'.00', N'') AS [Avg Reads],
    REPLACE(CONVERT(NVARCHAR(30), CAST((PercentReads) AS MONEY), 1), N'.00', N'') AS [Read Weight],
    REPLACE(CONVERT(NVARCHAR(30), CAST((TotalWrites) AS MONEY), 1), N'.00', N'') AS [Total Writes],
    REPLACE(CONVERT(NVARCHAR(30), CAST((AverageWrites) AS MONEY), 1), N'.00', N'') AS [Avg Writes],
    REPLACE(CONVERT(NVARCHAR(30), CAST((PercentWrites) AS MONEY), 1), N'.00', N'') AS [Write Weight],
    REPLACE(CONVERT(NVARCHAR(30), CAST((AverageReturnedRows) AS MONEY), 1), N'.00', N'') AS [Average Rows],
	REPLACE(CONVERT(NVARCHAR(30), CAST((MinGrantKB) AS MONEY), 1), N'.00', N'') AS [Minimum Memory Grant KB],
	REPLACE(CONVERT(NVARCHAR(30), CAST((MaxGrantKB) AS MONEY), 1), N'.00', N'') AS [Maximum Memory Grant KB],
	REPLACE(CONVERT(NVARCHAR(30), CAST((MinUsedGrantKB) AS MONEY), 1), N'.00', N'') AS [Minimum Used Grant KB], 
	REPLACE(CONVERT(NVARCHAR(30), CAST((MaxUsedGrantKB) AS MONEY), 1), N'.00', N'') AS [Maximum Used Grant KB],
	REPLACE(CONVERT(NVARCHAR(30), CAST((AvgMaxMemoryGrant) AS MONEY), 1), N'.00', N'') AS [Average Max Memory Grant],
	REPLACE(CONVERT(NVARCHAR(30), CAST((MinSpills) AS MONEY), 1), N'.00', N'') AS [Min Spills],
	REPLACE(CONVERT(NVARCHAR(30), CAST((MaxSpills) AS MONEY), 1), N'.00', N'') AS [Max Spills],
	REPLACE(CONVERT(NVARCHAR(30), CAST((TotalSpills) AS MONEY), 1), N'.00', N'') AS [Total Spills],
	REPLACE(CONVERT(NVARCHAR(30), CAST((AvgSpills) AS MONEY), 1), N'.00', N'') AS [Avg Spills],
    PlanCreationTime AS [Created At],
    LastExecutionTime AS [Last Execution],
	PlanHandle AS [Plan Handle], 
	SqlHandle AS [SQL Handle], 
    COALESCE(SetOptions, '') AS [SET Options] 

FROM    ##BlitzCacheProcs
WHERE   SPID = @spid 
 AND ExecutionCount >= @minimumExecutionCount 
 ORDER BY  TotalCPU  DESC  OPTION (RECOMPILE) ; 
 
 
 
 
 
 
 
 
 
Building query plan summary data.

image
image

@BrentOzar

This comment has been minimized.

Copy link
Member

commented Apr 23, 2019

I'm totally stumped - I'm not able to reproduce this at all. Leaving it open in case someone else is able to reproduce it with details as to why it's happening.

@shaneholder

This comment has been minimized.

Copy link

commented May 16, 2019

I too am having problems running sp_BlitzCache on our Azure DB’s. I have localized it to a commit made 21 days ago #1e6bee2 air_quote_actual plans (#2034)

The commit just prior #7433274 works.

I also checked the most recent commit e0ce995 and it still returns no results for my Azure SQL databases.

@shaneholder

This comment has been minimized.

Copy link

commented May 16, 2019

I started working through the changes in 1e6bee2 and the problem seems to be stemming from the detection of @VersionShowsAirQuoteActualPlans but I'm not sure. When I run the query

SELECT * FROM sys.all_columns WHERE OBJECT_ID = OBJECT_ID('sys.dm_exec_query_plan_stats') AND name = 'query_plan'

on my Azure databases it returns a row which sets @VersionShowsAirQuoteActualPlans to 1 however if I force @VersionShowsAirQuoteActualPlans to 0 then results are returned.

@BrentOzar

This comment has been minimized.

Copy link
Member

commented May 16, 2019

Ooo, interesting - your Azure SQL DB isn't populating valid data in sys.dm_exec_query_plan_stats then! Gotta love the cloud. Alright, I'm on it.

@BrentOzar BrentOzar self-assigned this May 16, 2019

@BrentOzar BrentOzar added this to the 2019-05 Release milestone May 16, 2019

BrentOzar added a commit that referenced this issue Aug 18, 2019
#2022 sp_BlitzCache disabling air_quote_actuals
This SQL 2019 & Azure SQL DB feature isn't ready yet, so disabling til Microsoft is ready. Closes #2022.
BrentOzar added a commit that referenced this issue Aug 18, 2019
#2022 sp_BlitzCache disabling air_quote_actuals (#2088)
This SQL 2019 & Azure SQL DB feature isn't ready yet, so disabling til Microsoft is ready. Closes #2022.
@BrentOzar

This comment has been minimized.

Copy link
Member

commented Aug 18, 2019

Thanks for the debugging, everybody! I removed air_quote_actuals from sp_BlitzCache for now. (They're still not working in SQL Server 2019 CTP 3.2, either.) Will revisit this later if MS gets it working.

BrentOzar added a commit that referenced this issue Aug 26, 2019
2019-08 Release (#2094)
* #2005 sp_Blitz version numbers (#2006)

Working on #2005.

* #2005 easier merges for versions

Putting Version, VersionDate variables on a single line. Closes #2005.

* #2010 sp_Blitz paused online index operations (#2011)

Add warning for rows in sys.index_resumable_operations. Closes #2010.

* Fix collation error. (#2014)

* #2015 sp_BlitzCache sorting in Azure SQL DB (#2016)

Instead of checking version numbers, look at sys.all_columns for the grant and spill columns. Closes #2015.

* #2024 sp_BlitzQueryStore s.is_cursor error (#2025)

Changed alias on a temp table to match other nearby aliases. Closes #2024.

* First responder consistency check (#1997)

* sp_Blitz update compile and runs on MSSQL 2014

* Update sp_Blitz.sql

Added @debug handling - bug found for missing mandatory component

* Update sp_Blitz.sql

* Update sp_Blitz.sql


CheckId familly:

226 - FRK consistency

Messages:
2260 - First Responder kit consistency check has been unexpectedly modified (check names)
2261 - First Responder kit consistency check has been unexpectedly modified (dynamic query failure)
2262 - First Responder kit mandatory component called %s is missing
2263 - First Responder kit consistency check has been unexpectedly modified (checks ordering)
2264 - Component %s is not at the minimum version required to run this procedure
2265 - First Responder kit consistency check (Failed dynamic SP call to %s)
2266 - First Responder kit consistency: outdated component (%s)

removed "everything ok message"

* Resolves almost all Brent's comments except CHAR(92) change

removed 2264 - Component %s is not at the minimum version required to run this procedure
changed detailled finding message for previous 2264 message

Checked compilation on SQL Server 2014

* #1994 sp_Blitz version checking (#2028)

Changed wording from "consistency check" to "version check," updated check IDs, removed mandatory scripts. Closes #1994.

* #1944 sp_BlitzIndex ignore databases (#2029)

Adds new IgnoreDatabases parameter, and skips databases with >100 partitions. Closes #1944.

* #2009 sp_BlitzFirst call BC differently (#2030)

Use SkipAnalysis = 1, SortOrder = all when calling sp_BlitzCache. Closes #2009.

* #2026 sp_BlitzCache show multiple plan count (#2031)

Closes #2026.

* #2017 sp_Blitz Evaluation Edition expiration check (#2032)

Closes #2017.

* #2018 sp_BlitzCache arith overflow (#2033)

Changing MONEY on totals to BIGINT. Closes #2018.

* #2019 sp_BlitzCache air_quote_actual plans (#2034)

Closes #2019.

* Add END in the right spot for IF @Help = 1 (#2035)

The END for IF @Help = 1 was in the wrong place, making the procedure a no-op *unless* you also specify @Help = 1.

* #2037 sp_BlitzLock support for AWS RDS (#2038)

Skips update stats with options that RDS doesn't support. Closes #2037.

* 2019_04_release_prep

Bumping version numbers, building installation scripts.

* Moved SQL header build outside of If block (#2040)

* #2042 implicit transaction troubleshooting (#2043)

Clarify warnings in sp_Blitz, sp_BlitzFirst. Closes #2042.

* edit hyperlink (#2056)

* add @OutputType = 'XML' (#2048)

* Update sp_blitzcache to avoid arithmetic overflow errors (#2046)

Updated sp_blitzcache to avoid arithmetic overflow errors - tabel definitions had changed to BIGINT but the actual logic in this section still casted values as money which was breaking the ceiling for this on our setup.  I've pretty much blanket chantged everything except averages to be bigint and have been running for a few days with no failrues since so looks ok and worth someone who knows what they're doing casting an eye over it!

* Update sp_ineachdb.sql (#2061)

Added 2 Variables 
@SQLVersion: This was implemented so that large organizations with SQL Server 2008 and R2 can use this sproc.  Currently without this change SQL Server versions 2008 and 2008R2 will error with the following.  Msg 208, Level 16, State 1, Procedure sp_ineachdb, Line 176
Invalid object name 'sys.dm_hadr_database_replica_states'.

@ServerName:  This was added to account for VMware SnapShots at times @@ServerName will either come up NULL or with old server name.  CONVERT(sysname, SERVERPROPERTY('ServerName')) is a better option and reduces minor nuisances.  

Feel free to blend in the code if you feel this brings value to the project.

* ignore readable secondaries in sp_blitzcache (#2049)

* ignore readable secondaries in sp_blitzcache

this is a fix for issue #2027

* Checking for is_primary_replica column

In sys.dm_hadr_database_replica_states.

* 2019_07 Release

Bumping version numbers and updating install scripts.

* Set DBOwner after restoring database (#2082)

* Default @RestoreDiff = NULL
If @RestoreDiff IS NULL and @BackupPathDiff != null: treat RestoreDiff=1

* Set database owner after restore

* Revert restorediff change from dev branch

* Exclude Readable secondary DBs (#2073)

Temp table added (#ReadableDBs) this table is populated with all read intent database id's which is used to filter out these database when querying against dm_exec_requests or sessions with a cross apply against a plan cache function.

* #2067 Updating directory "/" fixing to work with URLs (#2071)

* Updating directory "/" fixing to work with URLs

Also correcting incorrect logging message for fixing @MoveLogDrive slashes

* Fixing variable typo

Data should be log

* #2076 sp_BlitzIndex columnstore to table (#2085)

When saving columnstore definitions to table, trim them to 4000 characters to avoid an overflow. Closes #2076.

* @2060 sp_BlitzFirst 2TB RAM (#2086)

Casting RAM as a BIGINT instead of INT. Closes #2060.

* #2053 sp_BlitzIndex ignoring databases (#2087)

Strip out CR/LF from @IgnoreDatabases, trim spaces afterwards. Closes #2053.

* #2022 sp_BlitzCache disabling air_quote_actuals (#2088)

This SQL 2019 & Azure SQL DB feature isn't ready yet, so disabling til Microsoft is ready. Closes #2022.

* #2044 sp_Blitz new power mode (#2089)

Adds Windows 10's new Ultimate Performance Power Mode, whatever that is. Closes #2044.

* #2062 sp_BlitzFirst false alarm on index reorgs (#2091)

Filtering for sql_text not like %alter index%'. Closes #2062 with duct tape.

* #2052 sp_BlitzCache prioritization (#2092)

Fixes bug in AirQuoteActual plans, plus if plan cache is 75% new as of today, raise priority 1 warning. Closes #2052.

* #2070 sp_BlitzCache duplicated index count (#2093)

Filters index recommendations by spid. Closes #2070.

* sp_DatabaseRestore case bug

Inconsistent case sensitivity.

* 2019-09 release prep

Bumping version numbers and dates.
BrentOzar added a commit that referenced this issue Aug 26, 2019
Updating multi-install scripts. (#2095)
* #2005 sp_Blitz version numbers (#2006)

Working on #2005.

* #2005 easier merges for versions

Putting Version, VersionDate variables on a single line. Closes #2005.

* #2010 sp_Blitz paused online index operations (#2011)

Add warning for rows in sys.index_resumable_operations. Closes #2010.

* Fix collation error. (#2014)

* #2015 sp_BlitzCache sorting in Azure SQL DB (#2016)

Instead of checking version numbers, look at sys.all_columns for the grant and spill columns. Closes #2015.

* #2024 sp_BlitzQueryStore s.is_cursor error (#2025)

Changed alias on a temp table to match other nearby aliases. Closes #2024.

* First responder consistency check (#1997)

* sp_Blitz update compile and runs on MSSQL 2014

* Update sp_Blitz.sql

Added @debug handling - bug found for missing mandatory component

* Update sp_Blitz.sql

* Update sp_Blitz.sql


CheckId familly:

226 - FRK consistency

Messages:
2260 - First Responder kit consistency check has been unexpectedly modified (check names)
2261 - First Responder kit consistency check has been unexpectedly modified (dynamic query failure)
2262 - First Responder kit mandatory component called %s is missing
2263 - First Responder kit consistency check has been unexpectedly modified (checks ordering)
2264 - Component %s is not at the minimum version required to run this procedure
2265 - First Responder kit consistency check (Failed dynamic SP call to %s)
2266 - First Responder kit consistency: outdated component (%s)

removed "everything ok message"

* Resolves almost all Brent's comments except CHAR(92) change

removed 2264 - Component %s is not at the minimum version required to run this procedure
changed detailled finding message for previous 2264 message

Checked compilation on SQL Server 2014

* #1994 sp_Blitz version checking (#2028)

Changed wording from "consistency check" to "version check," updated check IDs, removed mandatory scripts. Closes #1994.

* #1944 sp_BlitzIndex ignore databases (#2029)

Adds new IgnoreDatabases parameter, and skips databases with >100 partitions. Closes #1944.

* #2009 sp_BlitzFirst call BC differently (#2030)

Use SkipAnalysis = 1, SortOrder = all when calling sp_BlitzCache. Closes #2009.

* #2026 sp_BlitzCache show multiple plan count (#2031)

Closes #2026.

* #2017 sp_Blitz Evaluation Edition expiration check (#2032)

Closes #2017.

* #2018 sp_BlitzCache arith overflow (#2033)

Changing MONEY on totals to BIGINT. Closes #2018.

* #2019 sp_BlitzCache air_quote_actual plans (#2034)

Closes #2019.

* Add END in the right spot for IF @Help = 1 (#2035)

The END for IF @Help = 1 was in the wrong place, making the procedure a no-op *unless* you also specify @Help = 1.

* #2037 sp_BlitzLock support for AWS RDS (#2038)

Skips update stats with options that RDS doesn't support. Closes #2037.

* 2019_04_release_prep

Bumping version numbers, building installation scripts.

* Moved SQL header build outside of If block (#2040)

* #2042 implicit transaction troubleshooting (#2043)

Clarify warnings in sp_Blitz, sp_BlitzFirst. Closes #2042.

* edit hyperlink (#2056)

* add @OutputType = 'XML' (#2048)

* Update sp_blitzcache to avoid arithmetic overflow errors (#2046)

Updated sp_blitzcache to avoid arithmetic overflow errors - tabel definitions had changed to BIGINT but the actual logic in this section still casted values as money which was breaking the ceiling for this on our setup.  I've pretty much blanket chantged everything except averages to be bigint and have been running for a few days with no failrues since so looks ok and worth someone who knows what they're doing casting an eye over it!

* Update sp_ineachdb.sql (#2061)

Added 2 Variables 
@SQLVersion: This was implemented so that large organizations with SQL Server 2008 and R2 can use this sproc.  Currently without this change SQL Server versions 2008 and 2008R2 will error with the following.  Msg 208, Level 16, State 1, Procedure sp_ineachdb, Line 176
Invalid object name 'sys.dm_hadr_database_replica_states'.

@ServerName:  This was added to account for VMware SnapShots at times @@ServerName will either come up NULL or with old server name.  CONVERT(sysname, SERVERPROPERTY('ServerName')) is a better option and reduces minor nuisances.  

Feel free to blend in the code if you feel this brings value to the project.

* ignore readable secondaries in sp_blitzcache (#2049)

* ignore readable secondaries in sp_blitzcache

this is a fix for issue #2027

* Checking for is_primary_replica column

In sys.dm_hadr_database_replica_states.

* 2019_07 Release

Bumping version numbers and updating install scripts.

* Set DBOwner after restoring database (#2082)

* Default @RestoreDiff = NULL
If @RestoreDiff IS NULL and @BackupPathDiff != null: treat RestoreDiff=1

* Set database owner after restore

* Revert restorediff change from dev branch

* Exclude Readable secondary DBs (#2073)

Temp table added (#ReadableDBs) this table is populated with all read intent database id's which is used to filter out these database when querying against dm_exec_requests or sessions with a cross apply against a plan cache function.

* #2067 Updating directory "/" fixing to work with URLs (#2071)

* Updating directory "/" fixing to work with URLs

Also correcting incorrect logging message for fixing @MoveLogDrive slashes

* Fixing variable typo

Data should be log

* #2076 sp_BlitzIndex columnstore to table (#2085)

When saving columnstore definitions to table, trim them to 4000 characters to avoid an overflow. Closes #2076.

* @2060 sp_BlitzFirst 2TB RAM (#2086)

Casting RAM as a BIGINT instead of INT. Closes #2060.

* #2053 sp_BlitzIndex ignoring databases (#2087)

Strip out CR/LF from @IgnoreDatabases, trim spaces afterwards. Closes #2053.

* #2022 sp_BlitzCache disabling air_quote_actuals (#2088)

This SQL 2019 & Azure SQL DB feature isn't ready yet, so disabling til Microsoft is ready. Closes #2022.

* #2044 sp_Blitz new power mode (#2089)

Adds Windows 10's new Ultimate Performance Power Mode, whatever that is. Closes #2044.

* #2062 sp_BlitzFirst false alarm on index reorgs (#2091)

Filtering for sql_text not like %alter index%'. Closes #2062 with duct tape.

* #2052 sp_BlitzCache prioritization (#2092)

Fixes bug in AirQuoteActual plans, plus if plan cache is 75% new as of today, raise priority 1 warning. Closes #2052.

* #2070 sp_BlitzCache duplicated index count (#2093)

Filters index recommendations by spid. Closes #2070.

* sp_DatabaseRestore case bug

Inconsistent case sensitivity.

* 2019-09 release prep

Bumping version numbers and dates.

* 2019-08 release

Updating multi-script installs.
BrentOzar added a commit that referenced this issue Sep 22, 2019
2019-09 Release (#2121)
* #2005 sp_Blitz version numbers (#2006)

Working on #2005.

* #2005 easier merges for versions

Putting Version, VersionDate variables on a single line. Closes #2005.

* #2010 sp_Blitz paused online index operations (#2011)

Add warning for rows in sys.index_resumable_operations. Closes #2010.

* Fix collation error. (#2014)

* #2015 sp_BlitzCache sorting in Azure SQL DB (#2016)

Instead of checking version numbers, look at sys.all_columns for the grant and spill columns. Closes #2015.

* #2024 sp_BlitzQueryStore s.is_cursor error (#2025)

Changed alias on a temp table to match other nearby aliases. Closes #2024.

* First responder consistency check (#1997)

* sp_Blitz update compile and runs on MSSQL 2014

* Update sp_Blitz.sql

Added @debug handling - bug found for missing mandatory component

* Update sp_Blitz.sql

* Update sp_Blitz.sql


CheckId familly:

226 - FRK consistency

Messages:
2260 - First Responder kit consistency check has been unexpectedly modified (check names)
2261 - First Responder kit consistency check has been unexpectedly modified (dynamic query failure)
2262 - First Responder kit mandatory component called %s is missing
2263 - First Responder kit consistency check has been unexpectedly modified (checks ordering)
2264 - Component %s is not at the minimum version required to run this procedure
2265 - First Responder kit consistency check (Failed dynamic SP call to %s)
2266 - First Responder kit consistency: outdated component (%s)

removed "everything ok message"

* Resolves almost all Brent's comments except CHAR(92) change

removed 2264 - Component %s is not at the minimum version required to run this procedure
changed detailled finding message for previous 2264 message

Checked compilation on SQL Server 2014

* #1994 sp_Blitz version checking (#2028)

Changed wording from "consistency check" to "version check," updated check IDs, removed mandatory scripts. Closes #1994.

* #1944 sp_BlitzIndex ignore databases (#2029)

Adds new IgnoreDatabases parameter, and skips databases with >100 partitions. Closes #1944.

* #2009 sp_BlitzFirst call BC differently (#2030)

Use SkipAnalysis = 1, SortOrder = all when calling sp_BlitzCache. Closes #2009.

* #2026 sp_BlitzCache show multiple plan count (#2031)

Closes #2026.

* #2017 sp_Blitz Evaluation Edition expiration check (#2032)

Closes #2017.

* #2018 sp_BlitzCache arith overflow (#2033)

Changing MONEY on totals to BIGINT. Closes #2018.

* #2019 sp_BlitzCache air_quote_actual plans (#2034)

Closes #2019.

* Add END in the right spot for IF @Help = 1 (#2035)

The END for IF @Help = 1 was in the wrong place, making the procedure a no-op *unless* you also specify @Help = 1.

* #2037 sp_BlitzLock support for AWS RDS (#2038)

Skips update stats with options that RDS doesn't support. Closes #2037.

* 2019_04_release_prep

Bumping version numbers, building installation scripts.

* Moved SQL header build outside of If block (#2040)

* #2042 implicit transaction troubleshooting (#2043)

Clarify warnings in sp_Blitz, sp_BlitzFirst. Closes #2042.

* edit hyperlink (#2056)

* add @OutputType = 'XML' (#2048)

* Update sp_blitzcache to avoid arithmetic overflow errors (#2046)

Updated sp_blitzcache to avoid arithmetic overflow errors - tabel definitions had changed to BIGINT but the actual logic in this section still casted values as money which was breaking the ceiling for this on our setup.  I've pretty much blanket chantged everything except averages to be bigint and have been running for a few days with no failrues since so looks ok and worth someone who knows what they're doing casting an eye over it!

* Update sp_ineachdb.sql (#2061)

Added 2 Variables 
@SQLVersion: This was implemented so that large organizations with SQL Server 2008 and R2 can use this sproc.  Currently without this change SQL Server versions 2008 and 2008R2 will error with the following.  Msg 208, Level 16, State 1, Procedure sp_ineachdb, Line 176
Invalid object name 'sys.dm_hadr_database_replica_states'.

@ServerName:  This was added to account for VMware SnapShots at times @@ServerName will either come up NULL or with old server name.  CONVERT(sysname, SERVERPROPERTY('ServerName')) is a better option and reduces minor nuisances.  

Feel free to blend in the code if you feel this brings value to the project.

* ignore readable secondaries in sp_blitzcache (#2049)

* ignore readable secondaries in sp_blitzcache

this is a fix for issue #2027

* Checking for is_primary_replica column

In sys.dm_hadr_database_replica_states.

* 2019_07 Release

Bumping version numbers and updating install scripts.

* Set DBOwner after restoring database (#2082)

* Default @RestoreDiff = NULL
If @RestoreDiff IS NULL and @BackupPathDiff != null: treat RestoreDiff=1

* Set database owner after restore

* Revert restorediff change from dev branch

* Exclude Readable secondary DBs (#2073)

Temp table added (#ReadableDBs) this table is populated with all read intent database id's which is used to filter out these database when querying against dm_exec_requests or sessions with a cross apply against a plan cache function.

* #2067 Updating directory "/" fixing to work with URLs (#2071)

* Updating directory "/" fixing to work with URLs

Also correcting incorrect logging message for fixing @MoveLogDrive slashes

* Fixing variable typo

Data should be log

* #2076 sp_BlitzIndex columnstore to table (#2085)

When saving columnstore definitions to table, trim them to 4000 characters to avoid an overflow. Closes #2076.

* @2060 sp_BlitzFirst 2TB RAM (#2086)

Casting RAM as a BIGINT instead of INT. Closes #2060.

* #2053 sp_BlitzIndex ignoring databases (#2087)

Strip out CR/LF from @IgnoreDatabases, trim spaces afterwards. Closes #2053.

* #2022 sp_BlitzCache disabling air_quote_actuals (#2088)

This SQL 2019 & Azure SQL DB feature isn't ready yet, so disabling til Microsoft is ready. Closes #2022.

* #2044 sp_Blitz new power mode (#2089)

Adds Windows 10's new Ultimate Performance Power Mode, whatever that is. Closes #2044.

* #2062 sp_BlitzFirst false alarm on index reorgs (#2091)

Filtering for sql_text not like %alter index%'. Closes #2062 with duct tape.

* #2052 sp_BlitzCache prioritization (#2092)

Fixes bug in AirQuoteActual plans, plus if plan cache is 75% new as of today, raise priority 1 warning. Closes #2052.

* #2070 sp_BlitzCache duplicated index count (#2093)

Filters index recommendations by spid. Closes #2070.

* sp_DatabaseRestore case bug

Inconsistent case sensitivity.

* 2019-09 release prep

Bumping version numbers and dates.

* 2019-08 release

Updating multi-script installs.

* #2097 sp_DatabaseRestore sysname caps (#2100)

Change from uppercase to lowercase for case-sensitive systems. Closes #2097.

* Issue 2063 selects with writes (#2101)

* Update sp_BlitzCache.sql

Adds check for selects that cause writes.

* Update BC docs

Adds a line to the documentation for BlitzCache, tidies up warning table message.

* Fix compression query perf (#2103)

Breaking this up into #temp tables has made it much faster on servers with many partitions.

* Issue 2051 query hash sort (#2102)

* Add query hash sort order

Grabs top10 highest cpu consuming plans with highest count of query hashes (multiple plans), and runs BlitsCache to find them.

* add a word

why not?

* Tweaks for query hash

* URL housekeeping (#2098)

* New Zealand is a real place and it's offensive for you to suggest otherwise 🥝

* revert edits to Install* scripts

* #2111 sp_BlitzCache missing union all (#2112)

Cleans up the documentation, closes #2111.

* Added Uninstaller Script (#2105)

* Added Uninstaller Script

Fulfills request #2080

Deletes all the FirstResponderKit procedures from the current DB or all DBs via parameter

* Case sensitive collation fixes

Gotta love those case sensitive servers.

* Remove stray semicolon (#2115)

Closes #2114 

Semicolon throws syntax error.

* Fix roll up where clause (#2116)

Closes #2113 

Where clause should only look at flagged lines.

* #2096 sp_BlitzFirst BST compatibility (#2117)

Not really about BST, heh, but just using the wrong date/time format. Closes #2096.

* 2109_sp_BlitzCache_AirQuoteActuals (#2118)

If the AirQuoteActual plan is longer than the regular plan, use it. Closes #2109.

* #2119 2019-09 release prep (#2120)

Bumping version numbers, dates, install scripts. Closes #2119.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
4 participants
You can’t perform that action at this time.