Does killed session appears in Query Store?



  • 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.



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2