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.

         

        Continues…