Günümüzde şirketler, aynı donanım kaynaklarını kullanarak daha fazla performans elde etmek için IT kaynaklarını optimize ediyorlar. Paralelinde, ihtiyaçlar ve müşteri talepleri de hızla artmaktadır, gerçek zamanlı datanın ihtiyacı her geçen gün artış göstermektedir.
Son zamanlarda birçok şirket, No-SQL çözümlerine yönelmesindeki en büyük sebeplerden birisi de yüksek performans ile write transaction yapabilmek istemeleridir. İlişkisel veritabanı sistemlerindeki (RDBMS) veri tutarlılığını sağlamak için kullanılan kontrollerin yavaşlığından dolayı, daha yüksek performans sağlayan No-SQL çözümleri ile eş zamanlı dataların işlenmesini sağlıyorlar. Veri bütünlüğü ve doğruluğunu ise data mining ve analysis çözümleri ile yaparak, birden fazla farklı sistemi kullanmak zorunda kalıyorlar. Kısacası, No-SQL, BigData, real-time analytics gibi farklı konseptleri şu ana kadar tek bir ürün çatısı altında sunan olmadı diyebiliriz.
Microsoft SQL Server 2019 ile bu ayrık dağıtık yapıyı tek çatı altında toplamış olduğunu söyleyebiliriz. Microsoft’un rakipleri diyebileceğimiz firmalar da, BigData ve NoSQL gibi çözümlerin olmasından ötürü, SQL Server 2019 ile tüm BigData ürünlerini tek çatı altında toplandığını görüyoruz. Bu makalede sizlere neden SQL Server 2019’a geçiş yapmalıyız? Gerek var mı? SQL Server 2019’un yenilikleri nelerdir? SQL Server 2019 beklediğiniz performans sorunlarına çözüm olabilecek mi? Sorularının yanıtlarını bulmanızda yardımcı olmaya çalışacağım.
SQL Server 2019 ile gelen Performans ve Optimizasyon yeni özelliklerini, bu makale de sizlere aktarmaya çalışacağım, uzun süredir Data Platform firmasının kurucu ortaklığını sürdürdüğümden dolayı makalelere ara vermiştim. Bu seri de sizlere, SQL Server 2019 sürümüne gelen yeni özellikleri kendi yapınıza nasıl kolayca entegre edebileceğinizi ve nasıl uygulamaya geçebileceğinizi göreceğiniz umuyorum.
Microsoft SQL Server 2019, Transaction Processing Performance Council (TPC) testlerinde On-Line Transaction Processing ve Decision Support benchmark testlerinde birinci database sistemi olarak testleri geçmektedir. SQL Server 2017’den önce Oracle bu konuda birinciliği kimseye kaptırmıyordu.
SQL Server 2019, en zorlu computing ortamlarında kullanılabilecek seviye gelmiş bir veritabanı sistemi olarak yayınlanmıştır. SQL Server 2017’deki performans başarısı, SQL Server 2019 daki BigData konseptine uyarlandığını görmekteyiz. In-Memory tekniği kullanarak gerçek zamanlı datanın işlenmesinde büyük bir performans sağladığını kanıtlamıştır.
Bu yeni sürümde, birçok zaman zorlanılan ve yapılması zor olan database operasyonlarının da kolaylıkla yapılabileceği, query tuninglerin otomatik olarak düzeltileceği bir yapıya geçilmiştir. Query store gibi özellikler ile performans sorunlarının hızlı bir şekilde tanımlanması sağlanmıştır.
Intelligent Query Processing (IQP) akıllı sorgu işleme diye çevirebildiğimiz bu yapıda, sorgu performansına manuel olarak tuning yapılmasına ihtiyaç duyulmadan, otomatik bir şekilde ihtiyaç duyulan en ideal query planının seçilmesine kadar, performans konusunda bizlere fayda sağlamaktadır.
Makale biraz uzun olacağından, bölümlere hakim olmanız için, makale bölümlerini inceleyebilirsiniz;
1 – Hybrid Transactional ve Analytical Processing (HTAP)
2- Clustered Columnstore Indexes
3 – Disk Tabloları mı? In-Memory Tablolar mı?
4 – In-Memory OLTP
5 – Natively Complied Stored Procedures
6 -TempdDB’deki Geliştirmeler
7 – Intelligent Query Processing
7.1. Batch Mode Adaptive Joins
7.2. Approximate query processing
7.3. Batch mode on rowstore
7.4. Table Variable Deferred Compilation
7.5. Scalar UDF Inlining
7.6. Memory Grant Feedback – Batch Mode
7.7. Memory Grant Feedback – Row Mode
8 – Hybrid Buffer Pool
9 – Query Store
10 – Automatic Tuning
10.1 Automatic Plan Correction
10.2 Automatic Index Management
1 – Hybrid Transactional ve Analytical Processing (HTAP)
SQL Server’da çoğu terimin çevirisi zor olduğunda İngilizcesini kullanmayı tercih ediyoruz, kısaltması HTAP olan hybrid yapıda bir transactional datanın, analytics işlemlerden geçerek, data warehouse operasyonlarına girmeden, çok hızlı veri işleme ve analitiğini yapılması da diyebiliriz.
Birçok yardımcı toolun bir araya gelmesi ile oluşan HTAP yapılarında, geleneksel mimariye göre göre daha performans sağlandığını görmekteyiz. Geleneksel eski mimarilerde, data warehouse gelen datanın raporlanması ve bunun analysis serverlar ile beslenmesi olarak düşünebiliriz. Bu eski yapıda OLTP sistemlerinden gelen datanın, ETL süreçleri ile data warehouse (DWH) yapısına aktarılması sürecinde birçok performans sorunu ve yönetimsel zorluklar ile karşılaşıyorduk.
Real-time datanın işlenmesinde geleneksel mimarinin artık yavaş kaldığı ve birçok data firmasının HTAP sistemlere geçtiğini takip etmekteyiz. SQL Server 2019’un yeni özellikleri de HTAP yapısını desteklemektedir. Bu yapıya örnek olarak, memory-optimized tablolar, natively complied stored procedureler ve Clustered Columnstore Indexleri örnek gösterebiliriz.
SQL Server 2019 makale serisinin bu bölümünde, HTAP kavramını kapsayan konuları ele alacağız. HTAP kavramı ile ilgili daha çok bilgiye Wikipedia’da linkinden ulaşabilirsiniz.
https://en.wikipedia.org/wiki/Hybrid_transactional/analytical_processing
2 – Clustered Columnstore Indexes
Clustered Columnstore Indexler, real-time analytics yani gerçek zamanlı analitik operasyonlarında, ciddi anlamdan performans sağlayan bir yapıya sahiptirler. Row table olarak bildiğimiz, geleneksel yapıdaki tablolara göre, data sıkıştırma da ve dataya erişiminde yüksek oranda erişimi sağlamaktadır.
Columnstore mimarisinde, row’lar (satırlar) ve column’lar (kolonlar) vardır, fakat data column formatın da tutulmaktadır. Rowgroup denilen satır kümeleri, columnstore formatın da 1 milyon satır olarak kümelenmektedir.
Bir rowgroup da, sütun olarak depolanan optimum sayıda satır vardır ve az sayıda olan row’lara sahip olan tablolarda, In-Memory operasyonlarını gerçekleştirme de yetersiz kalabilirler. Aslında bize şunu demek istiyorlar; az sayıda satırları olan tablolar için Columnstore ve In-Memory kullanmanın pek de anlamı yok.
Her satır, her biri compress edilmiş yani sıkıştırılmış satırdaki bir sütunu temsil eden sütun segmentlerinden oluşmaktadır.
İyice kafalar karıştığına göre aşağıdaki grafikte non-clustered columnstore index yapısına daha hakim olabilirsiniz.
Clustered columnstore indekslerde, fiziksel olarak disk üzerinde tablo segmentleri olarak tutulan indekslerdir. Performansın sürekliliğini sağlama, data fragmantaasyonunu azaltmak için, row table’larda önerilen clustered indeksler, columnstore içinde önerilmektedir. Columnstore ulaşabildiği en yüksek row’a gelen kayıttan sonra, kendini tekrardan yenileyerek yeni bir grup açarak performansı sağlamayı sürdürmeye devam eder. Bu aşamada clustered columstore indeks ile fiziksel indekslemenin performansa katkısı yüksektir.
Columnstore da datanın tutulması deltagrouplar diye adlandırılan gruplama ile yapılır. Row table’lar da bunun ismi rowgroup idi. Birde fazla delta rowgrouplar olabilir, delta rowgrouplara da deltastore denilmektedir. Bir tabloya 102.400 satır data girilmesi deltastore’u oluşturur,columnstore’a bir grup halinde verinin işlenmesini sağlar.
Columnstore’a data yüklenmesi ile ilgili detaylar için;
Aşağıdaki grafik de, clustered columnstore index yapısındaki bir tabloya bulk data yüklemesini görebilirsiniz;
Bu columnstore indeksleri nerede kullanacağız, esas soru bu olsa gerek. In-Memory table veya memory-optimized tablolar kullandığımızda ve bu In-Memory tablolar da transactional data üzerinde real-time analytics yapıldığında bizim işimize yarayacak en büyük özellik columnstore indekslerdir. OLTP sistemlerinde kullanılmasının çok da fazla bir anlamı olmayabilir. Daha fazla performans için OLTP sistemlerinizde columstore’a geçmeyi hemen düşünmeyin.
3 – Disk Tabloları mı? In-Memory Tablolar mı?
Disk-based tablolar ile memory-optimized tablolar arasında birkaç fark olduğunu söyleyebiliriz. Farklardan ilki, disk-based tablolar, rowlar yani satırlar üzerinde datayı 8k’lık page dediğimiz yapıda saklarlar ve bir page yalnızca tek bir tablodaki satırları depolar.
Memory-optimized tablolar da ise, rowlar ayrı ayrı depolanır, satırları içeren bir data file’ı birden çok memory-optimized tablolada tutabilir. Disk-based tablolardaki geleneksel mimarinin çok farklı bir yapısı mevcuttur.
Disk-based tablolardaki indeksler, aynı data rowlarda olduğu gibi pageler de depolanır. Datalardaki DML dediğimiz, Insert/Update/Delete operasyonları loglandığı gibi, indeksler de bu şekilde loglanmaktadır. In-Memory tablolarda ise, indekslerin tanımları tutulur, SQL Server yeniden başladığı gibi durumlarda, bu indeksler tekrardan In-memory tabloya yüklenmektedir. Ayrıca In-Memory tablolardaki indeksler, disk-table indeksler gibi loglanmazlar.
Data operasyonlarnın çok daha farklı olduğunu söyleyebiliriz. Memory-optimized tablolarda, bütün data operasyonları memory üzerinde tamamlanır. In-memory tabloda oluşan bir güncellemede log kayıtları anında oluşturulmuş olur. In-memory tablosunda oluşturulan bir log kaydı, eş zamanlı olarak disk üzerine de yazılmaktadır. Disk-based tablolarda ise, data operasyonlarında update için key columnlarına gerek olmaz iken, insert ve delete için key-columnlarına ihtiyaç vardır. Ayrıca data operasyonu tamamlandıktan sonra, disk üzerine yazılması gerçekleşir. Buna datanın commit olması da diyebiliriz.
Disk-based tablolarda, page’ler fragmented olabilir, yani page’ler de bozulmalar gerçekleşmesi doğaldır. Data üzerinde değişiklikler olduğunda, pagelerin kısmen dolması veya ardışık olarak dizilimler olmaktadır, bu iki sebep de performans açısından olumsuz değerlerdir. Memory-optimized tablolarda ise, rowların depolanmasında fragmantasyonu ortadan kaldırmaktadır, fakat insert, delete ve update operasyonlarında rowları sıkıştırabilecek şekilde yani compact bir durumda bırakır. Arka planda, bu sıkıştırma operasyonunu kendisi yürütür, böylelikle çok daha performanslı tablolar haline gelir.
Disk-based tablolar ile Memory-optimized tabloların farklarının detayı için aşağıdaki Microsof Doc sayfasını incelemeniz de fayda var;
4 – In-Memory OLTP
İlk olarak SQL Server 2014’te karşımıza çıkan In-Memory kavramı, OLTP olarak adlandırdığımız, on-line transaction processing, çevrim içi işlem işleme gibi bir çevirisi var, genelde bu kavramları İngilizce olarak kullanmak daha doğru oluyor. In-Memory OLTP dediğimizde, aklımıza ilk gelen, transactionların işlenmesinde daha iyi bir performans sağlamak olduğunu söyleyebiliriz. Sadece on-prem SQL Server’lar da değil, Azure SQL Database’lerinde ve Azure Managed Instances’da da In-Memory desteğini görmek mümkündür. In-Memory’nin uygulama tarafında 2-30 kat arası bir performans sağladığını söylemek daha gerçekçi olacaktır. İlk tanıtıldığında 30x performans artışı olduğunun güzel reklamı yapılmasına rağmen, yaptığımız testlerde, 2x ila 30x arası demek durumunda kalıyoruz, 2 kat performans bile güzel bir değer olduğunu da belirtmek isterim.
In-Memory’nin performansı nereden geliyor? Memory’den çalışan tablo olunca haliyle data memory üzerinde tutuluyor. Disk-based tablolardan en büyük farkı ise, transactionlar çalıştığında lock ve latch contention dediğimiz beklemelerin olmaması diyebiliriz. Lock, bildiğimiz transactionların birbirini kitlemesi, latch ise disk veya diğer donanımsal kaynaklardan dolayı transactionlar beklemesi olarak özetleyebiliriz. Hem memory’nin getirdiği hız, hem de In-memory tablolarının mimarisinin farklı olması, bize bu kadar performansı da beraberinde getiriyor.
In-Memory table’ları oluşturduğunuzda, hemen kullanmaya başlayabiliyorsunuz. Disk kullanmayacakları için Durabilityleri schema_only olarak kalmaktadır. Table parametleri de tanımlanabilmektedir. Native stored procedureler, triggerlar ve kullanıcı tanımlı functionlar da performanslı çalışan nesneleridir.
In-Memory OLTP’nin desteklediği fonksiyonları aşağıdaki linkten inceleyebilirsiniz;
In-Memory’den çalışacak tablolarımızı nasıl planlıyoruz, her tablo In-Memory olmasına gerek var mı? Tabi ki gerek yok, burada önemli olan, DML operasyonlarının çok olduğu tabloları tercih etmek önemli. Arşivleme için tabloların In-Memory çalışmasının hiçbir anlamı olmuyor.
Planlama aşamasında ilk olarak, geçiş yapacağımız database üzerinde Reports – Transaction Performance Analysis Overview raporunu açıyoruz.
Burada bize kısa bir bilgilendirme ile, In-Memory OLTP’ye geçiş yapacağınız tablolar hakkında bilgi vereceğinden bahsediyor.
Four-quadrant olarak gelen raporda, yatay olan çizgi, In-Memory’e geçerken ne kadar efor harcayacağımız hakkında bilgi veriyor, sağ taraftaki tablolar için minimal seviye migration işi çıkacağını belirtiyor. Dikey eksen ise, In-Memory’e geçersek ne kadar fayda sağlarız, bunu gösteriyor. Yukarı doğru yeşil olduğundan, bu tabloların geçişinde yüksek oranda performans sağlayacağını belirtiyor.
Grafikte de görüldüğü gibi, tüm tablolar bu grafiğe gelmedi, her tablo için In-Memory ihtiyacının yanlış olduğunu belirtmiştik.
Hangi tabloların In-Memory ihtiyacını tespit ettikten sonra, migration öncesinde bu tabloların In-Memory’e uyumlu olup olmadığını da, tablo üzerinde sağ-klik – Memory Optimization Advisor’a ile kontrol ediyoruz, uygun olmayan özellikleri karşımıza getiriyor.
İlk olarak, Memory Optimization Checklist geliyor, burada uyumsuzluklar hakkında bilgi vermektedir.
Karşımıza çıkan hatada, bu tabloda uyumsuz veri tipi olduğunu belirtiyor, bu veri tiplerini düzeltene kadar, In-memory table’e geçilmesine izin vermeyecektir.
In-Memory için uyumlu veri tiplerini aşağıdaki linkten inceleyebilirsiniz.
In-Memory için uygun olan bir tabloda örnek yapalım, ErrorLog tablosun da In-Memory Advisor’u çalıştırıyoruz.
Memory Optimization warnings ekranında, bu tablodaki, views, procedurelar, triggerlar, constraitler ve computed columnların, In-Memory’e geçişinden sonra, düzeltmelerinin yapılması gerektiği yönünde bir uyar alıyoruz.
In-Memory table’ın desteklemediği Transact-SQL komutlarını aşağıdaki linkten inceleyebilirsiniz.
Review Optimization Options ekranında;
– Memory optimized filegroup: In-Memory table’lar için oluşturulan filegroup
– Logical file name: Data dosyasının logical isminin verileceği alan
– File path: In-Memory table’lar memory de tutulsalar da, sunucunun bir sorundan dolayı yeniden başlaması veya donanımsal sorunlar için, bir kopyası da disk üzerinde tutulmaktadır.
– Rename the original table as: In-Memory tabloya geçiş yapılacak hedef tablonun adını değiştirirek, sonuna old yazılarak saklanmaktadır.
– Also copy table data to the new memory optimized table check box’ını işaretliyoruz ki, datalar yeni In-Memory tabloya geçiş yapabilsin.
– En alttaki check box’ı işaretlediğiniz de, data bütünlüğü göz ardı ederek data kopyalamayı yapar.
Review Primary Key Conversion, bu ekran da, primary keyleri otomatik olarak tespit edip, primary key datalarını doldurulmasını sağlamaktadır. Durable yani veri bütünlüğü olarak yapılan bir migration işleminde mutlaka primary key’e ihtiyaç duyulmaktadır. Eğer geçiş yapılan tabloda primary key yok ise, migration işlemi non-durable olarak yapılır ve bu ekran karşınıza çıkmaz.
Son ekran olan, Verify ekranında, migration hakkında özet bilgiyi vermektedir. Migrate diyip, In-Memory table geçişini başlatıyoruz.
Son olarak, başarılı olduğunda tüm alanlarda passed yazması gerekiyor.
Memory-optimized tablelar performansı yüksek, başarılı bir özellik olarak karşımıza çıkmaktadır. Fakat burada planlama çok önemlidir, analiz yapmadan In-Memory geçişlerinin rastgele yapılmaması gerekmektedir.
Örnek vermek gerekirse, In-Memory geçişi için yeni boş veritabanı açıp, migration işlemini bu veritabanı üzerine yapılması daha doğru olacaktır.
5 – Natively Complied Stored Procedures
Türkçe ismini en çok zorladığım başlıklardan birsidir, Doğal olarak derlenmiş saklı yordamlar 🙂 Natively complied sp’ler oluşturduklarında, query execution engine’i bypass ederek çalışırlar. Aslında In-Memory üzerinde çalışan ve performans katkısı yüksek olarak stored procedurler diyebiliriz. Bu proceduler, sunucu yeniden başladığında veya manuel olarak oluşturuldukların da derlenmektedirler.
Natively complied Stored Procedure’lar, SCHEMABINDING ve BEGIN ATOMIC fonksiyonları birlikte kullanılırlar. Bu iki fonksiyonu kısaca açıklamaya çalışalım.
SCHEMABINDING, stored procedure oluşturulduktan sonra değişikliği önlemek için tablo tanımını kilitler. SCHEMABINDING, derlenen stored procedure ile ilgili veri türlerinden emin olmasını sağlayan bir fonksiyondur. Natively complied Stored Procedure bulunan tablolar, SCHEMABINDING özelliği drop edilmeden değiştirilemezler. Ayrıca sorguda açık olarak alan isimlerinin kullanılması gerekmektedir, örnek olarka SELECT * Schemanbinding olduğunda çalışmamaktadır.
BEGIN ATOMIC ise sadece Natively complied Stored Procedure’ler için uygundur, Normal stored procedure’larda BEGIN TRAN blokunu kullanmanız gerekiyor. Natively complied Stored Procedure’lar da Atomic blok kullanılması, SQL Server’daki connectionlardan bağımsız olarak, transaction ayarlarının kendine özgü çalışacağını gösterir. Eğer mevcutta çalışan bir active transaction olduğu gibi durumlarda, BEGIN ATOMIC özelliği sayesinde, procedure kendisine bir point oluşturarak kaydeder ve fail olduğu durumda da otomatik olarak roll back yapar.
Memory-optimized bir tabloda, natively stored procedure nasıl oluşturulur buna bir örnek yapalım. Örneğe başlamadan önce ufak bir hatırlatma; memory-optimized tablolar clustered index içermezler, tablolar satırlar olarak saklanır, pageler üzerinde saklanmazlar.
İlk olarak memory-optimized tablomuzu oluşturuyoruz;
-- Memory-Optimized Table oluşturulması USE DataPlatformMemOptDB; GO CREATE TABLE dbo.DataPlatformMemOptTable ( id int not null, OlusturmaTarihi datetime not null, SiparisID nvarchar(10) not null CONSTRAINT pk_id PRIMARY KEY NONCLUSTERED (id) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
Natively stored procedeure’u oluşturalım
-- Natively Stored Procedure'un oluşturulması CREATE PROCEDURE dbo.DPNativeProcedure (@id int) WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' ) SELECT id, OlusturmaTarihi, SiparisID FROM dbo.DataPlatformMemOptTable WHERE id = @id END GO
Native Stored Procedure ile ilgili detaylar için aşağıdaki linki ziyaret edebilirsiniz
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/creating-natively-compiled-stored-procedures?view=sql-server-2017
6 – TempdDB’deki Geliştirmeler
SQL Server 2019’daki en büyük geliştirmelerden biris de , TempDB tarafında olmuştur. TempDB’nin memory-optimized olarak geliştirmesi duyurulmuş oldu. Daha önceden sıkça karşılaşılan Tempdb contention dediğimiz, tempdb’deki dar boğazların önüne geçebilmek için bu memory-optimized olarak tempdb de geliştirmeler yapıldı.
TempDB memory-optimized olarak ayarlamak için, ilk olarak server konfigürasyonunda değeri set ediyoruz, ardında da SQL Server servisini restart etmemiz gerekiyor.
Aşağıdaki komut ile TempDB memory-optimized özelliğini aktif edebilirsiniz.
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON
Kontrol etmek için de;
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');Dönen değer 1 ise, tempdb memory üzerine taşınmış demektir.
TempDB’nin memory-optimized olması performansı arttırıcı bir özellik olmasının yanında bazı kısıtlamaları da beraberinde getiriyor. Klasik TempDB yapısında system view’lerine bu yapıda erişemiyoruz. Aynı transaction içinde tempdb’deki system view’lerine erişmek istediğimizde bize user transaction’larının erişemeyeceğine dair bir hata mesajı veriyor.
Hatırlarsanız memory-optimized tablolardaki sorgular, locking ve isolation level hint’lerini desteklemiyordu. Aynı burada da benzer bir kısıtlama ile karşılaşmaktayız.
Yeni bir özellik olduğu için, tempdb kullanılan #tableadi temp tablolardaki columnstore indeksler de bazı sıkıntılar oluşmakta, şu an için columnstore indeksleri tempdb kullanmamakta fayda var, çıkacak olan cumulative updatelerde bu sorun düzeltilecektir diye umuyoruz.
Memory-optimized TempDB’ye ufak bir örnek yapalım, memory-optimized olmadan önce temp tabloya insert ve sonrasında memory-optimized tempd’yi etkinleştirerek temp tablo da ne gibi performans farkı oluşuyor bakalım.
Şehirler geçici tablosuna insert işlemi yapalım
CREATE TABLE #dataplatform_sehirler (id INT NOT NULL, sehirler VARCHAR (15)); INSERT INTO #dataplatform_sehirler (id, sehirler) VALUES (1, 'Adana'), (6, 'Ankara'), (16, 'Bursa'), (34, 'Istanbul'), (33, 'Mersin'), (38, 'Kayseri'), (35, 'İzmir'); -- Tablomuz temp tablolara gelmiş mi kontrol edelim SELECT * FROM tempdb.sys.tables;
Sorguyu çalıştırmadan önce performansını görebilmemiz için, sorgu ekranında sağ klik – Query Options – Advanced Sekmesinden SET STATISTICS TIME ve SET STATISTICS IO’yu seçiyoruz.
Sorgu sonucu aşağdaki gibi geldi;
Şimdi, memory-optimized tempdb özelliğini etkinleştirelim;
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON
Memory-optimized tempdb’nin etkin olup olmadığını kontrol ediyoruz, mutlaka SQL Server servisini restart etmeyi unutmayın.
Sorgumuzu tekrardan çalıştıralım, tekrar çalıştırdıktan sonraki IO değerlerine baktığımız da neredeyse hepsi 0 IO’lara düşmüş durumda.
Execution planı incelediğimizde, örnek olarak syssingleobjrefs deki index’in Storage kısmında MemoryOptimized yazıyor.
Yüksek oradan Tempdb kullanılana ortamlarda, memory-optimized tempdb ‘ye geçiş için yeteri kadar memory ayrılması, tempdb’lerin cpu’ya göre açılması ve ayrı disklerde tutulması da performans açısından önemlidir.
Memory optimized tempdb için detaylı bilgiyi aşağıdaki linkten alabilirsiniz;
Aşağıdaki 7 dakikalık video ile memory-optimized tempdb kullanımı hakkında güzel bir demo sunuyor;
7 – Intelligent Query Processing
Kısa adı IQP olan, Intelligent Query Processing, akıllı sorgu işleme olarak da çevirebiliriz, ilk olarak SQL Server 2017’de adaptive query processing olarak çıktı, SQL Server 2019’da da yeni özellikler ile sunulmaya başlandı. SQL Server 2019’a upgrade edilip ve compability level’ınızı 150’ye çıkardığınız da bu yeni akıllı query optimizer’ı kullanmaya başlıyorsunuz.
Özellikle belirtelim ki, SQL Server 2014’te değişen Cardinality estimator ve query engine’nin tamamen yenilenmesinden dolayı, SQL Server 2014 ve SQL Server 2016 sürümleri ciddi sıkıntıları da beraberinde getirmişlerdi. Neyse ki, Query Engine’deki bu sorunların bir çoğu SQL Server 2017’deki bu akıllı 🙂 query optimizer ile düzelmiş oldu. Bu düzeltmeler ve iyileştirmeler SQL Server 2019’da da devam ediyor.
SQL Server 2019’a geçiş yaptınız ve IQP’yi yani Intellient Query Processing’i kullanmak istiyorsunuz, bunun için yapmanız gereken, database’in properties-option sekmesindeki compability level’i 150’ye çekmeniz olacaktır. Bunu yaptığınızda otomatik olarak IQP’nin nimetlerinden faydalanmış olacaksınız.
Azure SQL tarafına da otomatik olarak geldiğini belirtmek isterim, son çıkan yenilikler ilk olarak Azure SQL’e gelip uygulanmaktadır.
Aşağıdaki grafikte IQP’nin şeması hakkında bilgi veriyor.
IQP ile ilgili 7 tane özellik bulunuyor, bu özelliklere kısaca değinelim;
7.1. Batch Mode Adaptive Joins
Batch mode adaptive join özelliği ile, memoryde tek bir plan üzerinde ilk girdi girerken, hash join veya nested loops join hangisini seçeceğine karar veren bir mekanizmadır. Nested loops’a ne zaman geçeceğini belirleyen bir parametre olmuştur. Sorgu çalıştığı sırada, execution plan dinamik olarak, hangi join stratejisi daha iyi ise onu seçecek bir yapıdır.
Burada önemli olan planın doğru joini seçerek, sorguyu daha hızlı çalıştırmasıdır, adaptive join de, satır sayısı hash join için az ise otomatik olarak nested loops’u tercih etmesidir. Eğer join’deki girdiler belli bir threshold’a ulaşır ise, plan burada hash join’i seçerek devam ederek.
Konuyu daha iyi anlamak için ufak bir örnek yapalım, bu örnekte WorldWideImportersDW veritabanını kullanacağız. Adaptive join’i kullanmak için en az SQL Server 2017 compability level’ında olmanız gerekiyor. WorldWideImportersDW veritabanını restore ettiğinizde comp. level 130 olarak gelir ilk olarak bunu 140’a çekmeyi unutmayın.
USE [master]; GO -- Database'in comp. level'ının 140 a çekiyoruz ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140; GO -- Cache'i temizleyelim ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; GO USE [WideWorldImportersDW]; GO -- Miktarı 360 olanları sorgulayalım SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity] FROM [Fact].[Order] AS [fo] INNER JOIN [Dimension].[Stock Item] AS [si] ON [fo].[Stock Item Key] = [si].[Stock Item Key] WHERE [fo].[Quantity] = 360; GO
Quantity 360 olan 206 adet kayıt geldi, execution plan’a baktığımızda, adaptive join operatörünü görüyoruz, eğer veritabanının comp. level’ı 130 olarak çalıştırırsanız adaptive join yerine Hash Match (Inner Join) operatörünü göreceksiniz.
Burada satır sayısı fazla olduğundan adaptive joinde Hash match’i kullandı.
Quantity 361 olanları silip, sonra ilk 5 361 adedi kaydedelim
-- 361 olanları silip, sonra ilk 5 adedi 361 adedi kaydedelim DELETE [Fact].[Order] WHERE Quantity = 361; INSERT [Fact].[Order] ([City Key], [Customer Key], [Stock Item Key], [Order Date Key], [Picked Date Key], [Salesperson Key], [Picker Key], [WWI Order ID], [WWI Backorder ID], Description, Package, Quantity, [Unit Price], [Tax Rate], [Total Excluding Tax], [Tax Amount], [Total Including Tax], [Lineage Key]) SELECT TOP 5 [City Key], [Customer Key], [Stock Item Key], [Order Date Key], [Picked Date Key], [Salesperson Key], [Picker Key], [WWI Order ID], [WWI Backorder ID], Description, Package, 361, [Unit Price], [Tax Rate], [Total Excluding Tax], [Tax Amount], [Total Including Tax], [Lineage Key] FROM [Fact].[Order]; GO
Satır sayısı daha az olan bir sorguda neler yapıyor bakalım
-- Satır sayısı 5 olan sorgudaki adaptive join SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity] FROM [Fact].[Order] AS [fo] INNER JOIN [Dimension].[Stock Item] AS [si] ON [fo].[Stock Item Key] = [si].[Stock Item Key] WHERE [fo].[Quantity] = 361; GO
Operatör adı aynı, adaptive join detayına bakalım
Satır sayısı 5 adet olduğu için, Actual Join type’da NestedLoops’u otomatik olarak kendisi seçti. Satır sayısına göre burada kararı adaptive join operatörü vermektedir. Böylelikle böyle bir sorgu için 2 tane plan kullanmak yerine, tek plan içerisinde adaptive operatörü hash mi yoksa nested loop mu otomatik olarak karar vererek, plan tarafındaki performansı sağlamaktadır.
Adaptive Join’i etkinleştirmek için;
ALTER DATABASE current SET COMPATIBILITY_LEVEL = 140; veya = 150; ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = OFF;
Adaptive Join’i devre dışı bırakmak için;
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;
OPTION(USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS'));7.2. Approximate query processing
SQL Server 2019 ile gelen yeni bir özellik, bu özelliği kullanabilmeniz için, veritabanı comp. levelınızın 150 olması gerekiyor. Approximate kelime anlamı yaklaşık olarak verilen değer olarak burada kullanacağız. Bir tablodaki satır sayısı için COUNT(DISTINCT()) kullanıyoruz, fakat büyük tablolarda count ile hesaplama yapmak performans olarak sıkıntılara yol açıyordu. APPROX_COUNT_DISTINCT aggregate funciton’ı ile de, yaklaşık olarak satır sayısını hesaplarken performansı da etkilemeden çalışmaktadır.
Aşağıdaki örnekte APPROX_COUNT_DISTINCT’i inceleyelim;
ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150; GO USE [WideWorldImportersDW]; GO -- Sorgu çalışma zamanlarını not ederek çalıştıralım SELECT COUNT(DISTINCT [Movement Key]) FROM [Fact].[Movement] SELECT APPROX_COUNT_DISTINCT([Movement Key]) FROM[Fact].[Movement]
Aradaki farka bakalım
Gerçek olan değer 236667 satır olması, Approx count ile de daha farklı, yani yaklaşık bir değer getirdi.
OI ve Time’lara bakalım
Approx count a bakalım
Neredeyse yarı yarıya fark var, Approx count’ları özellikle big data projelerinde ve çok büyük tabloların countlarını alırken kullanabiliriz. Yüzde 2 yanılma payı ile tahmini olarak satır sayısını doğru olarak verecektir.
7.3. Batch mode on rowstore
Batch’in anlamı için toplu bir şekilde diyebiliriz. SQL Server 2019, comp. level 150 ile gelen bir özelliktir. Batch mode on rowstore, analytic iş yüklerinde, columnstore indeksler olmadan toplu bir şekilde sorgu çalıştırmaya yarayan bir özelliktir. Bu özellik disk heaplerini ve B-tree indeksleri desteklemektedir.
İlk olarak SQL Server 2012’de analytic iş yükleri için columnstore indeksler duyurulmuştu. Daha sonraki versiyonlarda da columnstore indekslerde performansa yönelik iyileştirmeler yapıldı. Tablolarda columstore indeksler oluşturularak, analytic iş yüklerinin daha hızlı çalışması sağlandı.
Batch mode on rowstore iki farklı özelliği bir arada kullanan bir yapı olduğunu söyleyebiliriz. Bunlardan birincisi, columnstore indeksler, analytic sorguların ihtiyaç duyduğu veriye sütunlar üzerinde erişmesini sağlamaktadır. Columstoreların formatındaki page compression özelliği sayesinde de, geleneksel rowstore indekslere oranla daha etkili bir performans görebiliriz.
Diğer ikinci yapıda, batch mode processing denilen, query operatörlerinin veriyi daha verimli biçimde işlediği yapıdır. Bu yapıda, her seferinde bir satır işlem yerine, dizi olarak satırları işlenmesidir, buna kısaca batch diyebiliriz.
Bu iki yapının bir arada çalışması bizlere, input/output I/O ve CPU utilizasyonlarında belirgin olarak iyileştirme sağlamasıdır. Columstore indeksleri kullanarak I/O iş yüklerini azaltabilir, batch mode processing ile de CPU kullanımı etkin bir şekilde kullanabiliriz.
Bu iki teknolojiyi aynı anda kullandığımızda, batch mode işlemleri columnstore index scan’ın bir parçası olarak değerlendirilir ayrıca, columstore’daki veri de sıkıştırılmış olduğundan, batch mode joinler ve batch mode aggreate işlemleri daha etkili olarak çalışacaktır.
En doğru sonucu almak için bu özelliği kullanırken, tablonuz da en az bir tane columstore indeksinizin olması gerekiyor, bu yapı columnstore indeksin üzerine kurulmuş diyebiliriz. Bununda bazı zorlukları var tabiki, columnstore indeksler bazı uygulamalar için uygun değildir. Uygulamaların bazı özellikleri uygun olmayabilir, örnek olarak in-place modifikasyonları columstore compression yapısına uygun değildir. Tablolardaki trigger’lar da clustered columnstore indekslerini desteklemezler. DELETE ve UPDATE içinler de ek yük getirdiklerini söyleyebiliriz.
Batch mode on rowstore bize ne faydalar sağlar inceleyelim;
- İş yükünün çoğunluğu analitik sorgular olan, joinler aggregate functionlar, yüzlerce binlerce process yapılan satırlar
- İş yükü CPU’ya bağlıdır, CPU üzerine yük getireceğinden, I/O’larınızı ne durumda olduğundan emin olmalısınız, yeteri kadar kaynağınız olması önemli
- Columstore indeks oluşturmak, mevcut transactional iş yüklerinizin üzerine ek yük eklenmesi demektir ve uygulamalarında desteklemesi konusunda sıkıntılar yaşayabilirsiniz. Batch mode on rowstore’da bu taraftaki sıkıntıları çözmek için idealdir.
- Batch mode on rowstore CPU tüketiminin azaltılmasında yardımcı olur. Eğer genel olarak I/O kaynaklı bir darboğaz yaşıyor iseniz, batch mode on rowstore sorgu çalışma zamanlarınızı iyileştirmeyecektir. Cache için yeteri kadar memory’niz yok ise performans iyileştirmesi için fayda sağlamayacaktır.
Batch mode on rowstore’u etkinleştirmek için, database compability level’i 150’ye set etmeniz yeterli olacaktır. Aşağıdaki komutlar ile de etkinleştirip – devre dışı bırakabilirsiniz.
-- Batch mode on rowstore devre dışı bırakma ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF; -- Batch mode on rowstore etkinleştirme ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON;
Sorgu içinde kullanmak isterseniz;
SELECT ....
FROM
....
OPTION(RECOMPILE, USE HINT('ALLOW_BATCH_MODE'));Sorgu özelinde devre dışı bırakma isterseniz de;
SELECT ....
FROM
....
OPTION(RECOMPILE, USE HINT('DISALLOW_BATCH_MODE'));7.4. Table Variable Deferred Compilation
Table variable deferred compilation, execution plan ve sorgu performansını, tablodaki değişkenleri referans alarak arttıran yeni bir özelliktir. SQL Server 2019 ile gelmiştir, comp. level’ın 150 olması gerekmektedir. Execution plandaki iyileştirmeleri, cardinality estimator’un doğru tahminleme yapabilmesi için, tablo değişkenleri yardımıyla gerçekleşen satır sayısını doğru hesaplayarak yapar. İlk actual plan oluşana kadar tablo değişkenlerinin referanslarını erteler, OPTION (RECOMPILE) ile benzer özelliklere sahiptir. Temporary tablolar gibi davranır.
Column statisticslere yeni bir ekleme yapmaz, recompilation yenileme sıklığını da arttırmaz. En çok işe yaradığı yerler ise, hash joinlerin daha uygun olduğu yerlerde nested loop joinler yerine kullanılmasını sağlar, memory’nin yetersiz kaldığı yerlerde performansı artı yönde etkisi vardır.
Table variable deferred’i etkinleştirmek için, database comp. level’ı 150 yapmanız yeterlidir. Aşağıdaki komutlar ile de etkinleştirip – devre dışı bırakabilirsiniz.
-- Table variable deferred etkinleştirme ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON; -- Table variable deferred devre dışı bırakma ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;
Sorgu özelinde devre dışı bırakma içinde USE HINT kullanımı aşağıdaki gibidir;
SELECT ....
FROM
....
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));Table variable deferred ile alakalı ufak bir demo yapalım, ilk olarak compl. level 140 da, OrderHistory tablosundan quantity’si 99’dan büyük olan değerleri insert edip, sonrasında unitprice’ı 0,1 den büyük değerleri sorgulayalım.
-- Comp level'ı 140'a çekiyoruz USE [master]; GO ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140; GO -- Cache'i temizleyelim ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; GO -- Quantity 99'dan büyük olanlar insert edelim USE [WideWorldImportersDW]; GO DECLARE @Order TABLE ([Order Key] BIGINT NOT NULL, [Quantity] INT NOT NULL ); INSERT @Order SELECT [Order Key], [Quantity] FROM [Fact].[OrderHistory] WHERE [Quantity] > 99; -- Unit Price'ı 0,1 den büyükleri sorguluyoruz, estimated row ve join'lere bakalım SELECT oh.[Order Key], oh.[Order Date Key], oh.[Unit Price], o.Quantity FROM Fact.OrderHistoryExtended AS oh INNER JOIN @Order AS o ON o.[Order Key] = oh.[Order Key] WHERE oh.[Unit Price] > 0.10 ORDER BY oh.[Unit Price] DESC; GO
Yukarıdaki compl. level 140’da 1.9 milyon read yaptı, sorgu 12 saniye sürdü. SQL Server 2019’da compl. level 150’de nasıl görelim şimdi;
-- Comp level'ı 150'a çekiyoruz USE [master] GO ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150 GO -- Yukardaki aynı sorguyu comp level 150 de çalıştıyoruz USE [WideWorldImportersDW] GO DECLARE @Order TABLE ([Order Key] BIGINT NOT NULL, [Quantity] INT NOT NULL ); INSERT @Order SELECT [Order Key], [Quantity] FROM [Fact].[OrderHistory] WHERE [Quantity] > 99; SELECT oh.[Order Key], oh.[Order Date Key], oh.[Unit Price], o.Quantity FROM Fact.OrderHistoryExtended AS oh INNER JOIN @Order AS o ON o.[Order Key] = oh.[Order Key] WHERE oh.[Unit Price] > 0.10 ORDER BY oh.[Unit Price] DESC; GO
Comp level 140’da 1,9 milyon read yapan tablo, comp .level 150’de 755bin’ e kadar düşüyor. 140 level’da 12 saniye sürmüştü, 150 level’da 8 saniye sürdü. Yaptığımız ise sadece SQL Server 2017’den 2019 comp levelinde çalıştırmak oldu.
7.5. Scalar UDF Inlining
Inlining terimi farklı bir kavram olarak karşımıza çıkıyor. Scalar User Define Function’ları otomatik olarak transform ederek, SQL sorgusu içerisinde gömülü olarak çağrılmasını sağlıyor. SQL Server 2019 ile gelen yeni bir özelliktir, comp level’ın 150 yapılması ile devreye girmektedir.
User Defined Function’lar (UDF) T-SQL kodu içerisinde tek bir değer döndürerek, birden çok karmaşık hesaplamalarda kolaylıklar sağlayan bir özelliktir. UDF’ler kullanılarak karmaşık ve kompleks sorgularda hesaplamalar rahatlıkla yapılabilmektedir.
UDF’lerin bu kolaylıklarının yanında bazı performans sorunlarını beraberinde getiriyordu. Sorgu optimizasyonları hesaplanırken, relational operatörlerin maliyetlerine bakıldığından, scalar operatörlerin optimizasyon sırasında kullanılmıyor. Scalar operatörlere ufak bir CPU maliyeti eklenerek planlar hesaplanmaktadır. Tabi bu da gerçek plan maliyetini tam olarak yansıtmamaktadır. UDF’lerin plan cachleri her defasından yeniden hesaplanmaktadır, statement bazında cacheleme yaptığından, sürekli yeni bir plan oluşturmaktadır. Ayrıca UDF’ler de paralelizm kullanılmasına izin verilmez.
UDF’ler ile ilgili yukarıda bahsettiğimiz performans sorunlarının önüne geçebilmek için de SQL Server 2019’da Scalar UDF inlining özelliği gelmiş oldu. Bu özellikteki amaç, Scalar UDF’lerin sorgularda ki performansını arttırmaktır. Scalar UDF Inlining daha iyi anlamak için aşağıda örneklerine bakalım;
İlk olarak comp level’ı 150 ye çekiyoruz, cache’i de temizliyoruz
USE [master]; GO -- Comp level'ı 150 ye getiriyoruz ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150; GO -- Cache'i temizleyelim ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; GO
OrderHistory’de sipariş miktarlarını gruplandıran bir function oluşturalım
CREATE FUNCTION dbo.ufn_customer_category(@CustomerKey INT) RETURNS CHAR(10) AS BEGIN DECLARE @total_amount DECIMAL(18,2); DECLARE @category CHAR(10); SELECT @total_amount = SUM([Total Including Tax]) FROM [Fact].[OrderHistory] WHERE [Customer Key] = @CustomerKey; IF @total_amount < 500000 SET @category = 'REGULAR'; ELSE IF @total_amount < 1000000 SET @category = 'GOLD'; ELSE SET @category = 'PLATINUM'; RETURN @category; END GO
Oluşturduğumuz function’ı ilk olarak Scalar UDF Inlining kapalı olarak çalıştırıyoruz, sorguyu çalıştırmadan önce Include Actual Execution Plan’ı seçelim
SELECT TOP 100
[Customer Key], [Customer],
dbo.ufn_customer_category([Customer Key]) AS [Discount Price]
FROM [Dimension].[Customer]
ORDER BY [Customer Key]
OPTION (RECOMPILE,USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));
GOBu şekilde sorgu 32 saniye de tamamlandı
Execution planı şu şekilde
Sorguyu şimdi de Scalar UDF Inlining ile çalıştıralım
SELECT TOP 100
[Customer Key], [Customer],
dbo.ufn_customer_category([Customer Key]) AS [Discount Price]
FROM [Dimension].[Customer]
ORDER BY [Customer Key]
OPTION (RECOMPILE);
GO
Sorgu 10 saniye de tamamlandı ve en önemlisi I/O ları daha gerçekçi değerler ile görebiliyoruz, execution planı da şu şekilde
Execution planda da daha ayrıntılar SQL Server 2019 ile birlikte geldiğini söyleyebiliriz.
Scalar UDF Inlining’i sorgu içinde devre dışı bırakmak için;
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));User Define Function içinde devre dışı bırakmak istersek de INLINE = OFF ile yapabiliriz;
CREATE OR ALTER FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))
RETURNS DECIMAL (12,2)
WITH INLINE = OFF -- etkinleştirmek için ON
AS
BEGIN
RETURN @price * (1 - @discount);
END;7.6. Memory Grant Feedback – Batch Mode
SQL Server’da bir sorgu çalışmadan önce, tahmin edilen – estimated execution plan, minimum olarak yetecek kadar memory’i hesaplamaktadır. Memory’den ayrılan alanlar yetersiz olduğunda performans sorunları da başlamış oluyor, yanlış hesaplanan planlar memory’de eş zamanlı çalışmayı da engelliyor. Memory ayrılan alanlar yetersiz olduğunda, bu olumsuzluk disk tarafına negatif olarak yansımaktadır. Tekrarlanan iş yüklerinde, batch mode memory grant feedback, sorguların ihtiyaç duyduğu memory alanını tekrardan hesaplayarak, cache planları güncellenmektedir.
Sorguya atanmış memory iki kereden fazla kullanılıyor ise, memory grant feedback memory kullanımı tekrardan hesaplayacaktır ve cache plan’ı da buna göre güncelleyecektir. Memory’deki planlar 1 mb’ın altında ise yeniden hesaplama yapmaz. Memory’nin yetersiz olduğu durumlarda, batch mode operatörleri diske gitmeye başlayacaklarından, memory grant feedback özelliği sayesinde tekrardan memory’e yerleşmeleri için hesaplamalar yeniden yapılacaktır.
Farklı değerler atanan parametreler, farklı query planları oluşturmaktadır. Bu tip sorgulara parametre hassasiyeti olan sorgularda diyebiliriz. Bu gibi stabil olmayan sorgularda memory grant feedback devre dışı kalabilir. Birden çok tekrar eden bu sorgular için, memory grant feedback’in yeniden hesapladığı planlar devre dışı kalmaktadır. Bu sorguları izlemek için memory_grant_feedback_loop_disabled xEvent’ini kullanabiliriz.
Memory grant feedback’i devre dışı bırakmak için;
-- SQL Server 2017 ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON; -- SQL Server 2019, ve Azure SQL Database ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;
Yeniden etkinleştirmek için ise;
-- SQL Server 2017 ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF; -- SQL Server 2019, ve Azure SQL Databas ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;
Sorgu içinde kullanmak için, OPTION parametresi;
SELECT * FROM .....
WHERE ....
OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK')); 7.7. Memory Grant Feedback – Row Mode
Row mode memory grant feedback, batch mode memory grant feedback özelliği ile memory’i daha fazla ve efektif kullanılmasını, batch ve row operatörlerinde de uygular. Row mode SQL Server 2019 ile gelen bir özelliktir, etkinleştirmek için compl. level’I 150 yapmanız yeterlidir. Azure SQL Database’de otomatik olarak devreye girmektedir.
Row mode memory grant feedback’i izlemek isterseniz de, memory_grant_updated_by_feedback XEvent ile monitör edebilirsiniz. Row mode özelliği ile iki yeni query plan özelliği gelmiş oldu. IsMemoryGrantFeedbackAdjusted ve LastRequestedMemory, XML plan da ise MemoryGrantInfo olarak karşımıza çıkmaktadır.
Row mode memory grant feedback’i devre dışı bırakmak için;
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;
Yeniden etkinleştirmek için;
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;
Sorgu içinde kullanmak için, OPTION parametresi;
SELECT * FROM ......
WHERE ........
OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK')); 8 – Hybrid Buffer Pool
SQL Server 2019 ile duyurulan yeni bir özelliktir. Bu yeni özellik direk persisten memory’e (PMEM – Persisten Memory) cihazlarına erişim sağlamaktadır. Bu persisten memory, fiziksel ram ile SSD diskler arasında yüksek performans sağlayan yeni bir yapıdır.
Bu yeni memory mimarisi Intel Optane Techonolgy olarak isimlendirilmektedir. Aşağıdaki grafikte, bu mimarinin yapısı hakkında bilgi vermektedir.
Bu yapı hakkında detaylı bilgi için aşağıdaki bağlantıyı ziyaret edebilirsiniz.
https://www.intel.com/content/www/us/en/products/docs/storage/optane-technology-brief.html
Persistent memory CPU’un memory controller’ına entegre bir yapısı vardır, Server kapansa bile verileri saklama özelliğine sahiptir. Persistent memory aygıtlarının pek çok yönü herhangi bir yazılım değişikliği olmadan gerçekleştirilebilse de, Hybrid Buffer Pool gibi özellikler yeni depolama hiyerarşisinden yararlanabilir ve dosyalara doğrudan bellek erişimi sağlayabilir.
Direct Access mimarisi
Hybrid Buffer Pool özelliği, SQL Server Instance seviyesinde etkinleştirilmesi gereken bir özelliktir, varsayılan olarak kapalı olarak gelmektedir. Etkinleştirdikten sonra SQL Server servisinin yeniden başlatılması gerekmektedir.
Hybrid Buffer Pool’u etkinleştirmek için;
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED HYBRID_BUFFER_POOL = ON;
Ayrıca, Hybrid Buffer Pool sadece memory-optimized veritabanlarında çalıştığını belirtelim, veri tabanında memoery-optimized özelliğinin açık olması gerekmektedir.
ALTER DATABASE <veritabaniadi> SET MEMORY_OPTIMIZED = ON -- OFF (devre dışı bırkamak için OFF)
Hybrid Buffer Pool konfigürasyonu ile ilgili sorgu aşağıdaki gibidir;
SELECT * FROM sys.configurations WHERE name = 'hybrid_buffer_pool'; SELECT name, is_memory_optimized_enabled FROM sys.databases;
Windows Server 2019’daki persisten memory mimarisi ve monitoring edebileceğiniz power shell komutlarına aşağıdaki linkten ulaşabilirsiniz;
https://docs.microsoft.com/en-us/windows-server/storage/storage-spaces/deploy-pmem
9 – Query Store
Query Store ilk olarak SQL Server 2016’da duyurulmuştu, query execution planların belirli periyodlar ile saklanarak, sonrasında plan analizlerini ve maliyeti en düşük planları seçmemizde bizlere yardımcı olan bir özelliktir. Bu özellik ile birlikte sys.dm_db_tuning_recommendations view’ını kullanarak execution planlardaki değişiklikleri izleyebiliyoruz. Düşük maliyeti olan bir planı da, sp_query_store_force_plan stored procedure’u ile de kullanılması için zorlaya biliyoruz.
SQL Server 2019’da Query Store ile ilgili ilave bazı geliştirmeler yapıldı. Query Store’u etkinleştirdiğiniz de varsayılan olarak gelen parametreler, QUERY_CAPTURE_MODE özelliği ve fast forward – static cursors desteği gelmiş oldu.
Query Store’u kullanmak için, management studio veya T-SQL kodları ile etkinleştirip, devre dışı bırakabilirsiniz. veritabanı üzerinde sağ klik properties ekranında en altta Query Store bölümü yer almaktadır. Bu ekrandan, Operation mode’unu (off, read-only, or read/write), Query store boyutunu ve diğer ayarları buradan konfig edebilirsiniz.
SQL Server 2017 de farklı olan, iki parametrenin SQL Server 2019’da varsayılan değerleri değiştirilmiş olarak gelmektedir;
- MAX_STORAGE_SIZE_MB varsayılan değeri 1000 mb
- QUERY_CAPTURE_MODE varsayılan değeri AUTO
Önceki SQL Server versiyonlarında Query Store Capture Mode varsayılan olarak ALL seçili geliyordu, bu değiştirilerek AUTO olarak gelmektedir. AUTO olması, bir gün içerisinde ilk 29 execution planı store ederek, 30. plana geldiğinde statisticsleri ve execution planları saklamayacaktır.
Query_Capture_mode, custom mod da ise 4 farklı seçenek ile ayarlayabilirsiniz. Bu seçenekler;
– EXECUTION_COUNT
– TOTAL_COMPILE_CPU_TIME_MS
– TOTAL_EXECUTION_CPU_TIME_MS
– STALE_CAPTURE_POLICY_THRESHOLD
Bu ayarları set etmeden önce, query store yapılandırmasını sys.database_query_store_options view’ını kullanarak kontrol edebilirsiniz.
SELECT actual_state_desc, stale_query_threshold_days, query_capture_mode_desc, capture_policy_execution_count, capture_policy_total_compile_cpu_time_ms, capture_policy_total_execution_cpu_time_ms FROM sys.database_query_store_options
Sonuç aşağıdaki gibi bir örnek gelecektir;
Query capture custom mode da ise, aşağıdaki örnekteki gibi, istediğiniz parametrelerle değişiklik yaparak da kullanabilirsiniz;
ALTER DATABASE AdventureWorks2017
SET QUERY_STORE = ON
(
QUERY_CAPTURE_MODE = CUSTOM, QUERY_CAPTURE_POLICY =
(
EXECUTION_COUNT = 20,
TOTAL_COMPILE_CPU_TIME_MS = 1000,
TOTAL_EXECUTION_CPU_TIME_MS = 100,
STALE_CAPTURE_POLICY_THRESHOLD = 7 DAYS
)
);10 – Automatic Tuning
İlk olarak Azure’daki PaaS SQL’de çıkan yeni bir özellik idi. Sorguların ve indekslerin performans genelinde optimizasyon yapılmaları gereken unsurlarıdr. Sorgularda maliyeti düşük planların seçilmesi, ihtiyaç duyulan indekslerin oluşturulması veya gereksiz indekslerin silinmesi gibi konular SQL Server’ın performansını yüksek oranda etkileyen konulardır.
Automatic Tuning kapsamında iki özelliği kullanabilmekteyiz, bu özellikler varsayılan olarak kapalı gelmektedir, kullanmadan önce mutlaka etkinleştirmek gerekmektedir.
10.1 Automatic Plan Correction
Automatic plan correction ile performans sağlamak için, Query Store’u mutlaka açmak gerekir. Query Store’da sorguların planlarının toplanması gerekiyor ki, Automatic Plan Correction özelliği de, Query Store’daki planları izleyerek, bu planlardan hangisinin maliyeti en düşük ise, en uygun planı seçebilecektir.
Automatic plan correction, sorgu execution planlarınızdaki değişiklikleri tanımlama sürecidir. Execution planlar, bir sorgudaki, bir önceki plan mevcut plandan daha kötü ise, sorgulardaki execution planları SQL Server Query Optimizer engine’i değiştirir, plan değişikliği buna denir. Plan değişikliklerinde, Query Store sorgunun çalışma zamanı ve çalışma istatistiklerini toplar.
Database engine, plan değişikliklerini Query Store özelliği sayesinde kaydeder. Daha detayına da, plan değişiklerini izlemek için sys.dm_db_tuning_recommendations view’ini kullanabiliriz. Bu view sayesinde hangi planda değişiklik olmuş veya planda force edilmiş bir olan var mı kontrol edebiliriz.
Bir sorgunun planındaki değişiklik olduğunda, bu query optimizer tarafından not edilir, database engine’de en son kullanılan maliyeti en düşük planı force edecektir. Database engine en iyi planı force ettikten sonra işi bitmiyor, sürekli planları monitör ederek daha iyi bir plan gelene kadar izlemeye devam eder. Daha iyi bir plan geldiğinde force ettiği planı unforce eder, yeni execution planı derler.
Automatic plan correction varsayılan olarak kapalı gelir. Aşağıdaki script ile automatic plan correction’ının durumunu görüntüleyebilirsiniz.
SELECT name, desired_state_desc, actual_state_desc FROM sys.database_automatic_tuning_options
Aşağıdaki script ile de automatic plan correction’ı etkinleştirebilirsiniz.
ALTER DATABASE current SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON )
Eğer database üzerinde Query Store özelliğini açmadıysanız aşağıdaki hatayı göreceksiniz.
Otomatik olarak planların force edilmesinde, database engine iki kritere göre karar verir;
– Tahmini CPU süresinin 10 saniyeden yüksek olması
– Önerilen plandaki hata sayılarının, yeni plandaki hata sayılarından düşük olması
Planlarda değişiklik olduğunda, execution planların force edilmesi sorgu performansına önemli derece katkı sağlamaktadır. Burada çok önemli bir ama dememiz gerekiyor, kesinlikle kalıcı bir performans çözümü değildir, force edilen planlar sürekli bu şekilde düzgün çalışacağı anlamına gelmez.
Force edilen planlar, iki sebepten dolayı devreden çıkabilir;
– Database engine tarafından force edilen planlar, SQL Server restart edildiğinde devre dışı kalırlar
– Force edilen planlar, statistics update ve schema değişikliklerinde unforce edilirler
10.2 Automatic Index Management
Indexler, SQL Server’ın belki de en önemli performans etkenleridir diyebiliriz. Index yönetiminde, optimal olarak indekslerin sorgudaki read değerlerini hızlandırmak, böylelikle insert ve update operasyonlarının da daha da kısatılmasını sağlayan yeni bir özellik diyebiliriz. Automatic index management özelliği ile otomatik olarak ihtiyaç duyulan indekslerin oluşturulması ve işe yaramayan indekslerinde silinmesini sağlamaktadır.
Sorguların en çok ihtiyaç duyduğu indekslerin otomatik olarak oluşturulması ve gereksiz indekslerin silinmesi, sürekli istenen ve bakım planlarında olsun, SQL Server performans çalışmalarında olsun, her zaman istenen bir özelliktir. Bu yeni özellik tabi ki birçok performans sorununa çare gibi gözükse de, mutlaka indeks yönetimi kontrol edilmesini gereken bir yapı olduğunu unutmamak lazım.
Database seviyesinde Automatic Tuning etkin olup olmadığını aşağıdaki scriptle kontrol edebilirsiniz;
SELECT name, desired_state_desc, actual_state_desc FROM sys.database_automatic_tuning_options;
Varsayılan olarak kapalı gelmektedir;
Automatic tuning’i etkinleştirmek için;
ALTER DATABASE current SET AUTOMATIC_TUNING (CREATE_INDEX = ON, DROP_INDEX = ON);
SQL Server 2019 Performans ve Optimizasyon makalemizin sonuna gelmiş bulunuyoruz 🙂 yeni gelen bir çok özelliği bu makalede toparlamak istedim. Security ve High Availability tarafındaki yenilikleri de fırsat buldukça aktarmaya çalışacağım.
Her zaman dediğimiz gibi, İşimiz Data, meraklısı için makale istatistikleri şu şekilde 🙂
6362 kelime okudunuz, Word dökümanı olarak da 43 sayfa 🙂
“SQL Server 2019 – Performans ve Optimizasyon” için 2 yanıt
Hocam elinize sağlık çok değerli ve çok güzel bilgilerin olduğu bir makale olmuş.
Emeğinize sağlık.
[…] kolonunda yapılan benzerlik araması, varsayılan olarak tüm satırları tarar (brute force) — klasik SQL Server performans optimizasyonu yaklaşımlarımızda pek tercih etmediğimiz türden bir lineer tarama. Bu, 10.000-50.000 satır […]