November 26, 2012 at 11:44 am
SQL_ME_RICH (11/26/2012)
This is what happens when I respond to posts before coffee - thanks for that Gail!:crazy:
😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 26, 2012 at 11:46 am
hehe - thank you too Perry.
🙂
November 26, 2012 at 12:22 pm
To ensure in the future that the option persists you could use policy management to create a new policy enforcing the snapshot isolation state. You'll need to select the Database facet and check for the @SnapshotIsolationState for snapshot isolation or @IsReadCommittedSnapshotOn for read committed snapshot isolation
😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 26, 2012 at 12:52 pm
That is a project I was planning to define once we get out of our 'code freeze' period that the company is in right now. That won't be until Jan.2013, but this gives me an idea on some things I was already working on. Again - thank you both for your tremendous response and support!
🙂
November 26, 2012 at 1:27 pm
Doesn't stop you creating the policies in your dev system, they'll be ready to deploy in Jan then 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 26, 2012 at 1:28 pm
Doing that locally - too many cooks in the kitchen can spoil the soup.
😉
December 5, 2012 at 7:21 pm
Just one other concern that I am trying to understand (because I am encountering conflicting information from BOL, a tool called SQL Enlight, and various postings out here in the community (not the SQLServerCentral community per se, but just in general).
I'm being told that the use of more than 1 hint in a T-SQL statement in SQL 2005 is deprecated unless you specify the WITH keyword with the hint. Now - in particular to this thread that I have been discussing the effects of (NOLOCK) on a database isolation level change with, I am needing to know if a piece of code in a Stored Procedure is in fact going to use (NOLOCK) in multiple places if the keyword WITH is not accompanying it. This is what is stated in SQL Enlight (a T-SQL analysis tool) regarding this rule...
'It is recommended that hints be specified using the WITH keyword. SQL Server 2005 does not support the use of more than one hint in a T-SQL statement, unless the WITH keyword is specified.'
Now - I have seen others say that this is not true, but they were not addressing the specific (NOLOCK) hint, nor were they from out here. So - I am hoping for some guidance from all of you on this. Is it true that if I have a T-SQL statement using (NOLOCK) in multiple places in my Stored Procedure (FROM, INNER JOIN, etc...) that those hints are being bypassed all together if the WITH keyword is not leading the hint?
Thanks much to you all in advance!
December 6, 2012 at 1:41 am
SQL_ME_RICH (12/5/2012)
Is it true that if I have a T-SQL statement using (NOLOCK) in multiple places in my Stored Procedure (FROM, INNER JOIN, etc...) that those hints are being bypassed all together if the WITH keyword is not leading the hint?
No.
From BoL (Table Hints):
WITH ( <table_hint> ) [ [ , ]...n ]
With some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. Table hints also must be specified with parentheses.
Important:
Omitting the WITH keyword is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints should be specified alone. For example:
FROM t (TABLOCK)
When the hint is specified with another option, the hint must be specified with the WITH keyword:
FROM t WITH (TABLOCK, INDEX(myindex))
If you try to specify a hint without the WITH where it's not permitted, they query throws an error and fails. It is not silently ignored.
The 'does not support more than one' is the WITH (TABLOCK, INDEX = 1, UPDLOCK) scenario, not multiple (NOLOCK) hints in a query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 6, 2012 at 8:19 am
SQL_ME_RICH (11/22/2012)
...Is there any good reason you can think of to always be using the WITH(NOLOCK) hint in all queries?...Wouldn't the WITH(NOLOCK) defeat the whole purpose of discouraging against dirty reads? Or is it such a performance increase that it's worth it to be used indiscriminately? I've read that DEVS will use it to get around back database design on long running queries
people used to use NOLOCK hints or READ UNCOMMITTED isolation level back in the SQL Server 2000 and earlier days, because SQL Server would hold locks on SELECT queries and create a situation where readers blocked writers and writers blocked readers. In an OLTP system this caused the system to respond slower than it really should have. In SQL Server 2005, when SNAPSHOT was introduced, it provided the best of both worlds, readers no longer blocked writers and writers no longer blocked readers, clean query results, with the cost of higher TEMPDB usage. If you're using SNAPSHOT isolation then NOLOCK hints will not significantly improve performance unless there is a bottleneck in TEMPDB.
Having said that, one case where I've seen the combination of SNAPSHOT isolation and NOLOCK can produce wrong results is if you have alot of queries that utilize windowed functions, such as ROW_NUMBER() OVER().
December 6, 2012 at 11:05 am
Gail, Chris - thank you for clearing this up. Like I said, I did see the part that Gail shared, but was getting conflicting information, and just wanted to be 100% sure.
Thanks again!
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply