Problem We have two different situations in our SQL Server 2000 and 2005 application where we suspect we have locking caused by SELECT statements. First, we have reports that need to run in our production environment from a third party application. These seem to be the worst offenders since some of the reports are ad-hoc in a sense and others are the same report issued repeatedly. Second, as a portion of our OLTP application we have some fairly complex SELECT statements referencing fairly complex VIEWs. These are isolated to a specific portion of the application and we have a good sense of their usage. In either of these situations can you offer any suggestions to reduce the locking?
Solution Let's take a look at each situation independently to see how we can address the issue. Let's also balance that with the fact that SQL Server has a lock manager for a reason. That is to ensure data integrity in a highly concurrent database environment. So changing SQL Server's default behavior is not be a good idea and should be completely understood before making any changes.
With the reporting situation, see if the following options are possible:
For both the ad-hoc and repeatable reports, is it possible to execute the reports on another SQL Server i.e. a reporting server? The data can be replicated (transactional replication, log shipping, third party replication, etc.) in a number of different ways, but the data may be slightly out of date. However, it would prevent reporting from competing with OLTP applications.
For the repeatable reports, see if the report data can be pre-aggregated every minute or every five minutes. Once again the data will be slightly out of date, but the value can be calculated once then read repeatedly without causing any locking issues on the base tables every time the queries are issued. The locking would only occur the one time a minute or every five minutes when calculation is issued.
If these options are not possible then consider the following:
If there is no issue reading dirty data in your application and your reporting application supports session level parameters then consider using changing the isolation level for each of the sessions from your reporting application. Most likely they are following the SQL Server (2000 and 2005) default isolation level of READ COMMITTED. The READ COMMITTED isolation level does not permit dirty reads and prevents non-repeatable reads. As such, the appropriate isolation level in your circumstances would be READ UNCOMMITTED because no shared locks are issued and no exclusive locks are honored. As far as where and how to make the isolation level change in your application, reference the documentation from your reporting tool.
Here would be the sample code included at the start of the SQL Server session from your reporting application:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO
Since we broached the subject of isolation levels, let's briefly review the options:
SQL Server Isolation Levels
SQL Server Versions
Equal to a NOLOCK hint for an individual SELECT statement which means that shared locks are issued and no exclusive locks are honored.
Any transaction that explicitly sets the transaction isolation level to snapshot will not acquire share locks and will not wait on any locks. Rather, you will get the committed version of all rows at the time the transaction begins.
To permit this isolation level, it must be enabled at a database level before a session can use this option.
In the circumstance of the OLTP application locking with VIEWS, using one of the non default isolation levels probably does not make sense since you do not want to reduce the concurrency or read dirty data for the entire session. In that circumstance you probably just want to add the NOLOCK hint to your SELECT statements for only those sets of code. Keep in mind with the NOLOCK hint no shared locks are issued and no exclusive locks are honored.
Keep in mind adding the NOLOCK hint or changing the isolation level does change how either locking is handled for either an individual query or for all commands in the session. These changes should be thoroughly tested to validate they meet your needs.
Adding NOLOCK to all of your SELECT statements is not a good idea if reading dirty data is not acceptable, which is the case in most environments. So automatically adding NOLOCK hints is typically not a good practice in an OLTP environment.
Stay tuned for more tips on hinting in SQL Server and the associated benefit.