SQL Server’da İstatistik Kavramı ve Performansa Etkisi

“SQL Server’da Index Kavramı ve Performansa Etkisi” isimli makalemde index’in bir sorgunun performansına ne kadar etki edeceğinden bahsettik. SQL Server’a bir sorgu geldiğinde, SQL Server sorgunun nasıl çalışacağına karar verir. Sorgunun nasıl çalışacağına karar verirken istatistikler den faydalanır. İstatistikler tabloda veya indexed view’lerde verinin dağılımını gösterirler. SQL Server’da istatiskler’den faydalanarak sorgunun aşağıdaki yöntemlerden biri veya birden fazlasının bir araya gelmesiyle aradığı kayda ulaşmasını sağlar.

• Sadece Index’e giderek index’in hepsini taramadan direk aradığı kaydı bulabilecek mi?(Index Seek)
• Sadece Index’e giderek index’in hepsini tarayarak mı aradığı kaydı bulabilecek?(Index Scan)
• Önce index’ gidip index’in hepsini taramadan aradığı kaydın bir kolonunu bularak daha sonra bu index üzerindeki key değeri ile clustered index’e giderek aradığı kaydın diğer kolonlarını bulabilecek?(Index Seek+Key Lookup)
• Önce index’ gidip index’i tarayarak daha sonra bu index üzerindeki key değeri ile clustered index’e giderek mi aradığı kaydı bulabilecek?(Index Scan+Key Lookup)
• Tabloda nonclustered index yok, clustered index’e giderek direk aradığı kaydı bulabilecek mi?(Clustered Index Seek)
• Tabloda nonclustered index yok, clustered index’in hepsini tarayarak mı aradığı kaydı bulabilecek?(Clustered Index Scan)

Örneğin Table1 isminde bir tabloda 100 kayıt olsun. Bu tablodaki A kolonunda index olsun. A kolonundaki 100 kayıttan 3 tanesi Ahmet değerini içeriyor. 97 tanesi de Mehmet değerini içeriyor olsun.

select A from Table1 where A=’Ahmet’  sorgusunu çalıştırdığımda sql server istatistiklere gidecek ve Ahmet değerinin tablodaki oranının %3 olduğunu bulacak ve istatistiklerin yardımıyla index seek’in en hızlı çözüm olacağına karar verecektir.

select A from Table1 where A=’Mehmet’ sorgusunu çalıştırdığımda sql server istatistiklere gidecek ve Mehmet değerinin tablodaki oranının %97 olduğunu bulacak ve istatistiklerin yardımıyla index scan’ın en hızlı çözüm olacağına karar verecektir. 97 kere seek yapmak yerine tabloyu baştan taramak daha hızlı olacaktır.

Eğer yukardaki sorgu bir sp içersinden gelirse query plan bir kere oluşturulacak ve ikinci sefer ilk oluşturulan query plan kullanılacağı için parameter sniffing oluşacaktır. “Sp(Stored Procedure) Nedir” ve “Parameter Sniffing” isimli makalelerimi okumanızı tavsiye ederim.

İstatistiklere geri dönecek olursak,

Bir index oluşturduğumuzda o index’e ait istatistik de oluşur. Ama index birden fazla kolon içeriyorsa sadece ilk kolon için istatistik oluşturulur. Index’i rebuild ettiğimizde istatistik te güncellenir.

Veritabanı bazında istatistiklerle ilgili birkaç ayar vardır.

Veritabanı üzerinde sağ tıklayarak properties, ardından Options sekmesine geçtiğimizde aşağıdaki gibi bir ekran gelir.

İstatistikleri güncellemenin iki yöntemi var.

İstatistiklerle ilgili bilmeniz gereken en önemli şey güncel kalmaları gerektiğidir. Güncel kalmalarını sağlamak için yukarda bahsettiğim ayarların dışında istatistikleri güncelleyen bir job’ınız olması gerekir. Ben aşağıdaki script’i düzenli çalışan bir job haline getirdim. Genelde haftada bir çalışacak şekilde oluşturdum ama bazı sistemlerde istatistik daha hızlı bozuluyor ve güncelledikten birkaç saat sonra istatistik bozulduğu için performans azalmaya başlayabiliyor. O yüzden bir sistemde performans sorunu varsa ilk yapacağımız şeyin istatistikleri güncellemek olması gerekir. Günlük hayattan bir önrek verecek olursam çok yoğun kullanılan bir sistemde istatistikleri güncelleyen job’ım saatte bir çalışıyor. Tabi siz ilk olarak haftada bir çalışacak şekilde set edip ihtiyaç halinde bu süreyi azaltarak istatistiklerinizi daha sık güncelleyebilirsiniz.

Instance üzerindeki tüm veritabanlarındaki tüm istatistikleri güncelleyen ve haftada bir çalışacak şekilde job olarak oluşturmanız gereken script’i aşağıda paylaşıyorum. Eğer instance üzerinde restoring,read only ya da offline mode’da bir veritabanı varsa script hata verecektir.

DECLARE @SQL NVARCHAR(1000) 

DECLARE @DB sysname 

DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR 
   SELECT [name] 
   FROM master..sysdatabases
   WHERE [name] NOT IN ('model', 'tempdb','master','msdb')
   ORDER BY [name]
OPEN curDB 
FETCH NEXT FROM curDB INTO @DB 
WHILE @@FETCH_STATUS = 0 
   BEGIN 
   IF DATABASEPROPERTYEX(@DB,'Updateability') = 'READ_WRITE'
   BEGIN
       SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'EXEC sp_updatestats' + CHAR(13) 
       exec sp_executesql @SQL 
END
       FETCH NEXT FROM curDB INTO @DB 
   END 
CLOSE curDB 
DEALLOCATE curDB