Did You Know? Query Options Setting in SSMS

re: Did You Know? Query Options Setting in SSMS

Mike Lawell — Thu, 20 Oct 2011 16:54:53 GMT

That's a great question...but snapshot isolation is set at the database level, how would SSMS handle the snapshot default if the database didn't have snapshot isolation "turned on"?

It almost requires a separate check box that says use snapshot when allowed.

re: Did You Know? Query Options Setting in SSMS

Kalen Delaney — Thu, 20 Oct 2011 17:42:33 GMT

Hi Mike

Thanks for your input.

At the database level we ALLOW snapshot isolation, but the actual isolation level is always set at the SESSION level.

SSMS wouldn't have to 'handle' anything. It would issue the SET command for the iso level for each connection, and then the session might get an error, just as if you manually issues a SET to SNAPSHOT when the database didn't allow it.

As you suggest, there are probably other things that could be done within SSMS, but it's really up to whoever is using SSMS and issuing queries to have some clue (I know that's asking a lot. :-) ) There are certainly other things in SSMS that could mess you up if you don't know what you're doing!

Thanks

Kalen

re: Did You Know? Query Options Setting in SSMS

Mike Lawell — Thu, 20 Oct 2011 19:56:27 GMT

That's kind of what I thought it would do. Haha..yes. So true. It was a pleasure meeting you at PASS Summit.

re: Did You Know? Query Options Setting in SSMS

Jason — Tue, 22 Nov 2011 20:00:12 GMT

Definitely yes. Shapshot isolation needs to be available. All options need to be available. Are they still avialable via T-SQL? DBA would have to set alter database allow snapshot isolation. We have some 3rd party software database, I forced them to use snapshot isolation due to earlier trouble. It has been working well since. Beware your resource usage.

Jason

http://usa.redirectme.net