November 24, 2009 at 3:58 pm
I've done a lot of searching and haven't had a lot of luck with this one. I have a few tables that have a very large amount of unused space and I can't figure out how to make them release that space. Beyond the 'wasted' drive space, I'm also concerned that having that much unused space could have a negative impact on performance.
I'll detail one of the largest problematic tables. I've checked all the usual suspects(or at least the ones that I'm aware of). I've run a DBCC DBREINDEX on the table. (Yes, it has a clustered index) I've run a DBCC CLEANTABLE.
Can anyone think of anything I've missed? I'd bet a lot of the indexes are of very limited use, but I don't really want to drop them until we get on 2008 and I can verify that the'yre not being used.
Here is a bunch of information on the table/indexes.
sp_spaceused
Rows Reserved (MB) Data (MB) Indexes (MB) Unused (MB)
5,888,697 13,201 3,748 4,246 5,205
SHOWCONTIG
- Pages Scanned................................: 468612
- Extents Scanned..............................: 58880
- Extent Switches..............................: 58979
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.32% [58577:58980]
- Logical Scan Fragmentation ..................: 0.02%
- Extent Scan Fragmentation ...................: 7.39%
- Avg. Bytes Free per Page.....................: 1358.5
- Avg. Page Density (full).....................: 83.22%
CHECKALLOC
Index ID 1. FirstIAM (1:10429). Root (1:5478552). Dpages 468612.
Index ID 1. 471040 pages used in 58880 dedicated extents.
Index ID 2. FirstIAM (1:10441). Root (1:5839913). Dpages 48915.
Index ID 2. 49093 pages used in 6140 dedicated extents.
Index ID 4. FirstIAM (1:10888). Root (1:5934526). Dpages 36454.
Index ID 4. 36678 pages used in 4588 dedicated extents.
Index ID 5. FirstIAM (1:15435). Root (1:5940941). Dpages 33635.
Index ID 5. 33821 pages used in 4228 dedicated extents.
Index ID 7. FirstIAM (1:15704). Root (1:5974872). Dpages 41839.
Index ID 7. 42126 pages used in 5273 dedicated extents.
Index ID 8. FirstIAM (1:15715). Root (1:6046386). Dpages 41742.
Index ID 8. 42019 pages used in 5253 dedicated extents.
Index ID 10. FirstIAM (1:15718). Root (1:6071816). Dpages 41741.
Index ID 10. 42025 pages used in 5258 dedicated extents.
Index ID 11. FirstIAM (1:15791). Root (1:6140337). Dpages 46007.
Index ID 11. 46351 pages used in 5794 dedicated extents.
Index ID 12. FirstIAM (1:17032). Root (1:6218072). Dpages 47589.
Index ID 12. 47970 pages used in 6001 dedicated extents.
Index ID 13. FirstIAM (1:51417). Root (1:6458499). Dpages 41739.
Index ID 13. 42017 pages used in 5253 dedicated extents.
Index ID 14. FirstIAM (1:51421). Root (1:6507262). Dpages 36466.
Index ID 14. 36692 pages used in 4598 dedicated extents.
Index ID 15. FirstIAM (1:55234). Root (1:6545302). Dpages 36331.
Index ID 15. 36545 pages used in 4569 dedicated extents.
Index ID 16. FirstIAM (1:62444). Root (1:6744906). Dpages 36324.
Index ID 16. 36526 pages used in 4567 dedicated extents.
Index ID 26. FirstIAM (1:79997). Root (1:6834510). Dpages 36329.
Index ID 26. 36543 pages used in 4569 dedicated extents.
Total number of extents is 124971.
Indexes: (replaced column names with data types)
ALTER TABLE [dbo].[MyTable] WITH NOCHECK ADD CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
([nvarchar(10)],[int],[int]) WITH FILLFACTOR = 80 ON [PRIMARY]
CREATE UNIQUE INDEX [IX_MyTable_B] ON [dbo].[MyTable]([uniqueidentifier]) WITH FILLFACTOR = 80 ON [PRIMARY]
CREATE INDEX [IX_MyTable_C] ON [dbo].[MyTable]([nchar(1)]) WITH FILLFACTOR = 80 ON [PRIMARY]
CREATE INDEX [IX_MyTable_D] ON [dbo].[MyTable]([nchar(1)]) WITH FILLFACTOR = 80 ON [PRIMARY]
CREATE INDEX [IX_MyTable_E] ON [dbo].[MyTable]([datetime]) WITH FILLFACTOR = 80 ON [PRIMARY]
CREATE INDEX [IX_MyTable_F] ON [dbo].[MyTable]([datetime]) WITH FILLFACTOR = 80 ON [PRIMARY]
CREATE INDEX [IX_MyTable_G] ON [dbo].[MyTable]([datetime]) WITH FILLFACTOR = 80 ON [PRIMARY]
CREATE INDEX [IX_MyTable_H] ON [dbo].[MyTable]([nvarchar(8)]) WITH FILLFACTOR = 80 ON [PRIMARY]
CREATE INDEX [IX_MyTable_I] ON [dbo].[MyTable]([nvarchar(10)]) WITH FILLFACTOR = 80 ON [PRIMARY]
CREATE INDEX [IX_MyTable_J] ON [dbo].[MyTable]([datetime]) WITH FILLFACTOR = 80 ON [PRIMARY]
CREATE INDEX [IX_MyTable_K] ON [dbo].[MyTable]([nchar(1)]) WITH FILLFACTOR = 80 ON [PRIMARY]
CREATE INDEX [IX_MyTable_L] ON [dbo].[MyTable]([nvarchar(3)]) WITH FILLFACTOR = 80 ON [PRIMARY]
CREATE INDEX [IX_MyTable_M] ON [dbo].[MyTable]([nvarchar(10)], [int], [int], [nchar(1)]) WITH FILLFACTOR = 80 ON [PRIMARY]
CREATE INDEX [IX_MyTable_N] ON [dbo].[MyTable]([nvarchar(6)]) WITH FILLFACTOR = 80 ON [PRIMARY]
November 24, 2009 at 6:18 pm
I think you might need to create anew table followed by exporting all the current table data to this new table and finally renaming the new table. As you are making use of nvarchar columns this might be the final resolution.
http://www.simple-talk.com/sql/database-administration/on-the-trail-of-the-expanding-databases/
MJ
November 25, 2009 at 1:19 am
Do the tables contain a blob column?
If so, you may be a victim of a known SQL Server 200 bug.
Have a look at my response to this post earlier this year.
November 25, 2009 at 7:35 am
Ian, Manu, thanks for the feedback. I meant to include in the original post that the table did not have any blob columns in it, sorry about that. As far as the other reasons included in that article, they focus around:
Adding a clustered index (this one already has one)
Using Text-In-Row (No text columns in the table)
Using DBCC CLEANTABLE (I did this, but I haven't dropped any variable length columns, so it had no effect)
Fixing a SQL 7.0 bug (We're in compatability mode 80, and the bug is targeting text columns which this doesn't have).
Server abnormally creating extra space when it runs out of memory. This is possible. That server is pretty beefy (dual quad cores, 32g of ram, 3 arrays), but there is a very large amount of poorly written code that still taxes it.
I believe that the new table solution would work(at least temporarily), and I may try that, but I'm slightly concerned that if I don't *change* something that it'll just soak the space back up. Also, does anyone have any idea as to whether or not that extra space can cause performance issues?
There isn't an issue with being out of drive space on the server, so I don't see a ton of point in doing the table copy if both of the following are true:
A. The space is just going to be gradually re-claimed
B. There is no performance hit for having it.
Any other thoughts?
November 25, 2009 at 3:08 pm
The amount of freespace in the table is pretty much consistent with the fact that the clustered index has a FILL FACTOR of 80. The only way to make the table have less unused space is to increase the size of the FILL FACTOR. Of course, that could have a devastating effect on inserts and upates especially if the clustered index isn't in some temporal column such as an IDENTITY (tmporal only in relational order) column or a date column.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 25, 2009 at 3:17 pm
Does a fill factor of 80 really explain that discrepancy though? The way I understood it, that should fill the pages 80% full, leaving unused space equal to 25% of the data size.
If that's true, I should have unused of approximately 937 MB of unused space. Even applying that same math to all of the index storage as well, I'm only coming to right at 2G. I've got 5.2G of unused.
November 25, 2009 at 9:33 pm
Agreed.... if everything were absolutely perfect. The problem is that clustered indexes can do page splits and non-clustered indexes do "extent splits". Guess what happens when you do DBCC DBREINDEX? In SQL Server 2000, the sorts get done in whatever database that's being reindex and that will be part of the "unused" space above and beyond the FILL FACTOR. If you have a lot of indexes and some really wide clustered indexes, it can make your databases absolutely huge compared to what they ought to be for data size. I don't believe there's anything that can be done short of dropping the indexes and rebuilding them from scratch.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply