SQL Server’da DMV’leri Kullanarak Yavaş SQL Sorguları Nasıl Bulunur?

Dinamik yönetim görünümleri ((Dynamic management views)(DMV’ler)) Nedir? SQL Server 2005’te tanıtılan Dinamik yönetim görünümleri (DMV’ler), SQL Server’ın önemli özellikleridir. Yürütme planları, sorgu istatistikleri, son sorgular vb. hakkında veri sağlayan birkaç DMV vardır. Bunlar, bir SQL Server örneğinde neler olup bittiğini belirlemek için birlikte kullanılabilir. Bu gönderide, kaynaklarınızın nereye harcandığını ve çok daha fazlasını belirlemenize yardımcı olabilecek bazı…

May 22, 2023 by Aryasoft IT

Dinamik yönetim görünümleri ((Dynamic management views)(DMV’ler)) Nedir?

SQL Server 2005’te tanıtılan Dinamik yönetim görünümleri (DMV’ler), SQL Server’ın önemli özellikleridir.

Yürütme planları, sorgu istatistikleri, son sorgular vb. hakkında veri sağlayan birkaç DMV vardır. Bunlar, bir SQL Server örneğinde neler olup bittiğini belirlemek için birlikte kullanılabilir.

Bu gönderide, kaynaklarınızın nereye harcandığını ve çok daha fazlasını belirlemenize yardımcı olabilecek bazı yararlı sorguları listeleyeceğim.

NOT: Sorgular şunlar için geçerlidir: SQL Server 2014 (12.x) ve sonrası.

Top TSQL by reads

Storage okumaları SQL’in yapabileceği en yavaş işlemdir. Bu nedenle, ayarlama yaparken, en fazla mantıksal okumaya neden olan TSQL çağrılarına odaklanmak genellikle mantıklıdır.

Depolama erişimi azaltılırsa SQL daha az CPU’ya ihtiyaç duyar ve sorgunun süresi artar.

Mantıksal (depolama) okumalara göre en pahalı 10 TSQL çağrısını elde etmek için aşağıdaki sorguyu çalıştırın:

SELECT TOP(10) DB_NAME(t.[dbid]) AS [Database],

REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),”), CHAR(13),”) AS [ShortQueryTXT],

qs.total_logical_reads AS [TotalLogicalReads],

qs.min_logical_reads AS [MinLogicalReads],

qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],

qs.max_logical_reads AS [MaxLogicalReads],

qs.min_worker_time AS [MinWorkerTime],

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],

qs.max_worker_time AS [MaxWorkerTime],

qs.min_elapsed_time AS [MinElapsedTime],

qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],

qs.max_elapsed_time AS [MaxElapsedTime],

qs.execution_count AS [ExecutionCount],

CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N’%%’ THEN 1 ELSE 0 END AS [HasMissingIX],

qs.creation_time AS [CreationTime]

,t.[text] AS [Complete Query Text], qp.query_plan AS [QueryPlan]

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp

ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE)

Top TSQL by CPU

Toplam Çalışan Süresi en yüksek toplam CPU döngüsü anlamına gelir.

En pahalı ilk 10 TSQL CPU tüketicisini bulmak için aşağıdaki sorguyu çalıştırın.

SELECT TOP(10) DB_NAME(t.[dbid]) AS [Database],

REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),”), CHAR(13),”) AS [ShortQueryText],

qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [MinWorkerTime],

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],

qs.max_worker_time AS [MaxWorkerTime],

qs.min_elapsed_time AS [MinElapsedTime],

qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],

qs.max_elapsed_time AS [MaxElapsedTime],

qs.min_logical_reads AS [MinLogicalReads],

qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],

qs.max_logical_reads AS [MaxLogicalReads],

qs.execution_count AS [ExecutionCount],

CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N’%%’ THEN 1 ELSE 0 END AS [HasMissingIX],

qs.creation_time AS [CreationTime]

,t.[text] AS [Query Text], qp.query_plan AS [QueryPlan]

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp

ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE)

Top TSQL by execution count

En iyi 10 TSQL çağrısını almak için aşağıdaki sorguyu çalıştırın.

SELECT TOP(10) LEFT(t.[text], 50) AS [ShortQueryText],

qs.execution_count AS [ExecutionCount],

qs.total_logical_reads AS [TotalLogicalReads],

qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],

qs.total_worker_time AS [TotalWorkerTime],

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],

qs.total_elapsed_time AS [TotalElapsedTime],

qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],

CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N’%%’ THEN 1 ELSE 0 END AS [HasMissingIX],

qs.creation_time AS [CreationTime]

,t.[text] AS [CompleteQueryText],

qp.query_plan AS [Query Plan]

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp

WHERE t.dbid = DB_ID()

ORDER BY [ExecutionCount] DESC OPTION (RECOMPILE)

Ortalama Değişken Zamana Göre Top SPs

Bu, sorgunun bazen hızlı bazen yavaş olduğunu gösterir.

Genellikle bu, kötü sorgu planının önbelleğe alındığı ve SP yeniden çalıştırıldığında kötü plan kullandığı anlamına gelir.

Bunu ayarlamak kolay olabilir.

Ortalama değişken zamana göre ilk 10 Stored procedure almak için aşağıdaki sorguyu çalıştırınız:

SELECT TOP(10) p.name AS [SPName],

qs.min_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],

qs.max_elapsed_time, qs.last_elapsed_time, qs.total_elapsed_time, qs.execution_count,

ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],

qs.total_worker_time AS [TotalWorkerTime],

CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N’%%’ THEN 1 ELSE 0 END AS [HasMissingIX],

FORMAT(qs.last_execution_time, ‘yyyy-MM-dd HH:mm:ss’, ‘en-US’) AS [LastExecutionTime],

FORMAT(qs.cached_time, ‘yyyy-MM-dd HH:mm:ss’, ‘en-US’) AS [PlanCachedTime]

,qp.query_plan AS [QueryPlan]

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE qs.database_id = DB_ID()

AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0

ORDER BY [AvgElapsedTime] DESC OPTION (RECOMPILE)

Top SPs by CPU usage

Toplam Çalışma Süresi–, SQL Engine yeniden başlatıldıktan sonra bu store procedure tarafından tüketilen toplam CPU maliyetidir.

CPU’ya göre en pahalı 10 stored procedures almak için aşağıdaki sorguyu çalıştırın.

SELECT TOP(10) p.name AS [SPName],

qs.total_worker_time AS [TotalWorkerTime],

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],

qs.execution_count AS [ExecutionCount],

ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],

qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],

CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N’%%’ THEN 1 ELSE 0 END AS [HasMissingIX],

FORMAT(qs.last_execution_time, ‘yyyy-MM-dd HH:mm:ss’, ‘en-US’) AS [LastExecutionTime],

FORMAT(qs.cached_time, ‘yyyy-MM-dd HH:mm:ss’, ‘en-US’) AS [PlanCachedTime]

,qp.query_plan AS [Query Plan]

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE qs.database_id = DB_ID()

AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0

ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE)

 

Top SPs By Execution Count

En çok yürütülen ilk 10 stored procedure ‘leri almak için aşağıdaki sorguyu çalıştırın.

SELECT TOP(10) p.name AS [SPName],

qs.execution_count AS [ExecutionCount],

ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],

qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],

qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],

CASE WHEN CONVERT(nvarchar(max), qp.query_plan) LIKE N’%%’ THEN 1 ELSE 0 END AS [HasMissingIX],

FORMAT(qs.last_execution_time, ‘yyyy-MM-dd HH:mm:ss’, ‘en-US’) AS [LastExecutionTime],

FORMAT(qs.cached_time, ‘yyyy-MM-dd HH:mm:ss’, ‘en-US’) AS [PlanCachedTime]

,qp.query_plan AS [QueryPlan]

FROM sys.procedures AS p WITH (NOLOCK)

INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)

ON p.[object_id] = qs.[object_id]

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE qs.database_id = DB_ID()

AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0

ORDER BY [ExecutionCount] DESC OPTION (RECOMPILE)

Top SPs by I/O

Bu, en çok I/O’ya neden olan stored procedure’lerin hangileri olduğunu gösterir.

Ortalama I/O’ya göre en pahalı 10 store procudere çağrısını almak için aşağıdaki sorguyu çalıştırın.

SELECT TOP(10) OBJECT_NAME(qt.objectid, dbid) AS [SPName],

(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [AvgIO],

qs.execution_count AS [ExecutionCount],

SUBSTRING(qt.[text],qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2

ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) AS [QueryText]

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

WHERE qt.[dbid] = DB_ID()

ORDER BY [AvgIO] DESC OPTION (RECOMPILE)

 

 

Size ve Veritabanlarınıza Yardımcı Olmak İçin Bekliyoruz!