August 16, 2010 at 8:40 am
Hi again,
Question regarding a partitioned table and indexes.... we have a large table that has been partitioned by years. There is a clustered index on the year field. There are about 15-20 other non clustered indexes that use a variety of field combinations.
We have a query that looks like this:
select fund, beg_bfy,end_bfy,dvsn,prgm,
bud_org, sch_num, tc, tt, trans_num,
gl_acct, [description],userid, vend_code,
sch_fy, gl_amt * -1, post_date, job_num,
fy, fisc_mon, post_num, substring(description,14,15)
from <tablename>
where fy = 2009 and fisc_mon = '07'
and gl_acct = '4801'
and tc in ('IP','IR','CO') and
trans_num = '91132100323'
We have a non clustered index that uses fisc_mon, gl_acct, tc and trans_num.
This query is taking about 2 minutes to run to produce 1 record. When I check the query execution path, it is using the clustered index on FY to run this query and not any other index.
When I remove the FY field from the where clause it then uses the non clustered index I mentioned above and takes 22 seconds to run instead of 2 minutes.
So I guess my question is:
1. Since the table is partitioned by fiscal year (the FY column), does that mean when we run queries it will ALWAYS use the clustered index no matter what we do?
2. Since the table is partitioned by fiscal year, should we even have a clustered index using the fiscal year?
3. Is there a better syntatical way to query a partitioned table?
August 19, 2010 at 12:39 pm
did you update stats after partitioning?
also you can use index hints:
select * from table_name with (index(index_name))
August 20, 2010 at 10:49 am
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
August 20, 2010 at 3:20 pm
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply