The queries come from SQL Server Analysis Services (partition queries) and cannot be parameterized; only ad-hoc queries are allowed. I could create a plan guide, force the parameterization and try OPTIMIZE FOR UNKNOWN/Static Value, but this would mean I need to create a plan guide for each and every query-(type) which makes a join to DimDatum, and these are almost all of them. And also don't forget to create plan guides/use OPTIMIZE FOR hints for reports that go directly to relational database, not SSAS.
-creating more filtered statistics exactly on the problematic ranges
I hoped I've done it creating a filtered statistic for <200 unique values in DimDatum. Actually I don't see how I can guarantee that SQL Server stops consolidating histograms other that create a statistic per EACH record in DimDatum (Number of Days (1000) x OnAverage3VersionsPerDay = 3000 statistics objects). I can't believe there is no more elegant solution to this trivial problem.
Ideas/suggestions are very welcome (if it helps, I can also provide a small sample database where the problem can be reproduced)
No comments:
Post a Comment