SQL Overview VI - Monitoring Process Locks

,

Lock, unlock, lock, unlock is what databases do to prevent data

from being updated from multiple sources at the same time. This of

course can cause lock waits (blocks) for other SQL statements. This in

turn causes applications to slow, jobs to run long, and eventually

your pager to go off. To help you start working on the issue before

your pager alerts you, you can use this new SSIS package to monitor for

blocks across all your SQL Server 2000, 2005, and 2008 instances. In

addition, it can check for some long running SQL Agent Jobs and

verify SQL Agent is running.

This package is similar to the previous SQL Overview package, except

it only retrieves data from the system table "dbo.sysprocesses" and is

executed repeatedly throughout the day. Thus is the reason for it

being a new package. You might have noticed the "dbo" system table is

used instead of the "sys" system table. This was done to allow data to

be collected from SQL Server 2000 instances.

Details on how to create this package is not covered by this article,

instead it will cover how to use the package and its reports. If you

wish to create one from scratch, please refer to the

article SQL Overview Part 1

on how to get started. This article's file attachment contains the full

package, reports, and setup instructions. The reports are created once

a day with the other SQL Overview reports. Any issues detected

during the previous 24 hours will be reported at that time.

Package

The package is executed using SQL Server 2005. It is capable of

accessing SQL Server 2000, 2005, and 2008 instances. The information it

collects is stored in the new table dbo.SQL_Sysprocesses. Any

errors detected while trying to connect are stored in the new

table dbo.SSIS_Errors_Sysprocesses.

All servers in the table SSIS_ServerList are processed by this package

unless its column Skip_SQL_Processes is set to True. The default

is to check each server.

Rows in the table dbo.sysprocesses older than 7 days are deleted by

this package. To change this setting, you need to update the

package container "Prune SQL_Sysprocesses" with the desired value.

SQL used when collecting data from sysprocesses

DECLARE @@Package_Run_Date AS datetime

SET @@Package_Run_Date  GETDATE()

SELECT

   @@SERVERNAME AS [Server],

   CASE WHEN program_name LIKE 'SQLAgent - TSQL%' 

        THEN (SELECT msdb.dbo.sysjobs.name

                FROM msdb.dbo.sysjobs

                WHERE program_name LIKE 'SQLAgent - TSQL%' 

                 AND SUBSTRING(program_name,  328)=

 

       (SUBSTRING(LEFT(msdb.dbo.sysjobs.job_id,8),7,2) +

       SUBSTRING(LEFT(msdb.dbo.sysjobs.job_id,8),5,2) +

       SUBSTRING(LEFT(msdb.dbo.sysjobs.job_id,8),3,2) +

       SUBSTRING(LEFT(msdb.dbo.sysjobs.job_id,8),1,2))

           )           

        ELSE '' 

        END AS JobName,

   RTRIM([loginame]AS [loginame],

   [spid],

   [kpid],

   [blocked],

   [waittype],

   [waittime],

   RTRIM([lastwaittype]AS [lastwaittype],

   RTRIM([waitresource]AS [waitresource],

   DB_NAME (dbidAS DatabaseName,

   USER_NAME([uid]AS UserName,

   [cpu],

   [physical_io],

   [memusage],

   [login_time],

   [last_batch],

        DATEDIFF(minute0GETDATE() - last_batch AS  DurationMinutes,

   GETDATE() - last_batch AS DurationDatetime,

   [ecid],

   [open_tran],

   RTRIM([status]AS [status],

   RTRIM([hostname]AS [hostname],

   RTRIM([program_name]AS [program_name],

   [cmd],

   RTRIM([nt_domain]AS [nt_domain],

   RTRIM([nt_username]AS [nt_username],

   [net_address],

   [net_library],

   @@Package_Run_Date  AS [Package_run_date]

FROM

   [master].[dbo].[sysprocesses]

Tables

New tables

  • dbo.SQL_Sysprocesses - Stores the results of querying dbo.sysprocess. See the SQL above for the columns that are captured.
  • dbo.SSIS_Errors_Sysprocesses - Stores errors encountered while executing the package.
  • rep.Sysprocesses_Blocked - Contains SQL processes reported as being blocked and the processes identified as the blocker.
  • rep.SQL_Overview_Last_Run_Date_Sysprocesses - Contains the last run

    date of the package. This date is used when checking for long running

    sysprocesses.

Existing Tables Used

  • dbo.SSIS_ServerList - Controls which instances are checked. Two

    new columns will be used: Skip_SQL_Processes and

    Skip_SQLAgent_Check.

  • rep.Long_Running_Jobs - Contain settings for checking for long running SQL Agent jobs. 

Reports

This package comes with four new reports that are

executed once a day and sends an email whenever there is anything

to report. Each report requires you to set the variable @EmailRecipients

to the e-mail account of those who are to receive the report. If you

specify multiple addresses, use a semicolon to separate them.

  • Report: SQL Server - SQL Overview - Sysprocesses Table Refresh Errors 

    Job: DBA-SQL Overview - Report Table Refresh Errors 

    Description: Report errors encountered during the previous 25 hours

    by the new sysprocesses package. After the report is sent, any

    errors older than 25 hours are deleted from the table

    SSIS_Errors_Sysprocesses.

  • Report: SQL Server - Blocked Sysprocesses Summary

    Job: DBA-SQL Overview - Report Blocked Sysprocesses 

    Description:  Report processes blocked during the previous

    24 hours that had a wait time of 1 minute or more. Both the process

    being blocked and the process causing the block are included in the

    report.

  • Report: SQL Server - Long Running Executing Jobs (Sysprocesses)

    Job: DBA-SQL Overview - Report Long Running Executing Jobs-Sysprocesses 

    Description: Report any running SQL Agent job that is running

    longer than expected. The threshold used to identify the job is stored

    in the table rep.Long_Running_Jobs.

    A system default value is also stored in this table. Once a job is

    reported as running long, a row is added to this table allowing you to

    customize its setting. Maintenance Plan jobs are not reported because

    they do not include the Job Id in its program_name. For me it listed

    "Microsoft SQL Server Management Studio" instead of the Job Id.

  • Report: SQL Server - SQL Agent Stopped on Server

    Job: DBA-SQL Overview - Report SQLAgent Stopped

    Description: This report checks the captured sysprocesses tables for

    a SQL Agent processes. For servers missing this process, a report is

    generated. This test is not 100% accurate. So far, it has been right

    more times than wrong. The time when it has been wrong is when

    instance connections are failing. Right or wrong, this extra check

    cannot hurt. However, it has helped in determining why a server is

    missing all its last nights' backups.

    To skip any instances for this reports, set the column Skip_SQLAgent_Check to True in the table SSIS_ServerList. The default is to check.

Customization

  • To execute the reports after each time the package has finished, add start report job steps to the "DBA-SQL Overview - Sysprocesses".
  • To collect information about SQL Agent jobs and sysprocesses near the same time, add a start Job for "DBA-SQL Overview - Sysprocesses" in the job "SQL Overview" and disable the schedule in the sysprocesses job. See SQL Overview V for this package.
  • To adjust the time when you will receive an alert long run time, change the settings in rep.Long_Running_Jobs.
  • To skip an instance when collecting sysprocesses date, change its Skip_SQL_Processes setting in SSIS_ServerList  to be skipped.
  • To skip a instance when checking if SQL Agent is running, change its Skip_SQLAgent_Check setting in SSIS_ServerList to be skipped
  • To change the number of days rows are kept in dbo.sysprocesses, update the package container "Prune SQL_Sysprocesses" with the desired value. 

Conclusion:

Now you will know when locks are causing issues and can take the

appropriate action. The reports are created once a day but can be

modified to run more often. All the code is included in the attachments

and you are welcome to customize it. 

The run time for this package is quick. For me it runs 2.5

minutes and collects the sysprocesses table data from 30+ instances. It

will run longer when an instance is down because it has to wait to

timeout before connecting to the next server.

Additional Resources

Resources

Rate

4.17 (12)

Share

Share

Rate

4.17 (12)