MiniProfiler SqlServerStorage becomes quite slow

Question

We use mini profiler in two ways:

1. On developer machines with the pop-up
2. In our staging/prod environments with SqlServerStorage storing to MS SQL

After a few weeks we find that writing to the profiling DB takes a long time (seconds), and is causing real issues on the site. Truncating all profiler tables resolves the issue.

Looking through the SqlServerStorage code, it appears the inserts also do a check to make sure a row with that id doesnt already exist. Is this to ensure DB agnostic code? This seems it would introduce a massive penalty as the number of rows increases.

How would I go about removing the performance penalty from the performance profiler? Is anyone else experiencing this slow down? Or is it something we are doing wrong?

Cheers for any help or advice.

Answer 1

Hmm, it looks like I made a huge mistake in how that MiniProfilers table was created when I forgot about primary key being clustered by default... and the clustered index is a GUID column, a very big no-no.

Because data is physically stored on disk in the same order as the clustered index (indeed, one could say the table is the clustered index), SQL Server has to keep every newly inserted row in that physical order. This becomes a nightmare to keep sorted when we're using essentially a random number.

The fix is to add an auto-increasing int and switch the primary key to that, just like all the other tables (why I overlooked this, I don't remember... we don't use this storage provider here on Stack Overflow or this issue would have been found long ago).

I'll update the table creation scripts and provide you with something to migrate your current table in a bit.

Edit

After looking at this again, the main MiniProfilers table could just be a heap, meaning no clustered index. All access to the rows is by that guid ID column, so no physical ordering would help.

If you don't want to recreate your MiniProfiler sql tables, you can use this script to make the primary key nonclustered:

-- first remove the clustered index from the primary key
declare @clusteredIndex varchar(50);

select  @clusteredIndex = name
from    sys.indexes
where   type_desc = 'CLUSTERED'
        and object_name(object_id) = 'MiniProfilers';

exec ('alter table MiniProfilers drop constraint ' + @clusteredIndex);

-- and then make it non-clustered
alter table MiniProfilers add constraint 
  PK_MiniProfilers primary key nonclustered (Id);

Another Edit

Alrighty, I've updated the creation scripts and added indexes for most querying - see the code here in GitHub.

I would highly recommended dropping all your existing tables and rerunning the updated script.