Building an mvc-mini-profiler dashboard

Update: The sample project is now available on Google Code.

In a departure from my normal blog topics, I thought I would dip a toe in writing about web development with ASP.NET MVC.  I have recently integrated the wonderful mvc-mini-profiler tool into JobSeriously and wanted to share how I set up a great dashboard for it.

They typical use case for mvc-mini-profiler is to embed a floating UI on the page which looks like a hovering chicklet in the corner.  Clicking the mvc-mini-profiler UI shows a popup box that has detailed timings.  This works if you only want to profile a few people (e.g. only the site developers) and you want these stats in their face all the time.  However, this does not work well if you want to profile a segment of your real users.  Additionally, this approach usually relies on authentication cookies which can be stolen (as happened to some folks on the StackOverflow site itself).  It would be bad enough to have a bad guy effectively impersonating you on the site, you certainly wouldn’t want to expose the internals of your code via your profiler as well.

What I wanted to do for JobSeriously was to log these timings to our database and be able to review the results on a dashboard that only admins could see.  Using out-of-the box functionality of the mvc-mini-profiler, I was able to set up logging to the database fairly easily.  Then I sprinkled in some Google Visualization API and was able to come up with a dashboard that looks like this:

My requirements were that the dashboard should help me target the worst offenders on the list without distracting me with outliers.  Therefore, I decided to use box plots to visualize the data.  For those of you who may not be familiar with box plots, the box portion shows every sample between the 25th and 75th percentiles.  The line at the top and bottom of the box plot shows the top and bottom 25% of samples. This helps you see if your distribution is skewed towards the top or  to the bottom (as is the case with JobSeriously).  Using the box plot also allows you to quickly see how much variability there is in page load times.  In short, the boxes represent what the majority of your users are experiencing while the wicks show the extremes. Read more about box plots on WikiPedia.

The first step to setting up the dashboard is to configure your database with the proper tables for mvc-mini-profiler.  Fortunately, the profiler comes with the necessary scripts built in.  However, the create script is actually embedded in the compiled code with no way to get to it.  I recommend that you manually extract the code and create your own create/rollback scripts that suit your environment.  The script is actually located at the bottom of the SqlServerStorage source code file (extraced SQL Code).

After configuring the SQL server itself, we need to configure the mvc-mini-profiler to log to the SQL server instead of outputting the results to the page.  Luckily, this is a single line of code change in the Globals.ascx.cs file.

MiniProfiler.Settings.Storage = new SqlServerStorage("your SQL connection string");

I placed the preceding code into my OnApplicationStarted method.

Next, we want to add a controller (or a method to an existing controller) that will display the results.  The code is very simple in that it just executes a single SQL statement and passes the data directly to the view we will create next.

    #region Imports

    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Web.Mvc;

    using Dapper;

    #endregion

    [Authorize(Roles = "Administrator")]
    public class PerformanceController : Controller
    {
        #region Constants and Fields

	// Change this to point to your SQL Server
        private readonly string connectionString = "Sql server connection string";

        #endregion

        #region Public Methods

        public ActionResult Index()
        {
            const string sql =
                @"select SRC.Name as WebRoute, count(SRC.name) as Samples, avg(DurationMilliseconds) as AvgD, min(DurationMilliseconds) as Low, max(DurationMilliseconds) as High, max(Ranks.Under10) as LowSample, max(Ranks.Over90) as HighSample, max(LowRanks.LongestDuration) as BoxLow, max(HighRanks.LongestDuration) as BoxHigh
from
(
	select Name,
		DurationMilliseconds,
		Dense_Rank() over (partition by Name order by DurationMilliseconds) as drank
	from MiniProfilers
) AS src
LEFT OUTER JOIN (
	select Name, floor( (max(src2.drank) - min(src2.drank)) * 0.25 ) + 1 as Under10, ceiling( (max(src2.drank) - min(src2.drank)) * 0.75 ) + 1 as Over90
	from
	(
		select Name,
			DurationMilliseconds,
			Dense_Rank() over (partition by Name order by DurationMilliseconds) as drank
		from MiniProfilers
	) AS SRC2
	group by name
) AS Ranks ON Src.Name = Ranks.Name
LEFT OUTER JOIN (
	select Name,
		DurationMilliseconds as LongestDuration,
		Dense_Rank() over (partition by Name order by DurationMilliseconds) as drank
	from MiniProfilers
	group by name, DurationMilliseconds
) AS LowRanks ON Src.Name = LowRanks.Name AND Ranks.Under10 = LowRanks.drank
LEFT OUTER JOIN (
	select Name,
		DurationMilliseconds as LongestDuration,
		Dense_Rank() over (partition by Name order by DurationMilliseconds) as drank
	from MiniProfilers
	group by name, DurationMilliseconds
) AS HighRanks ON Src.Name = HighRanks.Name AND Ranks.Over90 = HighRanks.drank
group by SRC.Name
order by BoxHigh DESC;";
            IEnumerable<dynamic> data;

            using (DbConnection conn = GetOpenConnection())
            {
                data = conn.Query(sql);
            }

            return View(data);
        }

        #endregion

        #region Methods

        /// <summary>
        /// 	Returns a connection to Sql Server.
        /// </summary>
        protected DbConnection GetConnection()
        {
            return new SqlConnection(connectionString);
        }

        /// <summary>
        /// 	Returns a DbConnection already opened for execution.
        /// </summary>
        protected DbConnection GetOpenConnection()
        {
            DbConnection result = GetConnection();
            if (result.State != ConnectionState.Open)
            {
                result.Open();
            }
            return result;
        }

        #endregion
    }

The code I used relies on a lightweight ORM called Dapper.  If you are not using Dapper, you may need to change the data = conn.Query(sql); line to use a DataReader to read the rows from the return set.

I know that SQL statement is a huge mess.  Unfortunately, MS SQL Server 2008 lacks some of the basic statistical functions required to make this a little simpler.  If you have any tips for how to make the SQL statement any better I would love to get some feedback.

The last step in creating the mvc-mini-profiler dashboard is to create the view.  You will likely need to change the MasterPage referenced in that example.  What the view does is include the Google Visualization API libraries and create the box plot with a table below it.  The table is page-able and sort-able.

<%@ Page Title="" Language="C#" MasterPageFile="~/Areas/Admin/Views/Shared/Admin.Master" Inherits="System.Web.Mvc.ViewPage<dynamic>" %>

<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">
    Performance Reporting
</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">

<div id="visualization" style="width: 100%; height: 450px;"></div>
<div id="dataTable"></div>

<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
    google.load('visualization', '1', { packages: ['table', 'corechart'] });
</script>
<script type="text/javascript">
    function drawVisualization() {
        // Populate the data table.
        var dataTable = new google.visualization.DataTable();

    	dataTable.addColumn('string', 'Route');
    	dataTable.addColumn('number', 'Low');
    	dataTable.addColumn('number', '25%');
    	dataTable.addColumn('number', '75%');
    	dataTable.addColumn('number', 'High');
    	dataTable.addColumn('number', 'Count');
    	dataTable.addColumn('number', 'Average');

        <% foreach (var row in (IEnumerable<dynamic>)Model) { %>
    	dataTable.addRow(['<%: row.WebRoute %>', <%: row.Low %> , <%: row.BoxLow %> , <%: row.BoxHigh %> , <%: row.High %> , <%: row.Samples %>, <%: row.AvgD %> ]);
        <% } %>

    	var dataView = new google.visualization.DataView(dataTable);
        dataView.setColumns([0, 1, 2, 3, 4]);
    	dataView.setRows(0, 9);

    	var table = new google.visualization.Table(document.getElementById('dataTable'));
        table.draw(dataTable, { page: 'enable', pageSize: 10 });

        // Draw the chart.
        var chart = new google.visualization.CandlestickChart(document.getElementById('visualization'));
        chart.draw(dataView, { legend: 'none', 'title': 'Highest peaks', 'vAxis': {'title': 'Milliseconds (1000 = 1s)'} });
    };

    google.setOnLoadCallback(drawVisualization);
    </script>
</asp:Content>

Please let me know if you have any questions regarding my code.  If you are looking for a job, I would love to get some feedback on JobSeriously as well.