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!
3 Responses
For anyone who is playing around with this, you may also be interested in this item about the ‘Dark Theme’ I spotted on Reddit: https://www.reddit.com/r/SQLServer/comments/5r9ryh/ssms_2016_dark_theme/
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.
Sheldon,
I’m looking forward to hearing if you experience much of a difference.