Geliştiriciler için SQL Veritabanı Performans Ayarı

SQL performans ayarı, özellikle en küçük değişikliğin bile performans üzerinde dramatik (olumlu veya olumsuz) bir etkisi olabileceği büyük ölçekli verilerle çalışırken inanılmaz derecede zor bir görev olabilir.

Orta ölçekli ve büyük şirketlerde, çoğu SQL performans ayarı bir Veritabanı Yöneticisi (DBA) tarafından gerçekleştirilecektir. Ama inanın bana, DBA benzeri görevleri yerine getirmesi gereken çok sayıda geliştirici var. Ayrıca, DBA'ları olduğunu gördüğüm birçok şirkette, genellikle geliştiricilerle iyi çalışmak için mücadele ediyorlar - pozisyonlar sadece farklı problem çözme modları gerektiriyor ve bu da iş arkadaşları arasında anlaşmazlığa yol açabiliyor.

Bunun üzerine, kurumsal yapı da bir rol oynayabilir. DBA ekibinin tüm veritabanlarıyla birlikte 10. katta, geliştiriciler 15. katta veya hatta tamamen ayrı bir raporlama yapısı altında farklı bir binada olduğunu varsayalım - bu koşullar altında sorunsuz bir şekilde birlikte çalışmak kesinlikle zor.

Bu yazıda iki şeyi başarmak istiyorum:

1. Geliştiricilere bazı geliştirici tarafı SQL performans ayarlama teknikleri sağlayın.
2. Geliştiricilerin ve DBA'ların birlikte nasıl etkili bir şekilde çalışabileceğini açıklayın.

SQL Performans Ayarı (Kod Tabanında): Dizinler

Veritabanlarında tamamen yeniyseniz ve hatta kendinize “SQL performans ayarı nedir?” diye soruyorsanız, geliştirme sırasında genellikle ihmal edilen SQL veritabanınızı ayarlamak için indekslemenin etkili bir yol olduğunu bilmelisiniz. Temel olarak, bir dizin, hızlı rastgele aramalar ve sıralı kayıtlara verimli erişim sağlayarak bir veritabanı tablosundaki veri alma işlemlerinin hızını artıran bir veri yapısıdır. Bu, bir dizin oluşturduğunuzda, satırlarınızı eskisinden daha hızlı seçebileceğiniz veya sıralayabileceğiniz anlamına gelir.

Dizinler ayrıca, başka hiçbir sütunun aynı değerlere sahip olmadığını garanti edecek bir birincil anahtar veya benzersiz dizin tanımlamak için de kullanılır. Tabii ki, veritabanı indeksleme, bu kısa açıklamayla hakkını veremediğim çok ilginç bir konu (ama burada daha ayrıntılı bir yazı var).

Dizinler konusunda yeniyseniz, sorgularınızı yapılandırırken bu diyagramı kullanmanızı öneririm:

Temel olarak amaç, ana arama ve sıralama sütunlarını indekslemektir.

Tablolarınız INSERT , UPDATE ve DELETE tarafından sürekli olarak kırılıyorsa, dizin oluştururken dikkatli olmanız gerektiğini unutmayın; bu işlemlerden sonra tüm dizinlerin değiştirilmesi gerektiğinden performansın düşmesine neden olabilirsiniz.

Ayrıca, DBA'lar, ekleme işlemini hızlandırmak için milyonlarca satırdan oluşan toplu eklemeler gerçekleştirmeden önce SQL dizinlerini sık sık düşürür. Toplu iş eklendikten sonra dizinleri yeniden oluştururlar. Ancak, bırakılan dizinlerin o tabloda çalışan her sorguyu etkileyeceğini unutmayın; bu nedenle bu yaklaşım yalnızca tek, büyük bir ekleme ile çalışırken önerilir.

SQL Ayarı: SQL Server'da Yürütme Planları

Bu arada: SQL Server'daki Yürütme Planı aracı, dizin oluşturmak için yararlı olabilir.

Ana işlevi, SQL Server sorgu iyileştiricisi tarafından seçilen veri alma yöntemlerini grafik olarak görüntülemektir. Onları daha önce hiç görmediyseniz, ayrıntılı bir izlenecek yol var.

Yürütme planını almak için (SQL Server Management Studio'da), sorgunuzu çalıştırmadan önce "Gerçek Yürütme Planını Dahil Et" (CTRL + M) öğesine tıklamanız yeterlidir.

Daha sonra “Yürütme Planı” adlı üçüncü bir sekme görünecektir. Tespit edilen eksik bir dizin görebilirsiniz. Oluşturmak için, yürütme planına sağ tıklayın ve “Eksik Dizin Ayrıntıları…” öğesini seçin. Bu kadar basit!

( Yakınlaştırmak için tıklayın )

SQL Ayarı: Kodlama Döngülerinden Kaçının

1000 sorgunun veritabanınızı sırayla etkilediği bir senaryo hayal edin. Gibi bir şey:

 for (int i = 0; i < 1000; i++) { SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES..."); cmd.ExecuteNonQuery(); }

Kodunuzda bu tür döngülerden kaçınmalısınız. Örneğin, birden çok satır ve değer içeren benzersiz bir INSERT veya UPDATE ifadesi kullanarak yukarıdaki parçacığı dönüştürebiliriz:

 INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)

WHERE yan tümcenizin, mevcut değerle eşleşiyorsa saklanan değeri güncellemekten kaçındığından emin olun. Böyle önemsiz bir optimizasyon, binlerce yerine yalnızca yüzlerce satırı güncelleyerek SQL sorgu performansını önemli ölçüde artırabilir. Örneğin:

 UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A <> @VALUE -- VALIDATION

SQL Ayarı: İlişkili SQL Alt Sorgularından Kaçının

İlişkili bir alt sorgu, üst sorgudaki değerleri kullanan bir alt sorgudur. Bu tür SQL sorgusu, dış sorgu tarafından döndürülen her satır için bir kez olmak üzere satır satır çalıştırma eğilimindedir ve bu nedenle SQL sorgusu performansını düşürür. Yeni SQL geliştiricileri genellikle sorgularını bu şekilde yapılandırırken yakalanırlar çünkü bu genellikle kolay yoldur.

İşte ilişkili bir alt sorgu örneği:

 SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

Özellikle sorun, dış sorgu ( SELECT c.Name… ) tarafından döndürülen her satır için iç sorgunun ( SELECT CompanyName… ) çalıştırılmasıdır. Ama neden dış sorgu tarafından işlenen her satır için Company tekrar tekrar gözden geçirelim?

Daha verimli bir SQL performans ayarlama tekniği, ilişkili alt sorguyu bir birleştirme olarak yeniden düzenlemek olacaktır:

 SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

Bu durumda, başlangıçta Company tablosunun üzerinden sadece bir kez geçiyoruz ve Customer tablosuna KATILIYORUZ. O andan itibaren ihtiyacımız olan değerleri ( co.CompanyName ) daha verimli bir şekilde seçebiliriz.

SQL Ayarı: Az Seçin

En sevdiğim SQL optimizasyon ipuçlarından biri SELECT * ! Bunun yerine, ihtiyacınız olan belirli sütunları tek tek eklemelisiniz. Yine, bu kulağa basit geliyor, ancak bu hatayı her yerde görüyorum. Yüzlerce sütun ve milyonlarca satır içeren bir tablo düşünün; uygulamanızın gerçekten yalnızca birkaç sütuna ihtiyacı varsa, tüm verileri sorgulamanın bir anlamı yoktur. Büyük bir kaynak israfı. ( Daha fazla sorun için buraya bakın. )

Örneğin:

 SELECT * FROM Employees

vs.

 SELECT FirstName, City, Country FROM Employees

Her sütuna gerçekten ihtiyacınız varsa, her sütunu açıkça listeleyin. Bu bir kural değil, gelecekteki sistem hatalarını ve ek SQL performans ayarlamasını önlemenin bir yolu. Örneğin, bir INSERT... SELECT... kullanıyorsanız ve kaynak tablo yeni bir sütunun eklenmesiyle değiştiyse, hedef tablo bu sütuna ihtiyaç duymasa bile sorunlarla karşılaşabilirsiniz, Örneğin:

 INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

SQL Server'dan bu tür bir hatadan kaçınmak için her sütunu ayrı ayrı bildirmelisiniz:

 INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees

Ancak, SELECT * kullanımının uygun olabileceği bazı durumlar olduğunu unutmayın. Örneğin, bizi bir sonraki konumuza götüren geçici tablolarla.

SQL Ayarı: Geçici Tabloların Akıllıca Kullanımı (#Temp)

Geçici tablolar genellikle bir sorgunun karmaşıklığını artırır. Kodunuz basit ve anlaşılır bir şekilde yazılabilirse, geçici tablolardan kaçınmanızı öneririm.

Ancak, tek bir sorgu ile işlenemeyen bazı veri işlemeleri olan bir saklı yordamınız varsa, nihai bir sonuç oluşturmanıza yardımcı olması için geçici tabloları aracı olarak kullanabilirsiniz.

Büyük bir tabloya katılmanız gerektiğinde ve söz konusu tablo üzerinde koşullar varken, verilerinizi geçici bir tabloya aktararak ve ardından bunun üzerinde birleştirme yaparak veritabanı performansını artırabilirsiniz. Geçici tablonuz orijinal (büyük) tablodan daha az satıra sahip olacak, bu nedenle birleştirme daha hızlı bitecek!

Karar her zaman açık değildir, ancak bu örnek size geçici tabloları kullanmak isteyebileceğiniz durumlar için bir fikir verecektir:

Milyonlarca kayıt içeren bir müşteri tablosu hayal edin. Belirli bir bölgede birleştirme yapmanız gerekir. Bunu bir SELECT INTO ifadesi kullanarak ve ardından geçici tabloya katılarak başarabilirsiniz:

 SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

( Not: Bazı SQL geliştiricileri, bu komutun tempdb veritabanını kilitlediğini ve diğer kullanıcıların geçici tablolar oluşturmasına izin vermediğini söyleyerek, geçici tablolar oluşturmak için SELECT INTO kullanmaktan da kaçınır. Neyse ki, bu 7.0 ve sonraki sürümlerde düzeltilmiştir .)

Geçici tablolara alternatif olarak, tablo olarak bir alt sorgu kullanmayı düşünebilirsiniz:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Fakat bekle! Bu ikinci sorguyla ilgili bir sorun var. Yukarıda açıklandığı gibi, yalnızca ihtiyacımız olan sütunları alt sorgumuza dahil etmeliyiz (yani, SELECT * kullanmamalıyız). Bunu dikkate alarak:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Bu SQL parçacıklarının tümü aynı verileri döndürür. Ancak geçici tablolarla, örneğin, performansı artırmak için geçici tabloda bir dizin oluşturabiliriz. Burada geçici tablolar ve alt sorgular arasındaki farklar hakkında iyi bir tartışma var.

Son olarak, geçici tablonuzla işiniz bittiğinde, otomatik olarak silinmesini beklemek yerine (veritabanıyla bağlantınız sonlandırıldığında olacağı gibi) tempdb kaynaklarını temizlemek için onu silin:

 DROP TABLE #temp

SQL Ayarı: “Kaydım Var mı?”

Bu SQL optimizasyon tekniği, EXISTS() kullanımıyla ilgilidir. Bir kaydın olup olmadığını kontrol etmek istiyorsanız, COUNT() EXISTS() kullanın. COUNT() tüm tabloyu tararken, durumunuza uyan tüm girişleri sayarken, EXISTS() ihtiyaç duyduğu sonucu görür görmez çıkacaktır. Bu size daha iyi performans ve daha net kod verecektir.

 IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'

vs.

 IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'

SQL Server 2016 ile SQL Performans Ayarı

SQL Server 2016 ile çalışan DBA'lar muhtemelen farkında olduğundan, sürüm, varsayılanlar ve uyumluluk yönetiminde önemli bir değişime işaret etti. Ana sürüm olarak, elbette, yeni sorgu optimizasyonları ile birlikte gelir, ancak bunların kullanılıp kullanılmadığının kontrolü artık sys.databases.compatibility_level aracılığıyla kolaylaştırılmıştır.

SQL Performans Ayarı (Ofiste)

SQL veritabanı yöneticileri (DBA'lar) ve geliştiriciler genellikle verilerle ve verilerle ilgili olmayan sorunlar üzerinde çatışır. Deneyimlerime dayanarak, nasıl geçinip birlikte etkili bir şekilde çalışılacağına dair bazı ipuçları (her iki taraf için de) burada.

Cıvıldamak

Geliştiriciler için Veritabanı Optimizasyonu:

1. Uygulamanız aniden çalışmayı durdurursa, bu bir veritabanı sorunu olmayabilir. Örneğin, bir ağ sorununuz olabilir. Bir DBA'yı suçlamadan önce biraz araştırın!

2. Bir ninja SQL veri modelleyicisi olsanız bile, bir DBA'dan ilişkisel diyagramınızda size yardım etmesini isteyin. Paylaşacakları ve sunacakları çok şey var.

3. DBA'lar hızlı değişiklikleri sevmezler. Bu doğaldır: Veritabanını bir bütün olarak analiz etmeleri ve herhangi bir değişikliğin etkisini tüm açılardan incelemeleri gerekir. Bir sütundaki basit bir değişikliğin uygulanması bir hafta sürebilir - ancak bunun nedeni, bir hatanın şirket için büyük kayıplar olarak gerçekleşebilmesidir. Sabırlı ol!

4. SQL DBA'lardan üretim ortamında veri değişiklikleri yapmalarını istemeyin. Üretim veritabanına erişmek istiyorsanız, tüm değişikliklerinizden sorumlu olmalısınız.

SQL Server DBA'ları için Veritabanı Optimizasyonu:

1. İnsanların size veritabanı hakkında soru sormasından hoşlanmıyorsanız, onlara gerçek zamanlı bir durum paneli verin. Geliştiriciler her zaman bir veritabanının durumundan şüphelenir ve böyle bir panel herkesin zaman ve enerjiden tasarruf etmesini sağlayabilir.

2. Test/kalite güvencesi ortamında geliştiricilere yardımcı olun. Gerçek dünya verileri üzerinde basit testlerle bir üretim sunucusunun simülasyonunu kolaylaştırın. Bu hem kendiniz hem de başkaları için önemli bir zaman tasarrufu sağlayacaktır.

3. Geliştiriciler, tüm günlerini sık sık değişen iş mantığına sahip sistemlerde geçirir. Bu dünyanın daha esnek olduğunu anlamaya çalışın ve kritik bir anda bazı kuralları çiğneyin.

4. SQL veritabanları gelişiyor. Verilerinizi yeni bir sürüme geçirmeniz gereken gün gelecek. Geliştiriciler, her yeni sürümde önemli yeni işlevlere güveniyor. Değişikliklerini kabul etmeyi reddetmek yerine önceden plan yapın ve geçişe hazır olun.