BACKGROUND

Recently, I was on a project in which the customer needed real time reporting from a custom order management application.  The application ran on a Microsoft SQL Server 2005 back end.  The solution needed to provide reports, pulled from live data, without causing any database contention that would negatively impact the performance of the application.  Normally, this would not be a problem as you can write reports to read uncommitted and not worry about placing locks on database objects.  The problem, as it turned out, is that all end users of the application needed to be able to run reports against the one million+ row order tables.  As you can imagine, giving this functionality to over 100 concurrent users can cause resource availability issues on the server whether or not there is any blocking.  So, I needed a solution that would address this reporting requirement without negatively impacting the application’s performance.