Query Store Nedir ve Nasıl Aktif Edilir

Query Store SQL Server 2016 ile gelen bir özelliktir. Query Store’u anlamak için öncelikle query plan(execution plan)’ı anlamak gerekir. “Execution Plan Nedir” isimli makalede execution plan hakkında detaylı bilgi bulabilirsiniz.

Query Store özelliği ile birlikte execution plan seçimini ve performansa etkisini daha iyi kavrayabiliyoruz.

Query Store, sorguların, query plan’ların ve çalışma zamanı istatistiklerinin geçmişini saklar. Böylece query plan değişikliği nedeniyle oluşan sorunları kolay bir şekilde fark edebiliriz.

Bir sorguya ait query plan zaman içersinde bir çok nedenle değişir. Bazı durumlarda bu değişiklik sorgunun yavaş çalışmasına neden olur. Query Store özelliği gelmeden önce sorgunun yavaş çalışmaya başlamasının sebebinin query plan değişikliğinden dolayı olduğunu bulmak zor bir işti. Query Store ile artık bu işlem çok basit bir hale geldi.

Query Store’da bir sorgu için birden fazla query plan bulunur. Ve plan forcing ile spesifik bir query plan’ın kullanılması sağlanabilir. Plan forcing ile query plan değişikliği nedeniyle meydana gelen performans problemleri kısa bir sürede çözülebilir.

Query Store’da ayrıca sorguya ait cpu tüketimi, sorgunun yaptığı read ve write bilgileri de tutulur.

SQL Server Bekleme Türlerini(Wait Types) instance seviyesinde görebiliyorduk. SQL Server 2017 ile birlikte artık  Query Store üzerinden görebileceğiz.

SQL Server 2017 ile gelen yeni dmw’nin yardımıyla aşağıdaki gibi hangi sorgu hangi query plan hangi bekleme tiplerini yaşıyor görebiliriz.

select * from sys.query_store_wait_stats

Aşağıdaki sorgu yardımıyla da spesifik bir query plan’ın hangi bekleme türleri için toplam ne kadar beklediğini bulabiliriz.

select wait_category_desc AS 'Bekleme Türü',
sum(total_query_wait_time_ms) AS 'SorguToplamBeklemeSüresi_sn'
from sys.query_store_wait_stats
where plan_id = 9
group by wait_category_desc

Query Store’u veritabanı seviyesinde aktif edebiliyoruz. Veritabanı üzerine sağ tıklayarak Properties diyoruz ve Query Store sekmesine geliyoruz.

Operation Mode kısmından Read Write’ı seçiyoruz. Böylelikle Query Store gerekli bilgiyi toplayabilir.

Data Flush kısmında’ki değer de toplanan verinin ne sıklıkla diske yazıldığını belirtir. Diske yazılma işlemi asenkron olduğu için ciddi bir performans sıkıntısına sebep olmaz.

Statistics Collection Interval default olarak 1 Hour olarak geliyor. Query Store, zamanı Statistics Collection Interval’de belirttiğimiz ayara göre bölüyor ve he execution plan için bu aralıktaki istatistikleri query store’da saklıyor. Buradaki zaman dilimini daha düşük bir değere set etmeniz sorunu çözmeni kolaylaştıracaktır fakat Query Store’un boyutunuda büyütecektir. Başlangıç olarak default ayarlarıyla bırakıp daha sonra ihtiyaca göre set edebilirsiniz.

Max Size (MB) kısmından Query Store’un ulaşabileceği maksimum boyutu MB cinsinden set ediyoruz. Bu değer aşıldığında Operation Mode otomatik olarak Read Only’e dönüşecektir.

Query Store Capture Mode kısmı, query store’un hangi sorguları yakalayacağını belirler.

Default olarak All gelir.

Yani bütün sorgular için aktiftir.

Auto’yu seçerseniz sık gelmeyen sorguları yakalamaz.

None’ı seçerseniz hiçbir sorguyu yakalamaz.

Size Based Cleanup Mode kısmında, max size limitine yaklaşıldığında otomatik veri temizlemenin otomatik olarak çalışıp çalışmayacağını belirleriz. Default olarak Auto gelir ve böyle bırakmamız tavsiye ediliyor.

Stale Query Threshold kısmında query store’da saklanan bilgilerin ne  kadar süre sonra artık saklanmaması gerektiğini belirtiriz. Default olarak 30 gündür. Bu süreyi ihtiyacınıza göre set edebilirsiniz. Gereksiz yere fazla tutmanızı tavsiye etmem. Bence bir hafta bu ayar için yeterli hatta fazla olacaktır.

Query store oluştuktan sonra aşağıdaki gibi query store’a sağ tıklayarak araştırmak istediğiniz şeyi seçebilirsiniz.

Örneğin aşağıdaki şekilde en masraflı sorguları yakalayıp sağ taraftaki grafikten en iyi çalıştığı query plan’ı seçip force edebilirsiniz.

Yada execution plan değişikliği nedeniyle yavaş çalışan sorguları Regressed Queries’ten tespit ederek doğru execution plan ile çalışması için force edebilirsiniz.

Bir örnek yaparak konuyu netleştirelim.

Aşağıdaki gibi bir tablo oluşturuyoruz.

CREATE TABLE [dbo].[sehir](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[SehirAdi] [varchar](250) NULL,
 CONSTRAINT [PK_Sehir] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Daha sonra aşağıdaki script ile bu tabloya bazı kayıtlar ekliyoruz.

INSERT INTO [dbo].[sehir]([SehirAdi]) VALUES ('Ankara'),('İstanbul'),('İzmir'),('Adana'),('Bursa')

Bu tabloya select çekecek bir stored procedure oluşturuyoruz.

CREATE PROCEDURE QueryStoreOrnek
@sehiradi varchar(200)
AS
BEGIN
select * FROM [dbo].[sehir]
      WHERE SehirAdi=@sehiradi
END
GO

Bu stored procedure’ü aşağıdaki şekilde çalıştırıp query plan’ına bakalım.

Exec QueryStoreOrnek 'Adana'

Gördüğünüz gibi tabloda index olmadığı için Clustered Index Scan yaptı.

Şimdi bu tabloda SehirAdi kolonuna aşağıdaki gibi index koyarak tekrar query plan’ına bakalım.

CREATE NONCLUSTERED INDEX [IX_SehirAdi] ON [dbo].[sehir]
(
[SehirAdi] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Bu sefer index seek yaparak çalıştı.

Şimdi index’i silelim ve query store’a sağ tıklayarak view reggressed queries diyelim.

Karşımıza aşağıdaki gibi bir ekran çıkacak. Ekranda gördüğünüz View regressed queries in a grid format with additional details kutucuğuna tıklayarak daha rahat çalışabilirsiniz.

Sorgumuzu bulalım sağ taraftaki query plan’larına bakalım.

İki tane yuvarlak şekil görünüyor. Bunlar bu sorgu için kaydedilmiş query planlar.

Üst taraftaki yaklaşık 80 milisaniyede tamamlanmış.

Alt taraftaki de nerdeyse 0 milisaniyede tamamlanmış. Yuvarlakların üzerine gelerek detaylarını görebilirsiniz.

Bazı durumlarda sorgu bazı nedenlerle yanlış bir query plan ile çalışabilir. Örneğin parameter sniffing ya da istatistiklerin güncel olmaması.

“Parameter Sniffing” ve “SQL Server’da İstatistik Kavramı ve Performansa Etkisi” isimli makalelerde detayları bulabilirsiniz.

Sorgu yanlış query plan ile çalıştığında sisteminizdeki performansı olumsuz etkileyecektir. Query Store’u kullanarak bunu tespit edebilir ve kolayca düzeltebilirsiniz.

Mesela bizim sorgumuzda iki tane query plan var. Bunlardan daha hızlı çalışanı seçip aşağıdaki gibi Force Plan diyebiliriz.

Biz burada index seek yapması için query plan’ı set ettik. Ama aslında bildiğiniz üzere index yok. Dolayısıyla sorgu bizim set ettiğimiz query plan ile çalışamayacak. Böyle bir durumda set ettiğimiz query plan’dan sonraki en iyi query plan’ı kullanarak çalışacaktır.

Query Store’a sağ tıklayarak View Queries with Forced Plans diyerek force edilmiş query plan’lara ulaşabilirsiniz.

DBCC SHOW_STATISTICS Nedir ve Nasıl Kullanılır

DBCC SHOW_STATISTICS ile bir istatistik hakkında detaylı bir bilgi alabiliriz. Çoğunuzun bildiği gibi istatistikler query plan oluşturulurken sql server engine’in karar vermesindeki en önemli rollerden birine sahiptir. SQL Server Engine istatistiklere bakılarak bir index’in kullanılıp kullanılmayacağına ya da ne şekilde kullanılacağına karar verir. Bu yüzden herşeyden önce istatistiklerimizin güncel olması gerekir.

Bu makaleyi okumadan önce bu komuta neden ihtiyacınız olduğunu daha iyi anlamak için aşağıdaki makaleleri okumanızı tavsiye ederim.

“SQL Server’da İstatistik Kavramı ve Performansa Etkisi“,

“SQL Server’da Index Kavramı ve Performansa Etkisi“,

“Execution Plan Nedir“

DBCC SHOW_STATISTICS bir tablo’daki veya indexed view’deki bir istatistik hakkında detaylı bilgileri gösterir.

Bu komutun çıktısı 3 öğeden oluşur.

• Statistic Header
• Density Vector
• Histogram

Bir örnek üzerinden yukardaki kavramları ve detaylarını açıklayalım. AdventureWorks veritabanında aşağıdaki komutu çalıştırın.

Bu komutta,

HumanReources şema adını,

Department tablo adını,

AK_Department_Name’de istatistik adını belirtiyor.

DBCC SHOW_STATISTICS([HumanResources.Department],[AK_Department_Name])

Karşımıza aşağıdaki gibi bir sonuç kümesi çıkıyor.

Aşağıdaki tablolarda sonuç kümesinde gelen kolonların açıklamalarını görebilirsiniz.

Incremental Statistics Nedir

SQL Server 2014 ile gelen bir özelliktir. Eğer veritabanınızda partition varsa çok işinize yarayabilir. Partition yoksa bir işinize yaramayacaktır. Auto Update Statistics’i True yaparsanız tablodaki satır değişikliği %20’yi geçtiğinde istatistikler güncelleniyordu. Auto Create Incremental Statistics’i True yaparsanız bu işlem partition bazında gerçekleşir. Büyük veritabanlarında istatistik güncelleme işlemini partition bazında yaparak istatistik güncellemenin sisteme getireceği yükü azaltabilirsiniz.

Aşağıdaki script yardımıyla veritabanı seviyesinde auto create incremental statistics’i true yapabilirsiniz. Bu işlemi gerçekleştirdikten sonra artık veritabanı seviyesinde partition yapılmış tablolarda yeni bir istatistik oluşacağı zaman incremental yapıda oluşacaktır.

ALTER DATABASE test
SET AUTO_CREATE_STATISTICS ON ( INCREMENTAL = ON )

Mevcut bir istatistiğin incremental yapıda olup olmadığını aşağıdaki sorgu yardımıyla öğrenebilirsiniz.

USE AdventureWorks2014Yeni
GO
SELECT
    OBJECT_NAME(object_id) TableName
   ,name
   ,is_incremental
   ,stats_id
FROM sys.stats
WHERE name = 'PK_EmployeeID'

Aşağıdaki script yardımıyla da bir istatistiği  partition bazında update edebilirsiniz.

UPDATE STATISTICS [VeritabaniAdi].[SemaAdi].[TabloAdi]
(IstatistikAdi) WITH RESAMPLE ON PARTITIONS(1)

Eğer istatistik incremental yapıda değilse aşağıdaki gibi hata alırsınız.

Msg 9111, Level 16, State 1, Line 13

UPDATE STATISTICS ON PARTITIONS syntax is not supported for non-incremental statistics.

Aşağıdaki script yardımıyla da mevcut bir istatistiği incremental yapıya geçirebilirsiniz.  Bu script’i çalıştırdıktan sonra artık yukardaki hatayı almadan partition yapılmış tablolardaki istatistiklerinizi partition bazında update edebileceksiniz.

UPDATE STATISTICS [EBYSDB].[dbo].[DocumentAttachment]
(IX_DocumentAttachment_DocumentId) WITH INCREMENTAL = ON

Veritabanındaki tüm partition yapılmış tablolardaki tüm istatistikler için yukardaki script’i veren script’i aşağıda bulabilirsiniz. Script’te AND tbl.name=’TabloIsmi’ kısmının başındaki – – işaretini kaldırıp TabloIsmi yerine bir tablo ismi yazarsanız sadece ilgili tablo için gerekli script’i çıkaracaktır.

SELECT DISTINCT 'UPDATE STATISTICS ' + QUOTENAME(object_name(s.object_id)) + ' (' + QUOTENAME(s.name) + ') WITH INCREMENTAL = ON'
FROM sys.tables tbl
INNER JOIN sys.indexes ind
ON tbl.object_id = ind.object_id
INNER JOIN sys.data_spaces ds
ON ind.data_space_id = ds.data_space_id
INNER JOIN sys.stats s
ON ind.object_id = s.object_id
WHERE ds.type = 'PS' --AND tbl.name='TabloIsmi'
AND s.is_incremental = 0

İstatistikler hakkında daha detaylı bilgi almak için “SQL Server’da İstatistik Kavramı ve Performansa Etkisi” isimli makaleyi okumanızı tavsiye ederim.

Bir istatistiğin verileri dağıtımı hakkında detaylı bilgi almak içinde “DBCC SHOW_STATISTICS Nedir ve Nasıl Kullanılır” isimli makaleyi okumanızı tavsiye ederim.

Maintenance Plan Kullanarak İstatistikleri Update Etmek

Maintenance Plan kullanarak SQL Server üzerindeki bakım işlemlerini gerçekleştiririz. Bu makalede istatistikleri güncelleme işlemini anlatacağım. İstatistikler hakkında daha detaylı bilgi almak için “SQL Server’da İstatistik Kavramı ve Performansa Etkisi” isimli makalemi okumanızı tavsiye ederim. Maintenance plan kullanarak yapılabilecek diğer işlemlere ait makalelerin linklerini makalenin sonunda paylaşacağım.

SSMS üzerinden Management sekmesinden aşağıda gördüğünüz gibi Maintenance Plans ve Maintenance Plan Wizard seçeneklerini seçiyoruz.

Karşımıza gelen ekranda aşağıdaki gibi Do not show this starting page again’i seçerek next diyoruz.

Gelen ekranda aşağıdaki gibi Name kısmından Maintenance Plan’a bir isim veriyoruz. Bu makalede istatistik güncelleme işleminin gerçekleştirilmesini anlatacağım için ismine UpdateStatistics dedim.

Schedule kısmında Change’e tıklayarak aşağıdaki ayarlarla her hafta Pazar günü 00:00’da çalışacak şekilde ayarladık.

Bir sonraki ekranda istatistik güncelleme  işlemlerini yapacağımız için aşağıdaki gibi Update Statistics seçeneğini seçiyoruz ve next diyoruz.

Aşağıdaki ekrana geldiğimizde Databases kısmından Select one or more seçeneğine tıkladığımızda hemen altında gözüken pencere açılıyor.

All databases seçeneğini seçersek bütün veritabanları için istatistik güncelleme işlemlerini gerçekleştirecektir.

System databases seçeneğini seçersek sistem veritabanları için istatistik güncelleme işlemlerini gerçekleştirecektir.

All user databases seçeneğini seçersek bütün kullanıcı veritabanları için istatistik güncelleme işlemlerini gerçekleştirecektir.

These databases seçeneği seçeneğini seçerek spesifik veritabanı seçimi yapabiliriz. Örneğin biz bu örneğimizde aşağıdaki gibi sadece Test veritabanı için istatistik güncelleme işlemlerini yapacağız.

Ignore databases where the state is not online seçeneğini seçerseniz istatistik güncelleme işlemi sırasında veritabanı erişilemez durumda ise o veritabanını atlar ve işleme devam eder.

Bir sonraki ekranda aşağıdaki gibi Write a report to a text file seçeneğini seçerseniz Folder location seçeneğinde belirlediğiniz klasörde yapılan işlemlerle ilgili log kayıtlarını tutacaktır.

E-mail report seçeneğini seçerek yapılan işlemlerle ilgili raporları To: kısmında belirlediğiniz mail adresine gönderecektir.

Benim sunucumda tanımlı her hangi bir operatör olmadığı için aşağıdaki gibi bir hata aldım.

There are no operators defined on the system.

Operatörle ilgili detaylı bilgi almak için “Operator Tanımlamak” isimli makalemi okumak isteyebilirsiniz. Next ve Finish diyerek işlemi tamamlıyoruz.

Maintenance Plan’larla ilgili makaleleri de aşağıda bulabilirsiniz.

“Maintenance Plan Kullanarak Full Backup Almak“,

“Maintenance Plan Kullanarak Differential Backup Almak“,

“Maintenance Plan Kullanarak Log Backup Almak“,

“Maintenance Plan Kullanarak Veritabanı Tutarlılığını Kontrol Etmek“,

“Maintenance Plan Kullanarak Index’leri Rebuil Etmek“,

“Maintenance Plan Kullanarak Index’leri Reorganize Etmek“,

“Maintenance Plan Kullanarak İstatistikleri Update Etmek“,

“Maintenance Plan Kullanarak Backup, Restore, Job ya da Maintenance Plan Geçmişini Silmek“,

“Maintenance Plan Kullanarak Mevcut Bir Job’ı Çalıştırmak“,

“Maintenance Plan Kullanarak Bir Klasördeki Belirlenen Uzantıya Sahip Dosyaları Silmek(Maintenance Cleanup Task)“