Tips for optimizing SQL Server indexing

There are several tools in SQL Server 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).