June 26, 2009 at 9:53 am
For a heap, when you insert data, it adds to to the next page in the last extent (or makes a new page). For deletes, space is marked as deleted, but is not reused, which means you can get really fragmented.
For a CLI, space is made at the place needed in the CLI. The data is stored as the CLI order, so if it needs to make space, it will split pages or add pages in the middle. This can incur some overhead if there are lots of page splits and there is a counter to track that.
For deletes, same as heaps, but if space is left in a place that the CLI needs for a insert/update, it will reuse the space.
Is this an application with lots of inserts/deletes?
June 26, 2009 at 10:11 am
I've just gone through all the tables and identified which ones are heaps.
There are > 100, of which 14 have more than 1,000,000 rows :w00t:
4 of them, which seem to be related, have a grand total of 200 million rows. I really hope they're just 'backups' that people have just left lying around :rolleyes:
Heap is about right, but I'm thinking more in terms of heaps of s**t!
So with a heap, for an insert, everyone's hammering the last page?
June 26, 2009 at 10:14 am
Yes, you have a hot spot on the table. Note that depending on the CLI, you could have a hot spot there as well. The key is that you also consider this when picking the CLI to avoid a hot spot.
June 26, 2009 at 10:27 am
Sure.
Out of those 14 tables, 8 are used by stored procedures.
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply