SQL Server Tacklebox- P23: This book, as with almost all books, started out as an idea. I wanted to accumulate together those scripts and tools that I have built over the years so that DBAs could sort through them and perhaps adapt them for their own circumstances. | 4 Managing data growth Also it should be noted that users will be unable to connect to the Book_List table for the duration of the index build. Essentially SQL Server has to physically order those millions of records to align with the definition of the clustered index. Let s see what the index took out of my hide by way of space. The former index space for this table was 8K and data space was over 3 Gig. What does sp_spaceused tell me now See Figure . J_ n Results _J Messages name rows reserved data index_size unused 1 I B ook_ .List j 2902000 3322000 KB 3316576 KB 5376 KB 48 KB Figure Building the clustered index has increased the index_size to 5376KB. An increase in index_size to 5376K does not seem too significant. When you create a clustered index the database engine takes the data in the heap table and physically sorts it. In the simplest terms both a heap and a clustered table a table with a clustered index both store the actual data one is just physically sorted. So I would not expect that adding a clustered index for the Read_iD column to cause much growth in index_size. However while the data size and index size for the Book_List table did not grow significantly the space allocated for the database did double as you can see from Figure . 110 4 Managing data growth Figure Creating the clustered index caused the data file to double in size. So not only did the index addition take the table offline for the duration of the build 12 minutes it also doubled the space on disk. The reason for the growth is that SQL Server had to do all manner of processing to reorganize the data from a heap to a clustered table and additional space almost double was required to accommodate this migration from a heap table to a clustered table. Notice though that after the process has completed there is nearly 50 free space in the expanded file. The question remains did I benefit from adding this index and do I need to add any covering non-clustered indexes First
