July 20, 2009 at 12:10 pm
We've got the usual problem of having loads of unused space in tables. This isn't causing performance issues, but is causing big problems with disk space, backups etc. According to sp_spaceused we have 32GB of unused space in our tables (nearly 35%). I've read heaps of articles detailing ways to get round this, mostly based around clustered indexes and DBCC REINDEX, all of which is fine, but I still can't work out why this is happening in some instances.
Take this one simple table:-
[font="Courier New"]CREATE TABLE [dbo].[ws_pda_ack](
[job_number] [int] NULL,
[handheld_id] [char](15) NULL,
[del_ack_datetime] [datetime] NULL,
[read_ack_datetime] [datetime] NULL
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [ws_pda_ack_i01] ON [dbo].[ws_pda_ack]
([job_number] ASC)WITH FILLFACTOR = 85 ON [PRIMARY]
name rows reserved data index_size unused
-------------- ------- ----------- --------- ----------- ----------
ws_pda_ack 191180 324256 KB 43800 KB 4304 KB 276152 KB
[/font]
It is 85% unused, but rows are only ever inserted into it (or later updated). There are no updates or deletes for it, and it's never been manually cleared down.
I can easily reclaim the space on this one by using a clustered index, but the question is how can this be happening on a table where data is only ever being inserted?
July 21, 2009 at 4:14 am
CREATE NONCLUSTERED INDEX [ws_pda_ack_i01] ON [dbo].[ws_pda_ack]
([job_number] ASC)WITH FILLFACTOR = 85 ON [PRIMARY]
It is 85% unused, but rows are only ever inserted into it (or later updated). There are no updates or deletes for it, and it's never been manually cleared down.
I can easily reclaim the space on this one by using a clustered index, but the question is how can this be happening on a table where data is only ever being inserted?
I think it is happening due to page split.
Run DBCC SHOWCONTIG and check how much fragmentation is there.
July 21, 2009 at 4:26 am
Have you tried it with the @updateusage option? Sometimes when an index is dropped, the space stats can be out.
eg.
USE AdventureWorks;
GO
EXEC sp_spaceused @updateusage = N'TRUE';
GO
July 21, 2009 at 5:09 am
I used the DBCC UPDATEUSAGE method instead which seems do the same.
The output from DBCC SHOWCONTIG is as follows. Page density is very low at 19%, which seems to be tie up with the issue, so any ideas why this would occur on a table that is only ever inserted into? I'm wondering if this problem is maybe specific to SQL 2000(?) This table is just an example, but is typical of a number of tables we have, the worst of which has over 9GB of unused space in it.
I presume the best or only option is cluster the indexes(?) I'm a little wary of using clustered indexes on all tables, as has been recommended elsewhere, because in the past they have completely messed up perfectly good execution plans on some complex queries we have. It seems the optimiser will often favour these even when far better index is available. Anyway, that's another issue...
DBCC SHOWCONTIG scanning 'ws_pda_ack' table...
Table: 'ws_pda_ack' (1968829105); index ID: 0, database ID: 34
TABLE level scan performed.
- Pages Scanned................................: 5379
- Extents Scanned..............................: 4906
- Extent Switches..............................: 4905
- Avg. Pages per Extent........................: 1.1
- Scan Density [Best Count:Actual Count].......: 13.72% [673:4906]
- Extent Scan Fragmentation ...................: 75.89%
- Avg. Bytes Free per Page.....................: 6527.7
- Avg. Page Density (full).....................: 19.35%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
July 21, 2009 at 7:06 am
Page density is very low at 19%, which seems to be tie up with the issue, so any ideas why this would occur on a table that is only ever inserted into? I'm wondering if this problem is maybe specific to SQL 2000(?)
It is happening due to page split as I mentioned earlier. Page split happens during insert/update only. Not during delete. This is not specific to SQL 2000. I have checked page split during insert/update in SQL Server 2008 as well.
Row size also matters. If the row size is above 4000 bytes, there will be only one row in the page. Remaining space is wasted.
July 21, 2009 at 7:18 am
It's because it's a non clustered index.
Updates on a non clustered index can effectively be a delete and an insert - on a non clustered index the data is deleted from whatever page it was on and reinserted at the end. To get this level of fragmentation I'm guessing there are an average of about 4 updates per insert after you 'compress' the data by putting a clustered index on it and off again.
SQL Server needs clustered indexes to be efficient really. You should ideally have them on all tables.
I take your point about the optimisation though - though I have found that providing all the tables have clustered indexes it's usually OK.
Tim
.
July 21, 2009 at 7:20 am
Suresh B. (7/21/2009)
Page density is very low at 19%, which seems to be tie up with the issue, so any ideas why this would occur on a table that is only ever inserted into? I'm wondering if this problem is maybe specific to SQL 2000(?)
It is happening due to page split as I mentioned earlier. Page split happens during insert/update only.
Suresh.B, I don't think page splits will be applicable on a non-clustered index, because the data isn't ordered so never needs to split.
Tim
.
July 21, 2009 at 8:36 am
Thanks for the replies. It sounds possible that it's a problem with UPDATEs re-writing data at the end. I have tried this with small test script which inserted and updated 200,000 rows into this table. This didn't have any issues with unused space, but this may not be a realistic real-world test of an OLTP environment where the data has built up over many months.
I will implement clustered indexes on the problem tables which should sort it out, and maybe look at putting them on other tables too.
July 21, 2009 at 10:07 pm
Tim Walker (7/21/2009)
Suresh B. (7/21/2009)
Page density is very low at 19%, which seems to be tie up with the issue, so any ideas why this would occur on a table that is only ever inserted into? I'm wondering if this problem is maybe specific to SQL 2000(?)
It is happening due to page split as I mentioned earlier. Page split happens during insert/update only.
Suresh.B, I don't think page splits will be applicable on a non-clustered index, because the data isn't ordered so never needs to split.
Tim
Thanks Tim
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply