Top proceduri consumatoare de CPU

Configurare noua (How To)

Situatie

Pentru situatiile in care serverul de sql merge greu, si nu stim ce anume consuma resursele, am facut o procedura care, rulata pe server, intoarce procedurile de sql ce utilizeaza cel mai mult procesorul.

Procedura va avea un parametru de tip integer, care va spune numarul de proceduri care sa fie returnate (top x)

Coloanele returnate vor fi:

DatabaseName – numele bazei de date pe care este definita procedura

SchemaName – numele schemei aferente procedurii

ObjectName – numele procedurii

LastExecutionTime – ultima oara cand procedura a fost apelata

TotalNumberOfExecution – numarul total de apelari ale procedurii de la ultimul restart al serverului de sql

AverageLogicalReads – numarul mediu de citiri logice efectuate de catre CPU

AverageLogicalWrites – numarul mediu de scrieri logice efectuate

Solutie

— EXEC BD_TopXProcedureByCpuUtilization 10

alter procedure BD_TopXProcedureByCpuUtilization
@top int

as

declare @sql varchar(max)

set @sql =
‘SELECT
TOP ‘+CONVERT(VARCHAR(MAX),@top)+’ ‘+
‘DB_NAME(database_id) AS DatabaseName
,OBJECT_SCHEMA_NAME(object_id,database_id) AS SchemaName
,OBJECT_NAME(object_id,database_id)AS ObjectName
,last_execution_time AS LastExecutionTime
,execution_count AS TotalNumberOfExecution
,(total_logical_reads / execution_count) AS AverageLogicalReads
,(total_logical_writes / execution_count) AS AverageLogicalWrites
FROM sys.dm_exec_procedure_stats
ORDER BY AverageLogicalReads DESC

EXEC (@sql)

Tip solutie

Permanent
Etichetare:

Voteaza

(22 din 38 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?