Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

Do I need to compact my SQL Server database?

A question that occasionally comes up from people who have made the switch to Microsoft SQL Server from Microsoft Access is "If I delete a bunch of rows, do I need to compact my SQL Server database?"

It's been many years since I've used Access, but I still remember the reason for their concern. Microsoft Access would continually add rows to the end of the table. If some, or even all, of the rows were deleted from the table, Microsoft Access wouldn't reuse the space. It just kept adding rows to what was the very end of the table and never backfilled the "holes". Compacting the database would essentially get rid of the "holes".

Now, I'm not an expert in Microsoft Access and I'm not knocking it. I haven't even used Access since v2.0 back in the 90's. This behavior may have changed. Or perhaps I misremember the way it was back in the day. But sufficed to say with SQL Server, this is not an issue.

But don't take my word for it. Let's consider an example. Let's create a table with three columns and populate it with some test data as shown in the following code.

 

USE tempdb ;
GO

--create a test table
CREATE TABLE dbo.Test
(
col1 INT
,col2 VARCHAR(50)
,col3 VARCHAR(MAX)
) ;

--create some test data
DECLARE @cnt INT ;
SET @cnt = 0 ;
WHILE 1 = 1
BEGIN
SELECT
@cnt = @cnt + 1 ;
INSERT
dbo.Test ( col1,col2,col3 )
VALUES (
@cnt
,'test row # ' + CAST(@cnt AS VARCHAR(100))
,REPLICATE('A',@cnt)
) ;

IF @cnt = 1000
BREAK ;
END

Now let's view the table to make sure we have what we think we have.

--view the table 
SELECT
*
FROM
dbo.Test ;

 

CompactDbA-2008-01-10

 

Using a Dynamic Management View, let's see how much space our newly created table is consuming.

--check the size of the table 
SELECT
alloc_unit_type_desc
,page_count
,avg_page_space_used_in_percent
,record_count
FROM
sys.dm_db_index_physical_stats(
DB_ID()
,OBJECT_ID(N'dbo.Test')
,NULL
,NULL
,'Detailed') ;

 

On my system, it indicates that 84 data pages are used to store the 1000 rows. Each data page is, on average, 78.4% full.

CompactDbB-2008-01-10 

 

So, let's delete half of the rows in our Test table and view the results.

--delete the odd rows 
DELETE
Test
WHERE
col1 % 2 = 1
--view the table 
SELECT
*
FROM
dbo.Test ;

CompactDbC-2008-01-10

 

Now let's look at the space consumed by the table by running the DMV query again.

--check the size of the table 
SELECT
alloc_unit_type_desc
,page_count
,avg_page_space_used_in_percent
,record_count
FROM
sys.dm_db_index_physical_stats(
DB_ID()
,OBJECT_ID(N'dbo.Test')
,NULL
,NULL
,'Detailed') ;

The number of pages remained constant as expected; the percent used for each page was essentially cut in half, again as expected.

CompactDbD-2008-01-10

 

So let's add some new rows to the table to prove that the "holes" will be filled in.

--add some more test data
DECLARE @cnt INT ;
SET @cnt = 0 ;
WHILE 1 = 1
BEGIN
SELECT
@cnt = @cnt + 1 ;
INSERT
dbo.Test ( col1,col2,col3 )
VALUES (
@cnt
,'test row # ' + CAST(@cnt AS VARCHAR(100))
,REPLICATE('B',@cnt)
) ;

IF @cnt = 500
BREAK ;
END


--check the size of the table
SELECT
alloc_unit_type_desc
,page_count
,avg_page_space_used_in_percent
,record_count
FROM
sys.dm_db_index_physical_stats(
DB_ID()
,OBJECT_ID(N'dbo.Test')
,NULL
,NULL
,'Detailed') ;

 

The results confirm our expectations - the "holes" were re-used by SQL Server. There are still 84 data pages being used by the table and the average percent used for each page has increased to 60.1% from the prior value of 39.4%

CompactDbE-2008-01-10

 

Now, since the number of data pages in our example never changed, let's run one more little test prove to ourselves that it can in fact change. Let's add some more rows to the table.

--add yet some more test data
DECLARE @cnt INT ;
SET @cnt = 0 ;
WHILE 1 = 1
BEGIN
SELECT
@cnt = @cnt + 1 ;
INSERT
dbo.Test ( col1,col2,col3 )
VALUES (
@cnt
,'test row # ' + CAST(@cnt AS VARCHAR(100))
,REPLICATE('C',@cnt)
) ;

IF @cnt = 1000
BREAK ;
END


--check the size of the table
SELECT
alloc_unit_type_desc
,page_count
,avg_page_space_used_in_percent
,record_count
FROM
sys.dm_db_index_physical_stats(
DB_ID()
,OBJECT_ID(N'dbo.Test')
,NULL
,NULL
,'Detailed') ;

 

Our page count increased to 137.

CompactDbF-2008-01-10

 

One final script to clean up after ourselves.

--clean up
DROP TABLE dbo.Test ;

 

Hopefully this will help allay the concerns that former Access developers have regarding SQL Server's re-use of "holes".

For more information, check out Books Online for sys.dm_db_index_physical_stats to see how we measured the tables consumption of space.

Cheers!

Joe

kick it on DotNetKicks.com

Print | posted on Thursday, January 10, 2008 7:46 AM | Filed Under [ T-SQL SQL Server ]

Feedback

Gravatar

# re: Do I need to compact my SQL Server database?

Access still has the "feature" of not cleaning up after itself. Also if you have too many (and this varies) delete rows in a table SELECT's on that table will fail to return any rows. INSERTs work but then you can't pull back the rows just inserted. That will get you scratching your head. Repair/Compact and all the data shows up. Had this happen many times in production.

SQL server does need some periodic cleanup. The woe is transferred to the log portion and the data portion is not so much affected.
1/19/2008 9:51 AM | Charles Kincaid
Gravatar

# re: Do I need to compact my SQL Server database?

Thanks for confirming my memory of Microsoft Access.

And you're absolutely right, SQL Server is not completely devoid of maintenance tasks. To perform at its peak, you must defrag indexes, etc. But at least you don't have to worry with regularly compacting it.

Thanks for the feedback.

Joe
1/21/2008 7:38 AM | Joe Webb
Gravatar

# re: Do I need to compact my SQL Server database?

But don't take my word for it. Let's consider an example. Let's create a table with three columns and populate it with some test data as shown in the following code.


womens snow boots | columbia sportswear | cheap makeup | cheap north face jackets
10/20/2010 2:36 AM | snow boots for women
Gravatar

# re: Do I need to compact my SQL Server database?

Now, since the number of data pages in our example never changed, let's run one more little test prove to ourselves that it can in fact change. Let's add some more rows to the table.

womens snow boots | columbia sportswear | cheap makeup | cheap north face jackets
10/21/2010 4:10 PM | columbia jackets
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET