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!
