Query Data Store

SQL Server’da çoğu zaman query’lerden dolayı sistemlerde problemler yaşayabiliriz. Çözüm için bazı zamanlarda query’de tuning yapmamız gerekebilir.

Her zaman olmasa da kılavuz olarak kendimize ‘Sınırsız kaynak yoktur Performance Tuning vardır’ sözünü ilke edinebiliriz.

SQL Server query’ler de sıklıkla karşılaştığımız problemlerden biride SQL Server’ın doğasında olan Parameter Sniffing’tir.

Bu durumda kullanılan execution plan değişebilir ve normalde ms’lerde gelebilecek sorgular dakikalarca cevap alamayız.

Sonuç olarak bu durum sistemde bottleneck’e sebep olur ve servisin kapanmasına kadar gidebilir.

Microsoft bu ve benzeri durumların önüne geçebilmek için 2016 yılında Query Data Store (QDS) özelliğini tanıttı.

QDS ile query’ler toplanıp, diskte depolanarak kullanması gereken plana force ederek sorguları hızlandırabiliriz.

Query Data Store’un topladığı tüm sorguları aşağıdaki script ile kontrol edebilirsiniz.

SELECT

TOP 100

    qp.last_execution_time [qp__last_execution_time],

    ( select qt.query_sql_text for xml path, type ) query_sql_text,

    len(qt.query_sql_text) sql_length,     

    round(rs.avg_duration ,5) avg_duration

    ,max_rowcount,avg_rowcount,last_rowcount

FROM

    sys.query_store_plan qp

    INNER JOIN sys.query_store_query         [q]        ON qp.query_id = q.query_id

    INNER JOIN sys.query_store_query_text    [qt]       ON q.query_text_id = qt.query_text_id

    INNER JOIN sys.query_store_runtime_stats [rs]       ON qp.plan_id = rs.plan_id

order by len(qt.query_sql_text) desc

 

İlgili database üzerine gelip sağ tıklayıp properties dedikten sonra en altta Query Store özelliğini görebilirsiniz.

 

Aşağıdaki script ile QDS ilgili database üzerinde açılır.

alter database [AdventureWorks2017]  set query_store = on
go
alter database [AdventureWorks2017]  set query_store (operation_mode =read_write)
go

Yukarıdaki script ile QDS açıldıktan sonra değerlerin konfigüre edilmesi gerekmektedir.

Özellikle Operation Mode querylerin hem okunup hem yazılmasını istiyorsak bu mode seçilmeli. Eğer planı Force etmek istiyorsak Read Write olması gerekmektedir.

Eğer yukarıda belirttiğimiz boyut dolarsa tekrar yazamayacaktır. O yüzden QDS’in temizlenmesi gerekmektedir. Aşağıdaki script ile bu işlemi yapabilirsiniz.

 

alter database [AdventureWorks2017] set query_store clear all

or

EXEC sys.sp_query_store_flush_db

Aynı işlemi Purge Query Data butonu ile de yapabilirsiniz.

QDS için kullanılabilecek tüm store procedure ve viewları aşağıdaki script ile bulabilirsiniz.

select * from sys.all_objects where name like '%query_store%' or name ='%query_content_setings%'

QDS sorgularına ilişkin monitoring için yukarıdaki özellikleri inceleyebilirsiniz.

QDS özelliğini kapatmak isterseniz aşağıdaki script yardımı ile kapatabilirsiniz.

alter database [AdventureWorks2017]  set query_store = off

***QDS kullanımında özellikle dikkat edilmesi gereken hususlar!!!

1-) QDS özelliği aktif edildikten sonra DATA_FLUSH_INTERVAL_SECONDS özelliği default olarak 15 dakika olarak ayarlanır. Bu özellik toplanan sorguların ne aralıkla disklerden boşalacağını bize söyler. Toplanan sorguların optimizasyonu için verilerin bir kısmı Memory’de saklanır. Tüm bu verileri diske yazmak maliyet oluşturur. Bu yüzden sorguları geçici olarak memory’de saklar. DATA_FLUSH_INTERVAL_SECONDS özelliği de verilerin bellekten diske boşaltacağı süreyi belirler. Sunucu beklenmedik şekilde shutdown olduğunda bu süre zarfında toplanan verileri diske yazmak için beklemeniz gerekmektedir. Bu süreyi yoğun sistemlerde tahmin etmek oldukça güçtür. Bunun yerine –T7745 trace Flag’ını kullanarak. Makine kapatıldığında SHUTDOWN WITH NOWAIT komutu ile eşdeğer olan 7745 trace flag kullanarak diske yazmadan hızlı bir şekilde makinayı katabilirsiniz. Fakat bu süreçte QDS tarafından toplanan veriler kaybolacaktır.

2-) SQL Server Servisi açıldığında QDS sorguları toplmaya başlar ve bir anda memory’i işgal etmeye başlar. Özellikle bu süreç failover sonrası yük devralma esnasında memory’i kullanmak isteyen QDS, sistemin yük almasına engel teşkil eder. Bu durumda WAIT_INFO bilgisinde QDS_LOADDB olarak görebilirsiniz. Bu durum olası krizler (HA/DR) için kullandığınız failover’a yeni bir disaster durumu yaratabilir. Bu sorunun çözümü için –T7752 trace flag eklenerek, sistemin memory’i rahatça kullanması sağlanabilir.