2

I am trying to find plans for a particular set of queries (those that update MyTable) using sp_blitzcache:

EXEC sp_Blitzcache @SlowlySearchPlansFor = 'UPDATE [My Table]'

this returns 3 results, however, when I run the following query

SELECT  d.name,
        t.text AS TSQL_Text,
        s.creation_time, 
        s.execution_count,
        s.total_worker_time AS total_cpu_time,
        s.total_elapsed_time, 
        s.total_logical_reads, 
        s.total_physical_reads, 
        p.query_plan
FROM    sys.dm_exec_query_stats s
        CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) t
        CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
        JOIN sys.databases d
            ON t.dbid = d.database_id
WHERE   t.text LIKE '%UPDATE \[My Table\]%' ESCAPE '\'

it returns far more.

Is there some sort of limit in sp_blitzcache around what queries are found? For example, are low cost queries ignored?

5

Yes, there's a known issue with that parameter not returning all results due to spaces, and I wouldn't be surprised if that's also affecting you:

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/2202

However, let's zoom out a little: you can't rely on a text search of the plan cache to find everything updating a table. You could have multiple spaces, tabs, even line returns or comments between the word UPDATE and the start of your table name. The table name might also be prefixed with the database name or schema name.

As a result, I wouldn't recommend this technique to find queries that update a particular table.

| improve this answer | |
  • Thankyou Brent! – SEarle1986 Apr 3 at 12:23
  • I would say this is a bug, but maybe there is some legitimate reason for SQL Server trimming the result when casting from XML to varchar? – Jacob H Apr 3 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.