Determinare proceduri slab utilizate pe un server Microsoft SQL

Situatie

Mai jos este o functie ce primeste ca parametru un numar intreg si returneaza procedurile ce nu au fost rulate pe un server Microsoft SQL in ultimele x zile alese. (daca se pune 0 nu se mai tine cont de numarul de zile).

Solutie

–select * from dbo.fn_GetUnusedProcedureNoOfDaysAgo(0) where ProcedureName = ‘test’

create function fn_GetUnusedProcedureNoOfDaysAgo
(@lastaccessed_daysago int = 0)
returns @returntable table(ProcedureId int,ProcedureName varchar(max),LastExecutionTime datetime,ExecutionCount int,LastExecutedDaysAgo int)
as
begin

;with getprocaccessedlists
as
(
select ISNULL(deps.database_id,-1) database_id,p.object_id,p.name
,ISNULL(deps.type,’P’) type,ISNULL(deps.type_desc,’SQL_STORED_PROCEDURE’) type_desc
,ISNULL(deps.last_execution_time,’1900-01-01′) last_execution_time,ISNULL(deps.execution_count,-1) execution_count
,p.create_date,p.modify_date
from sys.dm_exec_procedure_stats deps
right outer join sys.procedures p on deps.object_id = p.object_id
)
Insert Into @returntable
select object_id ,name ,last_execution_time,execution_count,datediff(dd,last_execution_time,getdate()) last_execution_daysago
from getprocaccessedlists
where (datediff(dd,last_execution_time,getdate()) >= @lastaccessed_daysago)
order by last_execution_daysago,name

return;

end
go

Tip solutie

Permanent

Follow Us