how to store MVC MiniProfiler results

Question

I want to save the results of the MVC MiniProfiler to a sql server database. I profile a high load mvc4 page to spot a tricky performance problem which is non-reproducible on our test or development system and only happens sporadically on the production server.

What is the best way to hook into the mini profiler? Is there an existing extension to do that?

Answer 1

I've just been setting up MiniProfiler to save the results to SQL Azure, it's fairly easy. We're using MiniProfiler.MVC3 to take care of all the wiring up as described here.

The table create script is embedded into the assembly with the SqlServerStorage.TableCreationScript static field, so you can use that, but while digging into the code I found the latest development branch has enhanced the script slightly by adding some indexes. The table structure is otherwise unchanged, so it still works with the latest package available on nuget.

At the time of writing here is direct link to the latest table create script.

After that the only thing you need to do is set-up MiniProfiler to use SQL with single line of code:

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

If you're not using SQL Azure, that's it, but I found one issue when we tried to use it in Azure. I received the following exception (thank you ELMAH) when profiling tried to save:

System.Data.SqlClient.SqlException

Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

To solve this I had to add an additional (unused) column to the MiniProfilers table. Here is the beginning of the create table script in question:

create table MiniProfilers (     RowId integer not null identity constraint PK_MiniProfilers primary key clustered, -- Need a clustered primary key for SQL azure     Id    uniqueidentifier not null,     Name  nvarchar(200) not null, 

And since the Guid Id column was no longer the primary key, I added an additional index to ensure lookups are still fast:

create unique nonclustered index IX_MiniProfilers_Id on MiniProfilers (Id) 

Hope that helps.

---

Update

The change to support SQL Azure has been provided as a Pull Request and accepted. Thanks Jarrod.