7 Kas by NURULLAH ÇAKIR

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.