SQL Server Monitoring in 8 Steps: Lessons From the Field

Script 1.3 creates your stored procedure that will log a new value.

--Script 1.3:
CREATE PROC usp_refresh_log_sysperfinfo
as
-- declarations

DECLARE @previous_inserttime DATETIME, @current_inserttime DATETIME, @elapseTimeSec INT
DECLARE @previous_batch_request_sec FLOAT, @current_batch_request_sec FLOAT, @batch_request_sec FLOAT
DECLARE @current_Buffer_cache_hit_ratio FLOAT, @current_Buffer_cache_hit_ratio_base FLOAT, @Buffer_cache_hit_ratio FLOAT
DECLARE @Page_life_expectancy INT, @User_Connections INT

-- calculate elapse time

SELECT TOP 1 @current_inserttime = InsertTime FROM dbo.wt_current_sysperfinfo (NOLOCK)
SELECT TOP 1 @previous_inserttime = InsertTime FROM dbo.wt_previous_sysperfinfo (NOLOCK)
SELECT @elapseTimeSec = datediff(s, @previous_inserttime, @current_inserttime)

-- calculate @batch_request_sec

SELECT @current_batch_request_sec = cntr_value FROM dbo.wt_current_sysperfinfo (NOLOCK) WHERE counter_name = 'Batch Requests/sec'
SELECT @previous_batch_request_sec = cntr_value FROM dbo.wt_previous_sysperfinfo (NOLOCK) where counter_name = 'Batch Requests/sec'
SELECT @batch_request_sec = (@current_batch_request_sec - @previous_batch_request_sec) / @elapseTimeSec

-- calculate Buffer cache hit ratio

SELECT @current_Buffer_cache_hit_ratio = cntr_value FROM dbo.wt_current_sysperfinfo (NOLOCK) WHERE counter_name = 'Buffer cache hit ratio'
SELECT @current_Buffer_cache_hit_ratio_base = cntr_value FROM dbo.wt_current_sysperfinfo (NOLOCK) WHERE counter_name = 'Buffer cache hit ratio base'
SELECT @Buffer_cache_hit_ratio = @current_Buffer_cache_hit_ratio / @current_Buffer_cache_hit_ratio_base * 100.00

-- calculate Page life expectancy

SELECT @Page_life_expectancy = cntr_value FROM dbo.wt_current_sysperfinfo (NOLOCK) WHERE counter_name = 'Page life expectancy'

-- calculate User Connections

SELECT @User_Connections = cntr_value FROM dbo.wt_current_sysperfinfo (NOLOCK) WHERE counter_name = 'User Connections'

INSERT INTO dbo.tbl_log_sysperfinfo
(
[Batch Requests/sec],
[Buffer cache hit ratio],
[Page life expectancy],
[User Connections]
)
VALUES
(
@batch_request_sec,
@Buffer_cache_hit_ratio,
@Page_life_expectancy,
@User_Connections
)

Script 1.4 adds a record in the tbl_log_sysperfinfo table. This script should run on a regular basis as a SQL job step, scheduled, e.g., once per minute.

--Script 1.4:
DROP TABLE dbo.wt_previous_sysperfinfo
SELECT * INTO wt_previous_sysperfinfo FROM dbo.wt_current_sysperfinfo
TRUNCATE TABLE dbo.wt_current_sysperfinfo
INSERT wt_current_sysperfinfo (object_name, counter_name, instance_name, cntr_value, cntr_type)
SELECT object_name, counter_name, instance_name, cntr_value, cntr_type FROM master.dbo.sysperfinfo
EXEC usp_refresh_log_sysperfinfo

Step 2: Setup PerfMon

The following steps cover the setup of PerfMon. This setup contains settings specifically related to the way I use data in a later section. It’s not a copy of the BOL. Here, I describe all steps more in detail, compared to the setup in my previous article, but the result is the same.

Step 2.1: Create a system DSN on your production server database server connecting to your monitoring server.

Step 2.2: Change Log on as user for the "Performance Logs and Alerts" service on your production system. Use a domain account that:

• Is a member of the local admin group on your production server, and
   

• Is a member of the System Administrators server role on your production server, and
   

• Is a member of the db_owner database role on your monitoring database.
   

Step 2.3: Start Perfmon

• Add a new counter log
   

• In the General tab:

  • Click Add Objects to add all counters of following objects:

    • Memory

    • Physical Disk

    • Process

    • Processor

    • SQLServer:Access Methods

    • SQLServer:Buffer Manager

    • SQLServer:Cache Manager

    • SQLServer:Databases

    • SQLServer:General Statistics

    • SQLServer:Latches

    • SQLServer:Locks

    • SQLServer:Memory Manager

    • SQLServer:SQL Statistics

    • SQLServer:SQL Settable
       

  • Change the Interval to 1 minute
     

• In the Log Files tab:

  • Set Log file type = SQL database.
     

  • Configure: Choose the DSN you created in step 1.
     

• >In the Schedule tab:

  • Limit your schedule window by choosing appropriate Start and Stop settings.
     

  • When a log file closes: Run this command: use this to automatically run your consolidation and cleanup script.

Step 2.4: Start your counter log.