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)
Leave A Comment?