SQL Server – Telemetry / QueryStore – Query Error

Background

As part of monitoring our SQL Server noticed an error.

Error

Here is the error :-

  1. Error Number :- 4104
  2. Error Text :- The multi-part identifier “qry.is_internal_query” could not be bound.

Query Text


SELECT db_id() AS database_id,
       COUNT_BIG(1) QueryCount,
       SUM(count_compiles) TotalCompiles,
       MAX(last_compile_duration) MaxLastCompileDuration,
       MIN(last_compile_duration) MinLastCompileDuration,
       AVG(last_compile_duration) AvgLastCompileDuration,
       SUM(last_compile_duration) TotalLastCompileDuration
FROM sys.query_store_query AS Qry
WHERE qry.last_execution_time
         >= DATEADD(HOUR, -24, GETDATE())
  AND qry.is_internal_query = 0

Troubleshoot

The error was straight forward enough to troubleshoot.

Remedy

Outline

  1. Our database is case-sensitive
    • Replace alias Qry with qry

SQL


SELECT db_id() AS database_id,
       COUNT_BIG(1) QueryCount,
       SUM(count_compiles) TotalCompiles,
       MAX(last_compile_duration) MaxLastCompileDuration,
       MIN(last_compile_duration) MinLastCompileDuration,
       AVG(last_compile_duration) AvgLastCompileDuration,
       SUM(last_compile_duration) TotalLastCompileDuration

-- FROM sys.query_store_query AS Qry

FROM sys.query_store_query AS qry

WHERE qry.last_execution_time
        >= DATEADD(HOUR, -24, GETDATE())

AND qry.is_internal_query = 0

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s