dcsimg

"sysperfinfo" in SQL Server

October 28, 2004

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers