TAILIEUCHUNG - SQL Server Tacklebox- P23

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

TAILIEUCHUNG - Chia sẻ tài liệu không giới hạn
Địa chỉ : 444 Hoang Hoa Tham, Hanoi, Viet Nam
Website : tailieuchung.com
Email : tailieuchung20@gmail.com
Tailieuchung.com là thư viện tài liệu trực tuyến, nơi chia sẽ trao đổi hàng triệu tài liệu như luận văn đồ án, sách, giáo trình, đề thi.
Chúng tôi không chịu trách nhiệm liên quan đến các vấn đề bản quyền nội dung tài liệu được thành viên tự nguyện đăng tải lên, nếu phát hiện thấy tài liệu xấu hoặc tài liệu có bản quyền xin hãy email cho chúng tôi.
Đã phát hiện trình chặn quảng cáo AdBlock
Trang web này phụ thuộc vào doanh thu từ số lần hiển thị quảng cáo để tồn tại. Vui lòng tắt trình chặn quảng cáo của bạn hoặc tạm dừng tính năng chặn quảng cáo cho trang web này.