Start SQL Server Performance Counter (if stopped)

Use this script to start SQL Server Performance Counter (if stopped). Note that SQL Server Performance Counters must be setup.


CREATE PROCEDURE spc_Check_PERFMON_Status
as

--================================================================
--== Start Performance SQL Server Counter if stopped ==
--== ==
--== Created by: Aleksadnar Tosic ==
--== ==
--================================================================

--====================================================================================
--== NOTES
--== 14515200 records will be kept in counterdata for 3 months
--== data from CounterData odler than 3 months will be automatically deleted
--====================================================================================

-- check the status for SQL Server Performance counter
-- (start the counter is it stopped)

SET NOCOUNT ON

create table #PMON(txt nvarchar(2000))
--declare #PMON table (txt nvarchar(2000))

insert into #PMON (txt)
exec master.dbo.xp_cmdshell 'logman query'

delete from #PMON where txt not like '%SQL Server%' or txt is null
if exists(select null from #PMON where txt like '%SQL Server%Running%')
PRINT 'RUNNING'
else
begin
PRINT 'STOPPED'
execute master.dbo.xp_cmdshell 'logman start "SQL Server Performance"'
end

--create DBA_PERFMON_DATA is does not exists
if not exists (select null from DBA.dbo.sysobjects where name = 'DBA_PERFMON_DATA')
begin
CREATE TABLE [dbo].[DBA_PERFMON_DATA](
[CounterID] [int] NOT NULL,
[GUID] [uniqueidentifier] NOT NULL,
[RecordIndex] [int] NOT NULL,
[CounterDateTime] smalldatetime NOT NULL,
[CounterValue] [float] NOT NULL,
CONSTRAINT [PK_DBA_PERFMON_DATA] PRIMARY KEY CLUSTERED
([GUID] ASC,
[CounterID] ASC,
[RecordIndex] ASC)
)
end

-- keep counterid = 1,18,20,21,22,23,24,27 records in DBA_PERFMON_DATA for
-- performance analysis and reporting

--delete data only between 8pm and 11pm
if datepart(hour,getdate()) between 20 and 23
begin
insert into DBA.dbo.DBA_PERFMON_DATA
(CounterID,
GUID,
RecordIndex,
CounterDateTime,
CounterValue)
select
a.CounterID,
b.GUID,
b.RecordIndex,
cast(SUBSTRING(b.CounterDateTime, 1, 4) + '-'
+ SUBSTRING(b.CounterDateTime, 6, 2) + '-'
+ SUBSTRING(b.CounterDateTime, 9, 2) + 'T'
+ SUBSTRING(b.CounterDateTime, 12, 2) + ':'
+ SUBSTRING(b.CounterDateTime, 15, 2) + ':'
+ SUBSTRING(b.CounterDateTime, 18, 2) as datetime),
b.CounterValue
from DBA.dbo.CounterDetails a inner join DBA.dbo.CounterData b
on a.CounterID = b.CounterID
left join DBA.dbo.DBA_PERFMON_DATA c
on b.GUID = c.GUID
and b.RecordIndex = c.RecordIndex
and b.CounterID = c.CounterID
where b.counterid IN (1,18,20,21,22,23,24,27)
and (c.GUID is null or c.RecordIndex is null or c.CounterID is null)

--delete all data older than 90 days from CounterData
delete from CounterData
where cast(SUBSTRING(CounterDateTime, 1, 4) + '-'
+ SUBSTRING(CounterDateTime, 6, 2) + '-'
+ SUBSTRING(CounterDateTime, 9, 2) + 'T'
+ SUBSTRING(CounterDateTime, 12, 2) + ':'
+ SUBSTRING(CounterDateTime, 15, 2) + ':'
+ SUBSTRING(CounterDateTime, 18, 2) as datetime) < getdate()-90
end