Index space

Question

Index space

WhiteLotus

SSChampion

Points: 12120
More actions
October 24, 2018 at 12:34 am

#372284

Hi Guys
I just created an index ( with only 1 key column ) in testserver , index space has been increased 1GB ( from 3 GB to 4 GB )
The table has 100 million record data

Question :
If I put this index into a bigger table which has 20 billion recorddata ( 20 times bigger )
Do u think the index space will get bigger ? ( let’s say 20 times bigger )

Thank you

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

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

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 1

WhiteLotus - Wednesday, October 24, 2018 12:34 AM
Hi Guys
I just created an index ( with only 1 key column ) in testserver , index space has been increased 1GB ( from 3 GB to 4 GB )
The table has 100 million record data

Question :
If I put this index into a bigger table which has 20 billion recorddata ( 20 times bigger )
Do u think the index space will get bigger ? ( letâ€™s say 20 times bigger )

Thank you

The space used by indexes should increase roughly proportionally to the number of rows.
If you want to make it smaller you can compress the index to a fraction of its size.
BTW: 20 billion is 200 times bigger than 100 million.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 2

WhiteLotus - Wednesday, October 24, 2018 12:34 AM
Hi Guys
I just created an index ( with only 1 key column ) in testserver , index space has been increased 1GB ( from 3 GB to 4 GB )
The table has 100 million record data

Question :
If I put this index into a bigger table which has 20 billion recorddata ( 20 times bigger )
Do u think the index space will get bigger ? ( letâ€™s say 20 times bigger )

Thank you

You actually have a table with 20 BILLION rows that's not partitioned? Can you post the CREATE TABLE and CREATE INDEX ddl for that table and index? You may have bigger problems to contend with when your index starts to fragment.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WhiteLotus SSChampion Points: 12120 More actions · Answer 3

Thanks for the response
Sorry total record should be 1.5 Billion so (15 times bigger ) and No partition
Previously I created non clustered index with included columns (1 column key and 10 non column key) but the index space got increased 3 times bigger in table (100 millions record)
so I decided to use non clustered index ( without included columns ) as it lighter and performance is not much different
I have maintenance plan for index rebuild / reorganize every day , So I guess it will reduce the fragmentation ?

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 4

WhiteLotus - Wednesday, October 24, 2018 6:11 PM
Thanks for the response
Sorry total record should be 1.5 Billion so (15 times bigger ) and No partition
Previously I created non clustered index with included columns (1 column key and 10 non column key) but the index space got increased 3 times bigger in table (100 millions record)
so I decided to use non clustered index ( without included columns ) as it lighter and performance is not much different
I have maintenance plan for index rebuild / reorganize every day , So I guess it will reduce the fragmentation ?

You really should post the CREATE TABLE and the CREATE INDEX statements for the table and the index. That should also include the clustered index and the non clustered index you're speaking of. If you're using an actual maintenance plan for index rebuild/reorganize, you really should stop doing so until we figure out what that index is costing you in log file size, page splits, blocking, wasted memory/disk, and more. Also, how many rows per day are added to this table?

Last but not least, do you have any actual proof that either rendition of the index actually helped performance?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)