February 9, 2011 at 3:56 am
:blush:Hi All
The organization that I recently joined does almost all it's reporting off databases that are replications of live databases. Not my idea. It is what it is and it's not for me to change. Coupled with this, pretty much every bit of TSQL ever written based on these replicated databases, is littered with (NOLOCK) hints. Once again, whether or not I agree with the practise is irrelevant, it's not my role to convince people to change the practise. Their rationale for the (NOLOCK) hints is that omitting them is believed to hamper replication.
Anyways, I read the other day that the syntax used most of the time here (that is ...FROM mytable tbl (NOLOCK)...) is to be deprecated in a "future" release of SQL Server. Instead we should be using ...FROM mytable tbl WITH (NOLOCK)...
Now consider this. There are THOUSANDS upon THOUSANDS of stored procs, functions, views, rdls, SSIS packages, jobs, you name it - all riddled with this deprecated syntax. And as far as I can see, it's not a problem amenable to a "find and replace" solution because sometimes we have actually used WITH (NOLOCK), so using f and r we'd end up with WITH WITH (NOLOCK) in these cases. So I thought that maybe we could as a first step replace [WITH (NOLOCK)] with just [(NOLOCK)] and then replace all the [(NOLOCK)]s with [WITH (NOLOCK)]. That wouldn't be guaranteed to work because assumes a single space character is between the WITH and the (NOLOCK) but this won't always be the case. It could be any combination of any number of space chars, tab chars, and CRLF chars - or no gap at all between the alias and the left bracket.
Another complication is that versions of the live databases that we are replicating are determined not by us, but by the software vendors, so if they decide to go to the "future version" of SQL Server that will bring our world to an end, we really have to follow suit.
So I have a number of questions.
1. Does anyone have any suggestions about how they would resolve this?
2. Does anyone know which "future version" of SQL Server will cause our code to break?
3. Should I be looking for work somewhere else? :Whistling:
Cheers
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
February 9, 2011 at 4:14 am
1. Does anyone have any suggestions about how they would resolve this?
You can generate the reports of the SPS which are being used mostly and then can start tuning them. The problem could not be just because of nolock, the join within the query might be the cause of problem which need to be checked either execution plan.
2. Does anyone know which "future version" of SQL Server will cause our code to break?
As you posted this query to 2008 forum, I assume that your database already in 2008. I think 2008 is the next generation database and coming version must be compatible with 2008 but no one can predict.
The best person to answer this question is your vendor only. Ask them where they seems to be impacted with further upgrade of sql server.
3. Should I be looking for work somewhere else? :Whistling:
Join your vendor company. You will know the reality about their concern :w00t::w00t::w00t:
----------
Ashish
February 9, 2011 at 4:27 am
Apologies for my lack of clarity and thanks for the feedback Ashish. You say...
The problem could not be just because of nolock, the join within the query might be the cause of problem which need to be checked either execution plan.
The above does not seem to relate to my concern, which is that NOLOCK syntax in a "future release" of SQL will require the word WITH before it. This means a rewrite of basically our entire codebase... unless I'm not getting something obvious.
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
February 9, 2011 at 4:43 am
I couldn't give you the exact string, but using a Regular Expression in the find and replace should get you over the issues of white space/CRLF characters etc. Still not a pleasant task!!
I'm not sure if it's the case with all deprecated features, but... once you upgrade to a version where the syntax is deprecated, you should be able to run the database(s) in the compatibility mode for the earlier version that did support it.
February 9, 2011 at 5:41 am
I think Ian's answer will be right-on. I only learned about the "regular expression" engine at a PASS SQL Server Saturday a couple of weekends ago.
The regular expression processing isn't the "norm" and you have to enable it separately in the Find and Replace window. Open it up, and under the "Find Options" section, click on the "Use:" box to enable regular expression engine.
I've been using it when I copy and paste code from a web window into a query window of SSMS. I replace all occurances of "slash-n slash-n" with a single "slash-n" to remove all of the double-spacing when I paste code from a web site into SSMS. [Note: I couldn't enter the slash-n (newline) character in this post and have it displayed correctly...sorry.]
February 9, 2011 at 6:15 am
The worry about "with with" is reasonably easily resolved by a second find-and-replace.
Here's what I'd do:
1. Use the "generate scripts" option to generate a single script with drop and create statements for all procs, views, and UDFs in the database.
2. Search-and-replace "with (nolock)" and get rid of it.
3. Search-and-replace "(nolock)" and get rid of that too.
4. Use a macro to put "set isolation level read uncommitted" in each proc. Set that as the default connection setting as well (to handle views and UDFs).
5. Run the script and see what can't compile.
6. Fix those (it will probably only be a few).
7. Repeat (5) and (6) till it runs clean.
8. Review the need for nolock/read-uncommitted with those who do have the responsibility to make decisions about that, and bring up options like snapshot isolation with them.
The "read uncommitted" isolation level is the same as setting all tables in the query to "with (nolock)", but it's easier to implement and you only have to give the command once, not set the option for each table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2011 at 6:19 am
I'm fairly certain this was added to the deprecation list in 2005. That means that the very next release of SQL Server will remove it (assuming 2005, 2008, 2008 R2 as the three releases for a deprecation to be removed).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 9, 2011 at 10:15 am
In addition to Gus' method, a second pass will also ease your mass rewrite by simply updating all (NOLOCK) to WITH (NOLOCK), and then the second pass updating all WITH WITH to WITH . 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 9, 2011 at 12:29 pm
There is some great feedback here. I'm going to take it away, digest it and come back with some feedback of my own and hopefully the germ of a plan. Thanks all for sharing your time and wisdom.
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
February 9, 2011 at 1:07 pm
If you are currently using SQL 2008 .... there is a method to detect items that will be depreciated. Use:
SELECT * FROM sys.dm_os_performance_counters WHERE object_name LIKE ('%deprecated%')
Further read in BOL what is contained within the dynamic management view: sys.dm_os_performance_counters, one of the items to be depreciated is "NOLOCK or READUNCOMMITTED in UPDATE or DELETE".
This may not be all that you need, but hopefully it will make your task a bit easier.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply