Sql Server – SQL Server Agent – Optimizing DB Traffic

Background

In our last post, we spoke about how MSDB is responsible for some of our Single use queries.

Let us dig more in by identifying some of those queries and see if we can change, modify, or reduce their frequency.

 

MSDB – Queries

We have identified a couple of queries and here they are:

  1. msdb.dbo.sp_sqlagent_get_perf_counters
  2. msdb.dbo.sysmail_help_profile_*

 

SQL – msdb.dbo.sp_sqlagent_get_perf_counters

SQL Server Profiler

SQL Server Profiler – Captured

Image

sqlserverprofiler-20160908-0334pm

 

Textual

  1. Text Data :- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
  2. Application Name: – SQLAgent – Alert Engine

 

SQL Server Profiler – Explanation

  1. SQL Server Agent issues “EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters”
    • Every 20 seconds

 

Remediation

Steps

  1. Script out all current SQL Server Agent Alerts
  2. Remove all the alerts
    • Please keep in mind that it is not enough to disable them
Before

sqlserveragentalerts-20160908-0254pm

 

SQL Server Agent – Mail

SQL Server Profiler

Image

sqlserverprofiler-20160908-0426pm

Textual


exec msdb.dbo.sysmail_help_profile_sp @profile_name=N'Local Relay Server'
exec msdb.dbo.sysmail_help_profileaccount_sp @profile_id=1
exec msdb.dbo.sysmail_help_admin_account_sp @account_id=1

SQL Server Profiler – Explanation

  1. SQL Server Agent issues “EXECUTE msdb.dbo.sysmail_help*” commands
    • Every 5 minutes

Remediation

Steps

  1. Access SQL Server Agent Properties
  2. Access the “Alert System” Tab
  3. Disable “Enable Email profile”
Before

alertsystem-before

 

After
alertsystem-after

 

SQL Server Profiler ( After)

Image

sqlserverprofiler-20160908-0437pm

Textual


exec msdb.dbo.sysmail_help_profile_sp @profile_name=N''

SQL Server Profiler – Explanation

  1. SQL Server Agent issues “EXECUTE msdb.dbo.sysmail_help_profile_sp” commands
    • Every 5 minutes
    • Captured Output
      • Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42 profile name is not valid 

Summary

If you are not using SQL Server Agent for alerts and email notification services, you might be able to get away with removing alerts and disabling the email profile setup for SQL Server Agent.

Again, please help in mind we are specifically targeting a v2005 SQL instance.

2 thoughts on “Sql Server – SQL Server Agent – Optimizing DB Traffic

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s