Situatie
Am facut o procedura ce primeste ca parametru numele unei tabele si pe baza pe care este rulata, returneaza pentru acea tabela data si ora ultimului update facut in acea tabela, precum si data ultimului user scan, user seek si user lookup. Aceasta oferasi numarul de operatii efectuate pe fiecare tip de cautare de la ultimul restart al serverului de sql. Totodata, procedura returneaza si data la care serverul sql a fost restartat ultima oara.
Solutie
create procedure BD_LastTimeTableUpdate_prc
@tabela varchar(max)
as
declare @tabela_upper varchar(max)
set @tabela_upper = upper(@tabela)
SELECT
tbl.name
,ius.last_user_update
,ius.last_user_seek
,ius.last_user_scan
,ius.last_user_lookup
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
into #updates
FROM
sys.dm_db_index_usage_stats ius INNER JOIN
sys.tables tbl ON (tbl.OBJECT_ID = ius.OBJECT_ID)
WHERE ius.database_id = DB_ID() and
upper(tbl.name) like @tabela_upper
declare @max datetime
select @max = max(last_user_update) from #updates
delete from #updates where isnull(last_user_update,’19900101′) <> @max
select * from #updates
declare @LastServerRestart datetime
select @LastServerRestart = crdate from sys.sysdatabases where dbid = 2
select @LastServerRestart as LastServerRestart
Leave A Comment?