THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Did You Know? Query Options Setting in SSMS

Hi folks

I’m back from an amazing week at PASS and an awesome reception for SQL Server MVP Deep Dives Volume 2.

I’m starting to plan and write my next book on SQL Server 2012 Internals so blogging will kept to a minimum, not that I’ve had a lot of time to blog lately.

However, while working on the final quiz for my Concurrency class through SSWUG, I noticed something peculiar about the Query Options | Advanced dialog in Management Studio.  You have the option of choosing a default ISOLATION LEVEL and a DEADLOCK PRIORITY, and both those values have not been updated since before SQL Server 2005. The ISOLATION LEVEL choices do NOT include SNAPSHOT, which was introduced in SQL Server 2005 and the DEADLOCK_PRIORITY only shows Low and Normal. The value High and the numbers –10 through 10 were added back in SQL Server 2000!

SNAGHTML1bb0c86

So my first thought was that nobody had updated this dialog since then, but then I noticed that there was an option you can see under “Results” in the left pane, to give you Multiserver results, and Multiserver queries weren’t added until SQL Server 2008.

So what do you think? Should Microsoft update this dialog to give you all the possibilities?

~Kalen

Published Wednesday, October 19, 2011 6:09 PM by Kalen Delaney
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mike Lawell said:

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.

October 20, 2011 12:54 PM
 

Kalen Delaney said:

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

October 20, 2011 1:42 PM
 

Mike Lawell said:

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

October 20, 2011 3:56 PM
 

Jason said:

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

November 22, 2011 3:00 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement