As a DBA I spend a lot of time monitoring and babysitting database servers. This can become tedious, not to mention boring, for e. I would rather spend my time doing something more constructive, so I have come up with a simple SQL Server task that will monitor database activities for me and notify me when there is an issue. This is both pro-active and re-active, in my opinion, because it gathers the information and gets it to me automatically, so I can get to the root cause fast.
This process consists of the following:
- 1 database to house the information (I have a central server for my DBA activities)
- 1 table to house the server information
- 1 job to collect data and kick off reports
- At least 1 alert to notify and kick off the job (I use CPU usage greater than 90% and insufficient resources, for example, but you decide when you want notification)
I have created a database that I use for my DBA activities that I call SQL_Monitoring, however, you can call yours whatever you like. Just remember to change it in the scripts below. Please make sure that you have the following before you begin:
- DBMail enabled on your server(s) - this is absolutely key to this process
- Create yourself as an operator so you can receive notifications if your job fails as well as used on alerts. Personally, I like to add a notification on every job I create in case it fails. This is optional.
I'm not going to go into details on how to set these up as they are readily available from other sources, however, just consider them as prerequisites.
Step One: Create the Database (optional if you already have a DBA database as described above - go to step 2). This will be used as the central place to store data that will be used in the reports.
Copy/Paste/Run the script below to create the database. Make sure to change this to suit your environment (paths, server names, operator names, alert names).
--Create the database
USE [master]
GO
CREATE DATABASE [SQL_Monitoring] ON PRIMARY
( NAME = N'SQL_Monitoring', FILENAME = N'<ADD YOUR PATH HERE>\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL_Monitoring.mdf' , SIZE = 95040KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'SQL_Monitoring_log', FILENAME = N'<ADD YOUR PATH HERE>\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SQL_Monitoring_log.ldf' , SIZE = 69760KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [SQL_Monitoring] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SQL_Monitoring].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [SQL_Monitoring] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [SQL_Monitoring] SET ANSI_NULLS OFF
GO
ALTER DATABASE [SQL_Monitoring] SET ANSI_PADDING OFF
GO
ALTER DATABASE [SQL_Monitoring] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [SQL_Monitoring] SET ARITHABORT OFF
GO
ALTER DATABASE [SQL_Monitoring] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [SQL_Monitoring] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [SQL_Monitoring] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [SQL_Monitoring] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [SQL_Monitoring] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [SQL_Monitoring] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [SQL_Monitoring] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [SQL_Monitoring] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [SQL_Monitoring] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [SQL_Monitoring] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [SQL_Monitoring] SET DISABLE_BROKER
GO
ALTER DATABASE [SQL_Monitoring] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [SQL_Monitoring] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [SQL_Monitoring] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [SQL_Monitoring] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [SQL_Monitoring] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [SQL_Monitoring] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [SQL_Monitoring] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [SQL_Monitoring] SET READ_WRITE
GO
ALTER DATABASE [SQL_Monitoring] SET RECOVERY SIMPLE
GO
ALTER DATABASE [SQL_Monitoring] SET MULTI_USER
GO
ALTER DATABASE [SQL_Monitoring] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [SQL_Monitoring] SET DB_CHAINING OFF
GOStep Two: Create the SP_Who_Results Table. This is where the results from running sp_who will be collected and stored. This table is truncated and reloaded with every run. I am writing this to a table so I can easily query the results and only use the columns in which I am interested. Plus, I can add a “where clause” to narrow down the results in my report in step three.
Copy/Paste/Run the script below. Make sure to change this to suit your environment.
--Create The Table
USE [SQL_Monitoring]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SP_Who_Results](
[SPID] [nchar](10) NULL,
[ecid] [nchar](10) NULL,
[status] [varchar](200) NULL,
[loginame] [varchar](50) NULL,
[hostname] [varchar](100) NULL,
[blk] [nchar](10) NULL,
[dbname] [varchar](max) NULL,
[cmd] [varchar](max) NULL,
[request_id] [nchar](10) NULL
) ON [PRIMARY]
GOStep Three: Create the Job “Report What's Running Now”
This job calls queries to return the following information in a user friendly HTML email format:
First determine what is currently running in SQL Server. This is created from the query:
SELECT
program_name,
count(*)
FROM Master..sysprocesses
WHERE ecid=0
and program_name <> '' ''
GROUP BY program_name
ORDER BY count(*) descDetermine what processes are using a lot of CPU from SQL Server with this query:
SELECT
spid,
program_name,
datediff(second,login_time, getdate())
FROM master..sysprocesses
WHERE spid > 50
and PROGRAM_NAME not in (''Microsoft SQL Server Management Studio '')Get the top 10 questionable SQL Server processes:
SELECT top 10
spid,
blocked,
convert(varchar(50),db_name(dbid)), cpu, datediff(second,login_time, getdate()),
convert(varchar(16), hostname),
convert(varchar(50), program_name),
convert(varchar(20), loginame)
FROM master..sysprocesses
WHERE datediff(second,login_time, getdate()) > 0
and spid > 50
and cpu >=1024
ORDER BY 6 descFind SQL Server resource hogs:
SELECT
convert(varchar(50), program_name),
count(*),
sum(cpu),
sum(datediff(second, login_time, getdate()))
FROM master..sysprocesses
WHERE spid > 50
GROUP BY convert(varchar(50), program_name)
ORDER BY 7 DESCFind who is connected: this is created by running sp_who and dumping results in the table we created in step two.
Copy/Paste/Run the script below. Make sure to change this to suit your environment.
--Create the job
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Data Collector' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Data Collector'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job
@job_name=”Whats Running Now',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Collects process data for:
What is Currently Running in SQL Server
Processes using a lot of CPU from SQL Server
Top 10 Questionable SQL Server Processes
SQL Server Resource Hogs
Who is connected',
@category_name=N'Data Collector',
@owner_login_name=N'sa',
@notify_email_operator_name=N'<Add Your Operator Name Here>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id=@jobId,
@step_name=N'Run the Report',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'SET nocount ON
DECLARE @Subject VARCHAR (100)
SET @Subject=''SQL Server Whats Causing the High CPU & Performance Issues on '' + @@ServerName
--clean up sp_who
Truncate table [SP_Who_Results]
--run SP_Who
Insert into [SP_Who_Results]
execute sp_who
--Reports
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N''<strong><font color="red">What is Currently Running in SQL Server</font></strong> <br>
<table border="1">'' +
N''<tr>'' +
N''<th>Program Name</th>'' +
N''<th>Running Processes</th>'' +
N''</tr>'' +
CAST ( (SELECT td=program_name,''''
,td= count(*),''''
FROM Master..sysprocesses
WHERE ecid=0 and program_name <> '' ''
GROUP BY program_name
ORDER BY count(*) desc
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>
<br><br>
<strong><font color="red">Processes using a lot of CPU from SQL Server</font></strong> <br>''
DECLARE @tableHTML4 NVARCHAR(MAX) ;
SET @tableHTML4 =
N''<table border="1">'' +
N''<tr>'' +
N''<th>SPID</th>'' +
N''<th>Program Name</th>'' +
N''<th>Connected Seconds</th>'' +
N''</tr>'' +
CAST ((SELECT td=spid,''''
,td=program_name ,''''
,td=datediff(second,login_time, getdate()),''''
FROM master..sysprocesses WHERE spid > 50 and PROGRAM_NAME not in (''Microsoft SQL Server Management Studio '')
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>
<br><br>
<strong><font color="red">Top 10 Questionable SQL Server Processes</font></strong> <br>''
DECLARE @tableHTML5 NVARCHAR(MAX) ;
SET @tableHTML5 =
N''<table border="1">'' +
N''<tr>'' +
N''<th>SPID</th>'' +
N''<th>Blocked</th>'' +
N''<th>DB Name</th>'' +
N''<th>CPU</th>'' +
N''<th>Seconds</th>'' +
N''<th>Host Name</th>'' +
N''<th>Program</th>'' +
N''<th>Login Name</th>'' +
N''</tr>'' +
CAST ((SELECT top 10 td=spid,''''
,td=blocked,''''
,td=convert(varchar(50),db_name(dbid)),''''
,td=cpu,''''
,td=datediff(second,login_time, getdate()),''''
,td=convert(varchar(16), hostname),''''
,td=convert(varchar(50), program_name),''''
,td=convert(varchar(20), loginame),''''
FROM master..sysprocesses
WHERE datediff(second,login_time, getdate()) > 0 and spid > 50 and cpu >=1024
ORDER BY 6 desc
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>
<br><br>
<strong><font color="red">SQL Server Resource Hogs</font></strong> <br>''
DECLARE @tableHTML6 NVARCHAR(MAX) ;
SET @tableHTML6 =
N''<table border="1">'' +
N''<tr>'' +
N''<th>Program</th>'' +
N''<th>Client Count</th>'' +
N''<th>CPU Sum</th>'' +
N''<th>Seconds Sum</th>'' +
N''</tr>'' +
CAST ((SELECT td=convert(varchar(50), program_name),''''
,td=count(*),''''
,td=sum(cpu),''''
,td=sum(datediff(second, login_time, getdate())),''''
FROM master..sysprocesses
WHERE spid > 50
GROUP BY convert(varchar(50), program_name)
ORDER BY 7 DESC
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>
<br><br>
<strong><font color="red">Who is connected</font></strong> <br>''
DECLARE @tableHTML7 NVARCHAR(MAX) ;
SET @tableHTML7 =
N''<table border="1">'' +
N''<tr>'' +
N''<th>SPID</th>'' +
N''<th>Status</th>'' +
N''<th>Login Name</th>'' +
N''<th>Hostname</th>'' +
N''<th>DB Name</th>'' +
N''<th>Cmd</th>'' +
N''</tr>'' +
CAST ( (SELECT td=[spid],''''
,td= [status],''''
,td=[loginame],''''
,td=[hostname],''''
,td=[dbname],''''
,td=[cmd],''''
FROM [SP_Who_Results]
where dbname not IN (''master'', ''msdb'')
ORDER BY 4,5 desc
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>''
declare @body2 varchar(max)
set @body2 = @tableHTML + '' '' + @tableHTML4 + '' '' + @tableHTML5 + '' '' + @tableHTML6 + '' '' +@tableHTML7
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''<Add Mail Profile Here>'',
@recipients = ''<Add DBA Email Here>'',
@subject = @Subject,
@body = @body2,
@body_format = ''HTML'' ;
',
@database_name=N'master',
@output_file_name=N'<Add Path Here>\WhatsRunningErrors.txt',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GOStep Four: Create an alert. In order to tie all this together, you will need to call the job somehow. To be proactive I call it from an alert. You can call it from any alert you want however, in this case, I’m using CPU Utilization is High (>90%).
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'CPU Utilization is High',
@message_id=0,
@severity=1,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@notification_message=N'CPU has been over 90% for the last 60 seconds on <ServerName>',
@event_description_keyword=N'CPU Utilization',
@category_name=N'[Uncategorized]',
@job_id=N''
GOOnce the alert is created, open it and attach the job to it as the response as shown here:
Now when ever the CPU utilization reaches 90%, my report will run providing me with a report that looks like this in an email. I also attach this report to other alerts, feel free to choose your own – obviously, I’ve changed the names to protect the innocent:
What is Currently Running in SQL Server
Program Name | Running Processes |
Microsoft SQL Server Management Studio - Query | 6 |
Microsoft SQL Server Management Studio | 5 |
DatabaseMail - DatabaseMail - Id<1792> | 2 |
DatabaseMail - SQLAGENT - Id<932> | 1 |
Report Server | 1 |
SQLAgent - Alert Engine | 1 |
SQLAgent - Email Logger | 1 |
SQLAgent - Generic Refresher | 1 |
SQLAgent - Job invocation engine | 1 |
Processes using a lot of CPU from SQL Server
SPID | Program Name | Connected Seconds |
51 | DatabaseMail - SQLAGENT - Id<932> | 79 |
52 | SQLAgent - Email Logger | 2607227 |
53 | SQLAgent - Generic Refresher | 2607227 |
54 | Microsoft SQL Server Management Studio - Query | 10972 |
55 | SQLAgent - Alert Engine | 2607207 |
57 | DatabaseMail - DatabaseMail - Id<1792> | 195 |
58 | SQLAgent - Job invocation engine | 2607142 |
59 | Microsoft SQL Server Management Studio - Query | 2850 |
60 | DatabaseMail - DatabaseMail - Id<1792> | 40 |
61 | Report Server | 2 |
64 | User Db Name | 38 |
65 | Microsoft SQL Server Management Studio - Query | 2115 |
69 | Microsoft SQL Server Management Studio - Query | 11776 |
70 | Microsoft SQL Server Management Studio - Query | 2487 |
73 | Microsoft SQL Server Management Studio - Query | 15962 |
Top 10 Questionable SQL Server Processes
SPID | Blocked | DB Name | CPU | Seconds | Host Name | Program | Login Name |
55 | 0 | msdb | 1172017 | 2607207 | host name | SQLAgent - Alert Engine | Login name |
56 | 0 | master | 3880 | 8109 | host name | Microsoft SQL Server Management Studio | Login name |
58 | 0 | msdb | 4195 | 2607142 | host name | SQLAgent - Job invocation engine | Login name |
68 | 0 | User DB Name | 3494 | 11787 | host name | Microsoft SQL Server Management Studio | Login name |
SQL Server Resource Hogs
Program | Client Count | CPU Sum | Seconds Sum |
SQLAgent - Email Logger | 1 | 218 | 2607227 |
SQLAgent - Generic Refresher | 1 | 389 | 2607227 |
SQLAgent - Alert Engine | 1 | 1172017 | 2607207 |
SQLAgent - Job invocation engine | 1 | 4195 | 2607142 |
Microsoft SQL Server Management Studio | 5 | 9059 | 200228 |
Microsoft SQL Server Management Studio - Query | 6 | 733 | 46162 |
DatabaseMail - DatabaseMail - Id<1792> | 2 | 30 | 235 |
DatabaseMail - SQLAGENT - Id<932> | 1 | 16 | 79 |
Some user db | 1 | 0 | 38 |
Report Server | 1 | 0 | 2 |
Who is connected
SPID | Status | Login Name | Hostname | DB Name | Cmd |
68 | sleeping | User login | host | User db | AWAITING COMMAND |
69 | sleeping | User login | host | User db | AWAITING COMMAND |
61 | sleeping | User login | host | ReportServer | AWAITING COMMAND |
59 | runnable | User login | host | Some database | SELECT |
70 | sleeping | User login | host | Some database | AWAITING COMMAND |
64 | sleeping | User login | host | User db | AWAITING COMMAND |
Conclusion
Now you have a super simple way to proactivly monitor sql server without an expensive 3rd party application.
Included in this package, the following will be created:
Database: SQL_Monitoring
Table: SP_Who_Results
Job: Report Whats Running Now
