August 19, 2011 at 8:23 am
Hi All,
I am new to the company and here we have several tables being partitioned via a creation_date column on a weekly basis. The tables are relatively narrow being less than 20 columns mostly integer data types.
When I do a simple query on the tables to pull back rows between lets say the last two weeks based on both a creation_date column (which the partition is based on) and an updated_date column (which has a nonclustered index) I notice that the estimated number of rows is 1 and the actual number of rows is 565501. These statistics are causing the plan to use an Index Seek instead of scan, a Key Lookup and then combine the with a Nested Loops operator instead of a hash match.
I have done a decent amount of optimization in the past, just nothing dealing with Partitioned Tables. I manually update the statistics on the tables which took a long time but yielded no benefits.
Should we be doing index partitioning as well or are there additional maintenance tasks which we should be performing. Any help would be greatly appreciated.
Thank you in advance,
Paul
August 23, 2011 at 12:35 pm
So nobody has any clue on this one?
August 24, 2011 at 6:20 am
Partitioning is a very advanced/complex subject. You REALLY need to have experience/training to both setup/manage them properly as well as write proper queries that might take advantage of partition elimination if it is available. I highly recommend you get a professional on board to help you understand what you have and dig into your problems.
Having said that, if you post up complete table/index definitions and the query you are having issue with (with it's actual query plan) we might be able to help.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply