Running SSMS 2016 in Read Uncommitted Mode

Back in July when we received the first official release of SSMS not tied to an engine release we got a new SQL PowerShell module and we also received the ability to run SSMS in Read Uncommitted mode via a registry setting.

I was hopeful that this new option would help with situations like this Connect item Adam Machanic filed: “SSMS Gets Blocked and Locks Up During Index Creation

Unfortunately, the locks that index creation takes are not the kinds that can be avoided with this new feature. I have been running this for a while but I don’t do a ton of database maintenance/administration these days, mostly B/I work, so I probably don’t run into a situation where this would help very often.

I wanted to get the word out about this in case it does help someone else. To get this to work, just take the text below and put it into a simple text file on your desktop named [something].REG and then double-click on the file.

Windows Registry Editor Version 5.00 [HKEY_CURRENT_USER\SOFTWARE\Microsoft\Microsoft SQL Server\SMO\QueryIsolation\SSMS] “Prefix”=”read uncommitted” “Postfix”=”read committed”

David Shiflet ( t ) from the SQL team mentions:
“You could also use snapshot isolation level instead of read uncommitted if your database has it enabled.”

You’ll probably have to try to navigate Object Explorer in SSMS 2014 side-by-side with SSMS 2016 to know for sure if this change is having an impact. Please let me know if this works for you!

Please Share This:

Share on facebook
Share on twitter
Share on linkedin

You may also like:

3 Responses

  1. Thanks for this! I’m going to try this out for sure, as I’ve been really tired of having SSMS lock up when I’m doing various automated database builds that require schema lock on model. I think those creates somehow impact object explorer due to locks perhaps on schemas it’s trying to read.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow:

Subcribe to Blog Via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

What I'm Saying on Twitter

Subscribe via feedburner