SQL Server'daki yüksek CPU kullanımı sorunlarını giderme

Şunlar için geçerlidir: SQL Server

Summary

Microsoft SQL Server yüksek CPU kullanımı (sqlservr.exeaşırı işlemci süresi tüketen işlem), genellikle verimsiz sorgulardan, eksik dizinlerden, güncel olmayan istatistiklerden, parametreye duyarlı planlardan veya iş yükündeki artıştan kaynaklanabilir. Bu makalede, önce cpu baskısının kaynağının SQL Server olduğunu onaylamak ve ardından en çok CPU kullanan sorguları belirlemek için sıralı bir sorun giderme yordamında size yol gösterir. Ardından istatistikleri güncelleştirme, eksik dizin ekleme, parametre algılama ve SARGability sorunlarını çözme, ağır izlemeyi devre dışı bırakma ve spinlock çekişmesini azaltma gibi hedeflenen düzeltmeleri açıklar. Son olarak, Windows güç planı da dahil olmak üzere işletim sistemi ve sanal makine ayarlamayı ve daha fazla CPU'ya ne zaman ölçeklendirileceklerine ilişkin yönergeleri kapsar. Sürekli CPU kullanımını azaltmak, sorgu performansını geri yüklemek ve donanım ölçeğini artırmanın ne zaman gerekli olduğuna karar vermek için bu adımları kullanın.

SQL Server yüksek CPU kullanımının yaygın nedenleri

SQL Server yüksek CPU kullanımı için birçok olası neden olsa da, aşağıdaki nedenler en yaygın nedenlerdir:

Aşağıdaki koşullar nedeniyle tablo veya dizin taramalarından kaynaklanan yüksek mantıksal okumalar:
- Güncel olmayan istatistikler
- Eksik dizinler
- Parametreye duyarlı plan (PSP) ile ilgili sorunlar
- Kötü tasarlanmış sorgular
İş yükünde artış

SQL Server yüksek CPU kullanımı sorunlarını gidermek için aşağıdaki adımları kullanın.

1. Adım: SQL Server'ın yüksek CPU kullanımına neden olduğunu doğrulayın

SQL Server işleminin yüksek CPU kullanımına gerçekten katkıda bulunup bulunmadığını denetlemek için aşağıdaki araçlardan birini kullanın:

Görev Yöneticisi: İşlem sekmesinde, SQL Server Windows NT-64 Bit için CPU sütun değerinin yüzde 100'e yakın olup olmadığını denetleyin.
Performans ve Kaynak İzleyicisi (perfmon)
- Sayaç: Process/%User Time, % Privileged Time
- Örnek: sqlservr
Sayaç verilerini 60 saniyelik bir süre boyunca toplamak için aşağıdaki PowerShell betiğini kullanabilirsiniz:
```
$serverName = $env:COMPUTERNAME
$Counters = @(
    ("\\$serverName" + "\Process(sqlservr*)\% User Time"), ("\\$serverName" + "\Process(sqlservr*)\% Privileged Time")
)
Get-Counter -Counter $Counters -MaxSamples 30 | ForEach {
    $_.CounterSamples | ForEach {
        [pscustomobject]@{
            TimeStamp = $_.TimeStamp
            Path = $_.Path
            Value = ([Math]::Round($_.CookedValue, 3))
        }
        Start-Sleep -s 2
    }
}
```
Tutarlı olarak yüzde 90'dan büyükse % User Time (% Kullanıcı Süresi her işlemcideki işlemci süresinin toplamıdır), en yüksek değeri %100 * 'dir (CPU'lar olmadan)), SQL Server işlemi yüksek CPU kullanımına neden olur. Ancak % Privileged time sürekli olarak yüzde 90'dan büyükse virüsten koruma yazılımınız, diğer sürücüleriniz veya bilgisayardaki başka bir işletim sistemi bileşeni yüksek CPU kullanımına katkıda bulunur. Bu davranışın kök nedenini analiz etmek için sistem yöneticinizle birlikte çalışmanız gerekir.
Performans Panosu: SQL Server Management Studio'deSQLServerInstance'a< sağ tıklayın> ve Raporlar>Standart Raporlar>Performans Panosu'nu seçin.

Panoda, çubuk grafikli Sistem CPU Kullanımı başlıklı bir grafik gösterilir. Koyu renk SQL Server altyapısı CPU kullanımını gösterirken, açık renk genel işletim sistemi CPU kullanımını temsil eder (başvuru için grafikteki göstergeye bakın). Güncelleştirilmiş kullanımı görmek için döngüsel yenileme düğmesini veya F5'i seçin.

2. Adım: CPU kullanımına katkıda bulunan sorguları belirleme

Sqlservr.exe işlemi büyük ölçüde yüksek CPU kullanımına neden oluyorsa, bunun en yaygın nedeni tablo veya dizin taramaları gerçekleştiren SQL Server sorgularıdır, ardından sıralama ve karma işlemleri ile döngüler (iç içe döngü işleci veya WHILE (T-SQL)) gelir. Sorguların şu anda genel CPU kapasitesinden ne kadar CPU kullandığı hakkında bir fikir edinmek için aşağıdaki deyimi çalıştırın:

DECLARE @init_sum_cpu_time int,
        @utilizedCpuCount int 
--get CPU count used by SQL Server
SELECT @utilizedCpuCount = COUNT( * )
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE' 
--calculate the CPU usage by queries OVER a 5 sec interval 
SELECT @init_sum_cpu_time = SUM(cpu_time) FROM sys.dm_exec_requests
WAITFOR DELAY '00:00:05'
SELECT CONVERT(DECIMAL(5,2), ((SUM(cpu_time) - @init_sum_cpu_time) / (@utilizedCpuCount * 5000.00)) * 100) AS [CPU from Queries as Percent of Total CPU Capacity] 
FROM sys.dm_exec_requests

Şu anda yüksek CPU etkinliğinden sorumlu sorguları tanımlamak için aşağıdaki deyimi çalıştırın:

SELECT TOP 10 s.session_id,
           r.status,
           r.cpu_time,
           r.logical_reads,
           r.reads,
           r.writes,
           r.total_elapsed_time / (1000 * 60) 'Elaps M',
           SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
           ((CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.TEXT)
                ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2) + 1) AS statement_text,
           COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
           + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
           r.command,
           s.login_name,
           s.host_name,
           s.program_name,
           s.last_request_end_time,
           s.login_time,
           r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

Şu anda sorgular CPU'yu yönlendirmiyorsa CPU'ya bağlı geçmiş sorguları aramak için aşağıdaki deyimi çalıştırabilirsiniz:

SELECT TOP 10  qs.last_execution_time, st.text AS batch_text,
    SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
    (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
    (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
    (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC

3. Adım: İstatistikleri güncelleştirme

En çok CPU kullanan sorguları belirledikten sonra, bu sorguların kullandığı tabloların istatistiklerini güncelleştirin . Geçerli veritabanındaki sp_updatestats tüm kullanıcı tanımlı ve iç tabloların istatistiklerini güncelleştirmek için sistem saklı yordamını kullanın. Örneğin:

exec sp_updatestats

Not

sp_updatestats sistemi depolanmış yordamı, geçerli veritabanındaki tüm kullanıcı tanımlı ve iç tablolar için UPDATE STATISTICS çalıştırır. Düzenli bakım için zamanlanmış bakımınızın istatistikleri güncel tuttuğunuzdan emin olun. Dizin birleştirmeyi ve bir veya daha fazla veritabanının istatistik güncelleştirmelerini otomatik olarak yönetmek için Uyarlamalı Dizin Birleştirme gibi çözümleri kullanın. Bu yordam, diğer parametrelerin yanında bir dizini parçalanma düzeyine göre yeniden derlemeyi veya yeniden düzenlemeyi otomatik olarak seçer ve istatistikleri doğrusal eşikle güncelleştirir.

sp_updatestats hakkında daha fazla bilgi için bkz. sp_updatestats.

SQL Server hala aşırı CPU kapasitesi kullanıyorsa sonraki adıma geçin.

4. Adım: Eksik dizinleri ekleme

Dizinlerin eksik olması, sorguların daha yavaş çalıştırılmasına ve yüksek CPU kullanımına yol açabilir. Bu performans etkisini geliştirmeye yardımcı olmak için eksik dizinleri tanımlayabilir ve oluşturabilirsiniz.

Yüksek CPU kullanımına neden olan ve sorgu planında en az bir eksik dizin içeren sorguları belirlemek için aşağıdaki sorguyu çalıştırın:

-- Captures the Total CPU time spent by a query along with the query plan and total executions
SELECT
    qs_cpu.total_worker_time / 1000 AS total_cpu_time_ms,
    q.[text],
    p.query_plan,
    qs_cpu.execution_count,
    q.dbid,
    q.objectid,
    q.encrypted AS text_encrypted
FROM
    (SELECT TOP 500 qs.plan_handle,
     qs.total_worker_time,
     qs.execution_count FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS qs_cpu
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE p.query_plan.exist('declare namespace 
        qplan = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        //qplan:MissingIndexes')=1

Tanımlanan sorguların yürütme planlarını gözden geçirin ve gerekli değişiklikleri yaparak sorguyu ayarlayın. Aşağıdaki ekran görüntüsünde, SQL Server sorgunuz için eksik bir dizine işaret edeceği bir örnek gösterilmektedir. Sorgu planının Eksik dizin bölümüne sağ tıklayın ve dizini SQL Server Management Studio'daki başka bir pencerede oluşturmak için Eksik Dizin Ayrıntıları'nı seçin.

Eksik dizinleri denetlemek ve yüksek iyileştirme ölçüsü değerlerine sahip önerilen dizinleri uygulamak için aşağıdaki sorguyu kullanın. En yüksek improvement_measure değerine sahip çıktıdaki ilk 5 veya 10 öneriyle başlayın. Bu dizinler performans üzerindeki en önemli olumlu etkiye sahiptir. Bu dizinleri uygulamak isteyip istemediğinize karar verin ve uygulama için performans testinin yapıldığından emin olun. Ardından, istenen uygulama performansı sonuçlarını elde edene kadar eksik dizin önerilerini uygulamaya devam edin. Bu konu hakkında daha fazla bilgi için bkz. Kümelenmemiş dizinleri eksik dizin önerileriyle ayarlama.

SELECT CONVERT(VARCHAR(30), GETDATE(), 126) AS runtime,
    mig.index_group_handle,
    mid.index_handle,
    CONVERT(DECIMAL(28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
    'CREATE INDEX missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns,
        '') + CASE WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(mid.inequality_columns,
        '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')',
        '') AS create_index_statement,
    migs.*,
    mid.database_id,
    mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (DECIMAL (28, 1),
               migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

5. Adım: Parametreye duyarlı sorunları araştırma ve çözme

DBCC FREEPROCCACHE komutunu kullanarak plan önbelleğini serbest bırakabilirsiniz ve bunun yüksek CPU kullanımı sorununu çözüp çözmediğini de kontrol edebilirsiniz. Sorun giderildiyse parametreye duyarlı bir sorunun göstergesidir (PSP, "parametre algılama sorunu" olarak da bilinir).

Not

DBCC FREEPROCCACHE öğesini parametresiz kullanmak, tüm derlenmiş planları plan önbelleğinden kaldırır. Bu, yeni sorgu yürütmelerinin yeniden derlenmesine neden olur ve bu da her yeni sorgu için bir kez daha uzun süreye yol açar. En iyi yaklaşım, soruna hangi sorgunun neden olabileceğini belirlemek için DBCC FREEPROCCACHE ( plan_handle | sql_handle ) kullanmak ve ardından tek tek sorguyu veya sorguları ele almaktır.

Parametreye duyarlı sorunları azaltmak için aşağıdaki yöntemleri kullanın. Her yöntemin ilişkili ödünleri ve dezavantajları vardır.

RECOMPILE sorgu ipucunu kullanın. RECOMPILE tanımlanan bir veya daha fazla yüksek CPU sorgusuna bir sorgu ipucu ekleyebilirsiniz. Bu ipucu, her sorgu yürütmesi için daha iyi bir performansla, derleme CPU kullanımındaki küçük artışı dengelemeye yardımcı olur. Daha fazla bilgi için bkz. Parametreler ve Yürütme Planı Yeniden Kullanımı, Parametre Duyarlılığı ve RECOMPILE sorgu ipucu.

Bu ipucunu sorgunuza nasıl uygulayabileceğinize dair bir örnek aşağıda verilmiştir.
```
SELECT * FROM Person.Person 
WHERE LastName = 'Wood'
OPTION (RECOMPILE)
```
Gerçek parametre değerini, verilerdeki çoğu değeri kapsayan daha tipik bir parametre değeriyle geçersiz kılmak için OPTIMIZE FOR sorgu ipucunu kullanın. Bu seçenek, en uygun parametre değerlerinin ve ilişkili plan özelliklerinin tam olarak anlaşılmasını gerektirir. Burada, sorgunuzda bu ipucunun nasıl kullanılacağına dair bir örnek verilmiştir.
```
DECLARE @LastName Name = 'Frintu'
SELECT FirstName, LastName FROM Person.Person 
WHERE LastName = @LastName
OPTION (OPTIMIZE FOR (@LastName = 'Wood'))
```
Gerçek parametre değerini yoğunluk vektör ortalamasıyla geçersiz kılmak için OPTIMIZE FOR UNKNOWN sorgu ipucunu kullanın. Bunu, yerel değişkenlerdeki gelen parametre değerlerini yakalayarak ve sonra parametrelerin kendisini kullanmak yerine koşul içindeki yerel değişkenleri kullanarak da yapabilirsiniz. Bu düzeltme için ortalama yoğunluk, kabul edilebilir performans sağlamak için yeterli olabilir.
Parametre algılamayı tamamen devre dışı bırakmak için DISABLE_PARAMETER_SNIFFING sorgu ipucunu kullanın. Sorguda nasıl kullanılacağına yönelik bir örnek aşağıda verilmiştir:
```
SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))
```
Önbellekte yeniden derlemeleri önlemek için KEEPFIXED PLAN sorgu ipucunu kullanın. Bu geçici çözüm, "yeterince iyi" ortak planın zaten önbellekte olan plan olduğunu varsayar. Ayrıca, iyi planın çıkarılma ve yeni bir hatalı planın derlenme olasılığını azaltmak için otomatik istatistik güncelleştirmelerini devre dışı bırakabilirsiniz.
Uygulama kodu düzeltilene kadar DBCC FREEPROCCACHE komutunu geçici bir çözüm olarak kullanın. DBCC FREEPROCCACHE (plan_handle) komutunu kullanarak yalnızca soruna neden olan planı kaldırabilirsiniz. Örneğin AdventureWorks'te Person.Person tablosuna başvuran sorgu planlarını bulmak için bu sorguyu kullanarak sorgu tutamacını bulabilirsiniz. Ardından, sorgu sonuçlarının ikinci sütununda oluşturulan DBCC FREEPROCCACHE (plan_handle) öğesini kullanarak belirli sorgu planını önbellekten serbest bırakabilirsiniz.
```
SELECT text, 'DBCC FREEPROCCACHE (0x' + CONVERT(VARCHAR (512), plan_handle, 2) + ')' AS dbcc_freeproc_command FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%person.person%'
```

6. Adım: SARGability sorunlarını araştırma ve çözme

SQL Server motoru, sorgunun yürütülmesini hızlandırmak için bir dizin araması kullanabildiğinde, bir sorgudaki koşul SARGable (Arama ARGument-mümkün) olarak kabul edilir. Birçok sorgu tasarımı, SARGability'yi engeller ve tablo veya dizin taramalarına ve yüksek CPU kullanımına yol açar. Her bir ProductNumber öğesinin alınması ve SUBSTRING() işlevinin bir dize değişmez değeriyle karşılaştırılmadan önce bu veri tabanına uygulanması gereken AdventureWorks veri tabanında aşağıdaki sorguyu göz önünde bulundurun. Gördüğünüz gibi, karşılaştırma yapmadan önce tablonun tüm satırlarını getirmeniz ve ardından işlevi uygulamanız gerekir. Tablodan tüm satırların getirilmesi, daha yüksek CPU kullanımına yol açan bir tablo veya dizin taraması anlamına gelir.

SELECT ProductID, Name, ProductNumber
FROM [Production].[Product]
WHERE SUBSTRING(ProductNumber, 0, 4) =  'HN-'

Arama koşulundaki sütunlara herhangi bir işlev veya hesaplama sonucu uygulamak, genellikle sorguyu değiştirilemez hale getirir ve daha yüksek CPU tüketimine yol açar. Çözümler tipik olarak, SARGable yapmak için sorguları yaratıcı bir şekilde yeniden yazmayı içerir. Bu örneğe olası bir çözüm, işlevin sorgu koşulundan kaldırıldığı, başka bir sütunun arandığı ve aynı sonuçların elde edildiği bu yeniden yazmadır:

SELECT ProductID, Name, ProductNumber
FROM [Production].[Product]
WHERE Name LIKE  'Hex%'

Bir satış yöneticisinin büyük siparişlerde %10 satış komisyonu vermek isteyebileceği ve hangi siparişlerin 300 ABD dolarından büyük komisyona sahip olacağını görmek istediği başka bir örnek aşağıda verilmiştir. İşte bunu yapmak için mantıklı ama sargable olmayan bir yol.

SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice * 0.10 > 300

Hesaplamanın koşulun diğer tarafına taşındığı sorgunun daha az sezgisel ancak SARGable olan yeniden yazılabilir bir örneği aşağıdadır.

SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice > 300/0.10

SARGability yalnızca WHERE yan tümceleri için değil, aynı zamanda JOINs, HAVING, GROUP BY ve ORDER BY yan tümceleri için de geçerlidir. Sorgularda SARGability koşulunun önlenmesinin sık karşılaşılan örnekleri, sütunların taranmasına neden olan CONVERT() veya CAST() yan tümcelerinde kullanılan ISNULL(), COALESCE(), WHERE ve JOIN işlevlerini içerir. Veri türü dönüştürme olaylarında (CONVERT veya CAST) çözüm, aynı veri türlerini karşılaştırdığınızdan emin olmak olabilir. Aşağıda, T1.ProdID sütununun bir INT içindeki JOIN veri türüne açıkça dönüştürüldüğü bir örnek verilmiştir. Dönüştürme, birleştirme sütununda dizin kullanımını geçersiz kılar. Aynı sorun, veri türlerinin farklı olduğu ve SQL Server'ın birleştirmeyi gerçekleştirmek için bunlardan birini dönüştürdüğü örtük dönüştürmede de oluşur.

SELECT T1.ProdID, T1.ProdDesc
FROM T1 JOIN T2 
ON CONVERT(int, T1.ProdID) = T2.ProductID
WHERE t2.ProductID BETWEEN 200 AND 300

T1 tablosunun taranmasını önlemek için, düzgün planlama ve tasarımdan sonra ProdID sütununun temel veri türünü değiştirebilir ve ardından convert işlevini ON T1.ProdID = T2.ProductID kullanmadan iki sütunu birleştirebilirsiniz.

Bir diğer çözüm de T1 içinde aynı CONVERT() işlevi kullanan hesaplanan bir sütun oluşturmak ve ardından bu sütun üzerinde bir dizin oluşturmaktır. Bu, sorgu iyileştiricinin sorgunuzu değiştirmenize gerek kalmadan bu dizini kullanmasına olanak sağlar.

ALTER TABLE dbo.T1  ADD IntProdID AS CONVERT (INT, ProdID);
CREATE INDEX IndProdID_int ON dbo.T1 (IntProdID);

Bazı durumlarda sorgular SARGability koşuluna izin verecek şekilde kolayca yeniden yazılamaz. Bu gibi durumlarda, üzerinde bir dizin bulunan hesaplanan sütunun yardımcı olup olmadığını görün veya sorguyu daha yüksek CPU senaryolarına yol açabileceği farkındalığıyla olduğu gibi tutun.

7. Adım: Ağır izlemeyi devre dışı bırakma

SQL Server performansını etkileyen ve yüksek CPU kullanımına neden olan SQL İzleme veya XEvent izlemesini denetleyin. Örneğin, ağır SQL Server etkinliğini izlerseniz aşağıdaki olayların kullanılması yüksek CPU kullanımına neden olabilir:

Sorgu planı XML olayları (query_plan_profile, query_post_compilation_showplan, query_post_execution_plan_profile, query_post_execution_showplan, query_pre_execution_showplan)
Deyim düzeyinde olaylar (sql_statement_completed, sql_statement_starting, sp_statement_starting, sp_statement_completed)
Oturum açma ve oturumu kapatma olayları (login, process_login_finish, login_event, logout)
Kilit olayları (lock_acquired, lock_cancel, lock_released)
Bekleme olayları (wait_info, wait_info_external)
SQL Denetim olayları (denetlenen gruba ve o gruptaki SQL Server etkinliğine bağlı olarak)

Etkin XEvent veya Sunucu izlemelerini tanımlamak için aşağıdaki sorguları çalıştırın:

PRINT '--Profiler trace summary--'
SELECT traceid, property, CONVERT(VARCHAR(1024), value) AS value FROM::fn_trace_getinfo(
    default)
GO
PRINT '--Trace event details--'
SELECT trace_id,
    status,
    CASE WHEN row_number = 1 THEN path ELSE NULL end AS path,
    CASE WHEN row_number = 1 THEN max_size ELSE NULL end AS max_size,
    CASE WHEN row_number = 1 THEN start_time ELSE NULL end AS start_time,
    CASE WHEN row_number = 1 THEN stop_time ELSE NULL end AS stop_time,
    max_files,
    is_rowset,
    is_rollover,
    is_shutdown,
    is_default,
    buffer_count,
    buffer_size,
    last_event_time,
    event_count,
    trace_event_id,
    trace_event_name,
    trace_column_id,
    trace_column_name,
    expensive_event
FROM
    (SELECT t.id AS trace_id,
     row_number() over(PARTITION BY t.id order by te.trace_event_id, tc.trace_column_id) AS row_number,
     t.status,
     t.path,
     t.max_size,
     t.start_time,
     t.stop_time,
     t.max_files,
     t.is_rowset,
     t.is_rollover,
     t.is_shutdown,
     t.is_default,
     t.buffer_count,
     t.buffer_size,
     t.last_event_time,
     t.event_count,
     te.trace_event_id,
     te.name AS trace_event_name,
     tc.trace_column_id,
     tc.name AS trace_column_name,
     CASE WHEN te.trace_event_id in (23, 24, 40, 41, 44, 45, 51, 52, 54, 68, 96, 97, 98, 113, 114, 122, 146, 180) THEN CAST(1 as bit) ELSE CAST(0 AS BIT) END AS expensive_event FROM sys.traces t CROSS APPLY::fn_trace_geteventinfo(t.id) AS e JOIN sys.trace_events te ON te.trace_event_id = e.eventid JOIN sys.trace_columns tc ON e.columnid = trace_column_id) AS x
GO
PRINT '--XEvent Session Details--'
SELECT sess.NAME 'session_name', event_name, xe_event_name, trace_event_id,
    CASE WHEN xemap.trace_event_id IN(23, 24, 40, 41, 44, 45, 51, 52, 54, 68, 96, 97, 98, 113, 114, 122, 146, 180) 
    THEN Cast(1 AS BIT)
ELSE Cast(0 AS BIT)
END AS expensive_event
FROM sys.dm_xe_sessions sess
JOIN sys.dm_xe_session_events evt
ON sess.address = evt.event_session_address
INNER JOIN sys.trace_xe_event_map xemap
ON evt.event_name = xemap.xe_event_name
GO

8. Adım: Spinlock çekişmesi nedeniyle oluşan yüksek CPU kullanımını düzeltme

Spinlock çekişmesi nedeniyle oluşan yaygın yüksek CPU kullanımını çözmek için aşağıdaki bölümlere bakın.

SOS_CACHESTORE spinlock çekişmesi

SQL Server örneğinizde yoğun SOS_CACHESTORE spinlock çekişmesi yaşanıyorsa veya sorgu planlarınızın genellikle planlanmamış sorgu iş yüklerinde kaldırıldığını fark ederseniz, komutunu kullanarak aşağıdaki makaleye bakın ve izleme bayrağını T174DBCC TRACEON (174, -1) etkinleştirin:

DÜZELTME: Geçici SQL Server plan önbelleğinde SOS_CACHESTORE spinlock çekişmesi, SQL Server'da yüksek CPU kullanımına neden olur.

Yüksek CPU durumu T174 kullanılarak çözülürse bunu, SQL Server Yapılandırma Yöneticisi kullanarak başlangıç parametresi olarak etkinleştirin.

Büyük bellekli makinelerde SOS_BLOCKALLOCPARTIALLIST spinlock çekişmesi nedeniyle rastgele yüksek CPU kullanımı

Büyük bellekli makinelerde spinlock çekişmesi CPU SOS_BLOCKALLOCPARTIALLIST kullanımında rastgele ani artışlar oluşturabilir. Tek, sunucu genelindeki kısmi blok ayırma listesindeki çekişmesini birden çok liste arasında bölümleyerek azaltmak için aşağıdaki adımları kullanın.

Çekişme onaylayın. Spinlock'ta sys.dm_os_spinlock_stats yükseltilmiş collisions ve spins değerleri denetleyinSOS_BLOCKALLOCPARTIALLIST. Çekişme CPU ani artışlarıyla bağıntılıysa aşağıdaki adımlarla devam edin.
CPU ani artışını geçici olarak azaltın. Çalıştırma DBCC DROPCLEANBUFFERS , kısmi blok ayırma listesini serbest bırakır ve kalıcı düzeltmeyi planlarken geçici azaltma sağlar.
SQL Server derlemenizin düzeltmeyi içerdiğinden emin olun. Bölümleme davranışı 8142 ve 8145 izleme bayrakları aracılığıyla teslim edilir ve ilk olarak 2410400 hatasını gidermek için SQL Server 2019 Toplu Güncelleştirme 21'de kullanıma sunulmuştur. Daha eski bir derlemedeyseniz izleme bayraklarını etkinleştirmeden önce sürümünüz için en son toplu güncelleştirmeyi uygulayın.
İzleme bayrağı 8142'yi etkinleştirin. İzleme bayrağı 8142'yi genel başlangıç parametresi olarak etkinleştirin. Bu izleme bayrağı, spinlock korumalı listeyi CPU'ya göre bölümleyerek 64 bölüme kadar çıkarır ve bu genellikle çekişme ortadan kaldırılması için yeterlidir.
Çekişme devam ederse izleme bayrağı 8145'i etkinleştirin. 64 CPU bölümünün yeterli olmadığı sistemlerde ayrıca genel başlangıç parametresi olarak izleme bayrağı 8145'i etkinleştirin. İzleme bayrağı 8145, izleme bayrağı 8142 tarafından etkinleştirilen bölümleme işlemini CPU yerine soft-NUMA düğümü başına olacak şekilde değiştirir. İzleme bayrağı 8142 de etkinleştirilmediği sürece hiçbir etkisi olmaz.
Başlangıç izleme bayraklarının etkili olması için SQL Server hizmetini yeniden başlatın, ardından çekişmenin düştüğünü ve CPU kullanımının kararlı olduğunu onaylamak için yeniden denetleyinsys.dm_os_spinlock_stats.

Üst düzey makinelerde XVB_list'da spinlock çekişmesi nedeniyle yüksek CPU kullanımı

SQL Server örneğiniz yüksek yapılandırma makinelerinde XVB_LIST (çok sayıda yeni nesil işlemciye (CPU) sahip üst düzey sistemler) spinlock çekişmesi nedeniyle yüksek CPU senaryosuyla karşılaşıyorsa, TF8101 ile birlikte TF8102 izleme bayrağını etkinleştirin.

Not

Yüksek CPU kullanımı, diğer birçok spinlock türünde spinlock çekişmesiyle sonuçlanabilir. Spinlock'lar hakkında daha fazla bilgi için bkz . SQL Server'da spinlock çekişmesi tanılama ve çözme.

9. Adım: Güç planı ayarlarınızı işletim sistemi düzeyinde denetleyin

SQL Server iş yükleri düşük performansla karşılaşabilir ve Windows varsayılan Dengeli güç planıyla yapılandırıldığında sistemde yüksek CPU'ya neden olabilir. Dengeli güç planı ayarı, enerji tasarrufu sağlamak için CPU saat hızını düşürebilir. Örneğin, 3,00 GHz olarak derecelendirilmiş bir işlemci 1,2 GHz'e kadar azaltabilir. Sonuç olarak, genellikle yaklaşık 30% CPU kullanan iş yükleri, azalan saat hızı nedeniyle 100% kullanımına ulaşabilir. İşlem yoğunluklu SQL Server iş yüklerinde tutarlı ve en iyi performansı korumak için sistemi Yüksek Performanslı güç planını kullanacak şekilde yapılandırın. Bu ayar, CPU'ların tam hızlarda çalışabilmesini sağlar ve performans sorunlarını önlemeye yardımcı olur. Daha fazla bilgi için bkz . Dengeli güç planını kullanırken Windows Server'da yavaş performans.

10. Adım: Sanal makinenizi yapılandırma

Sanal makine kullanıyorsanız CPU'ları fazla sağlamadığınızdan ve bunların doğru yapılandırıldığından emin olun. Daha fazla bilgi için bkz. ESX/ESXi sanal makine performansı sorunlarını giderme (2001003).

11. Adım: Daha fazla CPU kullanmak için sistemin ölçeğini artırma

Tek tek sorgu örnekleri çok az CPU kapasitesi kullanıyorsa ancak tüm sorguların genel iş yükü yüksek CPU tüketimine neden oluyorsa, daha fazla CPU ekleyerek bilgisayarınızın ölçeğini artırmayı göz önünde bulundurun. Yürütme başına belirli bir ortalama eşiği ve maksimum CPU tüketimini aşan sorgu sayısını bulmak ve sistemde birçok kez çalıştırmak için aşağıdaki sorguyu kullanın. İki değişkenin değerlerini ortamınızla eşleşecek şekilde değiştirdiğinizden emin olun:

-- Shows queries where Max and average CPU time exceeds 200 ms and executed more than 1000 times
DECLARE @cputime_threshold_microsec INT = 200*1000
DECLARE @execution_count INT = 1000
SELECT qs.total_worker_time/1000 total_cpu_time_ms,
       qs.max_worker_time/1000 max_cpu_time_ms,
       (qs.total_worker_time/1000)/execution_count average_cpu_time_ms,
       qs.execution_count,
       q.[text]
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
WHERE (qs.total_worker_time/execution_count > @cputime_threshold_microsec
        OR qs.max_worker_time > @cputime_threshold_microsec )
        AND execution_count > @execution_count
ORDER BY  qs.total_worker_time DESC

Geri Bildirim

Bu sayfayı yararlı buldunuz mu?

Last updated on 2026-08-13