SQL Server INDEX OPTIMIZATION skills

Common knowledge about Indexes
Index is the biggest factor affecting database performance. Due to the complexity of the problem, I can only talk about it briefly. However, there are several good books for you to refer. Here I will only discuss two types of SQL Server indexes: clustered index and nonclustered index. When examining the types of indexes, you should consider the data type and the column that stores the data. Similarly, you must consider the types of queries that the database may use and the most frequently used types of queries.
Index type

If column stores highly relevant data and is frequently accessed in sequence, it is best to use the clustered index. This is because if the clustered index is used, the SQL Server physically goes in ascending order (default) or sort the data columns in descending order to quickly find the queried data. Similarly, when the search is controlled within a certain range, it is best to use clustered indexes for these columns. This is because there is only one clustered index on each table because of the physical data rearrangement.
In contrast to the above, if columns contains poor data relevance, you can use the nonculstered index. You can use up to 249 nonclustered indexes in a table-although I cannot imagine that so many indexes will be used in practical applications.
When a table uses the primary key (primary keys), SQL Server automatically creates a unique Cluster Index for the column (s) containing the key by default. Obviously, creating a unique index for these columns (s) means that the primary key is unique. When establishing a foreign key relationship, if you plan to use it frequently, it is a good method to create a nonclustered index on the external key cloumn. If a table has a clustered index, it uses a linked list to maintain the relationship between data pages. Conversely, if the table does not have a clustered index, SQL Server saves the data page in a stack.
Data Page

When an index is created, sqlserver creates a data page (datapage), which is a pointer to accelerate search. When an index is created, the corresponding fill factor is also set. The fill factor is set to indicate the percentage of data pages in the index. Over time, database updates will consume existing free space, which will cause the page to be split. The consequence of page splitting is that the index performance is reduced. Therefore, queries using this index will result in fragmented data storage. When an index is created, the fill factor of the index is set. Therefore, the fill factor cannot be dynamically maintained.
To update the fill factor on the data page, we can stop the old index, re-create the index, and re-set the fill factor (note: this will affect the operation of the current database, use it with caution in important cases ). DBCC indexdefrag and DBCC dbreindex are two commands used to clear tered and nonculstered index fragments. Indexdefrag is an online operation (that is, it does not block other table actions, such as queries), while dbreindex physically reconstructs the index. In most cases, re-indexing can better eliminate fragmentation, but this advantage is to block other actions on the table where the index is currently located at the cost. When a large fragmented index occurs, indexdefrag takes a long time because the command is run based on a small interactive block (transactional block ).
Fill Factor

When you execute any of the above measures, the database engine can return the indexed data more effectively. The fillfactor topic is beyond the scope of this article, but I still remind you to pay attention to the tables that intend to use the fill factor to create an index.
When executing a query, SQL Server dynamically selects which index to use. Therefore, SQL Server determines which index to use based on the statistics distributed on this keyword on each index. It is worth noting that, after daily database activities (such as inserting, deleting, and updating tables), these statistics used by SQL Server may have expired and need to be updated. You can run DBCC showcontig to view the statistics status. When you think that the statistic has expired, you can execute the update statistics command of the table, so that SQL Server refreshes the information about the index.
Create a database maintenance plan

SQL Server provides a tool to simplify and automatically maintain databases. The database maintenance plan wizard (dmpw) tool also includes index optimization. If you run this wizard, you will see the index statistics in the database. These statistics are used as logs and updated regularly, which reduces the workload caused by manual Index reconstruction. If you do not want to automatically refresh the index statistics on a regular basis, you can also choose to re-organize the data and data pages in dmpw. This will stop the old indexes and re-create indexes based on specific fill factors.