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

Makale
02/08/2024

Şunlar için geçerlidir: SQL Server

Bu makalede, Microsoft SQL Server çalıştıran bir bilgisayarda yüksek CPU kullanımının neden olduğu sorunları tanılamaya ve düzeltmeye yönelik yordamlar sağlanır. SQL Server'da yüksek CPU kullanımının birçok olası nedeni olsa da en yaygın nedenleri şunlardır:

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) 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ı kullanabilirsiniz.

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 * (CPU'lar olmadan) ise, 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.

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

Sqlservr.exe işlemi yüksek CPU kullanımına neden oluyorsa en yaygın neden tablo veya dizin taramaları gerçekleştiren SQL Server sorguları ve ardından sıralama, karma işlemleri ve döngüler (iç içe döngü işleci veya WHILE (T-SQL)) şeklindedir. Sorguların genel CPU kapasitesi dışında şu anda 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 yüksek CPU tüketimine sahip sorguları belirledikten sonra, bu sorgular tarafından kullanılan tabloların istatistiklerini güncelleştirin. Geçerli veritabanındaki tüm kullanıcı tanımlı ve iç tabloların istatistiklerini güncelleştirmek için sp_updatestats sistemi saklı yordamını kullanabilirsiniz. Örneğin:

exec sp_updatestats

Not

sp_updatestats sistemi saklı yordamı, geçerli veritabanındaki tüm kullanıcı tanımlı ve iç tablolara karşı UPDATE STATISTICS çalıştırır. Düzenli bakım için, düzenli olarak bakım zamanlamanın istatistikleri güncel tuttuğundan 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 ve istatistiği doğrusal bir eşikle güncelleştirmeyi otomatik olarak seçer.

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. 2. adımda tanımlanan bir veya daha fazla yüksek CPU sorgusuna bir RECOMPILE 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 altyapısı sorgunun yürütülmesini hızlandırmak için dizin aramasını kullanabildiğinde sorgudaki bir koşul SARGable (Search ARGument-able) 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, önce tablonun tüm satırlarını getirmeniz ve ardından karşılaştırma yapmadan önce işlevi uygulamanız gerekir. Tablodan tüm satırları getirmek, 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ütunlarda herhangi bir işlevin veya hesaplamanın uygulanması genellikle sorguyu sargable olmayan bir hâle getirir ve daha yüksek CPU tüketimine yol açar. Çözümler genellikle sorguların yaratıcı bir şekilde yeniden yazılmasını ve SARGable koşulunu sağlamayı içerir. Bu örneğin 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 yeniden yazma işlemidir:

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 WHERE veya JOIN yan tümcelerinde kullanılan CONVERT(), CAST(), ISNULL() ve COALESCE() 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 JOIN içindeki INT 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ği izlerseniz aşağıdaki olayların kullanılması yüksek CPU kullanımına neden olabilir:

XML olaylarını (query_plan_profile, query_post_compilation_showplan, query_post_execution_plan_profile, query_post_execution_showplan, query_pre_execution_showplan) sorgulama planı
Deyim düzeyinde olaylar (sql_statement_completed, sql_statement_starting, sp_statement_starting, sp_statement_completed)
Oturum açma ve oturum 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ı (o grupta denetlenen gruba ve 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 yüksek CPU kullanımını düzeltme

Spinlock çekişmesi nedeniyle yaygın olarak karşılaşılan 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, aşağıdaki makaleye bakın ve komutunu kullanarak izleme bayrağını T174DBCC TRACEON (174, -1) etkinleştirin:

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

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

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

SQL Server örneğiniz spinlock çekişmesi nedeniyle SOS_BLOCKALLOCPARTIALLIST rastgele yüksek CPU kullanımıyla karşılaşıyorsa, SQL Server 2019 için Toplu Güncelleştirme 21'i uygulamanızı öneririz. Sorunun nasıl çözüldüğü hakkında daha fazla bilgi için bkz. 2410400 hata başvurusu ve geçici azaltma sağlayan DBCC DROPCLEANBUFFERS .

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

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

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şmelerini tanılama ve çözme.

Adım 9: 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).

Adım 10: Sistemin ölçeğini büyüterek daha fazla CPU kullanma

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 büyütmeyi göz önünde bulundurun. Yürütme başına belirli bir ortalama ve maksimum CPU tüketimi eşiğini aşan ve sistemde birçok kez çalıştırılan sorgu sayısını bulmak için aşağıdaki sorguyu kullanın (iki 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