November 14, 2011 at 3:14 pm
I have the following table:
CREATE TABLE [dbo].[PartyAddress](
[PK_PartyAddress] [bigint] IDENTITY(1,1) NOT NULL,
[FK_AddressEntity] [bigint] NOT NULL,
[FK_PartyMain] [bigint] NOT NULL,
[RecordType] [int] NULL,
[FK_SourceID] [int] NOT NULL,
CONSTRAINT [PK_PartyAddresses_New] PRIMARY KEY CLUSTERED
(
[PK_PartyAddress] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
This table has a couple billion records. I am trying to run a query to draw records out of here based off of the FK_PartyMain value. This will be done in a stored procedure that will accept parameterized values from a console application, iterating through values of 1 through 1,000,000,000 for FK_PartyMain, in 5k chunks. I've created the following filtered index:
CREATE NONCLUSTERED INDEX [IX_PartyMain_incl_AddressEntitySourceRecordType_1_to_100000000] ON [dbo].[PartyAddress]
(
[FK_PartyMain] ASC
)
INCLUDE ( [FK_AddressEntity],
[RecordType],
[FK_SourceID])
WHERE ([FK_PartyMain]>=(1) AND [FK_PartyMain]<=(100000000))
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
However, the query plan refuses to use this index. I'm guessing that the reason because of this is due to the parameterized values. Since SS can't guarantee what those values will be, it can't guarantee that the index will be of any use. I know that the filter only covers 1/10th of the potential range, but it seems to me like this would be completely besides the point to SS.
If I'm right on that, how can I get around this issue? Right now, me and my .NET developer have found that if we write dynamic SQL where the 'parameters' are then 'hard-coded', the index is used, so we're considering going that route. Is there any other way to handle this?
Many thanks...
November 14, 2011 at 3:29 pm
What SP are you on? This was a bug in the RTM version of 2008.
Did you try with OPTION RECOMPILE? That should be enough to use the index.
However it's not really different from dynamic sql aside from not bloating the procedure cache.
November 14, 2011 at 4:08 pm
Thanks, tried recompile to no avail. Dynamic SQL it is!
November 14, 2011 at 11:03 pm
Have you tried WITH(INDEX(IX_PartyMain_incl_AddressEntitySourceRecordType_1_to_100000000))
?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 14, 2011 at 11:24 pm
Yes, and it wouldn't compile.
November 14, 2011 at 11:42 pm
Can you post your SELECT statement?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 15, 2011 at 12:11 am
coronaride (11/14/2011)
Yes, and it wouldn't compile.
What error did you receive when attempting to do a forced index retrieval?
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
November 15, 2011 at 1:22 am
http://sqlinthewild.co.za/index.php/2011/11/09/sql-university-advanced-indexing-filtered-indexes-2/
Query please?
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
November 15, 2011 at 9:54 am
The select statement in the actual query is complex, however, the same exact results (SQL not using the index in the execution plan) can be achieved by the following:
declare @begin bigint
declare @end bigint
set @begin = 10000
set @end = 15000
select FK_SourceID, RecordType from PartyAddress where FK_PartyMain between @begin and @end
In the execution plan for this query, only the clustered index is used, not the filtered index.
However, when I simply do this...
select FK_SourceID, RecordType from PartyAddress where FK_PartyMain between 10000 and 15000
...the filtered index is used.
November 15, 2011 at 9:59 am
And, again, option recompile doesn't use the index???
November 15, 2011 at 10:02 am
Again, not it doesn't. 🙁
November 15, 2011 at 10:06 am
Can you post the actual execution plan?
November 15, 2011 at 10:09 am
Haven't posted an exec plan to SSC before. What format is preferred?
November 15, 2011 at 10:09 am
Have you tried adding an extra redundant
and fk_partymain between 1 and 1000000000
November 15, 2011 at 10:11 am
coronaride (11/15/2011)
Haven't posted an exec plan to SSC before. What format is preferred?
Uploaded .sqlplan
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply