Sorgu Deposu Kullanım Senaryoları

Şunlar için geçerlidir: SQL Server 2016 (13.x) ve sonraki sürümleri Azure SQL VeritabanıAzure SQL Yönetilen ÖrneğiAzure Synapse Analytics (yalnızca ayrılmış SQL havuzu)Microsoft Fabric'teki SQL veritabanı

Sorgu Deposu, öngörülebilir iş yükü performansını izlemek ve sağlamak kritik olduğunda, çok çeşitli senaryolarda kullanılabilir. Aşağıda dikkate almanız gereken bazı örnekler verilmiştir:

• Plan seçimi regresyonlarıyla sorguları belirleme ve düzeltme

• En çok kaynak tüketen sorguları belirleme ve ayarlama

• A/B testi

• Daha yeni SQL Server'a yükseltme sırasında performans kararlılığını koruma

• Geçici iş yüklerini tanımlama ve geliştirme

• Sorgu Deposu ile yapılandırma ve yönetme hakkında daha fazla bilgi için bkz. Sorgu Deposukullanarak performansı izleme.

• Sorgu Deposu ile eyleme dönüştürülebilir bilgileri bulma ve performansı ayarlama hakkında bilgi için bkz. Sorgu Deposukullanarak performansı ayarlama.

• Azure SQL Veritabanı'nda Sorgu Deposu'yu çalıştırma hakkında bilgi için bkz. Azure SQL Veritabanı'nde Sorgu Deposunu çalıştırma.

Plan seçimi regresyonlarıyla sorguları belirleme ve düzeltme

Sorgu İyileştiricisi, normal sorgu yürütmesi sırasında önemli girişler farklı hale geldiği için farklı bir plan seçmeye karar verebilir: veri kardinalitesi değişti, dizinler oluşturuldu, değiştirildi veya bırakıldı, istatistikler güncelleştirildi vb. Genellikle, yeni plan daha iyidir veya daha önce kullanılan plandan yaklaşık olarak aynıdır. Ancak, yeni planın önemli ölçüde daha kötü olduğu durumlar vardır- bu durum plan seçimi değişikliği regresyonu olarak adlandırılır. Sorgu Deposu'na başlamadan önce, SQL Server yerleşik veri deposu sağlamadığından, kullanıcıların zaman içinde kullanılan yürütme planlarına bakması için tanımlanması ve düzeltilmesi zor bir sorundu.

Sorgu Deposu ile hızla şunları yapabilirsiniz:

• İlgilenilen zaman aralığında (son saat, gün, hafta vb.) yürütme ölçümlerinin düşürülmüş olduğu tüm sorguları belirleyin. Çözümlemenizi hızlandırmak için SQL Server Management Studio'da Regresyona Neden Olan Sorgular kullanın.

• Gerileyen sorgular arasında, birden çok planı olan ve hatalı plan seçimi nedeniyle düzeyi düşürülen sorguları bulmak kolaydır. Gerileyen SorgularPlan Özeti bölmesini kullanarak gerileyen sorguya yönelik tüm planları ve bunların zaman içindeki sorgu performansını görselleştirin.

• Daha iyi olduğu kanıtlandığında, geçmişteki bir önceki planı zorla uygula. Gerileyen Sorgular'da sorgu için seçili planı zorlamak amacıyla, Planı Zorla düğmesini kullanın.

Senaryonun ayrıntılı açıklaması için bkz. Sorgu Deposu: Veritabanınız için bir uçuş veri kaydedicisi blogu.

En çok kaynak tüketen sorguları belirleme ve ayarlama

İş yükünüz binlerce sorgu oluştursa da, genellikle yalnızca birkaçı sistem kaynaklarının çoğunu kullanır ve bu nedenle dikkatinizi gerektirir. En çok kaynak tüketen sorgular arasında genellikle gerileyen veya ek ayarlama ile geliştirilebilen sorgular bulabilirsiniz.

Keşfetmeye başlamanın en kolay yolu, Management Studio'da en çok kaynak tüketen sorgular açmaktır. Kullanıcı arabirimi üç bölmeye ayrılmıştır: En çok kaynak tüketen sorguları temsil eden histogram (sol), seçili sorgu için plan özeti (sağ) ve seçili plan için görsel sorgu planı (alt). Çözümlemek istediğiniz sorgu sayısını ve ilgilendiğiniz zaman aralığını denetlemek için 'ı seçin, ardından'i yapılandırın. Ayrıca, farklı kaynak tüketimi boyutları (süre, CPU, bellek, GÇ, yürütme sayısı) ile temel (Ortalama, Min, Maksimum, Toplam, Standart Sapma) arasında seçim yapabilirsiniz.

Yürütme geçmişini analiz etmek ve farklı planlar ve çalışma zamanı istatistikleri hakkında bilgi edinmek için sağdaki plan özetine bakın. Alt bölmeyi kullanarak farklı planları inceleyin veya bunları görsel olarak, yan yana işlenerek karşılaştırın (Karşılaştır düğmesini kullanın).

En iyi olmayan performansa sahip bir sorgu tanımladığınızda, eyleminiz sorunun doğasına bağlıdır:

1. Sorgu birden çok planla yürütüldüyse ve son plan önceki plandan önemli ölçüde daha kötüyse, SQL Server'ın gelecekteki yürütmeler için en uygun planı kullanacağından emin olmak için plan zorlama mekanizmasını kullanabilirsiniz

2. İyileştiricinin XML planında eksik dizinler önerip önermediğini denetleyin. Evet ise, eksik dizini oluşturun ve dizin oluşturulduktan sonra sorgu performansını değerlendirmek için Sorgu Deposu'nı kullanın

3. sorgu tarafından kullanılan temel tablolar için istatistiklerin up-to-date olduğundan emin olun.

4. Sorgu tarafından kullanılan dizinlerin birleştirildiğinden emin olun.

5. Pahalı sorguyu yeniden yazmayı göz önünde bulundurun. Örneğin, sorgu parametreleştirme avantajlarından yararlanın ve dinamik SQL kullanımını azaltın. Verileri okurken en iyi mantığı uygulayın (veri filtrelemeyi uygulama tarafında değil veritabanı tarafında uygulayın).

A/B testi

Uygulama değişmeden önce ve sonra iş yükü performansını karşılaştırmak için Sorgu Deposu'nı kullanın.

Aşağıdaki liste, ortamın veya uygulama değişikliğinin iş yükü performansı üzerindeki etkisini değerlendirmek için Sorgu Deposu'nun kullanabileceğiniz birkaç örnek içerir:

• Yeni uygulama sürümü dağıtılıyor.

• Sunucuya yeni donanım ekleme.

• Pahalı sorgular tarafından atıfta bulunulan tablolarda eksik dizinler oluşturma.

• Satır düzeyi güvenlik için filtreleme ilkesini uygulama. Daha fazla bilgi için bkz. Sorgu Deposu ile Satır Düzeyi Güvenliğini İyi hale getirme.

• OLTP uygulamalarınız tarafından sık değiştirilen tablolara zamansal sistem sürümü ekleme.

Bu senaryolardan herhangi birinde aşağıdaki iş akışını uygulayın:

1. Performans temeli oluşturmak için planlanan değişiklik öncesinde iş yükünüzü Sorgu Deposu ile çalıştırın.

2. Uygulama değişikliğini denetimli zamanda uygulayın.

3. Değişiklik sonrasında sistemin performans görüntüsünü oluşturmak için iş yükünü yeterince uzun süre çalıştırmaya devam edin

4. #1 ve #3 sonuçlarını karşılaştırın.

   1. Veritabanının tamamına etkisini belirlemek için Genel Veritabanı Tüketimi açın.

   2. Değişikliğin en önemli sorgulara etkisini analiz etmek için En Çok Kaynak Tüketen Sorgular açın (veya Transact-SQLkullanarak kendi çözümlemenizi çalıştırın).

5. Yeni performansın kabul edilemez olması durumunda değişikliğin mi yoksa geri alma işleminin mi gerçekleştirileceğine karar verin.

Aşağıdaki çizimde, eksik dizin oluşturma durumunda Sorgu Deposu analizi (4. adım) gösterilmektedir. Dizin oluşturma işleminden etkilenmesi gereken sorgunun bu görünümünü elde etmek için En Çok Kaynak Tüketen Sorgular / Plan özeti bölmesini açın:

Ayrıca, dizin oluşturmadan önceki ve sonraki planları yan yana işleyerek karşılaştırabilirsiniz. ("Seçili sorgunun planlarını ayrı bir pencerede karşılaştırın" araç çubuğu seçeneği, araç çubuğunda kırmızı kareyle işaretlenmiştir.)

Dizin oluşturulmadan önce plan yapın (yukarıda, plan_id = 1), çünkü eksik dizin ipucu var ve Kümelenmiş Dizin Taraması'nın sorgudaki en pahalı işlemci olduğunu (kırmızı dikdörtgen) inceleyebilirsiniz.

Eksik dizin oluşturma işleminden sonra sorgu planı (plan_id = 15, aşağıda) artık sorgunun genel maliyetini azaltan ve performansını artıran (yeşil dikdörtgen) Kümelenmemiş Dizin Araması'nı kullanıyor.

Analize bağlı olarak, sorgu performansı iyileştirildikçe dizini tutmanız olasıdır.

Daha yeni SQL Server'a yükseltme sırasında performans kararlılığını koruma

SQL Server 2014'ün (12.x) öncesinde, kullanıcılar en son platform sürümüne yükseltme sırasında performans regresyonu riskiyle karşı karşıyaydı. Bunun nedeni, yeni bitler yüklendikten sonra Sorgu İyileştiricisi'nin en son sürümünün hemen etkin hale geldi olmasıydı.

SQL Server 2014 (12.x) ile başlayarak, tüm Sorgu İyileştirici değişiklikleri en son veritabanı uyumluluk düzeyi olan'e bağlıdır; bu nedenle, planlar yükseltme sırasında değil, kullanıcı COMPATIBILITY_LEVEL'yi en son sürüme değiştirdiğinde değişir. Bu özellik, Sorgu Deposu ile birlikte yükseltme işlemindeki sorgu performansı üzerinde büyük bir denetim düzeyi sağlar. Önerilen yükseltme iş akışı aşağıdaki resimde gösterilmiştir:

Önerilen yükseltme iş akışını gösteren

1. Veritabanı uyumluluk düzeyini değiştirmeden SQL Server'ın yükseltmesini yapın. En son Sorgu İyileştiricisi değişikliklerini kullanıma sunmaz, ancak yine de Sorgu Deposu dahil SQL Server'ın daha yeni avantajlarını sağlar.

2. Sorgu Deposu'nı etkinleştirin. Daha fazla bilgi için bkz. Sorgu Deposu'iş yükünüzle uyumlu tutun.

3. Sorgu Deposu'un sorguları ve planları yakalamasına izin verin ve kaynak/önceki veritabanı uyumluluk düzeyiyle bir performans temeli oluşturur. Tüm planları yakalamak ve kararlı bir temel elde etmek için bu adımda yeterince uzun süre kalın. Bu, üretim iş yükü için normal bir iş döngüsünün süresi olabilir.

4. En son veritabanı uyumluluk düzeyine geçin: potansiyel olarak yeni planlar oluşturmak için iş yükünüzün en son Sorgu İyileştiricisi'ne açık olmasını sağlayın.

5. Analiz ve regresyon düzeltmeleri için Sorgu Deposu'nı kullanın: genellikle yeni Sorgu İyileştiricisi iyileştirmeleri daha iyi planlar üretmelidir. Ancak Sorgu Deposu, plan seçimi regresyonlarını belirlemek ve bunları bir plan zorlama mekanizması kullanarak düzeltmek için kolay bir yol sağlar. SQL Server 2017 (14.x) ile başlayarak, Otomatik Plan Düzeltmesi özelliğini kullanırken bu adım otomatik hale gelir.

   a. Regresyonların olduğu durumlarda Sorgu Deposu'nda önceden bilinen en iyi planı uygulayın.

   b. Zorlamayı başaramayan sorgu planları varsa veya performans hala yetersizse, veritabanı uyumluluk düzeyini önceki ayara geri almayı ve ardından Microsoft Müşteri Desteği ile iletişime geçmeyi göz önünde bulundurun.

Tip

Veritabanının veritabanı uyumluluk düzeyi yükseltmek için SQL Server Management Studio Veritabanını Yükseltme görevini kullanın. Ayrıntılar için bkz. Sorgu Ayarlama Yardımcısı kullanarak Veritabanlarını Yükseltme.

Geçici iş yüklerini tanımlama ve geliştirme

Bazı iş yüklerinde, genel uygulama performansını geliştirmek için ayarlayabileceğiniz baskın sorgular yoktur. Bu iş yükleri genellikle sistem kaynaklarının bir kısmını tüketen nispeten çok sayıda farklı sorguyla karakterize edilir. Benzersiz olan bu sorgular çok seyrek yürütülür (genellikle yalnızca bir kez, dolayısıyla geçici olarak adlandırılır), bu nedenle çalışma zamanı tüketimi kritik değildir. Öte yandan, uygulamanın her zaman net yeni sorgular oluşturması göz önünde bulundurulduğunda, sistem kaynaklarının önemli bir kısmı sorgu derlemesi için harcanıyor ve bu da en uygun seçenek değil. Bu durum, çok fazla sayıda sorgunun ve planın ayrılmış alanı kapladığı için Sorgu Deposu için ideal bir durum değildir. Bu da Sorgu Deposu'nun büyük olasılıkla çok hızlı bir şekilde salt okunur moda geçeceği anlamına gelir. Boyut Tabanlı Temizleme İlkesi (Sorgu Deposu'nu her zaman çalışır durumda tutmak için kesinlikle önerilen) etkinleştirdiyseniz, arka plan işlemi Sorgu Deposu yapılarını temizleme işlemi çoğu zaman önemli sistem kaynaklarını da alır.

En Çok Kaynak Tüketen Sorgular görünümü, iş yükünüzün geçici doğasının ilk göstergesini sağlar:

Yürütme Sayısı ölçümünü kullanarak en çok kullanılan sorgularınızın geçici olup olmadığını analiz edin (bunun için Sorgu Deposu'QUERY_CAPTURE_MODE = ALLile çalıştırmanız gerekir). Yukarıdaki diyagramda, En Çok Kaynak Tüketen Sorgular 90% yalnızca bir kez yürütüldüğünü görebilirsiniz.

Alternatif olarak, sistemdeki toplam sorgu metni, sorgu ve plan sayısını almak ve query_hash ve query_plan_hashkarşılaştırarak bunların ne kadar farklı olduğunu belirlemek için Transact-SQL betiği çalıştırabilirsiniz:

--Do cardinality analysis when suspect on ad hoc workloads
SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text;  
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;  
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM  sys.query_store_query;  
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;  
SELECT COUNT(DISTINCT query_plan_hash) AS  CountDifferentPlanRows FROM  sys.query_store_plan;  

Bu, geçici sorgularla iş yükü durumunda alabileceğiniz olası sonuçlardan biridir:

Sorgu sonucu, Sorgu Deposu'ndaki çok sayıda sorgu ve plana rağmen query_hash ve query_plan_hash aslında farklı olmadığını gösterir. Benzersiz sorgu metinleri ile 1'den çok daha büyük benzersiz sorgu karmaları arasındaki oran, sorgular arasındaki tek fark sorgu metninin bir parçası olarak sağlanan değişmez değer sabiti (parametre) olduğundan, iş yükünün parametreleştirme için iyi bir aday olduğunun göstergesidir.

Bu durum genellikle uygulamanız sorgular (saklı yordamlar veya parametreli sorgular çağırmak yerine) oluşturursa veya varsayılan olarak sorgu oluşturan nesne ilişkisel eşleme çerçevelerine dayanırsa ortaya çıkar.

Uygulama kodunun denetimi size aitse, saklı yordamları veya parametreli sorguları kullanmak için veri erişim katmanını yeniden yazmayı düşünebilirsiniz. Ancak bu durum, tüm veritabanı (tüm sorgular) veya aynı query_hashsahip tek tek sorgu şablonları için sorgu parametreleştirmesi zorlanarak uygulama değişiklikleri olmadan da önemli ölçüde iyileştirilebilir.

Tek tek sorgu şablonlarıyla yaklaşım için plan kılavuzu oluşturulması gerekir:

--Apply plan guide for the selected query template 
DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'<your query text goes here>',  
    @stmt OUTPUT,   
    @params OUTPUT;  
  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION (PARAMETERIZATION FORCED)';  

Plan kılavuzları içeren çözüm daha kesindir ancak daha fazla çalışma gerektirir.

Tüm sorgularınız (veya çoğu) otomatik parametreleştirme için adaysa, veritabanının tamamı için PARAMETERIZATION = FORCED yapılandırmayı göz önünde bulundurun. Daha fazla bilgi için bkz. Zorlamalı ParametreleştirmeKullanma Yönergeleri.

--Apply forced parameterization for entire database  
ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;  

Bu adımlardan herhangi birini uyguladıktan sonra En Çok Kaynak Tüketen Sorgular iş yükünüzün farklı resmini gösterir.

Bazı durumlarda, uygulamanız otomatik parametreleştirme için iyi aday olmayan birçok farklı sorgu oluşturabilir. Bu durumda, sistemde çok sayıda sorgu görürsünüz ancak benzersiz sorgular ile benzersiz query_hash arasındaki oran büyük olasılıkla 1'e yakındır.

Bu durumda, büyük olasılıkla yeniden yürütülmeyecek sorgularda önbellek belleği nin israf edilmesini önlemek için Geçici İş Yükleri sunucu için İyileştir seçeneğini etkinleştirmek isteyebilirsiniz. Sorgu Deposu'nda bu sorguların yakalanmasını önlemek için QUERY_CAPTURE_MODE değerini AUTOolarak ayarlayın.

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO 
  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE CLEAR;  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE = ON   
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);  

Sonraki Adımlar

• Sorgu Deposunu Kullanarak Performansı İzleme
• Sorgu Deposu ile En İyi Uygulama
• Sorgu Ayarlama Yardımcısını Kullanarak Veritabanlarını Yükseltme