Microsoft Press Configuring sql server 2005 môn 70 - 431 phần 6

Sau khi xây dựng chỉ số nhóm của bạn, bạn có thể tạo ra các danh mục nonclustered trên bàn. Ngược lại với một chỉ số nhóm, một chỉ số nonclustered không một lực lượng thứ tự sắp xếp trên các dữ liệu trong một bảng. trong | 454 Chapter 12 Using Transact-SQL to Manage Databases Quick Check What is external fragmentation Quick Check Answer External fragmentation is the condition in which the physical order of index pages does not match the logical order. PRACTICE Using ALTER INDEX to Correct Index Fragmentation Levels DBAs need to learn how to manage index fragmentation levels by using the ALTER INDEX REBUILD and ALTER InDeX REOrGaNIZE statements. The following two practices take you through the process of correcting index fragmentation levels by using these two statements. Practice 1 Use ALTER to Rebuild an Index In this practice you rebuild an index by using the ALTER INDEX REBUILD statement. 1. Start SSMS. 2. Connect to the instance containing the sample AdventureWorks database. 3. In Object Explorer right-click the AdventureWorks database and choose New Query to open the Query Editor pane. 4. In the Query Editor pane type in the following Transact-SQL statement to view current fragmentation levels rebuild the indexes on the HumanResources. Employee table and view fragmentation levels after the rebuild USE AdventureWorks --View the current fragmentation levels. SELECT index_id avg_fragmentation_in_percent avg_page_space_used_in_percent FROM DB_ID AdventureWorks OBJECT_ID NULL NULL DETAILED WHERE index_id 0 --does not return information about heaps --Rebuild all indexes on the table. --Create the indexes with a fill factor of 90. --Allow the index operation to take place ONLINE ALTER INDEX ALL ON REBUILD WITH FILLFACTOR 90 ONLINE ON --View the fragmentation levels after the index rebuilds. SELECT index_id avg_fragmentation_in_percent avg_page_space_used_in_percent FROM DB_ID AdventureWorks NULL NULL DETAILED WHERE index_id 0 --does not return information about heaps Lesson 1 Managing Index Fragmentation 455 Practice 2 Use ALTER .