Recognizing when SQL Server does not reclaim space in Visual C#.NET

Paint ANSI/AIM Code 128 in Visual C#.NET Recognizing when SQL Server does not reclaim space

Recognizing when SQL Server does not reclaim space
Code 128 Code Set B Maker In C#
Using Barcode creation for VS .NET Control to generate, create ANSI/AIM Code 128 image in VS .NET applications.
www.OnBarcode.com
Code 128 Code Set B Scanner In C#
Using Barcode reader for VS .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
,REPLICATE('WXYZ', ROUND(RAND() * @cnt, 0)) ) ; END
Code 39 Full ASCII Encoder In C#.NET
Using Barcode encoder for VS .NET Control to generate, create USS Code 39 image in Visual Studio .NET applications.
www.OnBarcode.com
Encoding Linear In Visual C#
Using Barcode creation for .NET Control to generate, create Linear Barcode image in .NET framework applications.
www.OnBarcode.com
Now let s once again use the sys.dm_db_index_physical_stats DMV to see the space used by the table. Figure 5 displays the result of the query.
Print 2D In Visual C#
Using Barcode creation for VS .NET Control to generate, create Matrix Barcode image in VS .NET applications.
www.OnBarcode.com
Barcode Creator In C#.NET
Using Barcode generation for .NET Control to generate, create Barcode image in VS .NET applications.
www.OnBarcode.com
Figure 5 Reviewing the space used by the dbo.Test table after inserting new rows
EAN13 Generator In C#.NET
Using Barcode drawer for Visual Studio .NET Control to generate, create EAN-13 image in Visual Studio .NET applications.
www.OnBarcode.com
DUN - 14 Encoder In Visual C#
Using Barcode creator for .NET framework Control to generate, create UPC Case Code image in Visual Studio .NET applications.
www.OnBarcode.com
Notice that the table is still using 158 data pages, but the average percent used for each page has increased to 62.9 percent from the prior value of 42.1 percent. The results confirm our expectations the holes were indeed reused by SQL Server. No additional pages were allocated; the free space available was used. At this point it is important to understand that, although SQL Server can and will automatically reuse space that was previously used by one or more rows, it will do so only under very specific circumstances. This behavior is most readily demonstrated through the use of a heap (a table without a clustered index) in which there is no predefined order of the rows. The behavior can also be observed in clustered tables when the newly inserted rows have key values that allow them to be inserted into the holes left by prior deletions. You shouldn t expect all newly inserted rows to fit nicely in the holes of a clustered table. The point is that SQL Server will reuse space as appropriate. To clean up after this example, let s execute one final statement to drop the dbo.Test table. Listing 6 displays the DROP statement.
Code-128 Generation In Java
Using Barcode creator for Java Control to generate, create Code 128A image in Java applications.
www.OnBarcode.com
Code 128B Generation In Visual Studio .NET
Using Barcode generator for Reporting Service Control to generate, create Code-128 image in Reporting Service applications.
www.OnBarcode.com
Listing 6 Dropping the dbo.Test table
GS1 128 Generation In Java
Using Barcode creator for Java Control to generate, create UCC.EAN - 128 image in Java applications.
www.OnBarcode.com
QR-Code Maker In VS .NET
Using Barcode printer for ASP.NET Control to generate, create QR Code 2d barcode image in ASP.NET applications.
www.OnBarcode.com
--clean up DROP TABLE dbo.Test ;
Print Code 128C In None
Using Barcode maker for Excel Control to generate, create Code 128 Code Set C image in Office Excel applications.
www.OnBarcode.com
EAN / UCC - 13 Generation In .NET Framework
Using Barcode creator for ASP.NET Control to generate, create UCC-128 image in ASP.NET applications.
www.OnBarcode.com
Recognizing when SQL Server does not reclaim space
Painting Code 128C In VS .NET
Using Barcode drawer for Visual Studio .NET Control to generate, create ANSI/AIM Code 128 image in VS .NET applications.
www.OnBarcode.com
Create Barcode In Visual Basic .NET
Using Barcode encoder for Visual Studio .NET Control to generate, create Barcode image in Visual Studio .NET applications.
www.OnBarcode.com
Under certain circumstances SQL Server does not automatically reclaim space that is no longer being used. If a table definition is altered to drop one or more variable length columns, the space consumed by those columns is not immediately made available for reuse by SQL Server. To illustrate this behavior, let s consider an example. Let s create another test table using the script in listing 7. The script creates the table and populates it with 1,000 rows of data.
Code 128 Code Set B Decoder In Java
Using Barcode reader for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
Barcode Drawer In Java
Using Barcode maker for BIRT Control to generate, create Barcode image in BIRT applications.
www.OnBarcode.com
Listing 7 Creating the dbo.Test2 table
Barcode Scanner In Java
Using Barcode recognizer for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
EAN 13 Decoder In Java
Using Barcode decoder for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
USE tempdb ; GO --create the dbo.Test2 table
CHAPTER 30 CREATE TABLE dbo.Test2 ( col1 INT ,col2 CHAR(25) ,col3 VARCHAR(4000) ) ; --create some test data DECLARE @cnt INT ; SET @cnt = 0 ; WHILE @cnt < 1000 BEGIN SELECT
Reusing space in a table
@cnt = @cnt + 1 ; INSERT dbo.Test2 ( col1,col2,col3) ( @cnt ,'test row # ' + CAST(@cnt AS VARCHAR(10)) ,REPLICATE('A', 4000) ) ; END VALUES
Figure 6 shows the results from the SELECT statement. This table has three columns of data, an integer in the first column, a character string in the second column that can contain up to 25 characters, and a variable length character string in the final column that contains 4,000 characters.
Figure 6 Viewing data in the dbo.Test2 table
Using the query shown in listing 3, we can see how much space our newly created dbo.Test2 table is consuming. Figure 7 shows the results. The newly created table takes up 500 data pages to store the 1,000 rows of data. Each data page is, on average, 99.9 percent full.
Figure 7 Space used by the dbo.Test2 table
Now to set up our test scenario, let s drop the third column, the one that consumes the most space. Listing 8 contains the ALTER TABLE script to drop col3; it then executes the DMV query to reveal the space used by the table.
Recognizing when SQL Server does not reclaim space
Listing 8
Dropping a varchar column in the dbo.Test table
--drop the last column ALTER TABLE dbo.Test2 DROP COLUMN col3 ; --check the space used again 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.Test2') ,NULL ,NULL ,'Detailed') ;
Looking at the results in figure 8, we can see that we get the same results as before 500 data pages, storing 1,000 rows, each 99.9 percent full, and this after dropping the column that consumed the most space.
Figure 8 Reviewing the space used after dropping a column
Why is this When a table is altered to drop a column, SQL Server does not remove the column data from the data pages. Instead it updates the metadata in the system tables so that when queried, it appears as if the column no longer exists. The data is still present in the data pages, but it s not returned as a part of a result set. Thus, the space cannot be reused initially. So let s add some additional rows to the table and see what happens. The script shown in listing 9 inserts 500 additional rows into the dbo.Test2 table. Notice that this script inserts only two columns of data because we ve dropped the third column, col3.
Copyright © OnBarcode.com . All rights reserved.