Use filtered index

Elessar

Hi,

I have a problem and hope that you can help me. The following statements make some trouble:

SELECT * FROM Customer Ledger Entry WITH(READUNCOMMITTED) WHERE "Posting Date" >= @P1 AND "Customer No" = @P2 ORDER BY "No" OPTION (OPTIMIZE FOR UNKNOWN)

SELECT * FROM Customer Ledger Entry WITH(READUNCOMMITTED) WHERE "Posting Date" >= @P1 AND "Customer No" = @P2 AND "No" > @P3 ORDER BY "No" OPTION (OPTIMIZE FOR UNKNOWN)

The second one is a dynamic cursor. The statements are very slow, so I created a NCI ("No", "Customer No", "Posting Date") and this helped a little bit, but the SQL-Server do an index scan on the NCI. To reduce the pages the server has to read I tried to create a filtered index, because the statement just use the entries with a "Posting Date" not older than 9 month. Sadly it doesn't work so I created the following plan guide:

use [NavisionDB]
exec sp_create_plan_guide
@name = N'CustLedgerEntry_Guide1',
@stmt = N'
SELECT * FROM Customer Ledger Entry WITH(READUNCOMMITTED) WHERE "Posting Date" >= @P1 AND "Customer No" = @P2 AND "No" > @P3 ORDER BY "No" OPTION (OPTIMIZE FOR UNKNOWN)',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@P1 datetime,@P2 varchar(20),@P3 varchar(20)',
@hints = N'OPTION (TABLE HINT("Customer Ledger Entry", INDEX(NCI_1), READUNCOMMITTED))'

Sadly, this plan guide also doesn't work. Do you have any ideas, why the plan guide doesn't work and if it's possible to use filtered indices?



Best regards
Elessar

Elessar

Hi,

anyone? Do you need further information?


Best regards
Elessar

arvkum

Hi Elessar,

Dont know if you have found any solution,
Normally you should check it using SQL profiler with ShowPlan XML Event to see th eexecution plan.

You should Keep your SQL Server to latest available build.

But may be you can try using the "FORCESEEK" Parameter in the N'OPTION (TABLE HINT("... plan guide, if this helps?

ndbcs

Afaik filtered indexes doesn't work with parameterized tsql, because the optimizer needs to create a plan that works for every possible parameter value.
So I think that forceseek also doesn't work, because it also creates a cached plan.

I think it will work if you use the recompile hint, because the plan will not be cached in that situation, so it's save for the optimizer to sniff parameters and create a plan for the actual parameter values. Iirc you need to have SQL Server 2008 R2 SP1 or later.

regards
ndbcs