There are several tools that allow you to detect, adjust, and optimize SQL Server performance. In this article, I will explain how to use SQL Server tools to optimize the use of database indexes, which also relates to general knowledge about indexes.
Common sense about indexing
The biggest factor affecting the performance of the database is the index. Due to the complexity of the problem, I can only talk about this problem, but about this problem, there are a good book for you to see it. I only discuss two SQL Server indexes here, that is, Clustered indexes and nonclustered indexes. When examining what type of index, you should consider the data type and save these data. Similarly, you must also consider the type of query that the database may be used and the most frequent query type used.
Index type
If Column holds a highly related data and is often accessed sequentially, it is best to use the Clustered index because if the CLUSTERED index is used, SQL Server will physically press ascending (default) or descend the data column, so You can quickly find the data being queried. Similarly, in the case of search control within a certain range, these column is also preferred to use the Clustered index. This is because only one clustered index is only physically rearranged.
In contrast to the above situation, if the data related to columns contains poor correlation, you can use the nonculstered index. You can use up to 249 nonclustered indexes in a table - although I think so many indexes will be used in actual applications.
When the table uses the primary keys, the SQL Server automatically establishes a unique Cluster index for Column (s) that contains the keyword by default. Obviously, these Column (s) to establish unique indexes means uniqueness of the primary key. When establishing a Foreign Key relationship, if you intend to use it frequently, establish a nonclustered index on the outside key ClouMN does not lose a good method. If the table has a clustered index, it uses a linked list to maintain the relationship between the data page. Conversely, if the table does not have a Clustered index, SQL Server saves the data page in a stack.
Data page
When the index is established, SQL Server establishes a data page (DataPage), and the data page is a pointer to accelerate the search. When the index is established, its corresponding fill factor is set. The purpose of setting the fill factor is to indicate the percentage of the data page in the index. Over time, the database update will consume existing idle space, which will cause the page to be split. The consequences of page splits are reduced the performance of the index, and thus the query using the index will cause the data storage to be broken. When an index is established, the fill factor of the index is set, so the fill factor cannot be dynamically maintained.
In order to update the fill factor in the data page, we can stop the old index and rebuild the index, and reset the fill factor (note: This will affect the current database operation, please use caution in important occasions). DBCC INDEXDEFRAG and DBCC DBREINDEX are two commands that clear the clustered and nonculstered index fragments. Indexdefrag is an online operation (that is, it does not block other table action, such as query), while DBREINDEX is physically reconstructing an index. In most cases, reconstructing indexes can better eliminate fragments, but this advantage is to replace other actions in the table in which it is currently occurring in the table. When a large fragment index appears, Indexdefrag will spend a longer time because the operation of the command is based on a small interactional block. Fill factor
When you perform any of the above measures, the database engine can more efficiently return data to the index. About the FillFactor topic has exceeded the category of this article, but I still remind you to pay attention to the form that intends to use the fill factor to establish an index.
When the query is executed, SQL Server dynamically selects which index is used. To this end, SQL Server decides which index that distributes on this keyword based on each index. It is worth noting that these statistics for SQL Server may have "expire" after daily database activities (such as insertion, deletion, and update forms). You can view the status of statistics by performing DBCC ShowContig. When you think the statistic has been "expired", you can perform the Update statistics command of the table so that SQL Server refreshes information about the index.
Establish a database maintenance plan
SQL Server provides a tool that simplifies and automatically maintains databases. This tool called Database Maintenance Plan Wizard, DMPW also includes an optimization of an index. If you run this wizard, you will see the statistics about the index in the database, these statistics are used as log work and timed, which reduces the workload of the manual reconstruction index. If you don't want to automatically refresh index statistics automatically, you can also choose to reorganize data and data pages in DMPW, which will stop the old index and rebuild indexes in a specific fill factor.
