Statistics ve Cardinality Estimation: Histogram, Density ve Yanlış Tahminin Bedeli

Sürüm notu: Bu yazıdaki T-SQL (Transact-SQL, Microsoft'un SQL (Structured Query Language) lehçesi) örnekleri SQL Server 2025 CU10 (17.0.5035.5, compatibility level 170) üzerinde denenmiştir; .NET tarafı için ayrıca bir kod gerekmedi, çünkü buradaki her sorgu Entity Framework Core (EF Core) ya da Dapper'dan aynı şekilde gelir.

Önce Tahmin Et

Bookstore'da 1 numaralı müşterinin (bir üniversite kütüphanesi) 200.000 siparişi var. Aynı sorguyu iki şekilde yazıyoruz: önce değeri doğrudan, sonra bir değişkenle:

SET STATISTICS PROFILE ON;

SELECT COUNT(*) FROM dbo.Orders WHERE CustomerId = 1 OPTION (RECOMPILE);

DECLARE @c int = 1;
SELECT COUNT(*) FROM dbo.Orders WHERE CustomerId = @c;

İki sorgu da 200.000 sayar. Optimizer plan kurarken her biri için kaç satır bekledi? Birincisi için "200.000" demek kolay. İkincisi?

    Rows EstimateRows
  200000     200000.0   |--Index Seek(OBJECT:([Orders].[IX_Orders_CustomerId]), SEEK:([Orders].[CustomerId]=(1)) ORDERED FORWARD)
  200000    10.223904   |--Index Seek(OBJECT:([Orders].[IX_Orders_CustomerId]), SEEK:([Orders].[CustomerId]=[@c]) ORDERED FORWARD)

10,22 satır. Gerçek değerin yirmi binde biri. Değişkenin değeri derleme anında bilinmediği için optimizer "ortalama bir müşteri"yi varsaydı. Bir COUNT(*)'ta bu tahminin bedeli yok; ama aynı tahmin bir join'e, bir sıralamaya ya da bir memory grant hesabına girdiğinde planın tamamı yanlış kurulur. Bu yazıda optimizer'ın bu sayıları nereden aldığını inceliyoruz: statistics nesnesi, histogram, density vector, statistics'in ne zaman oluşup güncellendiği, ascending key problemi, birbirine bağlı kolonlar ve yanlış tahminin gerçek bedeli.

Statistics Nesnesi: Header, Density Vector ve Histogram

Her index kendi key kolonları için bir statistics nesnesiyle gelir. IX_Orders_CustomerId'nin statistics'ine bakalım:

DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_CustomerId') WITH STAT_HEADER, NO_INFOMSGS;
DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_CustomerId') WITH DENSITY_VECTOR, NO_INFOMSGS;

Name                 Updated             Rows    Rows Sampled Steps Density    Average key length
-------------------- ------------------- ------- ------------ ----- ---------- ------------------
IX_Orders_CustomerId Nov  8 2026 12:00PM 1000000 1000000      96    0.12805143 8.0

All density  Average Length Columns
------------ -------------- --------------------
1.0223904E-5 4.0            CustomerId
0.000001     8.0            CustomerId, OrderId

Üç parça var. Header ne zaman, kaç satır üzerinden ve kaç satırı örnekleyerek oluşturulduğunu söylüyor; index SCHEMA.sql'de veri yüklendikten sonra yaratıldığı için 1.000.000 satırın tamamı okunmuş (FULLSCAN). Density vector her kolon öneki için 1 / (farklı değer sayısı): 97.810 farklı müşteri için 1,0224e-5. Bu, "değer bilinmiyorsa ortalama bir değer kaç satır getirir" sorusunun cevabı ve açılıştaki tahmin tam olarak buradan geldi: 1.000.000 × 1,0224e-5 = 10,22. İkinci satırdaki CustomerId, OrderId, clustering key'in her nonclustered index'e eklendiğinin bir başka kanıtı.

Üçüncü parça histogram. Yalnızca ilk key kolonu için tutulur ve en fazla 200 adımdır; bizimkinde 96 adım var. Belgelenmiş DMV (Dynamic Management View) sys.dm_db_stats_histogram ile okunabilir:

SELECT TOP (8) step_number, range_high_key, range_rows, equal_rows, distinct_range_rows, average_range_rows
FROM sys.dm_db_stats_histogram(OBJECT_ID('dbo.Orders'),
     INDEXPROPERTY(OBJECT_ID('dbo.Orders'), 'IX_Orders_CustomerId', 'IndexID'))
ORDER BY step_number;

step_number range_high_key range_rows equal_rows distinct_range_rows avg_range_rows
----------- -------------- ---------- ---------- ------------------- --------------
1           1              0          200000     0                   1.00
2           2              0          17359      0                   1.00
3           3              0          4458       0                   1.00
4           5              3030       2504       1                   3030.00
5           8              3919       1610       2                   1959.50
6           10             1456       1347       1                   1456.00
7           13             2376       1133       2                   1188.00
8           19             4785       860        5                   957.00

Her adım bir range_high_key değeriyle biter. equal_rows tam o değere eşit satır sayısı, range_rows önceki adımla bu değer arasındaki satırlar, distinct_range_rows o aralıktaki farklı değer sayısı ve avg_range_rows da ikisinin oranı. SQL Server çarpık değerleri kendi adımlarına koymuş: müşteri 1, 2 ve 3 tek başına birer adım. Uzun kuyruk ise geniş adımlarda toplanıyor; örneğin adım 73, 15.167 ile 18.412 arasındaki 3.245 müşteriyi tek bir ortalamaya indiriyor.

Tahmin Nasıl Hesaplanır?

Eşitlik koşulunda optimizer değerin histogramda nereye düştüğüne bakar. Dört örnek:

SELECT COUNT(*) FROM dbo.Orders WHERE CustomerId = 1 OPTION (RECOMPILE);
SELECT COUNT(*) FROM dbo.Orders WHERE CustomerId = 15000 OPTION (RECOMPILE);
SELECT COUNT(*) FROM dbo.Orders WHERE CustomerId = 6 OPTION (RECOMPILE);
SELECT COUNT(*) FROM dbo.Orders WHERE CustomerId BETWEEN 1 AND 3 OPTION (RECOMPILE);

    Rows EstimateRows
  200000     200000.0   SEEK:([Orders].[CustomerId]=(1))
      16    9.5975103   SEEK:([Orders].[CustomerId]=(15000))
    2070       1959.5   SEEK:([Orders].[CustomerId]=(6))
  221817     221817.0   SEEK:([Orders].[CustomerId] >= (1) AND [Orders].[CustomerId] <= (3))

Müşteri 1 bir range_high_key; tahmin doğrudan equal_rows, yani kusursuz. Müşteri 15.000 bir adımın içinde kalıyor; optimizer o adımın avg_range_rows değerini (9,60) kullandı, gerçek 16. Müşteri 6 da 5. adımın içinde: tahmin 1.959,5, gerçek 2.070. Bu sayıyı önceki yazıdaki tipping point tablosundan hatırlıyorsun; optimizer'ın index ile tarama arasındaki kararını tam bu ortalama verdi. Aralık koşulunda ise adımlar toplanır ve 1–3 aralığı için sonuç yine kusursuz.

Değişken, OPTIMIZE FOR UNKNOWN hint'i ya da derleme anında değeri bilinmeyen her durum density vector'e düşer. Stored procedure ve sp_executesql parametrelerinde ise optimizer ilk çalıştırmadaki değeri "koklar" (parameter sniffing) ve histogramı o değerle kullanır. Bunun iyi ve kötü taraflarını yedinci yazıda ayrıca ele alacağız.

Statistics Ne Zaman Oluşur ve Güncellenir?

Index olmayan bir kolon filtrelendiğinde, AUTO_CREATE_STATISTICS açıksa (varsayılan) SQL Server o kolon için otomatik bir statistics oluşturur. Status üzerinde index yok; tek bir sorgu yetti:

SELECT COUNT(*) AS pending FROM dbo.Orders WHERE Status = 'Pending';

SELECT s.name, s.auto_created, sp.rows, sp.rows_sampled, sp.steps, sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID('dbo.Orders');

name                      auto_created rows    rows_sampled sample_pct steps modification_counter
------------------------- ------------ ------- ------------ ---------- ----- --------------------
PK_Orders                 0            1000000 168134       16.81      184   0
_WA_Sys_00000004_4E88ABD4 1            1000000 168134       16.81      5     0
UX_Orders_OrderNumber     0            1000000 1000000      100.00     180   0
IX_Orders_CustomerId      0            1000000 1000000      100.00     96    0

_WA_Sys_... adındaki nesne otomatik oluşturulan statistics; satırların yalnızca %16,81'ini örnekledi. Histogramında Pending için 4.680 satır yazıyor, gerçek 4.800; örnekleme küçük bir sapma getirdi. PK_Orders da örneklenmiş: tablo boşken yaratıldığı için statistics'i sonradan otomatik güncellemeyle, yine örneklemeyle oluştu. İndex'in veri yüklendikten sonra yaratılması ise FULLSCAN demek.

Otomatik güncelleme için SQL Server her statistics'in key kolonlarındaki değişiklikleri modification_counter'da sayar. Compatibility level 130 ve üstünde eşik MIN(500 + 0,20 × n, √(1000 × n)); bir milyon satır için √1.000.000.000 ≈ 31.623 değişiklik. Bunu doğrudan denedim:

WITH x AS (SELECT TOP (31000) Status FROM dbo.Orders WHERE Status = 'Delivered' ORDER BY OrderId)
UPDATE x SET Status = 'Cancelled';
SELECT COUNT(*) AS cancelled FROM dbo.Orders WHERE Status = 'Cancelled';  /* statistics'i kullanan sorgu */
/* sonra 1.000 satır daha ve aynı sorgu */

31.000 değişiklik eşiğin altında kaldı ve statistics eskimiş sayılmadı. 32.000'de eşik aşıldı; güncellemeyi değişikliğin kendisi değil, statistics'i kullanan bir sonraki sorgunun compile'ı tetikledi ve sayaç sıfırlandı. Varsayılan güncelleme senkrondur: o sorgu statistics güncellenene kadar bekler. AUTO_UPDATE_STATISTICS_ASYNC açılırsa sorgu eski statistics'le hemen devam eder, güncelleme arka planda yapılır.

Ascending Key Problemi

31.623 değişiklik eşiği büyük tablolarda can sıkıcı bir boşluk bırakır. Sipariş tablosu gibi sürekli artan bir kolonu olan tablolarda en yeni satırlar hep histogramın son adımının dışındadır. CreatedAt üzerine bir index açtım (FULLSCAN statistics'le), sonra 20.000 yeni sipariş ekledim. Değişiklik sayısı eşiğin altında kaldığı için histogram hâlâ 20 Ekim 2026'da bitiyor. Şimdi yalnızca son günlerin siparişlerini müşteri e-postalarıyla getiren sorgu:

SELECT o.OrderId, o.CreatedAt, c.Email
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
WHERE o.CreatedAt >= '2026-10-21'
OPTION (RECOMPILE);

SQL Server 2014'ten beri varsayılan olan CE (cardinality estimation) model'i histogramın dışına düşen bu aralık için tam 6.000 satır tahmin etti; bu, 20.000'lik değişiklik sayacının %30'una denk geliyor. Bu tahminle hash join'li paralel bir plan seçti. Aynı sorguyu compatibility level 110 ve öncesinin legacy CE model'iyle (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')) çalıştırdım; eski bir veritabanında LEGACY_CARDINALITY_ESTIMATION = ON ayarını taşıyorsan bu senin varsayılanın:

/* Legacy CE: tahmin 1 satır */
Table 'Customers'. Scan count 0, logical reads 60000
Table 'Orders'. Scan count 1, logical reads 60044
   20000     1   1.0  |--Nested Loops(Inner Join, OUTER REFERENCES:([o].[CustomerId]))
   20000     1   1.0       |--Nested Loops(Inner Join, OUTER REFERENCES:([o].[OrderId]))
   20000     1   1.0       |    |--Index Seek(OBJECT:([Orders].[IX_Orders_CreatedAt] AS [o]), SEEK:([o].[CreatedAt] >= '2026-10-21 00:00:00'))
   20000 20000   1.0       |    |--Clustered Index Seek(OBJECT:([Orders].[PK_Orders] AS [o]), ... LOOKUP ORDERED FORWARD)
   20000 20000   1.0       |--Clustered Index Seek(OBJECT:([Customers].[PK_Customers] AS [c]), ...)

/* Varsayılan CE: tahmin 6.000 satır */
Table 'Orders'. Scan count 22, logical reads 3070
Table 'Customers'. Scan count 11, logical reads 1954
   20000    10   6000.0  |--Hash Match(Inner Join, HASH:([o].[CustomerId])=([c].[CustomerId]))

Legacy model histogramın ötesinde hiç satır olmadığını varsayıp 1 satır tahmin etti ve "bir satır için en ucuz plan"ı kurdu: iç içe iki nested loops, her satır için bir key lookup ve bir müşteri seek'i. 20.000 satır gelince bu plan 120.044 logical read tuttu; hash join'li plan 5.024. Aynı sorgu, aynı veri, 24 kat fark. UPDATE STATISTICS dbo.Orders IX_Orders_CreatedAt sonrasında legacy CE bile 17.772 satır tahmin edip hash join'e geçti.

Birbirine Bağlı Kolonlar ve CE Model'leri

Histogramlar tek kolonludur. Birden çok koşul olduğunda optimizer seçicilikleri birleştirmek için bir varsayım yapmak zorundadır ve gerçek hayatta kolonlar birbirinden bağımsız değildir. Customers tablosunda şehir ülkeye bağlı: Istanbul'daki herkes TR'de. 100.000 müşterinin 59.788'i TR'de, 41.755'i Istanbul'da; ikisini birlikte sağlayan da 41.755 kişi. Her kolon için FULLSCAN statistics açıp aynı sorguyu farklı model varsayımlarıyla çalıştırdım:

SELECT COUNT(*) FROM dbo.Customers WHERE Country = 'TR' AND City = N'Istanbul' OPTION (RECOMPILE);
/* aynı sorgu USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'), 'ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES'
   ve 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES' ile */

Hesaplar açık: tam bağımsızlık iki seçiciliği çarpar (0,5979 × 0,4176 = 0,2496). Varsayılan model en seçici koşulu tam, ikincisini karekökünü alarak çarpar (0,4176 × √0,5979 = 0,3229). Tam korelasyon yalnızca en seçici koşulu alır ve bu veride kusursuz sonuç veriyor. Ama hiçbir varsayım her veride doğru değil: Country = 'DE' AND City = N'Istanbul' sıfır satır döndürürken varsayılan model 9.885 satır bekledi. (Country, City) için multi-column statistics açmak da bunu çözmedi; o nesne yalnızca kombinasyonun ortalama density'sini tutar, değer çiftleri için histogram tutmaz ve bizim denememizde varsayılan modelin tahmini 24.964'e indi.

Varsayılan modelin bu tür hatalarına yönelik iki otomatik düzeltme mekanizması var. SQL Server 2022 (compatibility level 160) ile gelen CE feedback, tekrar eden sorgularda tahmin ile gerçek arasında büyük fark gördüğünde korelasyon, join containment ya da row goal varsayımını değiştirip yeni planı dener; işe yararsa bunu bir Query Store hint'i olarak kalıcı yapar. Query Store'un açık ve READ_WRITE olması gerekir ve edition tablosuna göre bu özellik Enterprise'da var. SQL Server 2025 (level 170) bunu CE feedback for expressions ile genişletti: öğrenilen model tercihi artık tek bir sorguya değil, farklı sorgulardaki aynı ifadelere de uygulanabiliyor.

SQL Server 2019/2022'de farkı: histogram, density vector, otomatik güncelleme eşiği ve CE 120+ modeli 2019'dan beri aynı. CE feedback 2022 (level 160) ile geldi; 2019'da yok. CE feedback for expressions yalnızca 2025 ve level 170'te. sys.dm_db_stats_histogram 2016 SP1 (Service Pack 1) CU2'den (Cumulative Update 2) beri mevcut.

Üretimde Dikkat

Statistics bakımını index bakımından ayır. Microsoft'un index bakımı rehberi de rebuild sonrası görülen hızlanmanın çoğu zaman FULLSCAN statistics güncellemesinden geldiğini söylüyor. Gece job'ında UPDATE STATISTICS çalıştırmak, rebuild'e göre çok ucuzdur.

Büyük ve sürekli büyüyen tablolarda otomatik eşiği bekleme. Sipariş, log, event gibi tablolarda yeni satırlar histogramın dışında birikir. Yüklemeden sonra ilgili statistics'i elle güncellemek ya da kritik statistics için daha sık bir job kurmak ascending key sorununu küçültür.

Örneklemeyi bilinçli seç. Çarpık dağılımlı kolonlarda varsayılan örnekleme önemli değerleri kaçırabilir. UPDATE STATISTICS ... WITH FULLSCAN ya da WITH SAMPLE 50 PERCENT, PERSIST_SAMPLE_PERCENT = ON ile bir oran belirleyip bunun otomatik güncellemelerde de korunmasını sağlayabilirsin.

Sık Düşülen Tuzaklar

"Index çalışmıyor" demeden önce tahmine bakmamak. Planda gerçek ve tahmini satır sayısını yan yana koy; aralarında 10 kattan büyük fark varsa önce statistics'i kontrol et. Index'i zorlamak çoğu zaman yanlış çözümdür.

Sorguda değişken kullanıp histogramı devre dışı bırakmak. T-SQL script'lerinde ve bazı raporlarda DECLARE edilen değişkenler optimizer'a yalnızca ortalamayı bırakır; açılıştaki örnekte 200.000 satır 10 satır sanıldı.

Legacy CE'yi "yükseltme sonrası regresyon oldu" diye veritabanı geneline açıp unutmak. Sorun tek bir sorgudaysa USE HINT ya da Query Store hint'iyle o sorguya özel çözmek, bütün veritabanını eski modele mahkûm etmekten iyidir.

Tek kolonlu statistics'in korelasyonu bildiğini sanmak. İki koşulun birlikte seçiciliğini hiçbir histogram tutmaz. Kritik bir sorguda yanlış tahmin görüyorsan veriyi tanıyan sen olduğun için filtered statistics, hint ya da sorguyu yeniden yazmak senin işin.

Küçük Bir Deneme

★☆☆ IX_Orders_CustomerId histogramında müşteri 20.000'in düştüğü adımı bul ve tahmini kâğıt üzerinde hesapla. Sonra SET STATISTICS PROFILE ON ile karşılaştır.

★★☆ Status kolonundaki otomatik statistics'i UPDATE STATISTICS dbo.Orders _WA_Sys_... WITH FULLSCAN ile güncelle. Pending tahmini 4.680'den neye değişti? Örnekleme oranını PERSIST_SAMPLE_PERCENT ile sabitlemeyi dene.

★★★ WHERE Country = 'TR' AND City = N'Istanbul' için doğru tahmini, hint kullanmadan, bir filtered statistics (CREATE STATISTICS ... WHERE Country = 'TR') ile elde edebilir misin? Sonucu ve sınırlarını açıkla.

Tahminlerin nereden geldiğini ve nerede yanıldığını gördük. Bir sonraki yazıda bu tahminlerin sonuçlandığı yere bakıyoruz: execution plan'ı okumak, estimated ve actual plan farkı, operator'ler ve üç join algoritması.