Starting with SQL Server 2005, you can now use Snapshot Isolation to eliminate blocking READ operations and get rid of those pesky SQL locks.
I've been accustomed to Snapshot Isolation for many years. In other databases, it's called Multi Version Concurrency Control (MVCC) where writers don't block readers and readers don't block writers. The drawback is there may be a slightly higher I/O usage since data needs to be copied (isolated) on every write transaction (you don't overwrite the data on the same physical area). Snapshot Isolation is a better choice for OLTP operations where you expect a large number of reads and moderate writes. By not requesting a READ lock, your system will be able to scale much higher.
By default, SQL Server 2005 uses the same concurrency mode as in SQL Server 2000. To use the new Snapshot Isolation, you need to make some changes to the database. Fortunately, Microsoft has enhanced their ALTER statement to allow switching to the new isolation mode.
Make sure your database has no running operations (disable remote connections will help). Then run this SQL command to enable snapshot isolation and update the database isolation level.
ALTER DATABASE [DATABASE_NAME] SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE [DATABASE_NAME] SET READ_COMMITTED_SNAPSHOT ON;
Restart your SQL server and you should be running the new Snapshot Isolation level. You can execute this query to see the isolation mode used and verify the changes are taken into effect:
SELECT [name], snapshot_isolation_state_desc, snapshot_isolation_state, is_read_committed_snapshot_on
FROM sys.databases
WHERE [name] = '[DATABASE_NAME]';