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 datetimeSET @@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, 32, 8)=
(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 (dbid) AS DatabaseName,
USER_NAME([uid]) AS UserName,
[cpu],
[physical_io],
[memusage],
[login_time],
[last_batch],
DATEDIFF(minute, 0, GETDATE() - 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.
