Index Hint Causing Confusing Error
-
I have a query:
Insert <table1>
Select tbl2.ColA,tbl3.ColB,NULL
From <table2> tbl2
Inner Join <table3> tbl3 with (NoLock)
on tbl3.ColA = tbl2.ColA
When I display the Estimated Execution Plan, I see that SQL Server is selecting an index for <table3> that does not cover the AcctNum column, and it is doing an index scan. Additionally, SQL Server displays a "Missing Index" error message that tells me I should create an index with Key Column "ColA" and Included Column "ColB".
Now this is where it gets strange and confusing...
First, there is an existing index on <table3> which has "ColA" as its Key Column and "ColB" as its Included Column. Why SQL sever is not using this index in the first place, I do not know.
Second, when I include an index hint in the query to use this existing index, I get the following message: "Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN." And, this message shows up when I do a "Display Estimated Execution Plan" AND when I try to execute the query (and I am not including an Actual Exection Plan).
Does anyone know what is going on here, and how to solve this problem?
Thank you.
-
Have you tried removing NOLOCK? You really should remove nolock regardless unless there is a good reason for it. In case you have never read it, here is a great article and discussion about the usage and ramifications of nolock. http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean,
Yes, I have tried removing the NoLock hint. It makes no difference.
-
Can you post ddl including indexes for the tables?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean,
You can ask me specific questions about the tables and indexes that relate to this problem, but I will not publish my table and index structures on this forum for anyone and everyone and their uncle to examine. Security considerations, you understand.
-
EdwinGene (2/17/2012)
Sean,You can ask me specific questions about the tables and indexes that relate to this problem, but I will not publish my table and index structures on this forum for anyone and everyone and their uncle to examine. Security considerations, you understand.
appreciate your concerns....so obfuscate....then provide as requested.
yeah..it requires effort...but so do tried and tested workable answers.
kind regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
Some generic questions:
1. What SQL Server version? "select @@VERSION"
2. Are any of the tables temporary tables?
3. Since you posted psuedo code, can you reproduce it with psuedo code?
4. Have you checked for fragmentation and reorged/rebuilt and/or run stats?
5. How large is this table, and how much memory is SQL Server allowed?
-
Ten,
If someone wants to look at table and index structures that tells me that they don't have a general idea of what they're looking for or where they expect to find it.
What I'm asking for is someone who has experience with this problem and can tell me what to look for and where to look for it. I don't want someone who wants to look through everything because they don't know what or where the problem is. I can do that and I can get my fellow DBA's to help me.
-
His user name isn't "Ten", it's "J Livingston SQL". Seeing small differences like that which you may have overlooked is why it's sometimes helpful to provide the DDL for review. Re-check your index already on <table3> with the one that's being suggested, perhaps it's slightly different (unique vs not?).
-
Brian and J,
Sorry about that. My eye stopped on the wrong line.
And, please don't confuse "Grasshopper" with inexperienced. Just because I'm new to SQL Server Central doesn't mean I'm new to SQL Server or to being a DBA.
-
the DDL of the table can help us solidly determine why the index gets skipped;
for example:
one wild guess: if tbl3.ColA and tbl2.ColA in your example below are not the same data types, then an implicit conversion will be used, which requires a table scan.
having that level of detail for the issue that mirror the real problem will allow us to give solid answers and not guesses.
...
on tbl3.ColA = tbl2.ColA
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
EdwinGene (2/17/2012)
If someone wants to look at table and index structures that tells me that they don't have a general idea of what they're looking for or where they expect to find it.That's a bad assumption to make. Most issues around exec plans and indexes are very specific to the query/structure, not general problems. I could make guesses without seeing at least the table and index designs (obfuscate the names, those don't matter. The types do) and some rough row counts, or preferably the plan itself.
What I'm asking for is someone who has experience with this problem and can tell me what to look for and where to look for it. I don't want someone who wants to look through everything because they don't know what or where the problem is. I can do that and I can get my fellow DBA's to help me.
I have a fair knowledge of indexes and execution plans, but without seeing more details I cannot tell you exactly what the problem is, I can only guess. Maybe there's only a non-sargable predicate (I don't know how far you obfuscated the query), maybe there's an implicit conversion, maybe the index is filtered, maybe there really is no predicate at all (so the entire table is necessary) and so scans are more optimal
That said, if the query is as simple as that the most likely reason for not having any seeks in this is that you have no row-limiting, sargable predicates. The only time joins cause seeks is when SQL does a nested loop join, and that requires a small number of rows in the outer table (and an appropriate index on the inner table), otherwise it'll scan both and hash or merge join. Just a guess without knowing row counts or seeing the exec plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
additionally the number of rows in the table can determine if an index gets skipped. Without reproducible code to work with, it's like calling your mechanic and saying "My car broke down, it won't start. Tell me how to fix it." Then when he asks for more information you respond "obviously you don't know what your doing or you would tell me."
The above is a bit extreme, but without sample code for the tables, indexes, and number of rows in each table... who knows?
-
Can you post ddl including indexes for the tables?
-
Gail,
Thank you for your suggestions. Your suggestion about sargable predicates has helped me to find an alternate way to invoke the query to get SQL Server to use the correct index.
Using the query as it was I was finally able to get SQL Server to use the correct index by inserting a Where clause that limited tbl3.ColA to a single value (Where tbl3.ColA = 'value'). Of course, this obviously would not return the result I was trying to get, but it got me thinking along the path which led to a workable solution (in this case).
I tried to come up with a way that I could limit tbl3.ColA with a Where clause, but I just couldn't find a way that would do that and still include all of the values (which could change) from <table2>. Selecting all the values from <table2> and using the In() function didn't work. Even selecting the max value from <table2> and using that in a Where clause (Where tbl2.ColA <= MaxValue) didn't work.
Then it dawned on me that a way to restrict tbl3.ColA to one value would be to use a Cursor and Dynamic SQL. I know - it didn't seem right to me either. However, given that I had stopped the original query test after 15 minutes and that <table2> is very small (just over 1500 rows) and that it would be using the correct index in <table3>, I figured WTF, I'll give it a try.
The Query is now returning a result set in three (count 'em: THREE) seconds, as compared to somewhere north of 15 minutes for the original query.
So, you see, this is why I wanted a response from someone who has seen this before or who has experience in this area. Your "guess", based on your experience, was just what I asked for and just what I needed to enable me to find a solution. And, you didn't need to see my table or index structures to do it.
Thank you very much for your help. This was much more valuable to me than having a solution spelled out for me. Give a man a fish and he eats for one day. Teach a man to fish, and he eats for the rest of his life.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply