SQL Server DBCC SHOW_STATISTICS Çıktısı Nasıl Yorumlanır

Problem

Bir SQL Server veritabanında istatistiklerin önemini öğrendiniz, ancak bunu tam olarak anlamıyorsunuz. Dahası, DBCC SHOW_STATISTICS komutunun istatistikler hakkında bilgi verdiğini öğrendiğinizde, komutu çalıştırırsınız ve çıktısı kafa karışıklığınızı artırır. Bu ipucunda, SQL Server DBCC SHOW_STATISTICS komutunun çıktılarını nasıl yorumlayacağınızı açıklayacağım.

Çözüm

Database Administration’ın en çok yanlış anlaşılan kavramlarından biri istatistikler ve bunların yorumlanmasıdır. İstatistiklerin önemi hakkında fazla bilgisi olmayanlar için, sadece bir fikir vermek gerekirse, veritabanı motorunun bir execution plan oluşturması gerektiğinde, hatta index’lerden bile daha belirleyici bir faktördürler.

Aslında, SQL Server bir index ile ilişkili mevcut istatistikleri, kullanımının faydalı olup olmadığına karar vermek için kullanacaktır. Bu gerçek genellikle yeni başlayanların istatistiklerin index’lerin bir parçası olduğunu düşünerek her iki kavramı karıştırmasına neden olur.

Index’ler ve istatistikler arasındaki ilişki simetrik değildir; her index’in kendisiyle ilişkili istatistikleri vardır, ancak tüm istatistiklerin karşılık gelen bir index’i yoktur.

DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS, bir table veya indexed view üzerindeki belirli bir statistic’in mevcut durumunu gösterir.

Bu komutun çıktısı üç öğeden oluşur; statistic header, density vector ve histogram. Bu ipucunda bu kavramları derinlemesine inceleyeceğiz, ancak önce size en temel syntax’ı ve çıktısını göstereceğim.

DBCC SHOW_STATISTICS(Object_Name, Statistic_Name)

Burada Object_Name table veya indexed view’in adıdır ve Statistic_Name statistic’in adıdır.

STATISTICS HEADER — DBCC SHOW_STATISTICS

Bu öğe bir statistics hakkındaki temel bilgileri gösterir.

Name İstatistik adını belirtir.

Updated İstatistiğin en son ne zaman güncellendiğini gösterir.

Rows İstatistikler en son güncellendiğinde, altında yatan nesnenin (bir tablo veya indexed view) sahip olduğu toplam satır sayısını temsil eder. İstatistiklerin güncel olup olmadığını kontrol etmenin kolay bir yolu, bu değeri mevcut satır sayısıyla karşılaştırmaktır. Ancak, eğer istatistik bir filtered index’e aitse, bu sütunun değeri yalnızca filtreyi karşılayan satırların sayısını gösterir.

Rows Sampled İstatistiklerin oluşturulması için analiz edilen satır sayısını belirtir.

Steps Histogramdaki adım sayısını ifade eder. Bu konuyu histogramı tanıttığım bölümde detaylandıracağım.

Density Sorgu optimize edicisi tarafından kullanılmaz; yalnızca geriye dönük uyumluluk için korunmuştur.

Average Key Length İstatistiği oluşturan sütunun veya sütun kümesinin ortalama boyutunu (byte cinsinden) belirtir.

String Index İstatistik, “Where Column LIKE ‘%Value’” biçimindeki sorguların optimizasyonunu iyileştirmek amacıyla özetlenmiş istatistikler içeriyorsa, bu değer YES olarak ayarlanır.

Filter Expression İstatistikler filtrelenmişse, kullanılan filtre ifadesini gösterir; aksi takdirde NULL olur.

Unfiltered Rows İstatistikler filtrelenmişse, filtre uygulanmadan önceki tablo satır sayısını gösterir. Filtre yoksa bu değer Rows ile aynıdır.

DENSITY VECTOR — DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS density vector, değerlerin dağılımını ölçer. Statistics’teki her column prefix’i için bir satır gösterir.

Örneğin, statistics üç column Col1, Col2, Col3'ten bu sırayla oluşuyorsa; o zaman üç density vector olacaktır: ilki Col1 column’ı için, ikincisi Col1 ve Col2 column’ları için ve sonuncusu Col1, Col2, Col3 column’ları için olacaktır.

All Density Bu, 0 ile 1 arasında bir sayıdır. n, farklı (distinct) değerlerin sayısı olmak üzere 1/n şeklinde tanımlanır. Density değeri sıfıra ne kadar yakınsa, anahtar değerler o kadar benzersizdir. Bu yüzden candidate key’ler daha düşük density değerine sahiptir.

Average Length İstatistiği oluşturan sütunun veya sütun kümesinin ortalama boyutunu (byte cinsinden) belirtir.

Columns All Density ve Average Length değerlerinin gösterildiği önek (prefix) içerisindeki sütun adlarını belirtir.

USE [master]
GO

CREATE DATABASE [testDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'testDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\testDB.mdf' ,
 SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'testDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\testDB_log.ldf' , 
SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 128MB)
GO

Şimdi bir Primary Key “PK” olarak tanımladığımız bir identity column “ID” ve bir varchar column “Name” içeren çok basit bir table oluşturuyoruz ve üzerine “IX_Name” adında bir index kuruyoruz

USE testDB
GO

CREATE TABLE testTable
    (
      ID INT IDENTITY(1, 1) ,
      Name VARCHAR(50) CONSTRAINT PK PRIMARY KEY CLUSTERED ( ID )
    )

CREATE INDEX IX_Name ON testTable (Name)

Bir sonraki adım, tabloya 2000 satır eklemek ve istatistiklerini güncellemektir. Satırların “Name” column’ı için aynı değeri içerdiğine dikkat edin.

USE testDB
GO

INSERT  INTO dbo.testTable
        ( Name )
VALUES  ( 'Murat'  
          )
GO 2000

exec sp_updatestats
go

Şimdi “PK” üzerindeki istatistikleri görebiliriz.

USE testDB
GO

DBCC SHOW_STATISTICS('testTable','PK')
GO

Aynı şeyi “IX_Name” üzerindeki istatistiklerle yapalım.

USE testDB
GO

DBCC SHOW_STATISTICS('testTable','IX_Name')
GO

Bir sonraki resimde görebileceğiniz gibi, Density Vector’ün density’si sıfıra çok yakındır çünkü ID column’ının tüm değerleri farklıdır.

HISTOGRAM — DBCC SHOW_STATISTICS

Histogram — DBCC SHOW_STATISTICS,

Sabit sayıda (en fazla 200) aralığa bölünmüş şekilde, değerlerin dağılımını temsil eden bir yapıdır. Histogramı oluşturmak için optimizer önce sütun değerlerini sıralar, ardından histogram adımlarının üst sınırlarını tanımlayacak limit değerlerini belirler. Bundan sonra optimizer, her adıma düşen değerleri özetler.

RANGE_HI_KEY Bir histogram adımının üst sınır sütun değeridir. Bu sütun değeri aynı zamanda key value olarak da adlandırılır.

RANGE_ROWS Sütun değeri, histogram adımının üst sınırı hariç olmak üzere bu adım aralığına düşen satırların tahmini sayısıdır.

EQ_ROWS Sütun değeri, histogram adımının üst sınırına eşit olan satırların tahmini sayısıdır.

DISTINCT_RANGE_ROWS Histogram adımının içinde (üst sınır hariç) benzersiz sütun değerine sahip satırların tahmini sayısıdır.

AVG_RANGE_ROWS Histogram adımının içinde (üst sınır hariç) yinelenen sütun değerine sahip satırların ortalama sayısıdır. Bu değer, DISTINCT_RANGE_ROWS sıfırdan büyükse RANGE_ROWS / DISTINCT_RANGE_ROWS formülüyle hesaplanır; aksi takdirde değeri 1 olur.

Effects of Statistics on Plan Generation

İstatistiklerin Plan Üretimi Üzerindeki Etkileri

AdventureWorks2016 veritabanı üzerinde basit bir test yapalım.

Person.Person tablosunu ve IX_Person_LastName_FirstName_MiddleName istatistik histogramını ele alalım.

Şimdi, select statement kullanarak, soyadı “Abbas” ve “Ashe” arasındaki kişilerle ilgili bir sorgu çalıştıracağız.

USE AdventureWorks2016
GO

SELECT  LastName ,
        modifieddate
FROM    [Person].[Person]
WHERE   LastName BETWEEN 'Abbas' AND 'Ashe'
GO

Aşağıdaki görselde, önceki sorgunun execution plan’ını görebiliyoruz. Query optimizer, en iyi planın bir index seek ve key lookup yapmak olduğuna karar vermiştir.

Öte yandan, aynı sorguyu çalıştırırsak, ancak bu sefer soyadı “Abel” ve “Bailey” aralığında olan kişileri listeleyeceksek, üst sınırda bir adım daha ileri gitmiş oluruz.

USE AdventureWorks2016
GO

SELECT  LastName ,
        modifieddate
FROM    [Person].[Person]
WHERE   LastName BETWEEN 'Abel' AND 'Bailey'
GO

Sonraki görselde görebileceğin gibi, artık query optimizer en iyi yaklaşımın bir clustered index scan gerçekleştirmek olduğuna karar vermiştir.