What is the SQL Server 2016 Query Data Store?

What is the SQL Server 2016 Query Data Store?

There's been some discussion and little confusion over a new feature that will deliver in SQL Server 2016 called the "Query Store." At first blush, you'd think that Microsoft was delivering a Windows-type app store for queries in SQL Server 2016. This by itself, would be fantastic. I can imagine SQL administrators salivating at the ability to "shop" for vetted SQL queries to use in their own environments instead of having to continually create their own or ask the SQL community to share their own. But, unfortunately, this is not what Query Store in SQL Server 2016 is. Not that the new feature isn't revolutionary and hugely valuable by any stretch.

The full name, per Microsoft, is Query Data Store, which makes the meaning more reasonably understandable. Query Data Store in SQL Server 2016 is intended as a service that monitors and optimizes query plans with full history of query execution. Query Data Store can help diagnose performance problems that are related to plan changes.

What it does:

  1. Stores the history of query plans

  2. Captures performance data for each query plan

  3. Identifies the most expensive queries and earmarks those queries that have degraded or gotten slower over time

  4. Gives you the ability to force old plans, should a new plan cause performance issues (almost like a system restore for Windows computers)

  5. Easily fixes plan regressions

  6. Analyzes workload patterns

  7. De-risks SQL Server upgrades with restart and failover

Microsoft has yet to communicate if Query Data Store will be available in the Standard edition of SQL Server 2016, but will definitely be available as a service for the Enterprise version.

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish