1. Use (nolock) hints for EVERY table used in any report query. This is most commonly done by including the hint in any reporting Views and Stored Procedures (unless the SP is doing an update). Ideally, it should be done even in the transactional queries whenever a dirty read is acceptable (which is more often than most people think).
2. Try to avoid doing a security check in each view that accesses a single table or (especially) a single record. If you must do this... use the (nolock) hint to minimize any contention.
SQL Server does not seem to handle locks fast enough and every single system I have worked on has had lost transactions due to locks timing out. The usual 'cure' is to avoid doing reporting on the live system, but this is actually not usually necessary so long as you use dirty reads properly.
I have seen systems that generated reports from 5 Views. Each View included the same security check and so it was happening 5 times on every record returned to the report. Since the security check was always hitting a single record, this was also disrupting live performance and multiple reports were conflicting with each other.
I removed the security checks from the views and had a single "WHERE <ID> IN (SELECT <ID> FROM <Security Table> (nolock))" check. The performance went from taking 4 hours (on the report server) to running in under a minute (on the live server). Your Results May Vary !
I hope that this helps,
Siggy.
2. Try to avoid doing a security check in each view that accesses a single table or (especially) a single record. If you must do this... use the (nolock) hint to minimize any contention.
SQL Server does not seem to handle locks fast enough and every single system I have worked on has had lost transactions due to locks timing out. The usual 'cure' is to avoid doing reporting on the live system, but this is actually not usually necessary so long as you use dirty reads properly.
I have seen systems that generated reports from 5 Views. Each View included the same security check and so it was happening 5 times on every record returned to the report. Since the security check was always hitting a single record, this was also disrupting live performance and multiple reports were conflicting with each other.
I removed the security checks from the views and had a single "WHERE <ID> IN (SELECT <ID> FROM <Security Table> (nolock))" check. The performance went from taking 4 hours (on the report server) to running in under a minute (on the live server). Your Results May Vary !
I hope that this helps,
Siggy.
