Use filtered index

Elessar
Member Posts: 28
Hi,
I have a problem and hope that you can help me. The following statements make some trouble:
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:
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
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
0
Comments
-
Hi,
anyone? Do you need further information?
Best regards
Elessar0 -
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?Regards
Arvind0 -
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
ndbcs0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions