Created
September 24, 2014 15:19
-
-
Save benbrandt22/f6d86b1f5e4576be195b to your computer and use it in GitHub Desktop.
Sql performance monitoring with EF6 through interception
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
System.Data.Entity.Infrastructure.Interception.DbInterception.Add(new SqlMonitorInterceptor()); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); | |
} | |
} | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<configuration> | |
<appSettings> | |
<add key="sqlPerformance_warningThresholdMilliseconds" value="1000"/> | |
</appSettings> | |
</configuration> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment