free to monitor your sqlserver easy and safe and ...

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

 

heckoldtable2_daily_08:30

 

EXEC checkoldtable2 -7

go

 

checkcputest_daily_09:00

EXEC checkcputest

go

 

 

you can see final  example