4

I have enabled Query Store on SQL 2017, and I'm seeing at query that's occurring frequently doing a SELECT * on a specific table.

I would like to narrow down where this request is coming from and see if we can find a better way than doing the SELECT *.

I have a query id, of course, from Query Store. I've also recently asked my developers to include Application Name in their connection strings, which many have done.

Is there a way (perhaps using a DMV, for example) that I can find out the Application Name associated with this query?

  • Run sp_who2 or activity monitor and see if yiu can spot it. Not the best options but quick and easy. – Sir Swears-a-lot May 24 '18 at 8:33
  • Thanks, but neither of those options let me see the query running at any given time. I supposed I could continuously run sp_WhoisActive, until it pops up but I was looking for a better option. – Del Lee May 24 '18 at 15:48
6

Query the query store DMVs for the query hash, then use that query hash in an Extended Events session to capture the pertinent details.

This should get you there:

SELECT qsq.query_hash
    , qsqt.query_sql_text
FROM sys.query_store_query qsq
    INNER JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
WHERE qsqt.query_sql_text LIKE '%some-pertinent-SQL%';

In the query above, I have LIKE '%some-pertinent-SQL%' - this limits the results of the query to match the target your looking for - you need to replace the some-pertinent-SQL with a unique piece of your target query.

Use the value from the query_hash column returned above in the Extended Events session definition:

CREATE EVENT SESSION [t] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name
    , sqlserver.client_hostname
    , sqlserver.nt_username
    , sqlserver.plan_handle
    , sqlserver.query_hash)
    WHERE ([sqlserver].[query_hash]=(0x00000000))
    )
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=OFF);
GO

Replace the 0x00000000 with the actual query hash value.

Start the Extended Events session with:

ALTER EVENT SESSION [t] ON SERVER 
STATE = START;

Right-click the Extended Events session in Object Explorer to see details about the client hostname, username, etc., for queries as they happen.

enter image description here

To test this, I executed the following code on my SQL Server 2016 test instance:

CREATE DATABASE QueryStoreTest
ON PRIMARY 
(
      NAME = QueryStoreTest_primary
    , FILENAME = 'C:\temp\QueryStoreTest_primary.mdf'
    , SIZE = 100MB
    , FILEGROWTH = 100MB
    , MAXSIZE = 1000MB
)
LOG ON 
(
      NAME = QueryStoreTest_log
    , FILENAME = 'C:\temp\QueryStoreTest_log.ldf'
    , SIZE = 100MB
    , FILEGROWTH = 100MB
    , MAXSIZE = 1000MB
);
GO
ALTER DATABASE QueryStoreTest 
SET QUERY_STORE = ON;

Here, I'll create a couple of objects in the QueryStoreTest database, and a test query:

USE QueryStoreTest;
CREATE TABLE dbo.qst
(
    id int NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.qst2
(
    id int NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

SELECT *
/*  test query store query  */
FROM dbo.qst
    LEFT MERGE JOIN dbo.qst2 on qst.id = qst2.id;

Here, I'll get the query hash from the query store:

SELECT qsq.query_hash
    , qsqt.query_sql_text
FROM sys.query_store_query qsq
    INNER JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
WHERE qsqt.query_sql_text LIKE '%test query store query%';

Results:

╔════════════════════╦════════════════════════════════════════════════════════╗
║     query_hash     ║                     query_sql_text                     ║
╠════════════════════╬════════════════════════════════════════════════════════╣
║ 0x3A0100223AD74766 ║ SELECT *  /*  test query store query  */  FROM dbo.qst ║
║                    ║  LEFT MERGE JOIN dbo.qst2 on qst.id = qst2.id          ║
╚════════════════════╩════════════════════════════════════════════════════════╝

Now, I'll create the Extended Events Session:

CREATE EVENT SESSION [t] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION (
          sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.nt_username
        , sqlserver.plan_handle
        , sqlserver.query_hash
    )
    WHERE ([sqlserver].[query_hash]=(0x3A0100223AD74766))
)
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=OFF)
GO

And start the session:

ALTER EVENT SESSION [t] ON SERVER 
STATE = START;

Now, if I run the test query and look at the live data from the session, I see:

enter image description here

Cleanup:

USE master;
GO
ALTER EVENT SESSION [t] ON SERVER 
STATE = STOP;
GO
DROP EVENT SESSION [t] ON SERVER;
GO
ALTER DATABASE QueryStoreTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE QueryStoreTest;
  • Thanks, this is a very informative use for extended events! – Del Lee May 29 '18 at 15:40

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.