8

Is there any option to see killed sessions in Query Store?

I am asking, because we have an additional tool which is terminating sessions if they take more than 30 minutes to run (KILL command).

I would like to check the execution plans in the Query Store for queries that have been terminated. I can not find in query store sessions/queries which were killed by this additional application.

0

2 Answers 2

5

You might want to consider just tracking queries that take longer than a certain amount of time using the Extended Events (Quick Start Extended Events) feature that is provided with newer version of SQL Server.

Let's create an Extended Event for you that captures statements that run longer than 60 seconds (just an example).

Navigate to the Extended Events in SSMS

SQL Server Instance | Management | Extended Events | Sessions

Here you can right-click on the Sessions branch to open up the context menu.

New Session...

New Session... context menu item

Contrary to the marked session, we will use the empty New Session... option.

New Session Wizard Introduction

New Session Wizard Introduction dialog

Click on Next >

New Session Wizard Set Session Properties

New Session Wizard Set Session Properties dialog

We'll give the session a name: Statement Duration > 1 Minute, then click the option to Start the event session at server startup, and then click on Next >..

New Session Wizard Choose Template

New Session Wizard Choose Template dialog

We won't be using a default template. Next >

New Session Wizard Select Event To Capture

New Session Wizard Select Event To Capture dialog

We'll only use the sql_transaction event and move that over to the right with the corresponding > arrow. Next >.

New Session Wizard Capture Global Fields

New Session Wizard Capture Global Fields dialog

We'll select at least the sql_text field, but I added some more like:

  • client_app_name
  • client_hostname
  • database_name
  • nt_username
  • plan_handle
  • query_hash
  • query_plan_hash
  • sql_text
  • username

...and then hit Next >.

New Session Wizard Set Session Event Filters

New Session Wizard Set Session Event Filters dialog

We'll filter on sqlos.task_execution_time for the moment and set a value of 6000. We'll come back on this setting to change it to duration. Hit Next >.

New Session Wizard Specify Session Data Storage

New Session Wizard Specify Session Data Storage dialog

Here you specify where you want to store the data in files or ring_bugger target. We'll use a file for the time being. Configure values that would suit your server and then click on Next >.

New Session Wizard Summary

New Session Wizard Summary dialog

Validate your configuration and click on Next >.

New Session Wizard Create Event Session

New Session Wizard Create Event Session dialog

Select both options to have something running and then click on Next >.

Extended Events | Sessions | Statement Duration > 1 Minute

Extended Events | Sessions | Statement Duration > 1 Minute picture

You'll now have a new Extended Event and a windows displaying the current output of this Extended Event.

Change Configuration

Right-click the session and stop it. Right-click again and go into the properties:

Session Properties

Select the sql_transaction and then click on Configure >...

Session Properties Configiuration Filter

...switch to the Filters tab and delete the current filter. Add a new filter with the values duration, greater than and 60000000 (microseconds).

Click on OK and then start your session again in the Extended Events. You might have to select Watch Live Data again to have a current window open.

Context Menu of Extended Event Session

The Fun Part

Now you have an Extended Event Session which will track statements that take longer than 60 seconds. Run a statement on that server with something like this:

BEGIN TRAN
    UPDATE DemoDB.dbo.Tweets SET TweetText = 'something else' WHERE TweetID = 2;
    WAITFOR DELAY '00:02';
COMMIT TRAN;

After a minute you should see an event pop up in your Watch Live Data window in SSMS. It might look like this:

Capture Data of Live Session

You can read the statement and with the plan_handle you can even go and query the sys.dm_exec_text_query_plan DMV to have a look at the execution plan:

SELECT 
CAST(query_plan AS XML) 
FROM sys.dm_exec_text_query_plan(0x06000100C0187C0A601FD200D902000001000000000000000000000000000000000000000000000000000000,0,-1)

Which shows:

Result Set of Query

Click on the XML link and voilà:

Graphical Execution Plan

This (in my example) execution plan can then be used to see if things are running ok, or if you might benefit from additional indexes or query tuning.

Answering Your Question

Is there any option to see killed sessions in Query Store?

If the statement has run before, but didn't take longer than your timeout, then these queries might be found in the Query Store.

If the statement terminates, because something changed, then maybe. It depends on the reason for the time-out.

Create an Extended Event as described in my solution and instead of setting it to 30 minutes set the time-out to 29 minutes and 59 seconds, or use the filter value of 1799000000 microseconds.

You should now be able to track long running transactions in the Live Data or in the File which is created.

8

They don't. I've tested this.

Create a database and enable query store.

CREATE DATABASE QueryStoreTest
GO
USE QueryStoreTest;
GO
ALTER DATABASE QueryStoreTest
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 1 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 50,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 10,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = ALL /* this is required for the testing */
    );
GO

Create and run a time-consuming query. I've used this one:

SELECT 
    *
FROM sys.all_columns AS ac
CROSS JOIN sys.all_parameters AS ap
CROSS JOIN sys.all_objects AS ao

Run the query to completion or stop the execution in the SSMS, and the query can be found in the query store.

SELECT 
    qsq.query_id
    , qsq.query_hash
    , qsp.plan_id
    , qsqt.query_sql_text
    , qsrs.count_executions
    , qsrs.execution_type_desc
FROM sys.query_store_query AS qsq
LEFT JOIN sys.query_store_plan AS qsp
    ON qsp.query_id = qsq.query_id
LEFT JOIN sys.query_store_query_text AS qsqt
    ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_runtime_stats AS qsrs
    ON qsrs.plan_id = qsp.plan_id
WHERE qsqt.query_sql_text LIKE '%cross join%'
AND qsqt.query_sql_text NOT LIKE '%query_store%'

The execution type is either Regular or Aborted. But if you kill the session from another window, there is neither increase in the count_executions nor a new record.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.