Microsoft® SQL Server® 2012 Bible

595

Chapter 22: Maintaining the Database

22

The following stored procedure adds 100,000 rows each time it’s executed:

CREATE PROC Add100K

AS

SET nocount on;

DECLARE @X INT;

SET @X = 0;

WHILE @X < 100000

BEGIN

INSERT Frag (Col1,Col2)

VALUES (@X, 'sample data');

SET @X = @X + 1;

END

The following batch calls Add100K several times and populates the Frag table (be patient,

the query can require several minutes to execute):

EXEC Add100K;

EXEC Add100K;

EXEC Add100K;

EXEC Add100K;

EXEC Add100K;

The dynamic management function sys.dm_db_index_physical_stats reports the

fragmentation details and the density for a given table or index. With half a million rows,

the Frag table is fragmented, and most pages are slightly more than half full, as the follow-

ing command shows:

USE tempdb;

SELECT * FROM sys.dm_db_index_physical_stats ( db_id('tempdb'),

object_id('Frag'), NULL, NULL, 'DETAILED');

In the following result (abridged), Index ID: 1 is the clustered primary-key index, so it’s

also reporting the data-page fragmentation. Index ID: 2 is the nonclustered index:

index_id: 1

index_type_desc: CLUSTERED INDEX

avg_fragmentation_in_percent: 99.1775717920756

page count: 22008

avg_page_space_used_in_percent: 68.744230294045

index_id: 2

index_type_desc: NONCLUSTERED INDEX

avg_fragmentation_in_percent: 98.1501632208923

page count: 2732

avg_page_space_used_in_percent: 58.2316654311836

c22.indd 595c22.indd 595 7/31/2012 9:24:30 AM7/31/2012 9:24:30 AM

http://www.it-ebooks.info