Query Store in action

In this section, you will see how Query Store collects information about queries and query plans and how it can identify and fix regressed queries. It will be demonstrated how Query Store supports and facilitates an upgrade to SQL Server 2016.

First, you will create a new database with a single table and populate it with two million rows. This database will simulate a database that is created and used in SQL Server 2012 and that you restored in SQL Server 2016 but left in the old compatibility mode. Use the following code to accomplish this task:

IF DB_ID('Mila') IS NULL CREATE DATABASE Mila; 
GO  
USE Mila; 
GO 
--help function GetNums created by Itzik Ben-Gan (http://tsql.solidq.com) 
CREATE OR ALTER FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE 
AS 
RETURN 
  WITH 
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1), 
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), 
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS...