Reuse of the data table space (Reusing space in a table)

If you delete some rows of data, I need to compress the SQL Server database? For these spaces can be re-used, which is a very good question, the cut is often asked, which relates to the management of the SQL Server database to delete and compression mechanism.

1 understand how SQL Server automatically re-use of the data table space, a brief description of the following examples do.

1.1 Establish a temporary test data

Create a test table: Test, fill in the blank 1000 rows of data.

Code

– Create a test table
CREATE TABLE dbo.Test to
(
col1 INT
, Col2 CHAR (25)
Col3 VARCHAR (4000)
);
– Create some test data
DECLARE @ Cnt Int;
SET @ Cnt = 0;
WHILE @ Cnt < 1000
BEGIN
SELECT
@ Cnt = @ Cnt + 1;
INSERT
dbo.Test (col1, col2, col3)
VALUES (
@ Cnt
, ‘Test row #’ + CAST (_AT_ cnt AS VARCHAR (10)) + ‘A’
REPLICATE (‘ABCD’, ROUND (RAND () * @ Cnt, 0))
);
END

  To introduce a DMV: sys.dm_db_index_physical_stats first

  There are several important fields:
  alloc_unit_type_desc: Valid values ??are: IN_ROW_DATA, LOB_DATA, ROW_OVERFLOW_DATA
  page_count: storing data line of data pages
 the average percentage of avg_page_space_used_in_percent: data page
  record_count: Total number of records
  According to the DMV to see the results of the test table Test:
– DMV SQL
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 ( ‘ Test ‘ ) NULL And NULL And ‘ Detailed ‘ )


The results are as follows:

You can see the 1000 data occupies 152 pages per page space was 84.2%
  1.2 delete some old data again to view the data space usage
  Remove half of the data.
– Delete the odd rows
DELETE FROM Test WHERE col1% 2 = 1
– View the table
SELECT * from Test

DMV SQL statement to re-run the above to get:

  Thus, the data store 500 is still occupied by the 152 data pages, an average of each page of data space utilization rate was 43.2% (84.2%, almost 1/2)
  1.3 add new rows of data
Code

– Add some more test data
DECLARE @ Cnt Int;
SET @ Cnt = 0;
WHILE @ Cnt < 500
BEGIN
SET @ Cnt = @ Cnt + 1;
INSERT INTO dbo.Test (col1, col2, col3) VALUES (
@ Cnt, ‘test row #’ + CAST (_AT_ cnt AS Varchar (10)), REPLICATE (‘WXYZ’, ROUND (RAND () * _AT_ Cnt, 0)));
END
  Add 500 data, the test table Test 1000 data, re-run again as the DMV the SQL statement:

  See store 1000 data is still used 152, but each page space utilization rate increased from 43.2% to 65%, the SQL Server to reuse the extra space, without adding any new data page.
  Please note that the use of this test table is a heap storage structure (not indexed), the rows in the table without prior specific sort is also applicable in the index table, SQL Server will reuse the spare space in the table (holes).
  1.4 CLEAN UP
– clean up
DROP TABLE dbo.Test;

The Server not 2.SQL automatically reclaim the space

  In some cases, SQL Server does not automatically recovered without space if the definition of a table is changed to swap one or several of the field (for example, DROP), the space has been consumed and will not be SQL Server immediate reuse.
  The following are examples:
  2.1 The establishment of test data
Code

CREATE TABLE dbo.Test2 to
(
col1 INT
, Col2 CHAR (25)
Col3 VARCHAR (4000)
);
– Create some test data
DECLARE @ Cnt Int;
SET @ Cnt = 0;
WHILE @ Cnt < 1000
BEGIN
SET @ Cnt = @ Cnt + 1;
INSERT INTO dbo.Test2 (col1, col2, col3) VALUES (
@ Cnt, ‘test row #’ + CAST (_AT_ cnt AS Varchar (10)), REPLICATE (‘A’, 4000));
END

Above as an example, SQL DMV query for the table: Test2
– SQL DMV
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 ( ‘ Test2 ‘ ) NULL And NULL And ‘ Detailed ‘ )

  As can be seen: 
  1000 lines of data occupies 500 pages, each page space usage rate of 99.8%
  2.2 drop off a field (such as col3)
ALTER TABLE dbo.Test2 DROP COLUMN col3;


See after running SQL DMV is:

 Out of a field and DROP: 1000 rows of data occupies 500 pages, each page space usage rate of 99.8%. SQL Server does not recover unused space, not removed from the data page from which column data.
  SQL Server is attempting to do is update the metadata in the system tables in the query this column does not show up as a result set, the space occupied by the data will not be freed.
  2.3 Add data
Code

– Insert additional rows
DECLARE @ Cnt Int;
SET @ Cnt = 0;
WHILE @ Cnt < 500
BEGIN
SET @ Cnt = @ Cnt + 1;
INSERT The INTO dbo.Test2 (col1, col2) VALUES (
@ Cnt, ‘test row #’ + CAST (_AT_ cnt AS VARCHAR (10)));
END

Run again SQL DMV query:

  1500 lines of data occupies 503 pages, each page space usage rate of 99.7%, you can see three new data page if the space can be reused, it should be more than enough space to store the new 500 line data does not need to add data page.

3 space reclamation (DBCC CLEANTABLE),

  Reuse SQL Server will automatically reclaim space in certain circumstances, but in some cases not.
  Can be recovered by DBCC COMMAND unused space.
DBCC CLEANTABLE ( ‘ TEST ‘ And ‘ dbo.Test2 ‘ );


Run-off can be seen after:

  Recover unused space, page space usage is greatly reduced. 503 page, and there is no compression.
 After DROP, DELETE with DBCC CLEANTABLE can timely recovery flagged for deletion for the space occupied, can better improve the page and disk space utilization.