Creating a custom ASP.Net outage message that returns a 503

If you ever need to bring down a website for maintenance you should consider the impact that may have on search engines' ability to index your site. If your site returns an incorrect status code e.g. 404, then there is a possibility that search engines will de-index your pages.

In this scenario, Google recommends returning a 503 Service Unavailable status to any requests.

In ASP.Net this is simple to achieve - just stop the Application Pool for the site!

However what if you wish to display a user-friendly error page that shows some useful information e.g. contact details.

Most error codes in IIS can be mapped to custom error pages or URLs however a 503 is not one of these. In order to get around this, one method is to return the 503 response via some custom code.

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        Response.StatusCode = 503;      
 Response.Cache.SetCacheability(HttpCacheability.NoCache);
    }
}

This will work well for requests to this particular page but we want this custom error to show for all request to the site. One way to ensure this is to map this page as a custom 404 error. That way any requests to the site will be shown the friendly outage message and the search bots will get their 503. Everybody's happy!

Dealing with a sub-optimal SQL execution plan for an NHibernate query

We have been struggling with a sporadic performance issue on our ASP.Net website involving a certain SQL Server query generated by NHibernate.
The issue would occur every few days and from then on a particular query would go from taking 20ms to 20+ seconds. To determine what was causing the issue a SQL Profiler trace was run to narrow down the offending query.
The next step was to run that query in SQL Management Studio. This caused some initial confusion as the same query that was taking 20 seconds on the site would run virtually instantly in Management Studio.
After some research it was discovered that in order to match the compiled execution plan that is chosen by SQL Server you must ensure that all the settings are identical. By default the arithabort setting is on in Management Studio but off in ADO.Net. By adding set arithabort off to the start of the query we were able to replicate the performance issue.
Once we had the offending query we could select include actual execution plan and analyse the chosen execution plan. (In the meantime, an index rebuild provided a temporary fix for our issue while we worked on a permanent solution.)
Analysing the execution plan showed a large discrepancy between the expected number of rows and the actual number of rows for various parts of the query. In one part the estimated number of rows was approximately 1 where the actual was around 1800. This then combined with a couple of left outer joins and some non-sequential difficult-to-predict primary keys.
Optimising the query would be difficult due to the design of the NHibernate mapping and would require extensive modification and testing of the associated application. We decided to force recompilation of the execution plan on each query at the expense of some performance.
In order to do this we would need to change our NHibernate HQL query in the application to a Native SQL query. We copied the captured query generated by NHibernate and modified it to match the syntax required for an NHibernate native SQL query. We then added OPTION (RECOMPILE) to the query in order to force the recompilation of the query each time. Initial tests showed the performance hit to be minimal so we implemented the fix on production and all is well with the world.