October 27, 2008 at 1:15 pm
My guess is this would get cleaned up over time, but I'd be curious to see.
And I agree with Johan, use a clustered index. Doesn't hurt you to have one.
October 27, 2008 at 1:21 pm
The clustered didnt hurt at all. 🙂 It actually helped... And this was a mystery to me.
Thanks Everyone..:-)
-Roy
October 27, 2008 at 2:02 pm
I got an answer from one of the storage engine team. He said that at some point in time, there must have been rows on all of those pages, even if they were added and then immediately deleted. With a heap, SQL will not deallocate the pages after the rows are deleted. It assumes they will be reused at some point.
To get the pages deallocated requires putting a clustered index on, dropping and recreating the table or, in SQL 2008 only, rebuilding the heap ALTER TABLE < Table Name > REBUILD
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2008 at 2:57 pm
GilaMonster (10/27/2008)
To get the pages deallocated requires putting a clustered index on, dropping and recreating the table or, in SQL 2008 only, rebuilding the heapALTER TABLE < Table Name > REBUILD
Very interesting - did not know that we can now rebuild HEAPS in SQL Server 2008. Do you know if that is documented anywhere?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 27, 2008 at 3:09 pm
Yup. It's documented in Books Online under < drum roll > Alter Table .:D
REBUILD
Use the REBUILD WITH syntax to rebuild an entire table including all the partitions in a partitioned table. If the table has a clustered index, the REBUILD option rebuilds the clustered index.
It doesn't say what rebuild does actually to a heap though. That still needs some investigating.
The main use for it is with data compression eg.
ALTER TABLE T1
REBUILD WITH (DATA_COMPRESSION = PAGE);
ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = NONE) ;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2008 at 3:10 pm
Hi Gail Shaw,
That explains it. Thanks for the update. I have updated our DBA Review guidelines for the developers to make sure that they add a clustered Index on a table that has lots of Inserts and delete happening in it even though it might have only 5 or 6 rows at any given point of time.
Roy
-Roy
October 27, 2008 at 3:39 pm
Well, of course that is where it would be. Stupid me - for some reason I saw ALTER INDEX not ALTER TABLE :w00t:
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 27, 2008 at 3:47 pm
Okay, now - we need to be just a bit careful with this. Found a couple of caveats we should be aware of before we start rebuilding heaps:
System tables cannot be enabled for compression. If the table is a heap, the rebuild operation will be single threaded. For a more information about data compression, see Creating Compressed Tables and Indexes.
If the heap is very large, single threading the rebuild could take a very long time.
The following example changes the compression of a nonpartitioned table. The heap or clustered index will be rebuilt. If the table is a heap, all nonclustered indexes will be rebuilt.
And, to top it off - if you have several non clustered indexes, you might just want schedule this for a weekend.
Still - good to know that we can do something about this in SQL Server 2008.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 27, 2008 at 4:03 pm
Jeffrey Williams (10/27/2008)
The following example changes the compression of a nonpartitioned table. The heap or clustered index will be rebuilt. If the table is a heap, all nonclustered indexes will be rebuilt.
And, to top it off - if you have several non clustered indexes, you might just want schedule this for a weekend.
Makes sense. After all, in a heap it's the RID (FileID, PageId, Slot index) that's used as the row's location in all of the nonclustered indexes. If that changes, and in a heap rebuild it probably will, then the nonclusters have to be rebuilt. It's kinda like rebuilding the cluster in SQL 2000, same thing happened.
Then again, if you have a really large heap with several NC indexes, it's possible that it's better off not being a heap.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2008 at 4:22 pm
GilaMonster (10/27/2008)
Makes sense. After all, in a heap it's the RID (FileID, PageId, Slot index) that's used as the row's location in all of the nonclustered indexes. If that changes, and in a heap rebuild it probably will, then the nonclusters have to be rebuilt. It's kinda like rebuilding the cluster in SQL 2000, same thing happened.Then again, if you have a really large heap with several NC indexes, it's possible that it's better off not being a heap.
Yeah - it does make sense. Just need to be aware of it before I go and rebuild the vendor's very large heap that I have requested they change to a clustered index. Still not sure why they think it is better off as a heap with several million rows in it, but not much I can do about it either. 😉
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 27, 2008 at 8:50 pm
1) I am sure heaps have their uses, like other tools/options
2) speaking of options, I like having the ability to rebuild heaps in 2008!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 28, 2008 at 12:57 am
Nice feedback all.
Even the ( drum roll ) worked :w00t:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply