SQL Server Query Store (SQL SERVER 2016)
Among that all the features of SQL server below i will discuss only Query Store in SQL SERVER 2016:-
Query Store is a new SQL Server component that captures queries, query plans, run time statistics, and
more in a persistent store inside the database. It is a database-scoped persistent store of query workload
history. You can think of it as a flight recorder, or black box, for your database. It can also enforce policies
to direct the SQL Server Query Processor to compile queries to be executed in a specific manner, such as
forcing plans.
Query Store primarily targets administrative scenarios for performance troubleshooting and identifying
regressed workloads. It also collects query texts and all relevant properties, as well as query plan choices
and performance metrics. This collection process works across restarts or upgrades of the server and
across recompilation of indexes, providing many configurable options for customization. Query Store
integrates with existing query execution statistics, plan forcing, and manageability tools. It is a dedicated
store for query workload performance data and captures the history of plans for each query. It also
captures the performance of each plan over time and persists the data to disk (works across restarts,
upgrades, and recompiled). Query Store enables you to:
==> Quickly find and fix a plan performance regression by forcing the previous query plan. It can fix
queries that have recently regressed in performance due to execution plan changes.
==> Determine the number of times a query was executed in a given time window and assist a DBA in
troubleshooting performance resource problems.
==> Identify costly queries (by execution time, memory consumption, and so forth) in the past “x”
hours, and audit the history of query plans for a given query.
==> Analyze the resource (CPU, I/O, and Memory) usage patterns for a particular database.
==> Maintain performance stability during upgrade to SQL Server 2016.
==> Perform A/B testing and back up changes with performance data.
The Query Store can be viewed and managed through Management Studio or by using views and
procedures. There are seven Query Store Catalog Views that can present information about the Query
Store. There are also various procedures to follow when configuring the Query Store:
sys.database_query_store_options: Returns query store options for a given database.
sys.query_context_settings: Contains information about the semantics affecting context settings associated with a query.
sys.query_store_plan: Contains information about each execution plan associated with a query.
sys.query_store_query: Contains information about the query and its associated overall aggregated run time execution statistics.
sys.query_store_query_text: Contains the Transact-SQL text and the SQL handle of the query.
sys.query_store_runtime_stats: Contains information about the run time execution statistics information for the query.
sys.query_store_runtime_stats_interval: Contains information about the start and end times of each interval over which run time execution statistics information for a query has been collected.
Live Query Statistics :
Before SQL Server 2016, developers and DBAs would have to troubleshoot query performance with
Show plan at query run time. This run time execution plan, often referred to as query execution statistics,
enables you to collect metrics about the query that occurred during its execution (such as its execution
time and actual cost) after the query finishes running.
SQL Server 2016 has a new feature—Live Query Statistics (LQS)—that allows you to view what is
happening during the query execution. LQS lets you view a list of active queries and associated
statistics, such as current CPU/memory usage, execution time, query progress, and so on. This enables
rapid identification of potential bottlenecks for troubleshooting query performance issues. LQS also allows
users to drill down into a query plan of an active query and view live operator-level statistics, such as the
number of generated rows, elapsed time, operator progress, and live warnings. This facilitates in-depth
troubleshooting of query performance issues without forcing you to wait for query completion, so you
can watch the statistics change during the query execution in real-time.
The live execution plan can also be accessed from the Activity Monitor :
Temporal database support :-
Data is rarely static. Seeing how data has evolved over time—or querying data as of a particular point in
time—can be very valuable. SQL Server 2016 promises to simplify this process with a new feature called
Temporal Tables. With this feature, one can specify that the data history for a table be stored
How does it work?
A temporal table is also referred to as a system-versioned table. Each temporal table (or
“system-versioned” temporal table) consists of two tables: one for current data, and the other for
historical data. Within each of these tables, two additional DateTime (DateTime2 datatype) columns are
used to define the period of validity for each record: a system start time (SysStartTime) column, and a
system end time (SysEndTime) column. The current table contains the current value for each record. The
history table contains previous values for each record, if any, and the start and end times for the period
for which it was valid.
INSERTS: On an INSERT, the system sets the value for the SysStartTime column to the UTC time
(coordinated universal time) of the current transaction based on the system clock, and it assigns
the value for the SysEndTime column to the maximum 9999-12-31. This marks the record as open.
UPDATES: On an UPDATE, the system stores the previous value of the record in the history table
and sets the value for the SysEndTime column to the UTC time of the current transaction based
on the system clock. This marks the record as closed, with a period recorded for which the record
was valid. In the current table, the record is updated with its new value, and the system sets the
value for the SysStartTime column to the UTC time for the transaction based on the system clock.
The value for the updated record in the current table for the SysEndTime column remains the
maximum value of 9999-12-31.
DELETES: On a DELETE, the system stores the previous value of the record in the history table and
sets the value for the SysEndTime column to the UTC time of the current transaction based on the
system clock. This marks the record as closed, with a period recorded to indicate when the record
was valid. In the current table, the record is removed. Queries of the current table will not return
this value. Only queries that deal with history data return data when a record is closed.
MERGE: On a MERGE, MERGE behaves as an INSERT, an UPDATE, or a DELETE based on the
condition for each record.
