---
title: "Using the Query Store with In-Memory OLTP | Microsoft Docs"
ms.custom: ""
ms.date: "03/29/2016"
ms.prod: sql
ms.prod_service: "database-engine, sql-database"
ms.reviewer: ""
ms.technology: performance
ms.topic: conceptual
helpviewer_keywords: 
  - "Query Store, in-memory"
ms.assetid: aae5ae6d-7c90-4661-a1c5-df704319888a
author: julieMSFT
ms.author: jrasnick
monikerRange: "=azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current"
---
# Using the Query Store with In-Memory OLTP
[!INCLUDE[appliesto-ss-asdb-xxxx-xxx-md](../../includes/appliesto-ss-asdb-xxxx-xxx-md.md)]

  [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Query Store allows you to monitor the performance of natively compiled code for workloads running in-memory OLTP.  
Compile and runtime statistics are collected and exposed the same way as for disk-based workloads.   
When you migrate to in-memory OLTP you can continue using Query Store views in [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)] as well as custom scripts you have developed for disk-based workloads before migration. This saves your investment in learning Query Store technology and makes it generally usable for troubleshooting all type of workloads.  
For general information on using the Query Store, see [Monitoring Performance By Using the Query Store](../../relational-databases/performance/monitoring-performance-by-using-the-query-store.md).  
  
 Using the Query Store with in-memory OLTP does not require any additional feature configuration. When you enable it on your database it will work for all types of workloads.   
However, there are some specific aspects that users should be aware of when using Query Store with in-memory OLTP:  
  
-   When Query Store is enabled, queries, plans and compile-time statistics are collected by default. However, runtime statistics collection is not activated unless you explicitly enable it with  [sys.sp_xtp_control_query_exec_stats (Transact-SQL)](../../relational-databases/system-stored-procedures/sys-sp-xtp-control-query-exec-stats-transact-sql.md).  
  
-   When you set *\@new_collection_value* to 0 Query Store will stop collecting runtime statistics for affected procedure or for the entire [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] instance.  
  
-   The value configured with [sys.sp_xtp_control_query_exec_stats (Transact-SQL)](../../relational-databases/system-stored-procedures/sys-sp-xtp-control-query-exec-stats-transact-sql.md) is not persisted. Make sure you check and configure again statistics collection after restarting [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)].  
  
-   As in case with regular query statistics collection, performance may decrease when you use Query Store to track workload execution. You may want to consider enabling statistics collection only for an important subset of natively compiled stored procedures.  
  
-   Queries and plans are captured and stored on the first native compilation and updated upon every recompilation.  
  
-   If you enabled Query Store or cleared its content after all native stored procedure were compiled you must recompile them manually in order to make them captured by the Query Store. The same applies if you removed queries manually by using [sp_query_store_remove_query (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-query-store-remove-query-transact-sql.md) or [sp_query_store_remove_plan (Transct-SQL)](../../relational-databases/system-stored-procedures/sp-query-store-remove-plan-transct-sql.md). Use [sp_recompile (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-recompile-transact-sql.md) to force procedure recompilation.  
  
-   Query Store leverages plan generation mechanisms from in-memory OLTP to capture query execution plan during the compilation. Stored plan is semantically equivalent to one that you would get by using `SET SHOWPLAN_XML ON` with one difference; plans in Query Store are split and stored per individual statement.  
    
-   When you run Query Store in a database with a mixed workload then you can use **is_natively_compiled** field from [sys.query_store_plan (Transact-SQL)](../../relational-databases/system-catalog-views/sys-query-store-plan-transact-sql.md) to quickly find query plans that are generated by the native code compilation.  
  
-   Query Store capture mode (*QUERY_CAPTURE_MODE* parameter in **ALTER TABLE** statement) does not affect queries from natively compiled modules as they are always captured regardless of the configured value. This includes setting `QUERY_CAPTURE_MODE = NONE`.  
  
-   The duration of query compilation captured by the Query Store includes only time spent in query optimization, before the native code was generated. More precisely, it doesn't include time for C code compilation and generation of internal structures necessary for C code generation.  
  
-   Memory grants metrics within [sys.query_store_runtime_stats (Transact-SQL)](../../relational-databases/system-catalog-views/sys-query-store-runtime-stats-transact-sql.md) are not populated for natively compiled queries - their values are always 0. The memory grants columns are: avg_query_max_used_memory, last_query_max_used_memory, min_query_max_used_memory, max_query_max_used_memory, and stdev_query_max_used_memory.  
  
## Enabling and using Query Store with In-Memory OLTP  
 The following simple example  demonstrates using Query Store with in-memory OLTP in an end-to-end user scenario. In this example we assume that a  database (`MemoryOLTP`) is enabled for in-memory OLTP.  
    For more details on prerequisites for memory-optimized tables, see [Creating a Memory-Optimized Table and a Natively Compiled Stored Procedure](../../relational-databases/in-memory-oltp/creating-a-memory-optimized-table-and-a-natively-compiled-stored-procedure.md).  
  
```  
USE MemoryOLTP;  
GO  
  
-- Create a simple memory-optimized table   
CREATE TABLE dbo.Ord  
   (OrdNo INTEGER not null PRIMARY KEY NONCLUSTERED,   
    OrdDate DATETIME not null,   
    CustCode NVARCHAR(5) not null)   
WITH (MEMORY_OPTIMIZED=ON);  
GO  
  
-- Enable Query Store before native module compilation  
ALTER DATABASE MemoryOLTP SET QUERY_STORE = ON;  
GO  
  
-- Create natively compiled stored procedure  
CREATE PROCEDURE dbo.OrderInsert(@OrdNo integer, @CustCode nvarchar(5))  
WITH NATIVE_COMPILATION, SCHEMABINDING  
AS   
    BEGIN ATOMIC WITH  
    (TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
    LANGUAGE = N'English')  
  
    DECLARE @OrdDate DATETIME = GETDATE();  
    INSERT INTO dbo.Ord (OrdNo, CustCode, OrdDate)   
        VALUES (@OrdNo, @CustCode, @OrdDate);  
END;  
GO  
  
-- Enable runtime stats collection for queries from dbo.OrderInsert stored procedure  
DECLARE @db_id INT = DB_ID()  
DECLARE @proc_id INT = OBJECT_ID('dbo.OrderInsert');  
DECLARE @collection_enabled BIT;  
  
EXEC [sys].[sp_xtp_control_query_exec_stats] @new_collection_value = 1,   
    @database_id = @db_id, @xtp_object_id = @proc_id;  
  
-- Check the state of the collection flag  
EXEC sp_xtp_control_query_exec_stats @database_id = @db_id,   
    @xtp_object_id = @proc_id,   
    @old_collection_value= @collection_enabled output;  
SELECT @collection_enabled AS 'collection status';  
  
-- Execute natively compiled workload  
EXEC dbo.OrderInsert 1, 'A';  
EXEC dbo.OrderInsert 2, 'B';  
EXEC dbo.OrderInsert 3, 'C';  
EXEC dbo.OrderInsert 4, 'D';  
EXEC dbo.OrderInsert 5, 'E';  
  
-- Check Query Store Data  
-- Compile time data  
SELECT q.query_id, plan_id, object_id, query_hash, p.query_plan,  
    p.initial_compile_start_time, p.last_compile_start_time,   
    p.last_execution_time, p.avg_compile_duration,  
    p.last_force_failure_reason, p.force_failure_count  
FROM sys.query_store_query AS q  
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.plan_id  
WHERE q.object_id = OBJECT_ID('dbo.OrderInsert');  
  
-- Get runtime stats  
-- Check count_executions field to verify that runtime statistics   
-- have been collected by the Query Store  
SELECT q.query_id, p.plan_id, object_id, rsi.start_time, rsi.end_time,    
    p.last_force_failure_reason, p.force_failure_count, rs.*  
FROM sys.query_store_query AS q  
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.plan_id  
JOIN sys.query_store_runtime_stats AS rs   
    ON rs.plan_id = p.plan_id  
JOIN sys.query_store_runtime_stats_interval AS rsi   
    ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id  
WHERE q.object_id = OBJECT_ID('dbo.OrderInsert');  
```  
  
## See Also  
 [Monitoring Performance By Using the Query Store](../../relational-databases/performance/monitoring-performance-by-using-the-query-store.md)   
 [Creating a Memory-Optimized Table and a Natively Compiled Stored Procedure](../../relational-databases/in-memory-oltp/creating-a-memory-optimized-table-and-a-natively-compiled-stored-procedure.md)   
 [Best Practice with the Query Store](../../relational-databases/performance/best-practice-with-the-query-store.md)   
 [Query Store Stored Procedures (Transact-SQL)](../../relational-databases/system-stored-procedures/query-store-stored-procedures-transact-sql.md)   
 [Query Store Catalog Views (Transact-SQL)](../../relational-databases/system-catalog-views/query-store-catalog-views-transact-sql.md)