THE SQL Server Blog Spot on the Web

Welcome to - 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!


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?


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



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!



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.


November 22, 2011 3:00 PM

king said:



January 16, 2018 11:33 PM

aaaa said:

mt0417 mt0417

April 16, 2018 8:50 PM

chenyingying said:


May 6, 2018 11:05 PM

linying123 said:


May 10, 2018 8:11 PM

linying123 said:



July 16, 2018 8:35 PM

obat diabetes said:

August 1, 2018 11:20 PM

obat bronkitis anak secara alami said:

August 7, 2018 7:50 PM

obat sinusitis said:

August 10, 2018 7:23 PM

masker wajah alami untuk menghilangkan jerawat said: Masker wajah alami untuk menghilangkan jerawat Obat liver alami aman dan terbaik Obat herbal sinusitis kronis terbaik Obat bronkitis anak secara alami Obat Kanker Payudara terbaru 2018 Cara meningkatkan nafsu makan secara alami Obat herbal diabetes menurunkan gula darah Obat herbal kusta terbaik Obat benjolan di leher tanpa operasi Obat Herbal Kanker Otak tuntas hingga akar Obat TBC alami aman tanpa efek samping Obat radang amandel sembuh tanpa operasi Obat asam urat alami tanpa efek sampinfg Obat tetes Sariawan secara alami Cara mengobati mata bengkak dengan cepat Obat tradisional kanker lambung terbaik Obat kanker paru paru Obat usus buntu alami tanpa operasi Obat asam lambung naik cara alami

August 20, 2018 9:00 PM

chenjinyan said:



August 22, 2018 11:26 PM

obat herbal koresterol terbaik said:

August 23, 2018 10:59 PM

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:22 AM

obat benjolan di tubuh said:

August 30, 2018 7:41 PM

obat wasir said:

September 4, 2018 8:28 PM

obat katarak said:

September 16, 2018 5:47 PM

obat limfoma said:


September 23, 2018 6:51 PM

yanmaneee said:">">

June 29, 2020 11:09 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement