-
- Üyelik Tarihi
- 24 Mar 2017
-
- Mesajlar
- 4,578
-
- MFC Puanı
- 1,437
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 procedureun diğer çalıştırılmalarında farklı parametre değerleri girilmesine rağmen plan cachede 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. Bu yazımızda SQL Server üzerinde hem plan cache hem de memory buffer temizleme işleminin nasıl yapılacağını ele alacağız.
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 cacheten 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 cachete 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 Cachete 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 cachete tutulan bütün execution planlarının silinmesini sağlayabileceğiniz gibi, parametre olarak execution planının silinmesini istediğiniz procedureun 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 Serverda ç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 cachete 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 cachein boşaltılması için SQL Server Database Engine Serviceinin 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 pageleri 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 cachein temizlenmesini sağlayan DBCC FREEPROCCACHE ve buffer cachein 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 cachete 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 cachete 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 cachete tutulan data ve index pagelerinden okuma gerçekleştirildiği için diskten okuma (physical ve read-ahead reads) yapılmamış ve verilerin tamamı memorydeki buffer cacheten (logical reads) okunarak getirilmiştir.
Son olarak DBCC DROPCLEANBUFFERS komutunu da çalıştırarak aynı sorgu için buffer cachete tutulan verilerin temizlenmesini sağlayalım.
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 pageleri 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.
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 cacheten 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 cachete 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 Cachete 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 cachete tutulan bütün execution planlarının silinmesini sağlayabileceğiniz gibi, parametre olarak execution planının silinmesini istediğiniz procedureun 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 Serverda ç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 cachete 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 cachein boşaltılması için SQL Server Database Engine Serviceinin 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 pageleri 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 cachein temizlenmesini sağlayan DBCC FREEPROCCACHE ve buffer cachein 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.
1SET STATISTICS IO ON
2SET STATISTICS TIME ON
3
4
5SELECT * FROM Sales.SalesOrderHeader, Sales.SalesOrderDetail
6WHERE SalesOrderDetailID = 1
2SET STATISTICS TIME ON
3
4
5SELECT * FROM Sales.SalesOrderHeader, Sales.SalesOrderDetail
6WHERE SalesOrderDetailID = 1
Bu sorgu ilk kez çalıştırıldığından execution plan oluşturulacak ve plan cachete 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 cachete 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.
1DBCC FREEPROCCACHE
2
3SELECT * FROM Sales.SalesOrderHeader, Sales.SalesOrderDetail
4WHERE SalesOrderDetailID = 1
2
3SELECT * FROM Sales.SalesOrderHeader, Sales.SalesOrderDetail
4WHERE SalesOrderDetailID = 1
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 cachete tutulan data ve index pagelerinden okuma gerçekleştirildiği için diskten okuma (physical ve read-ahead reads) yapılmamış ve verilerin tamamı memorydeki buffer cacheten (logical reads) okunarak getirilmiştir.
Son olarak DBCC DROPCLEANBUFFERS komutunu da çalıştırarak aynı sorgu için buffer cachete tutulan verilerin temizlenmesini sağlayalım.
1DBCC FREEPROCCACHE
2
3DBCC DROPCLEANBUFFERS
4
5
6SELECT * FROM Sales.SalesOrderHeader, Sales.SalesOrderDetail
7WHERE SalesOrderDetailID = 1
2
3DBCC DROPCLEANBUFFERS
4
5
6SELECT * FROM Sales.SalesOrderHeader, Sales.SalesOrderDetail
7WHERE SalesOrderDetailID = 1
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 pageleri 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.