SQL Server 2016 - Query Store

I was taking a Plural sight introduction class. Here I am sharing what I learnt from the course.

SQL Server 2016 Query Store

1. Enable on a per database level
2. Data persisted in internal tables in the user databases
3. Restored databases will have a query store database

Enabling Query Store with GUI

Enable Query Store using Script

USE [master]

GO

ALTER DATABASE [DBA] SET QUERY_STORE = ON

GO

ALTER DATABASE [DBA] SET QUERY_STORE (OPERATION_MODE = READ_ONLY)

GO

USE [master];

GO

ALTER DATABASE [DBA] 

SET QUERY_STORE = ON;

Go

ALTER DATABASE [DBA] 

SET QUERY_STORE (

OPERATION_MODE = READ_WRITE, 

CLEANUP_POLICY = 

(STALE_QUERY_THRESHOLD_DAYS = 30), 

DATA_FLUSH_INTERVAL_SECONDS = 900,  

INTERVAL_LENGTH_MINUTES = 60, 

MAX_STORAGE_SIZE_MB = 100, 

QUERY_CAPTURE_MODE = ALL, 

SIZE_BASED_CLEANUP_MODE = AUTO, 

MAX_PLANS_PER_QUERY = 200);

GO

Query Store collection setting

• Operation Mode = Read_Write | Read Only
• Query_Caputre_Mode = ALL | AUTO | NONE
• Max Plans Per Query = 200 different plan for a given query (default)
• Max Storage Size MB = 2 GB (Start with)
• CleanUp_Policy = (Stale query threshold days) 30 days
• Sized based cleanup mode  = Auto | OFF

USE [master];

GO

ALTER DATABASE [DBA] SET QUERY_STORE (

OPERATION_MODE = READ_WRITE,

/*

READ_WRITE = data collection

(default)

READ_ONLY = no data collection 

*/

QUERY_CAPTURE_MODE = ALL,

/* 

are ALL queries captured, 

or only "relevant" ones? 

ALL = every query executed 

(default)

AUTO = infrequent & insignificant

NONE = nothing new added

*/

MAX_PLANS_PER_QUERY = 200);

/*

INT data type

*/

GO

NOTE : Changing query store setting in a database will flush all cached plan from the particular database.

Query Store GUI

Permission to use Query Store

• VIEW DATABASE STATE
• db_owner needed to force/unforced plans

What is Query Store capturing?

Runtime Stats Store(Performance data)

• Compile time
• last execution time
• Duration
• CPU
• logical reads
• Physical reads
• Write

Plan Store

• Query text
• Query plan

Script to check Query store Setting in DMV

SELECT 

[actual_state_desc], 

[readonly_reason], 

[desired_state_desc], 

[current_storage_size_mb], 

    [max_storage_size_mb], 

[flush_interval_seconds], 

[interval_length_minutes], 

    [stale_query_threshold_days], 

[size_based_cleanup_mode_desc], 

    [query_capture_mode_desc], 

[max_plans_per_query]

FROM [sys].[database_query_store_options];

GO

Remove everything from Query Store

--Truncating sys tables behind the scenes

ALTER DATABASE DBA  SET QUERY_STORE CLEAR;GO

Query store System Views

• sys.query_store_query
• sys.query_store_query_text
• sys.query_store_plan
• sys.query_store_runtime_stats

Query Store system views script

Query compile and optimization information

SELECT 
[qst].[query_text_id], 
[qsq].[query_id], 
[qsq].[object_id], 
[qsq].[context_settings_id], 
[qst].[query_sql_text], 
[qsq].[initial_compile_start_time], 
[qsq].[last_compile_start_time],
[qsq].[last_execution_time], 
[qsq].[avg_compile_duration], 
[qsq].[count_compiles], 
[qsq].[avg_optimize_cpu_time], 
[qsq].[avg_optimize_duration]
FROM [sys].[query_store_query] [qsq] 
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id];
GO

Query plan and execution information

SELECT
[qsq].[query_id], 
[qsp].[plan_id],
[qsq].[object_id],
[qsq].[initial_compile_start_time],
[qsq].[last_compile_start_time], 
[rs].[first_execution_time],
[rs].[last_execution_time],
[rs].[avg_duration],
[rs].[avg_logical_io_reads],
TRY_CONVERT(XML, [qsp].[query_plan]),
[qsp].[query_plan],
[rs].[count_executions],
[qst].[query_sql_text]
FROM [sys].[query_store_query] [qsq] 
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp] 
ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs] 
ON [qsp].[plan_id] = [rs].[plan_id]
WHERE [qsp].[query_id] = 3

Queries executed in the last 8 hours with multiple plans

SELECT
[qsq].[query_id], 
COUNT([qsp].[plan_id]) AS [PlanCount],
[qsq].[object_id], 
MAX(DATEADD(MINUTE, -(DATEDIFF(MINUTE, GETDATE(), GETUTCDATE())), 
[qsp].[last_execution_time])) AS [LocalLastExecutionTime],
MAX([qst].query_sql_text) AS [Query_Text]
FROM [sys].[query_store_query] [qsq] 
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp] 
ON [qsq].[query_id] = [qsp].[query_id]
WHERE [qsp].[last_execution_time] > DATEADD(HOUR, -8, GETUTCDATE())
GROUP BY [qsq].[query_id], [qsq].[object_id]
HAVING COUNT([qsp].[plan_id]) > 1;
GO

Confirm the plan that's being used now

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
SELECT 
[qs].[execution_count], 
[qs].[last_execution_time],
[s].[text], 
[qp].[query_plan], 
[qs].[plan_handle]
FROM [sys].[dm_exec_query_stats] AS [qs]
CROSS APPLY [sys].[dm_exec_query_plan] ([qs].[plan_handle]) AS [qp]
CROSS APPLY [sys].[dm_exec_sql_text]([qs].[plan_handle]) AS [s]
JOIN [sys].[dm_exec_cached_plans] AS [cp] 
ON [qs].[plan_handle] = [cp].[plan_handle]
WHERE [s].[text] LIKE '%CustomerTransactionInfo%';
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

GO

Query Store for a stored proc

SELECT

[qsq].[query_id], 

[qsp].[plan_id], 

[qsq].[object_id], 

[rs].[count_executions],

DATEADD(MINUTE, -(DATEDIFF(MINUTE, GETDATE(), GETUTCDATE())), 

[qsp].[last_execution_time]) AS [LocalLastExecutionTime],

[qst].[query_sql_text], 

ConvertedPlan = TRY_CONVERT(XML, [qsp].[query_plan])

FROM [sys].[query_store_query] [qsq] 

JOIN [sys].[query_store_query_text] [qst]

ON [qsq].[query_text_id] = [qst].[query_text_id]

JOIN [sys].[query_store_plan] [qsp] 

ON [qsq].[query_id] = [qsp].[query_id]

JOIN [sys].[query_store_runtime_stats] [rs] 

ON [qsp].[plan_id] = [rs].[plan_id]

WHERE [qsq].[object_id] = OBJECT_ID(N'usp_GetMovie');

GO

Visual Studio 2017 Pro - SQL Schema Compare

SqlSchemaCompare 

SQL Schema Compare from VS 2017 will compare all the objects from Source database to Target database.

Click on Tools > SQL Server > New Schema Comparism

Enter Source and Target Server and Database name

Click on Compare

After clicking compare button, you will see the objects which needs to be deleted, added , chanaged in Target Database.

You can update the Target my clicking Update button.

To generate the script, click on generate Script button

Sometimes, we need to compare indexes and tables between two databases. This is a useful tool for us.

Day 4- R Data Structure

Data Structures

• Collection of data elements
• Grouped under one name
• Container

Homogeneouse data structure 
Atomic vector, Matrix, Array

Hetrogeneous data structure 
List, Data Frame

• Atomic vector
• 1 Dimension
• Matrix
• 2 Dimension
• Array
• n Dimension
• List
• 1 Dimension
• Data frame
• 2 Dimension

Basic Classes of Objects

Atomic Classes

Character

“A”, “c”

Numeric

4.36, 7.42

Integer

3,5

Logical

True, False

Complex

1 + 7i , 8 - 2i

Atomic Vector

•  Commonly knows as vectors
•  Homogeneous data structure

# Character

student.names <‐ c("Raj","Rahul","Priya","Poonam")

# Numeric

student.weights <‐ c( 60.5, 72.5 , 45.2, 47.5)

# Integer

student.physics.marks <‐ c( 70L , 75L , 80L, 85L)

# Logical

student.physics.interest <‐ c(FALSE, F, TRUE, T)

Common Operations on Atomic Vectors

• Arithmetic & logical Subsetting Coercion

Factor

• Special case of vector used to store nominal values

student.genders <‐ c(“Male",“Male",“Female",“Female")

student.genders <‐ factor(c(“Male",“Male",“Female",“Female"))

List

• Heterogeneous data structure
• Can contain items of different classes
• 1-Dimensional arrangement