June 13, 2002 at 7:19 am
Hello *,
I've got a couple of OLTP DB's and i'm checking out the indexes. To begin with, i found out that the space for indexes is bigger than that of the data, sometimes up to two times.( According to the 'advanced options' in the tuning wizard) Is this ratio for OLTP normal??
What are your rates?
TIA
June 13, 2002 at 8:39 am
Hard to say what is normal. Build the indexes you need, kill the ones you dont. If that means index size is bigger than the table, so what? I'll try to check sizes later today, but I'd guess that more index than table is probably over indexed - might just be a big compound index or something.
Andy
June 13, 2002 at 8:56 am
hi, I'm not too worried about one table being to big, but on a database with more than 600 tables I would assume that a 2/1 ratio is rather big.
Of course, there are some really big tables which are heavily indexed, but still...
just curious what the numbers are at other sites.
June 13, 2002 at 9:11 am
I think there is a contradiction here. On the one hand, you are saying OLTP, and then you're saying heavily indexed big tables. What do you mean by "heavily indexed" ?
Regards
Simon
June 13, 2002 at 9:17 am
Simon,
Having no seperate DB for olap, all reports are are run in the oltp environment.
Hence some big tables are well-indexed.
Cleared the 'contradiction' ?
June 13, 2002 at 10:06 am
As a thought, if you have an overnight process that needs one specific index to improve it, why not create the index within that stored procedure, or before it, then drop it afterwards? I also run one of these hybrid OLTP/reporting systems and I know of the trade-offs, but I don't have more than 5 indices on any table, although some of them are of the compound type to cover some regular queries. If you bear in mind that a clustered index appears in all non-clustered indexes, then you may be able to trim some of them out. You may also find that with 2000 your queries could use intersection if you narrow the index keys?
Regards
Simon
June 13, 2002 at 7:50 pm
If you are using the database like a data wharehouse then it is quite normal for the indexes to be larger than the table. My only thought is you may want to evaluate your indexes and make sure they are needed like they are or if you can remove some. May even want to use combo indexes and keep in mind two indexes can be used in index intersection in determining where the data exists.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 14, 2002 at 3:53 am
Thanx for the feedback, some ?? are !! now here 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply