Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 28, 2004

"sysperfinfo" in SQL Server - Page 2

By Muthusamy Anantha Kumar aka The MAK



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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM