Performans için Microsoft SQL Server Nasıl Ayarlanır

Kullanıcılarını elde tutmak için herhangi bir uygulama veya web sitesi hızlı çalışmalıdır. Kritik görev ortamları için, bilgi almada birkaç milisaniyelik gecikme büyük sorunlara neden olabilir. Veritabanı boyutları gün geçtikçe büyüdükçe, verileri olabildiğince hızlı getirmemiz ve verileri olabildiğince hızlı bir şekilde veritabanına geri yazmamız gerekiyor. Tüm işlemlerin sorunsuz bir şekilde yürütüldüğünden emin olmak için veritabanı sunucumuzu performansa göre ayarlamalıyız.

Bu makalede, piyasadaki en iyi veritabanı sunucularından biri olan Microsoft SQL Server (kısaca SQL Server) üzerinde temel performans ayarlaması için adım adım bir prosedürü anlatacağım.

#1 Suçluları Bulmak

Diğer tüm yazılımlarda olduğu gibi, SQL Server'ın karmaşık bir bilgisayar programı olduğunu anlamamız gerekiyor. Bununla ilgili bir sorunumuz varsa, neden beklediğimiz gibi çalışmadığını keşfetmemiz gerekir.

SQL Server'dan verileri olabildiğince hızlı ve doğru bir şekilde çekip göndermemiz gerekiyor. Sorunlar varsa, birkaç temel neden ve kontrol edilecek ilk iki şey şunlardır:

• SQL Server ihtiyaçları belirli olduğundan düzeltilmesi gerekebilecek donanım ve kurulum ayarları
• SQL Server'ın uygulaması için doğru T-SQL kodunu sağladıysak

SQL Server tescilli bir yazılım olmasına rağmen, Microsoft onu anlamak ve verimli bir şekilde kullanmak için birçok yol sağlamıştır.

Donanım sağlamsa ve kurulum düzgün bir şekilde yapıldıysa, ancak SQL Server hala yavaş çalışıyorsa, önce yazılımla ilgili herhangi bir hata olup olmadığını bulmamız gerekir. Neler olduğunu kontrol etmek için farklı iş parçacıklarının nasıl performans gösterdiğini gözlemlememiz gerekiyor. Bu, farklı iş parçacıklarının bekleme istatistiklerini hesaplayarak elde edilir. SQL sunucusu, her kullanıcı isteği için iş parçacığı kullanır ve iş parçacığı, SQL Server adlı karmaşık programımızın içindeki başka bir programdan başka bir şey değildir. Bu iş parçacığının SQL sunucusunun kurulu olduğu bir işletim sistemi iş parçacığı olmadığını not etmek önemlidir; SQL Server için sözde bir işletim sistemi olan SQLOS iş parçacığı ile ilgilidir.

Bekleme istatistikleri, mevcut durumu hakkında ek bilgi veren sys.dm_os_wait_stats Dinamik Yönetim Görünümü (DMV) kullanılarak hesaplanabilir. Bu görünümü sorgulamak için çevrimiçi olarak birçok komut dosyası var, ancak benim favorim Paul Randal'ın senaryosu çünkü anlaşılması kolay ve bekleme istatistiklerini gözlemlemek için tüm önemli parametrelere sahip:

 WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 ) SELECT MAX ([W1].[wait_type]) AS [WaitType], CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S], CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S], CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S], MAX ([W1].[WaitCount]) AS [WaitCount], CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage], CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S], CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S], CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S] FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum] GROUP BY [W1].[RowNum] HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold GO

Bu betiği çalıştırdığımızda, sonucun en üst sıralarına konsantre olmamız gerekiyor çünkü bunlar ilk olarak ayarlanıyor ve maksimum bekleme türünü temsil ediyor.

Doğru kararları verebilmemiz için bekleme türlerini anlamamız gerekir. Farklı bekleme türleri hakkında bilgi edinmek için mükemmel Microsoft belgelerine gidebiliriz.

Çok fazla PAGEIOLATCH_XX sahip olduğumuz bir örneği ele alalım. Bu, bir iş parçacığının, bir bellek bloğundan başka bir şey olmayan arabelleğe diskten veri sayfası okumasını beklediği anlamına gelir. Neler olduğunu anladığımızdan emin olmalıyız. Bu, mutlaka zayıf bir G/Ç alt sistemi veya yetersiz bellek anlamına gelmez ve G/Ç alt sistemini ve belleği artırmak sorunu çözecektir, ancak geçici olarak. Kalıcı bir çözüm bulmak için diskten neden bu kadar çok verinin okunduğunu görmemiz gerekiyor: Buna ne tür SQL komutları neden oluyor? where yan tümceleri gibi filtreler kullanarak daha az veri okumak yerine çok fazla veri mi okuyoruz? Tablo taramaları veya dizin taramaları nedeniyle çok fazla veri mi okunuyor? Mevcut dizinleri uygulayarak veya değiştirerek bunları dizin aramalarına dönüştürebilir miyiz? SQL Optimizer (SQL sunucu programımızın içindeki başka bir program) tarafından yanlış anlaşılan SQL sorguları mı yazıyoruz?

Çözümler bulmak için farklı açılardan düşünmemiz ve farklı test senaryoları kullanmamız gerekiyor. Yukarıdaki bekleme türlerinin her biri farklı bir çözüme ihtiyaç duyar. Bir veritabanı yöneticisinin herhangi bir işlem yapmadan önce bunları iyice araştırması gerekir. Ancak çoğu zaman sorunlu T-SQL sorguları bulmak ve bunları ayarlamak, sorunların yüzde 60 ila 70'ini çözecektir.

#2 Sorunlu Sorguları Bulma

Yukarıda belirtildiği gibi, yapabileceğimiz ilk şey sorunlu sorguları aramaktır. Aşağıdaki T-SQL kodu, en kötü performans gösteren 20 sorguyu bulacaktır:

 SELECT TOP 20 total_worker_time/execution_count AS Avg_CPU_Time ,Execution_count ,total_elapsed_time/execution_count as AVG_Run_Time ,total_elapsed_time ,(SELECT SUBSTRING(text,statement_start_offset/2+1,statement_end_offset ) FROM sys.dm_exec_sql_text(sql_handle) ) AS Query_Text FROM sys.dm_exec_query_stats ORDER BY Avg_CPU_Time DESC

Sonuçlara dikkat etmemiz gerekiyor; Bir sorgunun maksimum ortalama çalışma süresi olmasına rağmen, yalnızca bir kez çalıştırılırsa, sunucu üzerindeki toplam etkisi, ortalama ortalama çalışma süresine sahip ve günde birçok kez çalışan bir sorguya kıyasla düşüktür.

#3 İnce Ayar Sorguları

Bir T-SQL sorgusunun ince ayarı önemli bir kavramdır. Anlaşılması gereken temel şey, SQL iyileştiricisinin yapmasını istediğimiz şeyi yapmak için optimize edilmiş bir plan bulabilmesi için T-SQL sorguları yazıp dizinleri ne kadar iyi uygulayabileceğimizdir. SQL Server'ın her yeni sürümünde, optimize edilmemiş SQL sorguları yazarken yaptığımız hatalarımızı kapatacak ve ayrıca önceki optimize ediciyle ilgili tüm hataları düzeltecek daha karmaşık bir optimize edici elde ediyoruz. Ancak optimizer ne kadar zeki olursa olsun, ona ne istediğimizi söyleyemezsek (uygun bir T-SQL sorgusu yazarak), SQL optimizer işini yapamayacaktır.

SQL Server, gelişmiş arama ve sıralama algoritmaları kullanır. Arama ve sıralama algoritmalarında iyiysek, çoğu zaman SQL Server'ın neden belirli bir işlem yaptığını tahmin edebiliriz. Daha fazla bilgi edinmek ve bu tür algoritmaları anlamak için en iyi kitap Donald Knuth'un The Art of Computer Programming adlı kitabıdır.

İnce ayar yapılması gereken sorguları incelediğimizde, SQL sunucusunun bunları nasıl yorumladığını bulabilmemiz için bu sorguların yürütme planını kullanmamız gerekir.

Burada uygulama planının tüm yönlerini ele alamam, ancak temel düzeyde dikkate almamız gereken şeyleri açıklayabilirim.

• İlk önce hangi operatörlerin sorgu maliyetinin çoğunu aldığını bulmamız gerekiyor.
• Operatör çok masraf alıyorsa sebebini öğrenmemiz gerekiyor. Çoğu zaman, taramalar aramalardan daha fazla maliyet alacaktır. Dizin araması yerine neden belirli bir taramanın (tablo taraması veya dizin taraması) yapıldığını incelememiz gerekiyor. Bu sorunu tablo sütunlarına uygun dizinler uygulayarak çözebiliriz, ancak herhangi bir karmaşık programda olduğu gibi sabit bir çözüm yoktur. Örneğin, tablo küçükse, taramalar aramalardan daha hızlıdır.
• SQL Server yürütme planının çeşitli eylemlerini ve kararlarını temsil eden yaklaşık 78 operatör vardır. Bunları daha iyi anlayabilmemiz ve uygun eylemi gerçekleştirebilmemiz için Microsoft belgelerine başvurarak bunları derinlemesine incelememiz gerekir.

#4 Yürütme Planının Yeniden Kullanımı

Tablolara uygun indexler uygulasak ve iyi bir T-SQL kodu yazsak bile, yürütme planı tekrar kullanılmazsa performans sorunları yaşarız. Sorgulara ince ayar yaptıktan sonra, gerektiğinde yürütme planının yeniden kullanılabileceğinden emin olmamız gerekiyor. CPU zamanının çoğu, planı yeniden kullanırsak ortadan kaldırılabilecek yürütme planını hesaplamak için harcanacaktır.

Yürütme planının kaç kez yeniden kullanıldığını öğrenmek için aşağıdaki sorguyu kullanabiliriz, burada usecounts planın kaç kez yeniden kullanıldığını gösterir:

 SELECT [ecp].[refcounts] , [ecp].[usecounts] , [ecp].[objtype] , DB_NAME([est].[dbid]) AS [db_name] , [est].[objectid] , [est].[text] as [query_ext] , [eqp].[query_plan] FROM sys.dm_exec_cached_plans ecp CROSS APPLY sys.dm_exec_sql_text ( ecp.plan_handle ) est CROSS APPLY sys.dm_exec_query_plan ( ecp.plan_handle ) eqp

Yürütme planını yeniden kullanmanın en iyi yolu, parametreli saklı yordamları uygulamaktır. Saklı yordamları uygulayacak durumda olmadığımızda, SQL deyimlerindeki tek değişiklik parametre değerleri olduğunda T-SQL deyimlerini yürütmek için kullanılabilecek sp_executesql kullanabiliriz. SQL Server büyük olasılıkla ilk yürütmede oluşturduğu yürütme planını yeniden kullanır.

Yine, herhangi bir karmaşık bilgisayar programında olduğu gibi, sabit bir çözüm yoktur. Bazen planı yeniden derlemek daha iyidir.

Aşağıdaki iki örnek sorguyu inceleyelim:

• select name from table where name = 'sri';
• select name from table where name = 'pal';

name sütununda kümelenmemiş bir dizinimiz olduğunu ve tablonun yarısının sri değerine sahip olduğunu ve name sütununda birkaç satırda pal olduğunu varsayalım. İlk sorgu için SQL Server tablo taramasını kullanacak çünkü tablonun yarısı aynı değerlere sahip. Ancak ikinci sorgu için dizin taramasını kullanmak daha iyidir çünkü yalnızca birkaç satır pal değerine sahiptir.

Sorgular benzer olsa da, aynı yürütme planı iyi bir çözüm olmayabilir. Çoğu zaman farklı bir durum olacak, bu yüzden karar vermeden önce her şeyi dikkatlice analiz etmemiz gerekiyor. Yürütme planını yeniden kullanmak istemiyorsak, saklı yordamlarda her zaman “yeniden derleme” seçeneğini kullanabiliriz.

Saklı yordamları veya sp_executesql kullandıktan sonra bile, yürütme planının yeniden kullanılmayacağı zamanlar olduğunu unutmayın. Onlar:

• Sorgu tarafından kullanılan dizinler değiştiğinde veya bırakıldığında
• Sorgu tarafından kullanılan bir tablonun istatistikleri, yapısı veya şeması değiştiğinde
• “Yeniden derleme” seçeneğini kullandığımızda
• Çok sayıda ekleme, güncelleme veya silme olduğunda
• DDL ve DML'yi tek bir sorgu içinde karıştırdığımızda

#5 Gereksiz Dizinleri Kaldırma

Sorgulara ince ayar yaptıktan sonra, dizinlerin nasıl kullanıldığını kontrol etmemiz gerekiyor. Dizin bakımı, çok sayıda CPU ve G/Ç gerektirir. Bir veritabanına her veri eklediğimizde, SQL Server'ın da dizinleri güncellemesi gerekir, bu nedenle kullanılmadıkları takdirde bunları kaldırmak daha iyidir.

SQL sunucusu, dizin istatistiklerini bulmak için bize dm_db_index_usage_stats sağlar. Aşağıdaki T-SQL kodunu çalıştırdığımızda farklı indeksler için kullanım istatistiklerini alıyoruz. Hiç kullanılmayan veya nadiren kullanılan indeksler bulursak, performans kazanmak için onları bırakabiliriz.

 SELECT OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME], DB_NAME(IUS.database_id) AS [DATABASE NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = IUS.[OBJECT_ID] AND I.INDEX_ID = IUS.INDEX_ID

#6 SQL Server Kurulumu ve Veritabanı Kurulumu

Bir veritabanı kurarken, verileri ve günlük dosyalarını ayrı tutmamız gerekir. Bunun temel nedeni, veri dosyalarının yazılması ve erişilmesinin sıralı olmaması, günlük dosyalarının yazılmasının ve erişilmesinin sıralı olmasıdır. Bunları aynı sürücüye koyarsak optimize bir şekilde kullanamayız.

Depolama Alanı Ağı'nı (SAN) satın aldığımızda, bir satıcı bize bunun nasıl kurulacağı konusunda bazı önerilerde bulunabilir, ancak bu bilgi her zaman yardımcı olmaz. Verileri ve günlük dosyalarını ayrı ayrı ve optimize bir şekilde nasıl tutacağımız konusunda donanım ve ağ çalışanlarımızla ayrıntılı bir tartışma yapmamız gerekiyor.

#7 SQL Server'ı Aşırı Yüklemeyin

Herhangi bir veritabanı yöneticisinin birincil görevi, üretim sunucusunun sorunsuz çalıştığından ve müşterilere mümkün olduğunca iyi hizmet verdiğinden emin olmaktır. Bunu gerçekleştirmek için aşağıdaki ortamlar için ayrı veritabanları (mümkünse ayrı makinelerde) tutmamız gerekir:

• Üretim
• Gelişim
• Test yapmak
• Analitik

Bir üretim veritabanı için tam kurtarma modu olan bir veritabanına ihtiyacımız var ve diğer veritabanları için basit bir kurtarma modu yeterlidir.

Bir üretim veritabanında test yapmak, işlem günlüğüne, dizinlere, CPU ve G/Ç'ye çok fazla yük bindirecektir. Bu yüzden üretim, geliştirme, test ve analiz için ayrı veritabanları kullanmamız gerekiyor. Mümkünse, CPU ve G/Ç üzerindeki yükü azaltacağından, her veritabanı için ayrı makineler kullanın.

#8 İşlem günlüğü, tempdb ve bellek

Günlük dosyasındaki bir otomatik büyütme işlemi zaman alıcı olduğundan ve diğer işlemleri bu işlem tamamlanana kadar beklemeye zorlayabileceğinden, günlük dosyası normal işlemler için yeterli boş alana sahip olmalıdır. Her veritabanı için günlük dosyası boyutunu ve ne kadar kullanıldığını öğrenmek için DBCC SQLPERF(logspace) kullanabiliriz.

Tempdb'yi kurmanın en iyi yolu, onu ayrı bir diske koymaktır. Başlangıç ​​boyutunu karşılayabileceğimiz kadar büyük tutmalıyız çünkü otomatik büyüme durumuna ulaştığında performans düşecektir.

Daha önce de belirtildiği gibi, SQL sunucusunun ayrı bir makinede, tercihen üzerinde başka bir uygulama olmadan çalıştığından emin olmamız gerekiyor. İşletim sistemi için biraz bellek tutmamız gerekiyor, ayrıca bir kümenin parçasıysa biraz daha fazla bellek tutmamız gerekiyor, bu nedenle çoğu durumda 2 GB civarında olması gerekir.

Çözüm:

Burada tartışılan prosedürler ve öneriler yalnızca temel performans ayarlaması içindir. Bu adımları takip edersek, performansta ortalama yüzde 40 ila 50 civarında iyileşme elde edebiliriz. Gelişmiş SQL Server performans ayarlaması yapmak için burada kapsanan adımların her birini çok daha derine inmemiz gerekir.

Toptal Mühendislik Blogunda Daha Fazla Okuma:

• SQL Dizinleri ve Bölümleri ile Darboğazları Çözme
• Oracle'dan SQL Server'a ve SQL Server'dan Oracle'a Geçiş Kılavuzu