March 24, 2015 at 11:00 am
Hello all
I have a "weird" case on my hands
I have two servers one a 16 core with SQL Server 2008 R2 SP3 (server A) and the other and 8 core (server b) with the same version of sql
From the server A there is one table being replicated to server B, that is a table with 24 million rows and 204 columns
the indexes at both side are identical, no fragmentation and statistics freshly updated
On server A I can execute this query
select *
from [dbo].[Table_Report]
where record_ID = 'RECI1000615073749070079'
there is a nonclustered index by the record_id column
at server A that index is chosen and the results are retrieved very fast
at server B it chooses to use a clustered index scan and if I try this:
select *
from [dbo].[Table_Report] with(index (IDXByRecordID))
where record_ID = 'RECI1000615073749070079'
I get this message:
Msg 8622, Level 16, State 1, Line 1
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.
Does anyone have an idea why is this happening? or what should I do as to fix it?
Right now I dropped all the indexes at the Server B table and I am creating them again but I have little hope in that
Thanks in advance
March 24, 2015 at 1:51 pm
it seems that dropping and recreating the indexes did the trick
March 24, 2015 at 2:43 pm
I suspect there was something up with that index, possibly it wasn't even there. You'd only get an error if a hint suggested something that simply couldn't be done, which would be the case if the index was missing.
"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
March 24, 2015 at 3:04 pm
I had that idea, but well that was letting me rebuild that index so, it was in there...
March 24, 2015 at 3:56 pm
Not sure. Maybe the index is corrupt. I've only hit that error on hints when I was attempting to force the optimizer to do something it just couldn't do.
By the way, forcing the index on the plan if the statistics don't support it seldom works out well.
"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
March 24, 2015 at 4:03 pm
thanks
I always try not to use hints because things can change (like another dba dropping an index) and stuff will fail in weird ways 🙂
I just tried that as to see why the optimizer was selecting the clustered
I also think that the index was corrupted, it will be the first time I see that
March 25, 2015 at 6:02 am
The index wasn't corrupt. Corruption does not cause query optimiser errors. It causes:
Error 824, Severity 24, State 0
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xfdff74c9; actual: 0xfdff74cb). It occurred during a read of page (1:69965) in database ID 13 at offset 0x0000002229a000 in file 'D:\Develop\Databases\Broken1.mdf'.
Unless you're getting severity 24 errors, it's not corruption.
The index may have been disabled (ALTER INDEX ... DISABLE), that means that the index's metadata is there but the index tree isn't so the index can't actually be used. It can be rebuilt (which recreates the tree)
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
April 14, 2017 at 2:09 am
This also happens when you use a query hint on a filtered index that does not fall within the parameters of the where clause
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply