Unlike AWR in Oracle, Sqlserver does not have offical way to make history performance information for checking. until today.
I made it. you can deploy it safe and easy.
Needless to day:
getCPU
CREATE PROCEDURE [dbo].[getCPU]
AS
BEGIN
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
SELECT TOP(500) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -6 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int')
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%') AS x
) AS y
ORDER BY record_id DESC;
END
getperformancetest
CREATE PROCEDURE [dbo].[getperformancetest]
AS
BEGIN
SELECT TOP 10
Left(SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1
, ((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1), 200) AS "SQL_TEXT"
, qs.execution_count
, qs.total_worker_time /qs.execution_count/1000 as avg_worker_time
, qs.total_elapsed_time /qs.execution_count /1000 as avg_elapsed_time
, qs.total_physical_reads /qs.execution_count as avg_physical_reads
, qs.total_logical_reads /qs.execution_count as avg_logical_reads
, qp.query_plan
--, CAST(qp.query_plan AS nvarchar(max)) query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.last_execution_time > dateadd(minute, -1, getdate())
ORDER BY qs.total_elapsed_time/qs.execution_count DESC
END
checkcputest
create PROCEDURE [dbo].[checkcputest]
AS
BEGIN
DECLARE @SQL2 VARCHAR(MAX)
DECLARE @createtable VARCHAR(MAX)
DECLARE @inserttable VARCHAR(MAX)
DECLARE @inserttable2 VARCHAR(MAX)
DECLARE @@tablename2 VARCHAR(MAX)
SELECT @SQL2 = (select replace(replace(replace(CONVERT(varchar, getdate(), 112 ),'-',''),' ',''),':',''))
set @createtable='create table cpu_'+@SQL2+' ([SQLProcessUtilization] [numeric](3,0),[SystemIdle] [numeric](3,0),[OtherUtilization] [numeric](3,0),[eventime] [datetime2]) ON [PRIMARY];'
exec(@createtable)
set @inserttable='insert into cpu_'+@SQL2+' exec [dbo].[getCPU]'
exec(@inserttable)
END
checkperformancetest
create PROCEDURE [dbo].[checkperformancetest]
AS
BEGIN
DECLARE @SQL2 VARCHAR(MAX)
DECLARE @createtable VARCHAR(MAX)
DECLARE @inserttable VARCHAR(MAX)
DECLARE @inserttable2 VARCHAR(MAX)
DECLARE @@tablename2 VARCHAR(MAX)
SELECT @SQL2 = (select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':',''))
set @createtable='create table perf_'+@SQL2+' ([SQL_TEXT] [nvarchar](max),[execution_count] [numeric](18, 0),[avg_worker_time] [numeric](18, 0),[avg_elapsed_time] [numeric](18, 0),[avg_physical_reads] [numeric](18, 0),[avg_logical_reads] [numeric](18, 0),[query_plan] [xml]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];'
exec(@createtable)
set @inserttable='insert into perf_'+@SQL2+' exec [dbo].[getperformancet
exec(@inserttable)
END
showdelperftable
CREATE PROCEDURE [dbo].[showdelperftable] @days int
AS
BEGIN
select 'drop table dbo.'+name from sys.tables where name like 'perf_%' and create_date < dateadd(day, @days, getdate())
order by name asc,create_date asc
END
checkoldtable2
CREATE PROCEDURE [dbo].[checkoldtable2] @days int
AS
BEGIN
declare @m int
declare @n int
declare @tmp nvarchar(MAX)
declare @droptableList TABLE (id int IDENTITY(1,1) NOT NULL,contents VARCHAR(35) NOT NULL)
SET NOCOUNT ON
INSERT @droptableList (contents) exec showdelperftable @days
set @m=(select count(*) from @droptableList)
set @n=1
while @n<=@m
begin
set @tmp=(select contents from @droptableList where id=@n)
exec (@tmp)
--print (@tmp+CHAR(5))
set @n=@n+1
end
end
checkperformance_every_6_minutes
exec checkperformancetest
go
checkoldtable2_daily_08:30
EXEC checkoldtable2 -7
go
checkcputest_daily_09:00
EXEC checkcputest
go
you can see final example
Comments