sp_BlitzCache receiving invalid column names

[rlfink]

SELECT @Version = '8.17', @VersionDate = '20231010';

When running the following command against an Azure SQL Managed Instance I am receiving errors. It does run quite a while before receiving the errors. The procedure dumped 123 rows to the table before it errored out.

sp_BlitzCache @DatabaseName='datahub',@TOP=2000,@OutputDatabaseName='rlf',@OutputSchemaName='dbo',@OutputTableName='CacheResults'

From the Messages tab in SSMS:

Checking @MinutesBack validity. Checking database validity Checking sort order Creating temp tables for internal processing Checking for Read intent databases to exclude Checking plan cache age Checking for single use plans and plans with many queries Setting up variables Determining SQL Server version. Creating dynamic SQL based on SQL Server version. Ignoring readable secondaries databases by default Ignoring system databases by default Filtering database name chosen Applying chosen sort order Getting spill information for newer versions of SQL Adding additional info columns for newer versions of SQL Getting memory grant information for newer versions of SQL Getting spill information for newer versions of SQL Adding SQL to collect trigger stats. Creating temp tables for results and warnings. 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 Gathering stored procedure 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 wonky Table Spools Checking for selects that cause non-spill and index spool writes Checking for forced serialization Checking for downlevel cardinality estimators being used on SQL Server 2014. 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 Inserting to #missing_index_xml Inserting to #missing_index_schema Inserting to #missing_index_usage Inserting to #missing_index_detail Inserting to missing indexes to #missing_index_pretty Inserting to #index_spool_ugly Inserting to spools to #missing_index_pretty Updating missing index information 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. Writing results to table. Msg 207, Level 16, State 1, Line 87 Invalid column name 'PlanCreationTime'. Msg 207, Level 16, State 1, Line 87 Invalid column name 'CheckDate'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'ServerName'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'CheckDate'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'Version'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'QueryType'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'DatabaseName'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'AverageCPU'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'TotalCPU'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'PercentCPUByType'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'CPUWeight'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'AverageDuration'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'TotalDuration'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'DurationWeight'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'PercentDurationByType'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'AverageReads'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'TotalReads'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'ReadWeight'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'PercentReadsByType'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'AverageWrites'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'TotalWrites'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'WriteWeight'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'PercentWritesByType'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'ExecutionCount'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'ExecutionWeight'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'PercentExecutionsByType'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'ExecutionsPerMinute'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'PlanCreationTime'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'LastExecutionTime'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'PlanHandle'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'SqlHandle'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'QueryHash'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'QueryPlanHash'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'StatementStartOffset'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'StatementEndOffset'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'MinReturnedRows'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'MaxReturnedRows'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'AverageReturnedRows'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'TotalReturnedRows'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'QueryText'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'QueryPlan'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'NumberOfPlans'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'NumberOfDistinctPlans'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'Warnings'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'SerialRequiredMemory'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'SerialDesiredMemory'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'MinGrantKB'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'MaxGrantKB'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'MinUsedGrantKB'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'MaxUsedGrantKB'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'PercentMemoryGrantUsed'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'AvgMaxMemoryGrant'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'MinSpills'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'MaxSpills'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'TotalSpills'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'AvgSpills'. Msg 207, Level 16, State 1, Line 6 Invalid column name 'QueryPlanCost'.

Completion time: 2023-11-01T09:33:48.2220497-04:00

[BrentOzar]

Okay, no, first, don't do that. This isn't what sp_BlitzCache is for.

Your input parameters say you're asking for TWO THOUSAND execution plans, filtered for just one database. That doesn't make any sense.

What's the business problem you're trying to solve, and I can steer you towards a better solution?

[rlfink]

I am looking for recently used queries against a particular table within that database. The database is essentially used as a datastore for loading cubes and general query access by PowerBI users. Access to source queries is limited.

Thank you

From: Brent Ozar @.> Sent: Wednesday, November 1, 2023 11:04 AM To: BrentOzarULTD/SQL-Server-First-Responder-Kit @.> Cc: Randy Fink @.>; Author @.> Subject: Re: [BrentOzarULTD/SQL-Server-First-Responder-Kit] sp_BlitzCache receiving invalid column names (Issue #3390)

Okay, no, first, don't do that. This isn't what sp_BlitzCache is for.

Your input parameters say you're asking for TWO THOUSAND execution plans, filtered for just one database. That doesn't make any sense.

What's the business problem you're trying to solve, and I can steer you towards a better solution?

— Reply to this email directly, view it on GitHubhttps://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/3390#issuecomment-1789115927, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AOM4ABMNPI22756R7RICDALYCJQFJAVCNFSM6AAAAAA6ZNPBKWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOBZGEYTKOJSG4. You are receiving this because you authored the thread.Message ID: @.**@.>>

CONFIDENTIALITY NOTICE ROUND ROOM HOLDINGS INC. is the parent company for various brands including ROUND ROOM, REDUX, WIRELESS ZONE, TCC, TCC GIVES, and CULTURE OF GOOD. This electronic transmission (including any attachments) may contain legally privileged and confidential information which may be subject to attorney-client or attorney work-product protection, exempt from disclosure under applicable laws. This communication is intended solely for the use of the intended addressee(s). If you received this communication in error and are not the intended addressee, any distribution, copying or other use of this communication (or its attachments) is strictly prohibited. Please contact the sender by reply e-mail immediately and destroy all copies of the original message.

[BrentOzar]

@rlfink sp_BlitzCache isn't a good fit for that, unfortunately.

Your best bet there would be something like SQL Server's built-in Audit functionality: https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver16