“sysperfinfo” in SQL Server

The system table, "sysperfinfo," is the
representation of the internal performance counters of SQL Server. This article
demonstrates how to retrieve and store information from "sysperfinfo"
to a CSV file, so that it can be analyzed.

Step 1

Create the following procedure, "usp_WriteToFile,"
as shown in Fig 1.1.


set quoted_identifier off
go
use master
go
if exists (select * from dbo.sysobjects where id =
object_id(N’[dbo].[Usp_WriteToFile]’) and
OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[Usp_WriteToFile]
GO
CREATE PROCEDURE Usp_WriteToFile
(@FileName varchar(1000), @Text1 varchar(1000)) AS
–Objective: To Write a given string on to a given file
–Created by: MAK
–Date: Sep 25, 2004
DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult =
sp_OACreate ‘Scripting.FileSystemObject’,
@FS OUT
IF @OLEResult <> 0
PRINT ‘Error: Scripting.FileSystemObject’

–Open a file
execute @OLEResult = sp_OAMethod @FS,
‘OpenTextFile’, @FileID OUT,
@FileName, 8, 1
IF @OLEResult <>0 PRINT ‘Error: OpenTextFile’

–Write Text1
execute @OLEResult = sp_OAMethod @FileID,
‘WriteLine’, Null, @Text1
IF @OLEResult <> 0 PRINT ‘Error : WriteLine’

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

Download usp_writetofile.sp.



Fig 1.1

Step 2

Create the following procedure, "usp_perfmon,"
as shown in Fig 1.2.


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
use master
go
if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[usp_perfmon]’) and
OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[usp_perfmon]
GO
Create procedure usp_perfmon @Applicationlogpath varchar(600) = ‘d:\PerfMon.csv’
as
–Objective: TO gather information from sysperfinfo table
–Created by: MAK
DECLARE @value numeric(25, 2), @basevalue numeric(25, 2)
declare @size int
declare @fileexist int
declare @error varchar(1000)
declare @Cachehitratio varchar(100)
declare @SQLTargetMemory varchar(100)
declare @SQLTotMemory varchar(100)
declare @Freepages varchar(100)
declare @PageSplits varchar(100)
declare @transactions varchar(100)
declare @userconnections varchar(100)
declare @deadlocks varchar(100)
declare @date varchar(25)
declare @lockwait varchar(100)
declare @compilation varchar(100)
declare @totaldatagrowth varchar(100)
declare @totalloggrowth varchar(100)
declare @batchrequest varchar(100)

set @Cachehitratio =”
set @SQLTargetMemory =”
set @SQLTotMemory =”
set @Freepages =”
set @PageSplits =”
set @transactions=”
set @userconnections =”
set @deadlocks =”
set @date =”
set @lockwait =”
set @compilation=”
set @totaldatagrowth =”
set @totalloggrowth =”
set @batchrequest =”

set @date =convert(varchar(25),getdate(),109)
–Cache hit ratio
SELECT @value = cntr_value FROM sysperfinfo (nolock)
WHERE counter_name = ‘Buffer cache hit ratio’
SELECT @basevalue = cntr_value FROM sysperfinfo (nolock)
WHERE counter_name = ‘Buffer cache hit ratio base’
set @Cachehitratio= convert(varchar(100), (@value / @basevalue) *100)
–Free Pages
SELECT @value = cntr_value FROM sysperfinfo (nolock)
WHERE counter_name = ‘Free Pages’ and
object_name = ‘SQLServer:Buffer Manager’
set @Freepages= convert (varchar(100),@value )
–SQL Memory
select @value =cntr_value/1024 from sysperfinfo (nolock)
where object_name =’SQLServer:Memory Manager’ and
counter_name =’Total Server Memory (KB)’
set @SQLTotMemory=convert(varchar(100),@value)
select @basevalue=cntr_value/1024 from sysperfinfo (nolock)
where object_name =’SQLServer:Memory Manager’ and
counter_name =’Target Server Memory(KB)’
set @SQLTargetMemory=convert(varchar(100),@basevalue)
–Page Splits
select @value =cntr_value from sysperfinfo (nolock)
where object_name =’SQLServer:Access Methods’ and
counter_name =’Page Splits/sec’
set @PageSplits=convert(varchar(100),@value )
–# of transactions

select @value = cntr_value from sysperfinfo (nolock)
where object_name = ‘SQLServer:Databases’
and counter_name =’Transactions/sec’ and instance_name =’_Total’
set @transactions= convert(varchar(100),@value )

–User Connections
select @value = cntr_value from sysperfinfo (nolock)
where object_name = ‘SQLServer:General Statistics’
and counter_name =’User Connections’
set @userconnections=convert(varchar(100),@value )

–Deadlocks
select @value = cntr_value from sysperfinfo (nolock)
where object_name = ‘SQLServer:Locks’
and counter_name =’Number of Deadlocks/sec’
set @deadlocks = convert(varchar(25),@value)

–Lock waits/sec

select @value= cntr_value from sysperfinfo (nolock)
where object_name = ‘SQLServer:Locks’
and counter_name =’Average Wait Time (ms)’
set @lockwait = convert(varchar(25),@value)

–Database dataFile growth
select @value = cntr_value/1024 from sysperfinfo (nolock)
where object_name =’SQLServer:Databases’ and
counter_name =’Data File(s) Size (KB)’ and instance_name =’_Total’
set @totaldatagrowth = convert(varchar(100),@value)

–Database Log File growth
select @value = cntr_value/1024 from sysperfinfo (nolock)
where object_name =’SQLServer:Databases’ and
counter_name =’Log File(s) Size (KB)’ and instance_name =’_Total’
set @totalloggrowth = convert(varchar(100),@value)

–declare @LogMB numeric(25, 2)
select @value = cntr_value from sysperfinfo (nolock)
where object_name =’SQLServer:SQL Statistics’ and
counter_name =’Batch Requests/sec’
set @batchrequest = convert(varchar(100),@value)

–Compilations/sec
select @value= cntr_value from sysperfinfo (nolock)
where object_name =’SQLServer:SQL Statistics’ and
counter_name =’SQL Compilations/Sec’
set @compilation= convert(varchar(100),@value)

create table #fileexist (fileexist int, directory int, parent int)
insert #fileexist exec xp_fileexist @Applicationlogpath
select @fileexist = fileexist from #fileexist
if @fileexist <> 1
begin
set @error = ‘Cachehitratio’+ ‘,’+’SQLTargetMemory’+ ‘,’+’SQLTotMemory’ + ‘,’+
‘Freepages’ + ‘,’+’PageSplits’ + ‘,’+’transactions’+ ‘,’+’userconnections’ + ‘,’+
‘deadlocks’ + ‘,’+ ‘lockwait ‘ + ‘,’+ ‘compilation’ + ‘,’+ ‘totaldatagrowth ‘ + ‘,’+
‘totalloggrowth ‘ + ‘,’+ ‘batchrequest ‘ + ‘,’+ ‘date ‘
EXEC master..usp_WriteToFile @Applicationlogpath, @error

set @fileexist =1
end

create table #filedetails (name varchar(100),size int, date1 varchar(20),
time varchar(20), date2 varchar(10), time2 varchar(20), date3 varchar(20), time3 varchar(20),attr int)
insert #filedetails exec xp_getfiledetails @applicationlogpath

select @size =size from #filedetails

if @size <10
begin
set @error = ‘Cachehitratio’+ ‘,’+’SQLTargetMemory’+ ‘,’+’SQLTotMemory’ + ‘,’+
‘Freepages’ + ‘,’+’PageSplits’ + ‘,’+’transactions’+ ‘,’+’userconnections’ + ‘,’+
‘deadlocks’ + ‘,’+ ‘lockwait ‘ + ‘,’+ ‘compilation’ + ‘,’+ ‘totaldatagrowth ‘ + ‘,’+
‘totalloggrowth ‘ + ‘,’+ ‘batchrequest ‘ + ‘,’+ ‘date ‘
EXEC master..usp_WriteToFile @Applicationlogpath, @error
end

set @error =@Cachehitratio + ‘,’+@SQLTargetMemory+ ‘,’+@SQLTotMemory + ‘,’+@Freepages + ‘,’+
@PageSplits + ‘,’+@transactions+ ‘,’+@userconnections + ‘,’+@deadlocks + ‘,’+
@lockwait + ‘,’+ @compilation+ ‘,’+ @totaldatagrowth + ‘,’+ @totalloggrowth + ‘,’+
@batchrequest + ‘,”‘+ @date +'”‘

EXEC master..usp_WriteToFile @Applicationlogpath, @error

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Download usp_perfmon.sp.



Fig 1.2

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles