Performance tuning tips from the SQLGrease team

How to change your default transaction isolation level in SSMS

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.