Index rebuild with MAXDOP=x

Farklı maxdop varyasyonlarıyla index rebuild process’ine bir göz atalım. Index bakımlarında MAXDOP kullanımının süreyi etkileyebileceğini aşağı yukarı tahmin edebiliriz çünkü paralel kullanımlar genel olarak CPU tüketimi ile birlikte veritabanı üzerindeki işlemlerin hızlanmasını sağlamaktadır. Ancak hangi MAXDOP değeri bize ne fayda sağlayacak? Ya da sağlamayacak.

Demo

Öncelikle CPU tüketimi ve REBUILD süresine göz atalım.

CPU grafiği
Zaman grafiği (ms)

Grafiklerden de görüleceği üzere parallelism kapalı olduğunda (MAXDOP=1) REBUILD işlemlerimiz uzuyor ve CPU tüketimi de buna bağlı olarak artıyor. Diğer değerler içinde en optimum kaynak tüketimini MAXDOP=8 ele almış gibi duruyor. Ancak bu durum makine kaynaklarına bağlı olarak değişebilmektedir.

Production ortamlarına uygulamadan önce bir production replikası üzerinde test etmek önerilir.

Şimdi daha farklı bir taraftan bakıp parallelism değerlerine göre REBUILD edilen index’lerin fragmante oranlarına bakalım.

MAXDOP=1
MAXDOP=4
MAXDOP=8
MAXDOP=16
MAXDOP=32

Parallelism değerleriyle oynadığımda REBUILD ettiğim index’lerde bile fragmante olduğunu görüyoruz. Page’lerin thread’ler arasında dağılımından kaynaklı oluşan bu durum, daha büyük tablolar için daha fazla fragmante değerine sahip index’ler elde etmeye sebep oluyor.

MAXDOP kullanımının belirli bir best practice’i olmadığından yukarıdaki değerlere ve kendi ortamlarınızadaki değerlere göre bir MAXDOP belirlemek daha sağlıklı olacaktır.

My favorite is MAXDOP=1 

SQL Read-ahead reads nedir? #QueryOptimization

Database engine’in desteklediği optimizasyon araçlarından biri olan read-ahead reads, önden okuma olarak tanımlanmaktadır. Bir sorguyu çalıştırmak için gereken veri ve dizin sayfalarını tahmin eder ve sayfaları sorgu tarafından kullanılmadan önce buffer cache’e getirir. SQL Server fiziksel okumalar sırasında 8KB’lik page’ler yada extend page’ler okur ancak diskten daha fazla data okumak gerektiği sırada read-ahead reads devreye girerek bu yükü sırtlar.

Demo

Fazla page okuyacak bir sorgu yazıyorum, read-ahead reads ve diğer istatistik verilerini görebilmek için STATISTICS IO ON konumuna getiriyorum.

Ara adım : Buffer cache temizlemek için kullandığımız DBCC komutunu çalıştırıyorum bu sayede read-ahead reads‘ın devreye girmesini umuyorum.
Not : Canlı ortamlarda çalıştırılması, şirket çalışanlarının umarsızca “veritabanı yavaşladı” diyerek sağa sola koşuşturmasına sebep olabilir.

DBCC DROPCLEANBUFFERS

İstatistik çıktımız aşağıdaki gibi;

Table 'Posts'. Scan count 1, logical reads 12042, physical reads 1, page server reads 0, read-ahead reads 12048, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Eğer buffer cache temizlemeden tekrar çalıştırırsak bu mekanizmanın devreye girmediğini görebiliriz.

Table 'Posts'. Scan count 1, logical reads 12042, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Key Lookup nedir? #QueryOptimization

Key Lookup, query optimize ederken execution planlarda sık sık karşılaştığımız operatörlerden biridir.

Ne zaman oluşur?

SQL, bir sorgunun tamamını veya bir kısmını karşılamak için bir non-clustered index kullandığında oluşur, ancak bu index sorguya ait tüm bilgileri içermez bu sebeple eksik bilgilere ulaşabilmek için clustered index üzerinde arama işlemi yapılır.

İşte bir örnek;

SELECT BusinessEntityID,PersonType,FirstName,LastName
FROM Person.Person
WHERE FirstName=N’Roberto’

Key Lookup’a sebep olacak bir index atıyorum,

CREATE NONCLUSTERED INDEX [IX_Person_FirstName]
ON [Person].[Person] ([FirstName])
WITH (FILLFACTOR = 90)

Execution plan’da da görüleceği üzere, key loookup her zaman Nested Loops Join kullanmaktadır.

Çünkü her bir row için clustered index’e gidip nonclustered index’te olmayan bilgileri alması gerekiyor.

Peki bunu nasıl mı yapıyor?

Bunu yapabilmek için aslında create edilen bütün tablolarda var olan bir kolondan faydalanıyor. İşte o kolon ;

SELECT BusinessEntityID,PersonType,FirstName,LastName,%%lockres%%
FROM Person.Person
WHERE FirstName=N’Roberto’

Sorgunuza bu kolonu eklediğinizde clustered index’e sahip tablolarda sonuç kümesinde clustered index’in key’ini göreceksiniz. İşte Key Lookup operatörü bu bilgiden yararlanarak (her bir satır için clustered index’e giderek) diğer kolonlardaki bilgileri toplayabiliyor.

Buna göre, Number of executions (key lookup olduğunda) bilgisi bize aynı zamanda dönen row sayısı bilgisini de vermektedir. Bu durum parameter sniffing olan sorgularda acı verir.

Key Lookup operatörünü ortadan kaldırmak için Output List bölümündeki sütunları var olan index’imin include bölümüne eklemem yeterli olacaktır. (Tabii her zaman bu kadar basit olmayabilir.)

CREATE NONCLUSTERED INDEX [IX_Person_FirstName]
ON [Person].[Person]([FirstName])
INCLUDE ( [PersonType], [LastName]) WITH (FILLFACTOR = 90)

Index’imi düzenledikten sonra Key Lookup operatörü kalktı sadece non-clustered index üzerinden tüm bilgiler karşılanabildiği için bu index’i seek ederek, sonuçları döndürdü.

Sorgunun key lookup kullanırken ve düzelttikten sonraki istatistiksel okuma bilgilerini buraya koyuyorum.

set statistics io on

Bu basit bir örnekti ama bazen işler karmaşıklaşabilir.

— select * kullanımında (bunu sadece * olarak düşünmeyin, entity framework sorguları * yerine tüm sütun adlarını yazarak gönderiyor, eşdeğer sorgular diyebiliriz.)

Böyle durumlarda index’e bütün sütunları eklemek istemezsiniz bunun yerine öncelikle daha az sütun seçip seçemeyeceğinize karar vermek için sorgularınızı inceleyin.

Key Lookup her zaman kötü bir operatör değildir, bazı durumlarda olduğu şekilde bırakmanız gerekebilir. Bu sizin sorgunuza, index’lerinize ve tablo yapınıza göre değişiklik gösterecektir. Index’lerinizin include kısmını sütunlarla doldurmadan önce bunun maliyetini hesaplamanızı tavsiye ederim.

Stored Procedure Prefix : sp_ vs diğerleri #QueryOptimization

Hız artık her şeyimiz oldu. Daha hızlı alışveriş yapabilmek için kredi kartlarımızdaki temassız özelliğini kullandığımız, alışveriş sitelerine sonraki seferde zaman kaybetmemek için kaydettiğimiz bilgilerimizin sebebi hız.

Hızın bu kadar önemli olduğu bir dünyada SQL Server’ımızın daha hızlı olması için yapabileceğimiz bütün geliştirmeleri uygulamaya çalışıyoruz.

Peki procedure’lerimizde “sp_” prefix’i ya da başka bir prefix kullanmanın bize nasıl bir yararı veya zararı olabilir.

Şöyle ki;

1 — SQL Server system procedure’lerini “sp_” prefix’i kullanarak oluşturuyor. Bu sebeple “sp_” ile başlayan bir procedure çalıştırdığınızda öncelikle arayacağı yer system database’leri oluyor.

2 — “sp_” prefix’i kullanıldığında procedure düzenleme işlemleri sırasında yanlışlıkla system procedure’lerine zarar verebilirsiniz. İşte Microsoft dökümanı.

Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. Using the prefix can cause application code to break if there is a system procedure with the same name.

3- User database’leri üzerinde, system procedure’leriyle aynı isimde bir procedure oluşturup aşağıdaki şekilde çalıştırsanız bile, SQL Engine öncelikle master database’ine bakacağı için oradaki procedure’ü çalıştıracaktır.

Demolarımıza geçelim…

Şimdi system procedure’lerinden biriyle aynı isimde kendi database’im üzerinde bir procedure oluşturuyor ve çalıştırıyorum.

EXEC StackOverflow2010.dbo.sp_helpdb

Görüldüğü gibi benim procedure’de belirttiğim çıktıyı vermeyip, system database’leri altında olan aynı isimdeki SQL Server’a ait procedure’ü çalıştırarak sonuç verdi.

Şimdi başta bahsettiğimiz gibi “sp_” prefix’i olan ve olmayan aynı iki procedure arasındaki performans değerlerine bakalım.

Procedure içerikleri;

CREATE PROC [dbo].[sp_Posts] AS
BEGIN   
     SELECT top 10 * FROM Posts p
END

-----------------------------------

CREATE PROC [dbo].[usp_Posts] AS
BEGIN
     SELECT top 10 * FROM Posts p
END

Yine SQLQueryStress aracılığıyla procedure’lerimi 2 thread üzerinde 100’er kez çalıştırıyorum ve aynı IO işlemini yaptıkları halde CPU ve Elapsed Time üzerindeki farklılıkları elde ediyorum.

EXEC sp_Posts
  • Okunan page sayısı : 22.0000
  • Execute süresi : 0.0230 ms
  • CPU tüketimi : 0.0048 ms
EXEC usp_Posts
  • Okunan page sayısı : 22.0000
  • Execute süresi : 0.0134 ms
  • CPU tüketimi : 0.0035 ms

Değerler ufak gibi gözüküyor ama binlerce, milyonlarca kez çalışacak procedure’ler için önemli bir kazanç olacaktır.