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_USERSOFTWAREMicrosoftMicrosoft SQL ServerSMOQueryIsolationSSMS] “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!
The post Running SSMS 2016 in Read Uncommitted Mode first appeared on SQLvariations: SQL Server, a little PowerShell, maybe some Power BI.
