Finding Your Query in Query Store

Query Store is pretty amazing. I’m loving working with it. I think it’s likely to change how query tuning will be done in the future. Lots of people are probably going to just use the reports and tools in SQL Server Management Studio. However, a pretty healthy chunk of us will start using the system views in order to programmatically access the information stored in Query Store. One of the first things you’re going to want to do is track down your query.

The primary views you’ll want are sys.query_store_query and sys.query_store_query_text. They join together based on the query_text_id. Let’s take four scenarios and see if we can retrieve the correct query. First up, an ad hoc query:

SELECT  e.NationalIDNumber,
        p.LastName,
        p.FirstName,
        a.City,
        bea.AddressTypeID
FROM    HumanResources.Employee AS e
JOIN    Person.BusinessEntityAddress AS bea
        ON bea.BusinessEntityID = e.BusinessEntityID
JOIN    Person.Address AS a
        ON a.AddressID = bea.AddressID
JOIN    Person.Person AS p
        ON p.BusinessEntityID = e.BusinessEntityID
WHERE   p.LastName = 'Hamilton';

If we wanted to retrieve this from the Query Store AdventureWorks2014, we’d run a query like this:

SELECT * FROM sys.query_store_query_text AS qsqt
WHERE qsqt.query_sql_text = 'SELECT  e.NationalIDNumber,
        p.LastName,
        p.FirstName,
        a.City,
        bea.AddressTypeID
FROM    HumanResources.Employee AS e
JOIN    Person.BusinessEntityAddress AS bea
        ON bea.BusinessEntityID = e.BusinessEntityID
JOIN    Person.Address AS a
        ON a.AddressID = bea.AddressID
JOIN    Person.Person AS p
        ON p.BusinessEntityID = e.BusinessEntityID
WHERE   p.LastName = ''Hamilton''';

Of note, I had to drop the statement terminator from the text of the query, the semi-colon, in order to retrieve the correct query. That’ll be good to know in a moment. This retrieves the query information I requested, just fine.

For our next example, let’s simplify things a whole bunch:

SELECT  *
FROM    Production.BillOfMaterials AS bom
WHERE   bom.BillOfMaterialsID = 2363;

If I then attempt to retrieve the information from Query Store like this:

SELECT  *
FROM    sys.query_store_query_text AS qsqt
WHERE   qsqt.query_sql_text = 'SELECT  *
FROM    Production.BillOfMaterials AS bom
WHERE   bom.BillOfMaterialsID = 2363';

It actually doesn’t work. Note, I took off the statement terminator, just like before. In fact, the problem here can be identified if we look at the T-SQL from the execution plan from the second example:

SELECT * FROM [Production].[BillOfMaterials] [bom] WHERE [bom].[BillOfMaterialsID]=@1

This query has gone through simple parameterization. So, in order to retrieve the information from Query Store, we have a function, sys.fn_stmt_sql_handle_from_sql_stmt that we have to incorporate like this:

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
JOIN    sys.query_store_query AS qsq
        ON qsq.query_text_id = qsqt.query_text_id
CROSS APPLY sys.fn_stmt_sql_handle_from_sql_stmt('SELECT  *
FROM    Production.BillOfMaterials AS bom
WHERE   bom.BillOfMaterialsID = 2363;', qsq.query_parameterization_type) AS fsshfss

And note, I have the statement terminator back in place, but this function takes that into account. I joined to the query_store_query table in order to get the parameterization type value. With the function figuring out the statement handle based on the text I originally ran the query through, everything is hunky dory.

Let’s look at one more example:

EXEC dbo.spAddressByCity
    @City = N'London';

This is a stored procedure, so I can just do this:

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
JOIN    sys.query_store_query AS qsq
        ON qsq.query_text_id = qsqt.query_text_id
WHERE   qsq.object_id = OBJECT_ID('dbo.spAddressByCity');

However, you may have multi-statement stored procs, so you might want to query based on the text within the procedure like this:

SELECT  *
FROM    sys.query_store_query_text AS qsqt
WHERE   qsqt.query_sql_text = 'SELECT  a.AddressID,
        a.AddressLine1,
        a.AddressLine2,
        a.City,
        sp.Name AS StateProvinceName,
        a.PostalCode
FROM    Person.Address AS a
JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City = @City'

Bad news. That doesn’t work. If you looked at the qsqt.query_sql_text value in the previous Query Store query using the OBJECT_ID function, you’ll see that the query looks like this within the Query Store:

(@City nvarchar(30))SELECT  a.AddressID,          a.AddressLine1,          a.AddressLine2,          a.City,          sp.Name AS StateProvinceName,          a.PostalCode  FROM    Person.Address AS a  JOIN    Person.StateProvince AS sp          ON a.StateProvinceID = sp.StateProvinceID  WHERE   a.City = @City

But, if you look at the documentation for sys.fn_stmt_sql_handle_from_sql_stmt that I linked above, it only works with simple or forced parameterization (this, despite having options for none and user, 0 & 1 respectively in the documents). That means you may be resorted to the use of LIKE to retrieve particular statements:

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
WHERE qsqt.query_sql_text LIKE '%SELECT  a.AddressID,
        a.AddressLine1,
        a.AddressLine2,
        a.City,
        sp.Name AS StateProvinceName,
        a.PostalCode
FROM    Person.Address AS a
JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
WHERE   a.City = @City%';

At least, that’s where we’re at with the current, public, CTP.

Tracking down your query can be a little bit of work and hopefully these tips will make it a little easier.