ORM Profiling Tools for the .NET Platform
Sadly the terms “ORM” and “performance problems” often travel together. By hiding the underlying SQL from the developers, ORMs can offer a huge productivity boost. Unfortunately they also make it easy to generate ridiculously bad queries without realizing it. Usually database administrators can find the offending code by cross-referencing an offending stored procedure with the code that calls it. But with the dynamically generated SQL that ORMs rely on, that simple trick rarely works. So we are going to take a spin around some beefier tools for ORM profiling.
NHibernate Profiler (NHProf) includes what you would expect from a base-line product. In addition to keeping overall statistics, it shows a list of recent queries with the full SQL being called and back-traces to the code calling it complete with stack traces. The interface is clean and heavily cross-referenced, making it easy to switch from examining an interesting query to, for example, to looking at all the queries a block of code uses.
The real winner for NHibernate Profiler is alerts feature. Some alerts indicate immediate problems like database errors or accidentally using a session from multiple threads. Other alerts indicate possible issues like a large number of individual writes that probably should be grouped into a single batch. There are also alerts that warn of designs that lend themselves to future problems like unbounded result sets.
An unbounded result set is where a query is performed and does not explicitly limit the number of returned results using SetMaxResults() with NHibernate, or TOP or LIMIT clauses in the SQL. Usually, this means that the application assumes that a query will always return only a few records. That works well in development and in testing, but it is a time bomb waiting to explode in production.
The down-side of NHibernate Profiler is that it is somewhat invasive. You cannot attach to a running process, it has to be configured up front. While the recommended way is to include a reference to NHibernate Profiler directly in your code, users of log4net can also activate the profile via an app.config setting.
NHibernate Profiler is a commercial product that starts at about $300 dollars per user.
LINQ to SQL
DataContext logging is the first thing developers will probably consider when looking into LINQ to SQL issues. The DataConext object has a Log property that will route all SQL it generates to the TextWriter of your choice. Though free and easy, the sheer volume of raw SQL it spites out makes it of questionable use for non-trivial applications.
LINQ to SQL Profiler (L2SProf) is by the same company that built NHibernate Profiler and appears to work in a similar fashion. In addition to real-time profiling, you can configure it to write log files to be analyzed later. Like NHProf, it runs about $300 dollars per user. Currently it is in beta.
Huagati Query Profiler is another invasive profiler. They flat out say that “the runtime components are designed to be integrated and distributed with applications that are using either Linq-to-SQL, or LLBLGen Pro for accessing SQL Server.” Fortunately the profiler can be turned on and off at runtime as needed.
The user interface for Huagati is downright archaic. It consists of a single massive grid taking up half the screen with the SQL and stack traces beneath it. It lacks all of the niceties that NHProf/ L2SProf have like alerts and summary screen, but it does have one compelling feature for the hard-core developer, detail.
Huagati’s profiler doesn’t just give absolute durations and row counts, it really digs deep and pulls out all the information SQL Server makes available. Instead of just the round-trip time, you get to see important information like how much time was spent compiling the query verses executing it. I/O is included and you can even see the actual execution plan.
Huagati Query Profiler is a commercial product that starts at $50 dollars for a limited version and $120/user for the full version.
ADO.NET Entity Framework
The story here is downright pitiful. First of all, you don’t even get basic SQL logging like what’s available in LINQ to SQL. Moreover, it appears that no one is making profilers that work with it.
There are plenty of other ORM frameworks for .NET, but they are all pretty much in the same boat as ADO.NET Entity Framework. Aside from the two profiling tools mentioned here, there is little or no support for anything above using your database built-in profiler.
NHProf is coming for LINQ 2 SQL and Entity Framework, too
a couple of details
Thanks for the review. :)
Just a couple of minor details / things I'd like to clarify. I know it can't all be covered in a single article but I think these are important details so I hope you don't mind if I post them as a follow-up comment.
1) On invasiveness - to make the runtime logging less invasive, and to make the logs easier to navigate with no redundant 'fluff' we have runtime filters that can be controlled from code and/or from config settings in the application's own app.config/web.config. There is a whole bunch of built-in filters targeting various expensive and/or unwanted operations (high I/O, long execution times, low # of page reads per second (i/o contention and/or lock blocking), table scans, missing indexes etc). A summary of/help for the built-in runtime filters can be found here: www.huagati.com/L2SProfiler/runtimehelp/ . In addition to that, it is very easy to implement own custom filters. The filters are simple classes that inherit from a base class in the runtime component, and they can inspect all the data that the logging component has retrieved from SQL Server when filtering what to log and what not to log. This allows developers to target and log only queries that have a negative impact db-side, and/or that fulfill whatever arbitrary criteria they want, while everything else will just be bypassed and never hit the logs.
2) On alerts: although we don't yet provide any overview summaries such as total number of datacontext objects created etc we do have visual alerts at the query level. Both in the form of colored bars drawing attention to excessive I/O and/or long execution times, as well as popups for missing indexes.
I have a couple of blog posts in my blog with both a basic intro to L2S profiling, as well as more details on the more advanced filter options:
In the pipeline for future versions are:
a) a logging component for EF
b) a centralized log repository with more advanced report options, and log aggregation
We'll look into making the UI more pleasing - the idea with the grid is to get an easy overview showing as much data as possible but I'm all for UI eye candy so please keep an eye on upcoming versions. :)
I don't think adding a logging framework would actually be that difficult, but I don't have a lot of use for such a thing as SQL Profiler tells me what I need to know right now.
Entity Framework Profiler
It give you the same sort of interface and suggestions that the other does.
We hope to be able to take it to public beta soon.
There is also another sibling in that family, Hibernate Profiler, which allow you to profile Hibernate Java application.
SQL Tracing for EF
This project includes a toolkit for making your own provider wrapper:
Mike Hartington Jul 26, 2015