Where is this Query Coming From?

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?

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.

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:

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;

StackExchange Database Administrators Q#207684, answer score: 7