A database index lets SQL Server quickly find specific information in a table or indexed view. It contains keys built from one or more columns in the table or view, and pointers that map to the storage location of the specified data. You can significantly improve the performance of database queries and applications by creating well designed indexes to support your queries. Indexes can reduce the amount of data that must be read to return the query result set. Indexes can also enforce uniqueness on the rows in a table, ensuring the data integrity of the table data.
An index contains keys built from one or more columns in the table or view. These keys are stored in a structure known as a b-tree that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. A table or view can contain clustered or nonclustered indexes.
Clustered Indexes
Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index
definition. You can define only one clustered index per table because the data rows themselves can be sorted and stored in only one order.
Microsoft Official Training Materials for Microsoft Dynamics ®
Chapter 8: Optimization and Troubleshooting
Data rows in a table can be stored in order only when the table contains a clustered index. A table that has a clustered index is called a clustered
table. If you do not define a clustered index for a table, SQL Server
stores its data rows in an unordered structure called a heap.
Nonclustered Indexes
Nonclustered indexes have a structure separate from the data rows of the table itself. A nonclustered index contains nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the actual data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
Constraints
A constraint is a rule that the database server enforces automatically. Indexes are automatically created when you define PRIMARY KEY and UNIQUE constraints on table columns. For example, when you create a table and identify a particular column as the primary key, the SQL Server 2005 database engine automatically creates a PRIMARY KEY constraint and index on that column.
Query Optimizer
Well-designed indexes can reduce disk I/O operations and consume fewer system resources, thereby improving query performance. Indexes can be helpful for a variety of queries that contain SELECT, UPDATE, or DELETE statements. For example, suppose that you issue the following query against the AdventureWorks database:
SELECT Title, HireDate FROM HumanResources.Employee WHERE EmployeeID = 222
Defragmenting Indexes
Data stored inside the database files can become fragmented at the index level. This prevents SQL Server 2005 from using indexes optimally. Index fragmentation can be either internal or external. Pages that have a lot of free space are internally fragmented.
This happens when you remove rows by DELETE statements or when SQL Server splits and only partly fills pages. Empty space on pages means there are less rows per page, which in turn means more page reads.
8-6
External fragmentation occurs when pages are not contiguous. Inserting new rows and updating existing rows can result in page splits. When a new page is created from a page split, SQL Server allocates it in the same 8-page extent as the original page if there is room for it. If the extent is already full, SQL Server allocates a new extent to the index or table and places the new page there. Thus, the new page is not
contiguous to the original page.
You can use DBCC SHOWCONTIG to view the extent of index fragmentation. If you specify the TABLERESULTS option, you see extra output columns that describe index statistics. The following metrics assist you in auditing index defragmentation:
Avg. Page Density (full) - Shows how filled the pages are.
Scan Density - Shows the ratio between the Best Count of extents that should be necessary to read when scanning all the pages of the index and the Actual Count of extents that were read. This percentage should be as close to 100 as possible. Values less than 75 percent indicate serious external fragmentation.
Logical Scan Fragmentation - Shows the ratio of pages that are out of logical order. The percentage should be as close to 0 as possible. Values over 10 percent indicates external fragmentation.
You can use the following methods to defragment an index: Drop and re-create the index
CREATE INDEX … WITH DROP_EXISTING DBCC DBREINDEX
DBCC INDEXDEFRAG
You perform the first three options offline, which means that users cannot execute queries against the database while defragmentation is occurring. DBCC INDEXDEFRAG is an online operation, but SQL Server cannot defragment any indexes that are currently in use.
Rebuilding Indexes
You can use Object Explorer in SSMS to rebuild an index or all indexes on a table. Rebuilding an index drops and re-creates the index. This option removes fragmentation, reclaims disk space, and reorders the index rows in contiguous pages.
Microsoft Official Training Materials for Microsoft Dynamics ®
Chapter 8: Optimization and Troubleshooting
In Object Explorer, you connect to an instance of the SQL Server 2005 database engine, expand that instance, expand Databases and the relevant application database, navigate to the table that contains the index and then to the index itself. You then right-click the index, choose Rebuild, and click OK. To rebuild all the indexes in a table, you navigate to the table, right-click indexes, choose Rebuild All, and then click OK.
Creating Missing Indexes
When the query optimizer generates a query plan, it analyzes what the best indexes are for a particular filter condition. If the best indexes do not exist, the query optimizer generates a suboptimal query plan but stores information about the missing indexes. The missing indexes feature enables you to access information about these indexes so that you can decide whether you should implement them. You can then use missing index information to use CREATE INDEX statements that restore the missing indexes.
Dropping an Inappropriate Index
When you no longer need an index, you can remove it from a database by using the DROP INDEX Transact-SQL statement or by connecting to the database with SSMS, navigating to the index in Object Explorer, right-clicking it, choosing Delete, and clicking OK. This process
reclaims the disk space the index currently uses. Deleting an index is the same as dropping it.
Dropping a clustered index can take time because in addition to dropping the clustered index, SQL Server must rebuild all nonclustered indexes on the table. To avoid rebuilding indexes, drop the nonclustered indexes first and the clustered index last.
Profiler
Use SQL Server Profiler to capture a trace when a script containing the queries you want to analyze is running, or when you execute the queries directly by typing them into the Query Editor. Alternatively, you can capture a trace during a period of peak activity and filter the results to display the information you require. Profiler enables you to select the events you want to capture and specify the data columns from those events. You can use filters to obtain only the data you require. The events you should capture are the following:
• Stored Procedures—RPC:Completed
8-8
The first of these events captures stored procedures, and the second one captures all other Transact-SQL queries. Typically, the data columns you would specify are the following:
• Duration • Event Class • DatabaseID • TextData • CPU • Writes • Reads • StartTime • EndTime • ApplicationName • NTUserName • LoginName • SPID
StartTime, EndTime, ApplicationName, NTUserName, and LoginName are optional. You require DatabaseID only if you are accessing more than one database. Information in the Duration and TextData columns is especially useful in this analysis. Typically, you filter the trace to exclude system events and events with a duration of less than, for example, 5000 (5 seconds).