Skip to content

Instantly share code, notes, and snippets.

@benbrandt22

benbrandt22/ApplicationStartup.cs

Created September 24, 2014 15:19
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save benbrandt22/f6d86b1f5e4576be195b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save benbrandt22/f6d86b1f5e4576be195b to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. benbrandt22 created this gist Sep 24, 2014.
    2 changes: 2 additions & 0 deletions ApplicationStartup.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,2 @@

    System.Data.Entity.Infrastructure.Interception.DbInterception.Add(new SqlMonitorInterceptor());
    76 changes: 76 additions & 0 deletions SqlMonitorInterceptor.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,76 @@
    using Common.Logging;
    using System;
    using System.Configuration;
    using System.Data.Common;
    using System.Data.Entity.Infrastructure.Interception;
    using System.Diagnostics;

    namespace MySampleApp.Data.Sql
    {
    public class SqlMonitorInterceptor : IDbCommandInterceptor
    {
    private static readonly ILog logger = LogManager.GetCurrentClassLogger();
    private static readonly int sqlWarningThresholdMs = int.Parse(ConfigurationManager.AppSettings["sqlPerformance_warningThresholdMilliseconds"]);
    private readonly Stopwatch _stopwatch = new Stopwatch();

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    CommandExecuting();
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    CommandExecuted(command, interceptionContext);
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    CommandExecuting();
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    CommandExecuted(command, interceptionContext);
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    CommandExecuting();
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    CommandExecuted(command, interceptionContext);
    }

    private void CommandExecuting() {
    _stopwatch.Restart();
    }

    private void CommandExecuted<TResult>(DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
    {
    _stopwatch.Stop();
    LogIfError(command, interceptionContext);
    LogIfTooSlow(command, _stopwatch.Elapsed);
    }

    private void LogIfError<TResult>(DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
    {
    if (interceptionContext.Exception != null)
    {
    logger.ErrorFormat("Command {0} failed with exception {1}",
    command.CommandText, interceptionContext.Exception);
    }
    }

    private void LogIfTooSlow(DbCommand command, TimeSpan completionTime)
    {
    if (completionTime.TotalMilliseconds > sqlWarningThresholdMs)
    {
    logger.WarnFormat("Query time ({0}ms) exceeded the threshold of {1}ms. Command: \"{2}\"",
    completionTime.TotalMilliseconds, sqlWarningThresholdMs, command.CommandText);
    }
    }
    }

    }
    5 changes: 5 additions & 0 deletions web.config
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,5 @@
    <configuration>
    <appSettings>
    <add key="sqlPerformance_warningThresholdMilliseconds" value="1000"/>
    </appSettings>
    </configuration>