Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 63 of 212

52 C H A P T E R 4 | Improved database engine Figure 4-3: Enabling Query Store. You can also use the T-SQL ALTER DATABASE command to enable Query Store, as shown in Example 4-1. Example 4-1: Enabling Query Store ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE , CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 5 ) , DATA_FLUSH_INTERVAL_SECONDS = 2000 , MAX_STORAGE_SIZE_MB = 10 , INTERVAL_LENGTH_MINUTES = 10 ); Understanding Query Store components The query store contains two stores: a plan store that persists the execution plans, and a run-time stats store that persists the statistics surrounding query execution, such as CPU, I/O, memory, and other metrics. SQL Server retains this data until the space allocated to Query Store is full. To reduce the impact on performance, SQL Server writes information to each of these stores asynchronously. Note The default space allocation for Query Store is 100 MB. You can use the following five catalog views, as shown in Figure 4-4, to return metadata and query execution history from the query store: query_store_runtime_stats Run-time execution statistics for queries. query_store_runtime_stats_interval Start and end times for the intervals over which run-time execution statistics are collected.

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016