Depth The number of levels in the index, including the leaf level. Forwarded records The number of records in a heap that have forward pointers to another data location. Ghost rows The number of rows that are marked as deleted but not yet removed. Index type The type of index.
Leaf-level rows The number of leaf level rows. Maximum row size The maximum leaf-level row size. Minimum row size The minimum leaf-level row size. Pages The total number of data pages. Version ghost rows The number of ghost records that are being retained due to an outstanding snapshot isolation transaction.
In Object Explorer , connect to an instance of Database Engine. On the Standard bar, click New Query. Copy and paste the following example into the query window and click Execute. Employee table. In Object Explorer, Expand the database that contains the table on which you want to reorganize an index. Expand the table on which you want to reorganize an index. Right-click the index you want to reorganize and select Reorganize.
In the Reorganize Indexes dialog box, verify that the correct index is in the Indexes to be reorganized grid and click OK. Select the Compact large object column data check box to specify that all pages that contain large object LOB data are also compacted.
Click OK. Expand the table on which you want to reorganize the indexes. Right-click the Indexes folder and select Reorganize All.
In the Reorganize Indexes dialog box, verify that the correct indexes are in the Indexes to be reorganized. To remove an index from the Indexes to be reorganized grid, select the index and then press the Delete key. Right-click the index you want to reorganize and select Rebuild. In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to be rebuilt grid and click OK. The example rebuilds a single index on the Employee table. Copy and paste the following example into the query.
The example specifies the keyword ALL. This rebuilds all indexes associated with the table. Three options are specified. Reorganizing is a more lightweight "index repair" option. A reorganization involves the leaf level of the index, and is designed to repair the physical ordering of pages. The process also compacts pages to apply the previously set fill factor. Reorganizing can be performed when your app is online, and if you cancel it then it can just stop where it is and you can rollback relatively easily.
Note that the index rebuilding process is relatively resource intensive, and in most cases, it also locks the database resources. Join our email list and receive the latest case studies, event updates, product news, and much more.
SQL indexes are one of the greatest resources when it comes to performance gain. However, the thing about indexes is that they degrade over time. But it got me into thinking, optimization is a complex subject. There must be a better solution to maintain those SQL indexes. The answer is yes. This requires having an optimal maintenance strategy to ensure that all our indexes are performing well.
Otherwise, it would be kind of going backward reading this because this write-up is targeting someone who has basic knowledge, has created indexes before and it has been a while, they started to degrade, what do you do to get them back in the tip-top shape. The big deal about index maintenance and performance is index fragmentation. Fragmentation is what plague our indexes and of course hard disks in the OS world not so much since the solid-state drives come out.
As the data comes in and out, gets modified, etc. Fragmentation is basically storing data non-contiguously on disk. On the contrary, when the data is out of order then the OS must jump all over the place.
But for defragmentation, I think a better example would be a bookshelf. To sum things up, the internal fragmentation is the data itself within the pages usually caused by page splitting and the external fragmentation is new shelves placed out of order. It all comes down to just contiguousness and sequential. We want all our data to be contiguous. Contiguous data across contiguous pages is our goal also with indexes. The figure below shows when everything is in order.
We have two sequential pages, rows are also sequential across both pages. In other words, zero percentage fragmentation:.
On the other side, we could have something like figure below. As it can be noticed, we have pages out of order but also rows out of order. The SQL Server would look for an empty spot and the updated pages would look like this:. This is what a fragmented table looks like. Furthermore, if we insert another record, SQL Server would have to insert another page AKA page splitting, move half rows from the previous page, insert new ones, etc. By doing this, this table can become very fragmented rather fast.
Bear in mind that page splitting occurs not only on inserts but also on updates. This happens when want to update a record and it becomes too big to fit the page. The same process as within inserts happens all over again. Bear in mind that big performance problems come from poor design, lack of SQL indexing, and fragmented indexes. We can fix this in two ways.
0コメント