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…

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |