"sysperfinfo" in SQL Server

Thursday Oct 28th 2004 by Muthusamy Anantha Kumar aka The MAK
Share:

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

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



Step 3


Execute the following SQL Script to create an SQL Server scheduled job to collect information about SQL Server's performance.



-- Script generated on 9/25/2004 12:18 PM
-- By: SQL\Administrator
-- Server: SQL

BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) 
  FROM msdb.dbo.syscategories 
  WHERE name = 
    N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category 
   @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name 
     (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'Job_PerfMon')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
      FROM    msdb.dbo.sysjobservers 
      WHERE   (job_id = @JobID) AND 
	   (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job '
     'Job_PerfMon'' since there is already a 
      multi-server job with 
	    this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 	
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job 
	  @job_name = N'Job_PerfMon' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Job_PerfMon', 
     @owner_login_name = N'sa', @description = N'No description available.', 
     @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, 
     @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, 
    @step_name = N'Job_PerfMon', @command = N'usp_perfmon ''d:\PERFMON.csv''
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', 
    @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, 
    @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, 
    @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Job_PerfMon', 
    @enabled = 1, @freq_type = 4, @active_start_date = 20040924, @active_start_time = 0, 
    @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 15, 
    @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, 
    @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 

Download Job_PerfMon.sql.

This will create the following job, as shown in Fig 1.3, 1.4 and 1.5


Fig 1.3


Fig 1.4


Fig 1.5

Step 4

Execute the job as shown in Fig 1.6.


Fig 1.6

When the job is executed, it creates the D:\Perfmon.csv file as shown in the Fig 1.7. The job is executed every 15 minutes.


Fig 1.7

Conclusion

This article has shown how to retrieve and store information from "sysperfinfo" to a CSV file so that it can be analyzed. You can update this procedure for collecting additional counters from the "sysperfinfo" table.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved