Post reply

Indexes and Partitioned Table

  • August 19, 2010 at 12:39 pm

    #1209332

    did you update stats after partitioning?

    also you can use index hints:

    select * from table_name with (index(index_name))

    Alex S

  • August 20, 2010 at 10:49 am

    #1209808

    After you have updated the index. try the following

    did you check the Density of the col?

    Sounds like the index might not be using the Density is to large, therefore the op timer chooses use the clustered index instead.

    Use a hint to force the index to be used...Then measure the time...

    try this link as well

    http://sqlblog.com/blogs/tibor_karaszi/archive/2010/04/01/how-selective-do-we-need-to-be-for-an-index-to-be-used.aspx

  • August 20, 2010 at 3:20 pm

    #1209948

    Thanks guys, I did try using a hint but SQL decided it still wanted to use the clustered index. 🙂

    I did manage to fix the problem by running the query through the tuning advisor. It recommended an index change and it said it would improve the performance by 99%. The index was actually one we already had but the conditions on the index were a little different. I added the recommended index and the query comes back instantaneously using the proper index instead of the clustered one.

    I'm thinking the problem is more with the efficiency of the indexes we have on the table. They were carried over from SQL 2000 in our upgrade and the table never performed well before we partitioned it in 2005. So, I think SQL 2005 just sees those indexes and is like "OMG those idexes suck I'm not gonna use them!" 🙂

    So, we're gonna run through an effort now to clean up the 21 indexes on the table and reengineer them to make sure they are efficient and configured properly.

    I am still curious as to one of my questions about having a clustered index on the partitioned column of the table. All the literature I found when creating the partitions said to create a clustered index on the partitioned field but now I'm wondering if that is really necessary? So, for example, if we had a query that just had WHERE FY = 2009 in it, would the query still come back quickly if there was no clustered index on the partitioned column? Or does that index need to be there? I'm wondering cause I could certainly put a different field to good use in a clustered index besides the FY field (there are about 15 years worth of data in this table and its rougly 100 million records).

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply