How to search Query Store for a specific word or command in a query?

Question

I have an Enterprise Edition of SQL 2016 SP1, that has a database with Query Store active.

I want to search for a specific word or command in the saved queries. I have googled around and not seen anything. In this case I want to find 'SHRINK', but the same approach should be usable for any text in stored query.

Anticipating the "why?" comment. I have a vendor app that does a bunch of work and then shrinks the database and log files. I am trying to find the query that does it.

How to search Query Store for a specific word or command in a query?

Answer 1

As sp_BlitzErik mentioned in a comment, and as I gathered from my own research, you can find some things in sys.query_store_query_text but not necessarily always a SHRINK.

This query returns 22,497 results:

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
WHERE qsqt.query_sql_text LIKE '%Select%'

This query does not return any, except the second time I run it :)

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
WHERE qsqt.query_sql_text LIKE '%SHRINK%'

The foundation of the query above comes from a SQL with Bert post.

A coworker has shown me a query that finds the shrink and the time and login of who did it. So I have what I need for the my specific issue, but that is something of a side step from my question.

My workaround uses the default query trace. It shows multiple recent DBCC Shrinkdatabase ('MyDB', TRUNCATEONLY) that are run against the master database, which does not have query store.

A DBCC SHRINKFILE run in the context of a database with query store enabled does get recorded in the query store.