June 2, 2017 at 3:10 am
Hello.
Following on from a previous post about using plan guides as a short term fix, I am now looking at a different Application
that has an issue with Blocking.
What I am attempting to determine is if it is possible to INSERT a WITH(NOLOCK) against a table that is accessed in
some SQL that requires application changes to amend.
I seem to have found a way to get the creation f the plan guide(s) to work - but only if I add the WITH(NOLOCK) to
the SQL in the plan guide - otherwise I get an error on creation of the plan guide.
Here is what I coded for OPTION to get it to work (but only when I added WITH(NOLOCK) to the actual SQL
@hints = N'OPTION(Table Hint(ContractLinesTable, Nolock))'
However, although the template plan guide gets created for forced parameterisation, I get this error message when it tried to create the SQL Plan Guide :
Msg 8722, Level 16, State 1, Line 1
Cannot execute query. Semantic affecting hint 'nolock' appears in the 'TABLE HINT' clause of object 'ContractLinesTable' but not in the corresponding 'WITH' clause. Change the OPTION (TABLE HINTS...) clause so the semantic affecting hints match the WITH clause.
Adding WITH(NOLOCK) to the SQL in the Plan Guide creation creates the SQL PLan Guide without the above error. Of course, the plan guide does not then get used because the Live SQL does not contain the same exact code, i.e. WITH(NOLOCK).
If I spoof the Live SQL in SSMS and add the WITH(NOLOCK) the plan guide is used - but this is pointless as it is not a solution to
the issue - if I could amend the SQL to add WITH(NOLOCK) I wouldn't need a Plan Guide.
Despite seeming to support the Option, is it not actually possible to add the WITH(NOLOCK) to a table in SQL with a Plan Guide?
I have a suspicion if may be something to do with parameterisation (the query hint is not resolved as a parameter) but I am guessing.
TIA
Steve O.
June 2, 2017 at 3:15 am
Why nolock? Are you aware of the negative impact in terms of accuracy?
Have you considered the READ COMMITTED SNAPSHOT database option? That gets you read queries that don't lock, without the incorrect results that nolock can produce, along with a bit higher tempDB usage.
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
June 2, 2017 at 3:49 am
Hello Gail.
Thanks for the response - looking for a quick fix as this is affecting productivity - the sub-query is just doing a count on the table for KPI,
and the Developers were happy with NOLOCK as an interim fix.
Actually I did contact the Developers yesterday asking about the feasibility of using RCSI, and am waiting on a response.
Looking at this further the issue appears to be something to do with semantic-affecting hints.
In essence, if there was a query hint in the SQL I could override it, but if there isn't one there already I can't add it?
Thanks
Steve O.
June 2, 2017 at 5:11 am
SteveOC - Friday, June 2, 2017 3:49 AMHello Gail.Thanks for the response - looking for a quick fix as this is affecting productivity - the sub-query is just doing a count on the table for KPI,
and the Developers were happy with NOLOCK as an interim fix.
Actually I did contact the Developers yesterday asking about the feasibility of using RCSI, and am waiting on a response.Looking at this further the issue appears to be something to do with semantic-affecting hints.
In essence, if there was a query hint in the SQL I could override it, but if there isn't one there already I can't add it?Thanks
Steve O.
Can you post up a plan, Steve?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 2, 2017 at 6:37 am
ChrisM@Work - Friday, June 2, 2017 5:11 AMSteveOC - Friday, June 2, 2017 3:49 AMHello Gail.Thanks for the response - looking for a quick fix as this is affecting productivity - the sub-query is just doing a count on the table for KPI,
and the Developers were happy with NOLOCK as an interim fix.
Actually I did contact the Developers yesterday asking about the feasibility of using RCSI, and am waiting on a response.Looking at this further the issue appears to be something to do with semantic-affecting hints.
In essence, if there was a query hint in the SQL I could override it, but if there isn't one there already I can't add it?Thanks
Steve O.Can you post up a plan, Steve?
Hello Chris.
Thanks for the reply - I haven't tried to do this before so would have to figure it out over the weekend.
In the meantime the Developers have come back and said they also don't think it is possible to do this with Plan Guides -
so do you still want me to post the plan?
Regards
Steve O.
June 2, 2017 at 7:22 am
SteveOC - Friday, June 2, 2017 3:49 AMHello Gail.Thanks for the response - looking for a quick fix as this is affecting productivity - the sub-query is just doing a count on the table for KPI,
and the Developers were happy with NOLOCK as an interim fix.
Actually I did contact the Developers yesterday asking about the feasibility of using RCSI, and am waiting on a response.Looking at this further the issue appears to be something to do with semantic-affecting hints.
In essence, if there was a query hint in the SQL I could override it, but if there isn't one there already I can't add it?Thanks
Steve O.
You can add hints with plan guides, yes. However, I think you're saying that you added the hint to the T-SQL part of the guide. You can't do that. The T-SQL has to be exactly what it is, without any additions or changes, extra white space, or anything that prevents the match. However, to ensure that you have the syntax correct, run the query and add the hint, exactly as you have it, to the query directly. I'll bet it doesn't validate.
However, I'm with Gail. Don't use NOLOCK.
"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
June 2, 2017 at 9:08 am
Grant Fritchey - Friday, June 2, 2017 7:22 AMSteveOC - Friday, June 2, 2017 3:49 AMHello Gail.Thanks for the response - looking for a quick fix as this is affecting productivity - the sub-query is just doing a count on the table for KPI,
and the Developers were happy with NOLOCK as an interim fix.
Actually I did contact the Developers yesterday asking about the feasibility of using RCSI, and am waiting on a response.Looking at this further the issue appears to be something to do with semantic-affecting hints.
In essence, if there was a query hint in the SQL I could override it, but if there isn't one there already I can't add it?Thanks
Steve O.You can add hints with plan guides, yes. However, I think you're saying that you added the hint to the T-SQL part of the guide. You can't do that. The T-SQL has to be exactly what it is, without any additions or changes, extra white space, or anything that prevents the match. However, to ensure that you have the syntax correct, run the query and add the hint, exactly as you have it, to the query directly. I'll bet it doesn't validate.
However, I'm with Gail. Don't use NOLOCK.
Hello Grant.
I didn't have any choice but to add the hint to the T-SQL part of the guide (through experimentation) as it wouldn't allow me to create the plan guide with
the OPTION NOLOCK,since I get the error posted.
Which is no use to me as I know the real SQL does not contain the Hint (or I wouldn't need to add it) and if I could change the SQL I would.
This whole semantic affecting thing seems to imply that if there was already a Table Hint (or WITH statement) in the SQL, I could change it, but because
there isn't one I can't add it.
I will recreate this over the weekend to demonstrate that the hint can be changed but not added, just to re-inforce my understanding- although this
doesn't help me in this instance.
It just wasn't clear from BOL or anywhere else whether you can do this or not - but I am of the opinion that you can't.
Thanks
Steve O.
June 2, 2017 at 9:18 am
SteveOC - Friday, June 2, 2017 6:37 AMChrisM@Work - Friday, June 2, 2017 5:11 AMSteveOC - Friday, June 2, 2017 3:49 AMHello Gail.Thanks for the response - looking for a quick fix as this is affecting productivity - the sub-query is just doing a count on the table for KPI,
and the Developers were happy with NOLOCK as an interim fix.
Actually I did contact the Developers yesterday asking about the feasibility of using RCSI, and am waiting on a response.Looking at this further the issue appears to be something to do with semantic-affecting hints.
In essence, if there was a query hint in the SQL I could override it, but if there isn't one there already I can't add it?Thanks
Steve O.Can you post up a plan, Steve?
Hello Chris.
Thanks for the reply - I haven't tried to do this before so would have to figure it out over the weekend.
In the meantime the Developers have come back and said they also don't think it is possible to do this with Plan Guides -
so do you still want me to post the plan?Regards
Steve O.
Yes please Steve, as a .sqlplan attachment. An actual plan if you can, an estimated plan is better than nothing.
If the statement is truncated in the plan, could you post that too please? Ta.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 2, 2017 at 9:28 am
SteveOC - Friday, June 2, 2017 9:08 AMGrant Fritchey - Friday, June 2, 2017 7:22 AMSteveOC - Friday, June 2, 2017 3:49 AMHello Gail.Thanks for the response - looking for a quick fix as this is affecting productivity - the sub-query is just doing a count on the table for KPI,
and the Developers were happy with NOLOCK as an interim fix.
Actually I did contact the Developers yesterday asking about the feasibility of using RCSI, and am waiting on a response.Looking at this further the issue appears to be something to do with semantic-affecting hints.
In essence, if there was a query hint in the SQL I could override it, but if there isn't one there already I can't add it?Thanks
Steve O.You can add hints with plan guides, yes. However, I think you're saying that you added the hint to the T-SQL part of the guide. You can't do that. The T-SQL has to be exactly what it is, without any additions or changes, extra white space, or anything that prevents the match. However, to ensure that you have the syntax correct, run the query and add the hint, exactly as you have it, to the query directly. I'll bet it doesn't validate.
However, I'm with Gail. Don't use NOLOCK.
Hello Grant.
I didn't have any choice but to add the hint to the T-SQL part of the guide (through experimentation) as it wouldn't allow me to create the plan guide with
the OPTION NOLOCK,since I get the error posted.
Which is no use to me as I know the real SQL does not contain the Hint (or I wouldn't need to add it) and if I could change the SQL I would.
This whole semantic affecting thing seems to imply that if there was already a Table Hint (or WITH statement) in the SQL, I could change it, but because
there isn't one I can't add it.
I will recreate this over the weekend to demonstrate that the hint can be changed but not added, just to re-inforce my understanding- although this
doesn't help me in this instance.
It just wasn't clear from BOL or anywhere else whether you can do this or not - but I am of the opinion that you can't.Thanks
Steve O.
Yes, you are correct that you can't use a plan guide to force NOLOCK on a query that's not already specifying it.
From https://technet.microsoft.com/en-us/library/bb677261(v=sql.100).aspx
Table hints other than INDEX and FORCESEEK are disallowed as query hints in the plan guide unless the query already has a WITH clause specifying the table hint.
Cheers!
June 2, 2017 at 10:04 am
Jacob Wilkins - Friday, June 2, 2017 9:28 AMSteveOC - Friday, June 2, 2017 9:08 AMGrant Fritchey - Friday, June 2, 2017 7:22 AMSteveOC - Friday, June 2, 2017 3:49 AMHello Gail.Thanks for the response - looking for a quick fix as this is affecting productivity - the sub-query is just doing a count on the table for KPI,
and the Developers were happy with NOLOCK as an interim fix.
Actually I did contact the Developers yesterday asking about the feasibility of using RCSI, and am waiting on a response.Looking at this further the issue appears to be something to do with semantic-affecting hints.
In essence, if there was a query hint in the SQL I could override it, but if there isn't one there already I can't add it?Thanks
Steve O.You can add hints with plan guides, yes. However, I think you're saying that you added the hint to the T-SQL part of the guide. You can't do that. The T-SQL has to be exactly what it is, without any additions or changes, extra white space, or anything that prevents the match. However, to ensure that you have the syntax correct, run the query and add the hint, exactly as you have it, to the query directly. I'll bet it doesn't validate.
However, I'm with Gail. Don't use NOLOCK.
Hello Grant.
I didn't have any choice but to add the hint to the T-SQL part of the guide (through experimentation) as it wouldn't allow me to create the plan guide with
the OPTION NOLOCK,since I get the error posted.
Which is no use to me as I know the real SQL does not contain the Hint (or I wouldn't need to add it) and if I could change the SQL I would.
This whole semantic affecting thing seems to imply that if there was already a Table Hint (or WITH statement) in the SQL, I could change it, but because
there isn't one I can't add it.
I will recreate this over the weekend to demonstrate that the hint can be changed but not added, just to re-inforce my understanding- although this
doesn't help me in this instance.
It just wasn't clear from BOL or anywhere else whether you can do this or not - but I am of the opinion that you can't.Thanks
Steve O.Yes, you are correct that you can't use a plan guide to force NOLOCK on a query that's not already specifying it.
From https://technet.microsoft.com/en-us/library/bb677261(v=sql.100).aspx
Table hints other than INDEX and FORCESEEK are disallowed as query hints in the plan guide unless the query already has a WITH clause specifying the table hint.
Cheers!
Thanks for the confirmation Jacob.
I was using that page as one of my references and either I missed that because I was specifically searching for NOLOCK, or wasn't sure that I interpreted it correctly.
Regards
Steve O.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply