Mostly working...

Adventures in Coding
posts - 4 , comments - 0 , trackbacks - 0

Query Performance Logging with MiniProfiler


I have started to experiment with MiniProfiler to log our query performance. We currently use a mix of pure SQL and Entity Framework, since we need Fulltext search some pure SQL is unavoidable. Depending on the hotspots more queries will move back to pure SQL but for simplicity’s sake trivial queries will remain with EF.

Getting started is relatively easy once you figure out which part of the documentation applies – I found my ways through various Stackoverflow questions and GitHub.

Starting point is a RESTful ASP.NET Web API. To enable profiling I had to add a Profiling database connection to the web.config file and update Global.asax:

 
        void Application_Start(object sender, EventArgs e)
        {
            RegisterRoutes();
 
            enableProfiling = bool.Parse(ConfigurationManager.AppSettings["enable_profiling"]);
 
            if (enableProfiling)
            {
                var storage = new SqlServerStorage(ConfigurationManager.ConnectionStrings["Profiling"].ConnectionString);
                MiniProfiler.Settings.Storage = storage;
                MiniProfiler.Settings.SqlFormatter = new SqlServerFormatter();
                var factory = new SqlConnectionFactory(ConfigurationManager.ConnectionStrings["Profiling"].ConnectionString);
                var profiled = new MvcMiniProfiler.Data.ProfiledDbConnectionFactory(factory);
                Database.DefaultConnectionFactory = profiled;
            }
        }
 
        protected void Application_BeginRequest()
        {
            if (enableProfiling) { MiniProfiler.Start(); } //or any number of other checks, up to you
        }
 
        protected void Application_EndRequest()
        {
            if(enableProfiling)
                 MiniProfiler.Stop(); //stop as early as you can, even earlier with MvcMiniProfiler.MiniProfiler.Stop(discardResults: true);
        }

 

After this the only thing left to do is to add the actual query logging. For now I am only interested in EF SQL queries so I added a helper method which I use instead of directly instantiating the DB context:

 

    public static class FooEntitiesHelper
    {
        public static FooEntities Create()
        {
            var builder = new EntityConnectionStringBuilder(ConfigurationManager.ConnectionStrings["FooEntities"].ConnectionString);
            var sqlConnection = new SqlConnection(builder.ProviderConnectionString);
            var profiledConnection = new EFProfiledDbConnection(sqlConnection, MiniProfiler.Current);
            return profiledConnection.CreateObjectContext<FooEntities>();
        }
    }

Now every query that is using FooEntities is logged to the profiling database:

    using (var context = FooEntitiesHelper.Create())
    {
        //...
    } 

 

This is only the starting point – but I will now have the data to see where the hotspots and problems are and re-write the queries or update the database accordingly.

Print | posted on Wednesday, February 22, 2012 9:11 PM | Filed Under [ performance C# .NET SQL Server ]

Feedback

No comments posted yet.
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 

Powered by: