Tips for optimizing SQL Server indexing

server| Tips | index | Optimizing SQL Server has several tools that allow you to detect, tune, and optimize SQL Server performance. In this article, I'll show you how to use SQL Server's tools to optimize the usage of database indexes, and this article also involves general knowledge about indexes.

Common Sense About indexing

The biggest factor that affects database performance is the index. Because of the complexity of the problem, I can only talk about it briefly, but there are several good books available for you to see here. I only discuss two types of SQL Server indexes here, the clustered index and the nonclustered index. When looking at what type of index to establish, you should consider the data type and the column that holds the data. Also, you must consider the type of query the database might use and the most frequently used query type.

Types of indexes
If column holds highly correlated data and is often sequentially accessed, it is best to use the clustered index, because if you use the clustered index, SQL Server will rearrange the data columns physically in ascending (default) or descending order, This allows you to quickly find the data being queried. Similarly, the clustered index is best used for these column cases where the search control is within a certain range. This is because there is only one clustered index on each table because of the physical rearrangement of the data.

Conversely, if the columns contains poor data correlation, you can use the nonculstered index. You can use up to 249 nonclustered indexes in a table-although I can't imagine how many indexes will be used in practical applications.

When the table uses the primary key (primary keys), by default SQL Server automatically establishes a unique cluster index for the column containing that keyword. It is clear that establishing a unique index to these column (s) means uniqueness of the primary keyword. When establishing an external keyword (foreign key) relationship, if you intend to use it frequently, it is a good idea to establish a nonclustered index on the external keyword cloumn. If the table has a clustered index, it maintains the relationship between the data pages with a linked list. Conversely, if the table does not have a clustered index, SQL Server saves the data page in a stack.

Data pages
When the index is established, SQL Server establishes a data page (DataPage), which is the pointer to speed up 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 update of the database consumes the available free space, which causes the page to be split. The effect of a page split is to degrade the performance of the index, so queries that use the index can result in fragmentation of the data store. When an index is established, the fill factor for the index is set, so the fill factor cannot be dynamically maintained.

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 and be used sparingly on important occasions). DBCC INDEXDEFRAG and DBCC DBREINDEX are two commands for clearing clustered and nonculstered index fragmentation. Indexdefrag is an online operation (that is, it does not block other table actions, such as queries), while Dbreindex rebuilds the index physically. In most cases, rebuilding the index eliminates fragmentation better, but the advantage is in return for the cost of blocking other actions that are currently on the table where the index is located. When a large fragmentation index occurs, Indexdefrag takes a long time because the command is run based on a small interaction block (transactional blocks).

Fill factor
When you perform any of these measures, the database engine can more efficiently return indexed data. The topic of fill factor (FILLFACTOR) has gone beyond the scope of this article, but I'd like to remind you to pay attention to the tables that you're going to use to create indexes with fill factors.

When executing a query, SQL Server dynamically chooses which index to use. To do this, SQL Server determines which index to use based on the amount of statistics that are distributed on that keyword on each index. It is worth noting that, after routine database activities, such as inserting, deleting, and updating tables, the statistics used by SQL Server may have expired and need to be updated. You can view the status of statistics by performing DBCC SHOWCONTIG. When you think the statistics are "expired," You can execute the UPDATE STATISTICS command on the table so that SQL Server refreshes the information about the index.

Establish a Database maintenance plan
SQL Server provides a tool for simplifying and automatically maintaining databases. This tool, called the Database Maintenance Plan Wizard Wizard, DMPW, also includes optimization of indexes. If you run this wizard, you'll see statistics about the indexes in the database, which work as a log and are updated regularly, easing the effort to manually rebuild the index. If you do not want to automatically refresh the index statistics on a regular basis, you can also choose to rearrange the data and data pages in DMPW, which stops the old indexes and rebuilds the indexes by a specific fill factor.