SQL Server 2016 – Query Store

PROBLEM

SQL Server 2016 sürümünden önce 3prt Tool ‘lar ile geriye dönük Query Planların toplandığı ve karşılaştırma yapılıp performans metrikleri ile daha hızlı problemlerin çözümlerini gerçekleştiriyorduk fakat bu Tool ‘lar fiyat olarak pahalı ve her şirketin bu bütçeyi ayırarak Veritabanı İzleme Araçlarını içerlerinde barndırma imkanları bulunamamkta.

ÇÖZÜM

SQL Server 2016 ile hayatımıza giren özelliklerden bir tanesi Query Store ‘dur. Bu özellik sayesinde sorgularımızın execution planları (query plan) artık geçmişe yönelik saklayabileceğiz. Bu şekilde Planlarda oluşan değişikliklerin getirdiği performans farklılıklarını inceleyebilecek ve gerektiğinde saklanan  planlar üzerinden geriye dönük planlarınızı da force edebileceksiniz. Bu şekilde 3prt Tool maliyetini ortadan kaldırılmış oldu ve aynı zamanda DBA ‘lerin Performans sıkıntılarının çözümünde can simidi olarak kullanabileceği bir özelliktir.

UYGULAMA

Bu bölümde Query Store ‘u aktif ve pasif  etme ve bir kaç Plan ‘ı depolayarak Force ederek uygulamamız üzerinden adım adım özelliği aktarmaya çalışacağım.

Veritabanı üzerinde Query Store ‘u Nasıl aktif ederiz ?

Script :

-- Query Store Özelliğini Aktif etmek için...
ALTER DATABASE  [SQL_TURKIYE] SET QUERY_STORE = ON;

SSMS :

qstore1

Aynı şekilde istediğimiz zaman Query Store özelliğini de Pasif hale getirebiliriz.

Script :

-- Query Store Özelliğini Pasif hale getirmek için...
ALTER DATABASE  [SQL_TURKIYE] SET QUERY_STORE = OFF;

SSMS :

qstore2

 

Statistics Collection Interval kısmında Query Performans verilerinin toplanma süresini SET edebiliriz. Default olarak 1 saattir. Bu değeri değiştirmek için aşağıda ki Query kullanabiliriz.

Script :

-- Query Store Performans Verilerini Toplama Süresini SET etme. (Örneğimiz için 5 Dakikaya SET edelim.)
ALTER DATABASE CURRENT SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 5)

SSMS:

queryStore_Interval_3

Query Store Retention altında yer alan Max Size (MB) alanı da bizim diğer br öenmli özelliğimiz. Burada belirleyeceğimiz alan eğer dollar ise Query Store Operational Mod ‘unu Read/Write ‘dan direct Read Only ‘e çeker limitini doldurduğu için. Bu yüzden kullanacak arkadaşların bu özelliği iyi hesaplayarak SET etmeleri gerekmektedir.

 

Stale Query Threshold (Days) özelliği ise diğer bir önemli özelliğimizdir. Bu özelliğin Default değeri 367 gündür.Bu özellik ileQuery Store içerisinde Capture ettiğimiz datanın ne kadar süre kalacağını belirliyoruz.

 

Örneğimize Dönecek Olur isek ;

-- Personel Tablosunu Oluşturuyoruz...
Create Table Personel 
(
Identity_Number CHAR(11) PRIMARY KEY CLUSTERED,
Name VARCHAR (100),
SurName VARCHAR(100),
E_Mail VARCHAR(100),
Department_Id INT
)
-- Department Tablosunu Oluşturuyoruz...
Create Table Department
(
Department_Id INT PRIMARY KEY CLUSTERED,,
Department_Name VARCHAR(100),
Department_Desc VARCHAR(200)
)

-- Salary Tablosunu Oluşturuyoruz...
Create Table Salary 
(
Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Personel_Identity_Number CHAR(11),
January numeric (18,2),
February numeric (18,2),
March numeric (18,2),
April numeric (18,2),
May numeric (18,2),
June numeric (18,2),
July numeric (18,2),
August numeric (18,2),
September numeric (18,2),
October numeric (18,2),
November numeric (18,2),
December numeric (18,2)
)

-- Test Amaçlı Index leri oluşturuyoruz..
Create NonClustered Index NCIX_Department_DepartmentName ON Department (Department_Name)
Create NonClustered Index NCIX_Department_Department_Id ON Department (Department_Id)

Create NonClustered Index NCIX_Salary_Month ON Salary (January,February,March)
Create NonClustered Index NCIX_Salary_Identity_Month ON Salary (Personel_Identity_Number,January,February,March)

Create NonClustered Index NCIX_Personel ON Personel (Name,SurName,E_Mail,Department_Id)

-- Örnek Veri Basıyoruz..
INSERT INTO Personel VALUES ('33333333333','Paul','Ren','[email protected]',002)
Select * FROm Personel
INSERT INTO Department VALUES (5,'e-business','e-business')
Select * FROm Department
INSERT INTO Salary (Personel_Identity_Number,January) VALUES ('33333333333',3500)
Select * FROm Salary

Bu işlemlerden sonra aşağıda ki scriptleri iki ayrı session da bir kaç kez execute ediyoruz.

Session-A

queryStore_SessionASession-B

queryStore_SessionB

Bu işlemlerden sonra Query Store ‘a gidip Top Resource Consuming Queries bölümünden Query Planlarını Count değerine göre inceliyoruz.

Database altında yer alan QueryStore bölümünün altın Top Resource Consuming Queries alanını tıklıyoruz.

 

Query_Store_Consuming

Burada ekrana gelen pencerede dediğimiz üzere Metric kısmında Execution Count ‘u seçerek inelemelerimizi yapıyoruz.

query_store_executionCountDaha sonra yeşil Bar  da Execute ettiğimiz Query Planı nı ve bu Query nin iki adet Plan ‘ı olduğunu görmekteyiz.

Aşağıda ekranımızı incelediğimizde Yeşil Bar’ı tıkladığımızda Plan Summary de iki adet Query Plan ‘a sahip olduğunu görmekteyiz. 96 ve 111 Plan Id ‘li. İstersek burada istediğimiz planı Force edebiliriz.

queryStore_Plan

Hangi planınızın daha performanslı çalışacağını belirleyip onu Force ederek sistemimizde bu tarz yoğun süreçleri iyi bir şekilde analiz edip Performanslı hale getirebilirsiniz.

Query Store ‘un sahip olduğu yetkinlik ve özellikler için aşağıda ki script ‘ten yararlanabilirsiniz.

Select 
desired_state_desc,
actual_state_desc,
current_storage_size_mb,
max_storage_size_mb,
max_plans_per_query,
query_capture_mode_desc,
size_based_cleanup_mode
FROM sys.database_query_store_options

queryStore_Script

Bu özellik eski sürümlerde olmadığı için bu süreçleri yönetmek için ekstra Tool lar yazılmış ve bunlarla monitoring süreçleri yürütülmeye çalışılmıştır.  Bu Tool lar özelliklerine göre fiyatı yüksek maliyetli ürünlerdir. Sql Server 2016 ile hayatımıza giren Query Store özelliğini iyi yönetip avantajlı hale getirip Performance Tuning lerimizin iyi bir şekilde yönetimini sağlayabilirsiniz.

2 Replies to “SQL Server 2016 – Query Store”

    1. SElam ,

      Evet makalemde de belittiğim üzere,
      ÇÖZÜM

      SQL Server 2016 ile hayatımıza giren özelliklerden bir tanesi Query Store ‘dur. Bu özellik sayesinde sorgularımızın execution planları (query plan) artık geçmişe yönelik saklayabileceğiz. Bu şekilde Planlarda oluşan değişikliklerin getirdiği performans farklılıklarını inceleyebilecek ve gerektiğinde saklanan  planlar üzerinden geriye dönük planlarınızı da force edebileceksiniz.’

      Query Plan ‘ları Force edebiliyoruz ama zaten Plun Guide ile de belirli query plan üzerinden çalışmasını sağlayabiliyorduk. Plun Guide özelliği için gerekli bilgileri yakında paylaşacağım Advanced Performans Tuning Online Kursumda Demo ile gösteriyor olacağım. Hem Plun Guide ile hem de Query Store ile force etme demo ları yer alacak.

Leave a Reply

Your email address will not be published. Required fields are marked *