Microsoft SQL Server ürününü kullanarak veritabanı oluşturma, verilerin işlenmesi, verilerin saklanması ve veritabanının yönetilmesini rahatlıkla gerçekleştirebilmekteyiz. Özellikle veritabanı nesnelerinin oluşturulması sırasında performans ile ilgili noktalara ayrıca dikkat etmemiz gerekiyor. Ad hoc query olarak yazılan ve çalıştırılan bir sorgunun sonuçları çok kısa bir zamanda getirilebiliyorken, aynı sorguyu parametre kabul edecek şekilde bir Stored Procedure olarak yazdığımızda bazı durumlarda çok daha uzun bir sürede sonuçların getirildiğine şahit olabiliyoruz. Aslında literatürde “parameter sniffing” olarak adlandırılan bu olayda, SQL Server’ın çalışma planı diye adlandırılan Execution planının Memoryde saklanması sonucu, Stored Procedure’un diğer çalıştırılmalarında farklı parametre değerleri girilmesine rağmen plan Cache‘de tutulan Execution planının kullanılması bu performans kaybına neden olmaktadır. Parameter sniffing olayını başka bir yazımızda detaylı olarak ele alacağız. Ancak benzer bir senaryo ile karşılaştığımızda ne yapabileceğimizi de kısaca ele almak gerekiyor.
SQL Server üzerinde çalıştırılan sorguların Execution planları plan Cache adı verilen bellek bölgesinde belirli bir müddet tutulmaktadır. SQL Server bu bölgedeki yoğunluğu belirli periyotlarda kontrol ederek çok kullanılmayan ve bellekten düşürülmesi az maliyete sebep olacak olan planları plan Cache’ten düşürmektedir. Özellikle Stored Procedureler normal Ad hoc querylere göre daha çok tekrar kullanılabilir olduğundan, böyle durumlarda SP Execution planları bellekte daha uzun kalabilmektedir. Peki biz SQL Server için plan Cache’te tutulan planları kendimiz silmek istediğimizde ne yapmamız gerekiyor?
DBCC (Database Console Command “bazı kaynaklarda Database Consistency Checker olarak da geçiyor”) adı verilen komutlar vasıtasıyla bu işlemleri gerçekleştirebilmekteyiz. DBCC FREEPROCCACHE komutu ile SQL Server Plan Cache‘te tutulan Execution planlarının tamamının bellekten düşürülmesini ve bir daha çalıştırıldıklarında tekrar compile edilerek yeni Execution planlarının oluşturulmasını sağlayabilirsiniz. Bu komuta herhangi bir parametre verilmediğinde, plan Cache‘te tutulan bütün Execution planlarının silinmesini sağlayabileceğiniz gibi, parametre olarak Execution planının silinmesini istediğiniz Procedure’un plan handle bilgisini vermeniz durumunda da sadece ilgili nesneye ait Execution plan bellekten düşürülür.
Execution planların SQL Server Memory plan Cache bölümünden nasıl temizlenebileceğini görmüş olduk. Burada dikkat etmemiz gereken bir başka nokta daha mevcuttur. SQL Server’da çalıştırılan sorgular için okunan veri sayfaları (Data Pages) ve Index sayfaları (Index Pages), tekrar çalıştırılmaları durumunda daha hızlı sonuç döndürebilmesi için Buffer Cache denilen bellek bölgesinde saklanmaktadır. Dolayısıyla aynı sorguyu ikinci kez çalıştırdığınızda disk üzerindeki fiziksel dosyadan okuma yapmak yerine Buffer Cache‘te tutulan Data sayfaları üzerinde sadece mantıksal okuma (logical read) yaparak sorgu sonucu çok daha hızlı olarak client tarafına iletilir. Bazı durumlarda SQL Server Memory Buffer Cache bölgesinin de temizlenmesi ve burada tutulan Data sayfalarının ve Index sayfalarının temizlenmesi istenebilir. Normalde Buffer Cache’in boşaltılması için SQL Server Database Engine Service‘inin yeniden başlatılması gerekmektedir. Ancak SQL Server’ın Restart edilmesine gerek kalmadan DBCC DROPCLEANBUFFERS komutu çalıştırılarak da Buffer Cache temizlenebilir. Özellikle canlı sistemlerde bu komutların kullanımına çok dikkat etmek gerekir. Hem DBCC FREEPROCCACHE hem de DBCC DROPCLEANBUFFERS komutları çalıştırıldığında, sistemde bir sonraki seferde çalıştırılacak olan sorgularda compile işlemi tekrar yapılacağından ve Data ve Index page’leri tekrar fiziksel olarak okunacağından performans kaybı gerektirebilir. Ayrıca bu komutları çalıştırabilmek için de ALTER SERVER STATE yetkisinin olması gerekmektedir.
Şimdi plan Cache’in temizlenmesini sağlayan DBCC FREEPROCCACHE ve Buffer Cache’in temizlenmesini sağlayan DBCC DROPCLEANBUFFERS komutlarının çalıştırılmasını ve sonuçlarını beraber inceleyelim.
Öncelikle SET STATISTICS TIME ON ve SET STATISTICS IO ON komutlarını çalıştırarak sorgu çalıştırılma zamanlarını ile diskten yapılan physical read ve bellekten yapılan logical read sonuçlarını incelemek için aktif hale getirelim. Ardından Adventureworks veritabanında SELECT komutu ile bir sorguyu çalıştıralım ve sonuçları inceleyelim.
Bu sorgu ilk kez çalıştırıldığından Execution plan oluşturulacak ve plan Cache‘te saklanacaktır. Ayrıca sorgu sonuçlarının getirilmesi için hem physical read hem de logical read gerçekleştirlecektir.
Sonuçlarda görülebileceği gibi SQL Server sorgunun compile edilmesi ve Execution planının oluşturulması için 49 ms. zaman harcamıştır. Ayrıca sorgunun tamamının çalıştırılması 890 ms. zaman gerektirmiştir. SalesOrderHeader tablosundaki verilere erişmek için 689 logical read (Memoryden), 2 physical read (diskten), 685 read-ahead read (diskten) okuma gerçekleştirilmiştir. SalesOrderDetail tablosu için de 279 logical read, 4 physical read, 281 read-ahead read gerçekleştirilmiştir.
Şimdi DBCC FREEPROCCACHE komutunu çalıştırarak plan Cache‘te tutulan bütün Execution planlarının temizlenmesini sağlayalım ve SELECT sorgumuzu tekrar çalıştıralım, ardından sonuçları inceleyelim.
Görüldüğü üzere plan Cache temizlendiği için sorgu tekrar compile edilmiş olup Execution planın tekrar oluşturulması için SQL Server 52 ms. zaman harcamıştır. Ancak sorgu tekrar çalıştırıldığı için Buffer Cache‘te tutulan Data ve Index page’lerinden okuma gerçekleştirildiği için diskten okuma (physical ve read-ahead reads) yapılmamış ve verilerin tamamı Memorydeki Buffer Cache’ten (logical reads) okunarak getirilmiştir.
Son olarak DBCC DROPCLEANBUFFERS komutunu da çalıştırarak aynı sorgu için Buffer Cache‘te tutulan verilerin temizlenmesini sağlayalım.
Resim-6
Sonuç setinde görüldüğü gibi Execution plan yeniden (25 ms.) oluşturulmuş olup, DBCC DROPCLEANBUFFERS komutu ile Buffer Cache temizlendiğinden, aynı sorgu için tekrar diskten fiziksel okuma gerçekleştirilmiştir.
Sonuç olarak DBCC FREEPROCCACHE komutu ile plan Cache temizlenmekte ve Execution planlar tekrar oluşturulmaktayken, DBCC DROPCLEANBUFFERS komutu ile bellekte tutulan Data ve Index page’leri temizlenmekte ve tekrar diskten fiziksel okuma yapılmaktadır. Özellikle production ortamlarda bu komutların çalıştırılmaması veya çok dikkatle çalıştırılması gerektirğini tekrar hatırlatmak isterim. Aksi takdirde performans sorunları ile karşılaşıyor olabilirsiniz.