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