Is this an appropriate way to find all references to a database with sp_QuickieStore?

[ReeceGoding]

Which script is your question about?

Script Name: sp_QuickieStore

Script version

4.2

Is your question about how they work, or the results?

It's about how they work. I suspect that I'm misunderstanding the filters and the effect of putting [ in them.

Okay, what's your question?

In order to find all referenced to a database, I ran

EXEC sp_QuickieStore @get_all_databases = 1, @sort_order = 'executions', @top = 50, @query_text_search = 'NameHasADot.TheDbIWantToFind';
EXEC sp_QuickieStore @get_all_databases = 1, @sort_order = 'executions', @top = 50, @query_text_search = '[NameHasADot.TheDbIWantToFind]';

The first block returned a single cell containing #query_store_runtime_stats is empty. It also returned the "all done" table, but nothing else.

The second block returned the "all done" table and 281 rows (don't really understand that particular number) in the other table. The vast majority of the queries in these rows obviously do not hit the database and the given executions plans as well as the XML for said plans confirm this.

Despite this, I know that the database is being hit because the below returns a few results.

EXEC sp_QuickieStore @database_name = 'NameHasADot.TheDbIWantToFind', @sort_order = 'executions', @top = 50;

Do I have some big misunderstanding? Is what I'm trying to do achievable? As far as I can tell, the @query_text_search = '[NameHasADot.TheDbIWantToFind] filter is being treated as if I didn't supply the argument at all.

[erikdarlingdata]

@ReeceGoding Just to clarify, you're looking for queries in other databases that hit tables in a specific database?

NameHasADot.TheDbIWantToFind doesn't make a lot of sense to me, because it should be TheDbIWantToFind.SchemaName, probably. When you add brackets, it changes the semantics of LIKE search pattern matching. That's why the @escape_brackets parameter was added in #360

[ReeceGoding]

@erikdarlingdata That's right. I'm trying to find all queries in any database's Query Store that hit a specific database. The name of that database has a . in it.

It has now occurred to me that my "Despite this, I know that the database is being hit because the below returns a few results." objection might not be sensible. This is because the query text for queries inside the NameHasADot.TheDbIWantToFind database will usually not have NameHasADot.TheDbIWantToFind anywhere in their text. The rest of my points stand.

Fortunately, I suspect that @escape_brackets will be all that I need. I'm surprised that I missed it. It's not listed in the docs on GitHub but I should've known to check @Help = 1. That said, the following still returns empty even after running a query that absolutely references [NameHasADot.TheDbIWantToFind] from another database.

EXEC sp_QuickieStore @get_all_databases = 1, @sort_order = 'executions', @top = 50, @query_text_search = '[NameHasADot.TheDbIWantToFind]', @escape_brackets = 1;

I'll give the @Help = 1 output a much closer look, but I am not currently seeing anything obviously wrong with my approach.

[erikdarlingdata]

@ReeceGoding can you run it in debug mode and let me know if anything looks odd either in the intermediate temp table results, or with the final parameter state?

[erikdarlingdata]

@ReeceGoding I tried to repo this a bit, but I can't seem to.

Rather than create a database with a . in the name, I just did something similar in a query:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE p.Body LIKE '[something.stuff]';

When than's done, I can run either:

EXEC sp_QuickieStore 
    @database_name = 'StackOverflow2013', 
    @sort_order = 'recent', 
    @query_text_search = 'something.stuff';


EXEC sp_QuickieStore 
    @database_name = 'StackOverflow2013', 
    @sort_order = 'recent', 
    @query_text_search = '[something.stuff]',
    @escape_brackets = 1;

And get back the correct result, just one row:

Not sure what's going on on your end. I might need a repro from you to figure it out.

[ReeceGoding]

@erikdarlingdata While in the process of writing up a repro and looking at the @debug output, I found something interesting from running this.

EXEC sp_QuickieStore @get_all_databases = 1, @sort_order = 'executions', @top = 50, @query_text_search = '[NameHasADot.TheDbIWantToFind]', @escape_brackets = 1, @debug = 1;

Even on a server that does not have the database in question, the debug table that has its first cell contain "procedure_parameters" shows an interesting value in the "query_text_search" column. On a server with Query Store enabled on five non-system databases, the value in that cell is

%\\\\\[NameHasADot.TheDbIWantToFind]\\\\\]%'

and on a server with twenty-three such databases, it shows the same, but with twenty-three \ on each side.

Is that a sign that @get_all_databases = 1 and @escape_brackets = 1 don't work well together?

[ReeceGoding]

I'm closing this. It has become clear that my incorrect "Despite this, I know that the database is being hit because the below returns a few results." assumption poisoned everything else that I said. I'll re-raise my previous post as a new issue.