It’s not uncommon for me to see support professionals running SELECT queries against production databases that cause major performance issues due to shared locks. By default SQL Server Management Studio (SSMS) has its transaction isolation level set to READ COMMITTED. If your support users only have read access to your database I would suggest having them change their default transaction isolation level in SSMS. This can solve a lot of headaches caused by seemingly benign queries causing major log jams of locks. Although I generally do ask support users to use NOLOCK hints, there’s always the chance they will forget.
How do I do this?
From the tools menu select options.
Under Query Execution/SQL Server/Advanced, change the value of SET TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED.
This setting will only take effect on new connections created, so any existing sessions you have created will not have their transaction isolation level set to READ UNCOMMITTED.
Caveats
Of course there’s the caveat of performing dirty reads (i.e. you might be reading uncommitted data). In the case of what most support users do, reading dirty data might be good enough.
Thanks for tip
Where else could this READ UNCOMMITTED setting be used safely? On stored procedures run on Reporting Read-Only Databases? From frozen Tableau tables? From lookups on relatively static tables serving as Foreign Keys? Anywhere else?
Hi John,
Generally you should be okay on reporting databases if you’re okay with the results not being 100% accurate. Ideally, snapshot isolation is the best answer so far as getting a read consistent view that is using optimistic locking.