February 12, 2006 at 7:00 am
Hi
The query I use returns statistic data.
Actualy there are more than 10Mio. records in this table.
I tried to add a clustored index over all fields, than a clustered over the key and non clustered for the other fields, at least non clustered for each field. If I use the index or not, the query needs the same time: 30 Minutes.
SELECT stat_Session_ID, stat_Content_ID, COUNT(*)
FROM Statistik
WHERE (stat_Date > '2006-01-01')
GROUP BY stat_Session_ID, stat_Content_ID
order by stat_Content_id
Why the index takes no effect?
February 12, 2006 at 8:55 am
Indexes are only uses when the time using the index + looking up the linked records takes less time than a table scan (where one can read bigger chunks).
One decisive factor in this matter are statistics.
If your selected data is only 10% of the actual table, the use of indexes may be beneficial. If it is 50% of the actual table a table scan could be more appropriate.
Is there an index on stat_date? (the only restrictive thing)
Have you tried the index tuning wizard?
February 12, 2006 at 10:14 am
The selected data are 30%.
There is a non clustered index on stat_date.
Have not tried the wizard. Sould I delete the indexes first bevore running the tuning wizard?
February 12, 2006 at 11:17 am
The wizard has an option keep existing indexes.
You may set it to off and view the changes/proposals.
February 13, 2006 at 6:11 am
The important thing to understand about non-clustered indexes is that they are AWFUL at returning large numbers of rows. Jo quotes 10% as the cutoff point after which a NC index won't be used. In reality, I think you'll find it's much lower. Once the optimizer estimates that >1 or 2% of the total rows in the table will match the query it will not use a NC index, preferring to scan the clustered index instead (or even tablescan if there is no clustered index).
The only exception to this is if the NC index "covers" the query - I'll come on to that in a minute.
Clustered indexes, on the other hand, are very good indeed at returning large numbers of rows.
You say you added a clustered index "over all fields" ? I really wouldn't do that. Firstly it's unnecessary, and secondly it will increase the size of all your non-clustered indexes as well (since all columns in the clustered index are automatically added to each NC index, even if you don't specify this).
I'd recommend the following options (and this ONLY works for the query you've posted. The recommendations might be quite different for other queries)
1. Make sure the first column in your clustered index is the stat_Date column. This will enable an INDEX SEEK to be used for the ...WHERE stat_Date > '2006-01-01' line. I'd be tempted to also add stat_session_ID and stat_Content_ID (in that order), since this will help the GROUP BY clause in the query.
2. Alternatively, you could create a COVERED non-clustered INDEX on the 3 columns in the query, in the same order as the clustered index: stat_Date, stat_session_ID, stat_Content_ID. This means that all the data you need will be in the index, and the optimizer will never have to go to the main table. For this query, this will be even faster than a clustered index, since you will get more rows per page in the index than in the whole table, so less IO is required to get them all back again.
My personal preference would be:
- create a NON-clustered PK on whatever your PK columns are.
- create a clustered index on (stat_Date, stat_session_ID, stat_Content_ID)
February 13, 2006 at 2:12 pm
At least for testing purposes you can put a hint in the query to force it to use the index that you want and see what the performance is. Does this table get updated a lot. If not I would build the index at 100% fill factor to compact the data on the pages. I would also use the covering index that Philip suggested. Stat_date, stat_Session_ID, stat_Content_ID.
Tom
February 13, 2006 at 4:31 pm
@jo Pattyn, Couldn't use index tuning wizard because selection of tables are empty.
@Philip Yale, Only with a small set of data it increases, my query is slow as without index, so the index will be ignored by that amount of data, but thanks I let this index on the table.
@tom Goltl, This Statistic data takes the session hits, so it is updated a lot.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply