Microsoft Azure SQL Database plugin tuning & performance tweaks

The Microsoft Azure SQL Database plugin is a very popular plugin that New Relic offers. After some discussion with our friends at Microsoft we’ve got some tweaks that can be made to the plugin to help improve performance (hopefully we’ll be able to integrate the changes into the plugin itself soon).

In Azure SQL Database v12 there were some great changes made to improve write performance. Since this plugin queries the event_log Microsoft was kind enough to sort out some improvements that can be made for users on Azure SQL Database v12.

As always, make the modifications that best suit your needs. The following are just suggestions that may help improve performance that you can do now while we validate and verify the integration into our plugin.

1. Reduce the polling frequency

Consider reducing the polling frequency to better suit your needs and the needs of your system. The default polling interval is 60 seconds. Try modifying this to 120 (2 minutes) to decrease pressure on sys.event_log. This should help overall system performance while making sure you still get the metrics / data you need from the plugin. Find an increment that works best for your needs and your system.

To make this change you can…

  1. Edit the newrelic.json where the plugin is installed
  2. Set the “poll_interval” to something like 300 (if you do not see “poll_interval” in the file simply add it to the json).
  3. Restart the plugin host - depending on if you used the New Relic Platform installer or you installed the plugin manually the steps on this might vary. Please see the plugin docs for more details

2. Reduce the number of queries to sys.event_log per interval

Another change to consider would be to reduce the number of event_log queries per iteration from 2 to 1. In the plugin code on Github today, we attempt to optimize the data being returned from the query. This code can be found in the ServiceInterruptionEvents.AzureSQL.sql file. The following is the content of the file as it is today:

DECLARE @latestEventWindow datetime = (SELECT
                 MAX(e.end_time)
          FROM sys.event_log e
                 JOIN @Types t
                       ON e.event_subtype_desc = t.EventSubType
          WHERE DATEADD(MINUTE, 9, e.end_time) >= GETUTCDATE())

SELECT
       CASE
              WHEN t.DatabaseName = '' THEN 'N/A' ELSE t.DatabaseName
       END                                      AS DatabaseName,
       t.EventType,
       t.Description,
       ISNULL(event_count, 0)     AS EventCount
FROM @DBAndTypes t
LEFT JOIN sys.event_log e ON t.DatabaseName = e.database_name
       AND t.EventSubType = e.event_subtype_desc
       AND @latestEventWindow = e.end_time
/*{WHERE}*/
-- Beware of including items from sys.event_log in the WHERE. It will cause the LEFT JOIN to act like a JOIN.
ORDER BY t.DatabaseName,
       t.EventType,
       t.Description

Unfortunately, the current implementation of event_log in Azure SQL Database v12 does not benefit from the scoping clauses in the query. The data stored in the remote blobs is currently scanned completely into memory before being filtered.

(Note: To find out if you are currently on Azure SQL Database v12 you can run the @@version t-sql command while connected to your instance.)

Alternatively we can modify the query to simply make one query to the sys.event_log:

 INSERT INTO @Types
       VALUES ('Connectivity', 'login_failed_for_user', 'Login Failed')
 INSERT INTO @Types
       VALUES ('Throttling', 'long_transaction', 'Long Transaction')
 INSERT INTO @Types
       VALUES ('Throttling', 'excessive_lock_usage', 'Excessive Lock Usage')
 INSERT INTO @Types
       VALUES ('Throttling', 'excessive_tempdb_usage', 'Excessive TempDB Usage')
 INSERT INTO @Types
       VALUES ('Throttling', 'excessive_log_space_usage', 'Excessive Log Space Usage')
 INSERT INTO @Types
       VALUES ('Throttling', 'excessive_memory_usage', 'Excessive Memory Usage')
 INSERT INTO @Types
       VALUES ('Engine', 'deadlock', 'Deadlock')
 INSERT INTO @Types
       VALUES ('Throttling', 'reason_code', 'Other Throttling Code')
 
 
INSERT INTO @DBAndTypes
       SELECT
              name   AS DatabaseName,
              EventType,
              EventSubType,
              Description
       FROM   (SELECT
                                  name
                           FROM sys.databases
                           UNION
                           SELECT
                                  '')
                     AS DBs,
                     @Types
 

-- Since the log updates about 2-4 minutes after the end time,
 -- exclude items more than 9 minutes old
 -- Join to @Types to limit scope of max end_time to reduce noise from non aggregated items
 
 -- Cache sys.event_log into a table variable to avoid multiple calls to the view.
-- Cache table only has necessary columns needed for eventual output.
Declare @EventLogCache TABLE
(
       database_name sysname,
       start_time datetime,
       end_time datetime,
       event_type nvarchar(64),
       event_subtype_desc nvarchar(64),
       event_count int,
       PRIMARY KEY (database_name)
);    
 
insert into @EventLogCache (database_name, start_time, end_time, event_type, event_subtype_desc, event_count)
select database_name, start_time, end_time, event_type, event_subtype_desc, event_count
from sys.event_log
 DECLARE @latestEventWindow datetime = (SELECT
                     MAX(e.end_time)
              FROM @EventLogCache e
                     JOIN @Types t
                           ON e.event_subtype_desc = t.EventSubType
              WHERE DATEADD(MINUTE, 9, e.end_time) >= GETUTCDATE())
 
 
 
SELECT
       CASE
              WHEN t.DatabaseName = '' THEN 'N/A' ELSE t.DatabaseName
       END                                      AS DatabaseName,
       t.EventType,
       t.Description,
       ISNULL(event_count, 0)     AS EventCount
FROM @DBAndTypes t
 LEFT JOIN @EventLogCache e ON t.DatabaseName = e.database_name
       AND t.EventSubType = e.event_subtype_desc
       AND @latestEventWindow = e.end_time
 /*{WHERE}*/
 -- Beware of including items from sys.event_log in the WHERE. It will cause the LEFT JOIN to act like a JOIN.
 ORDER BY t.DatabaseName,
       t.EventType,
       t.Description

The Microsoft and New Relic teams are working hard to provide you with an amazing experience around monitoring your Azure SQL Database. We are always looking for ways to improve things so please let us know your experience with the suggestions above!

1 Like