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 the tools of SQL Server to optimize the usage of database indexes, and this article also covers general knowledge about indexes.
Common Sense About indexes
The biggest factor affecting 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 refer to in this regard. I'm here to discuss only two types of SQL Server indexes, the clustered index and the nonclustered index. When examining what type of index to build, you should consider the data type and the column that holds the data. Similarly, you must also consider the types of queries the database may use and the most frequently used query types.
Type of index
If column holds highly relevant data and is often accessed sequentially, it is best to use the clustered index, because if you use the clustered index, SQL Server will physically rearrange the data columns in ascending (default) or descending order. This makes it possible 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, because of the physical rearrangement of data, there is only one clustered index on each table.
In contrast to the above, if columns contains poor data correlation, you can use the nonculstered index. You can use up to 249 nonclustered indexes in a single table--although I can't imagine how many indexes can be used in real-world applications.
When the table uses the primary key (primary keys), by default SQL Server automatically establishes a unique cluster index on column (s) that contains the keyword. Obviously, establishing a unique index on these column (s) means that the primary key is unique. If you intend to use it frequently when creating a foreign keyword (foreign key) relationship, it is a good idea to build a nonclustered index on the external keyword cloumn. If the table has a clustered index, it uses a linked list to maintain the relationship between the data pages. 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 set up, SQL Server creates a data page (DataPage), which is a pointer to speed up the search. When the index is set up, its corresponding fill factor is set. The fill factor is set to indicate the percentage of the data page in the index. Over time, updates to the database consume existing free space, which causes the page to be split. The consequence of page splitting is that the performance of the index is reduced, so queries using that index can result in fragmentation of the data store. When an index is established, the fill factor for the index is set so that the fill factor cannot be maintained dynamically.
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 situations). DBCC INDEXDEFRAG and DBCC DBREINDEX are two commands to clear 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 the vast majority of cases, rebuilding an index can better eliminate fragmentation, but this advantage is in exchange for blocking the other actions that currently occur on the table where the index is located. When a large fragmented index occurs, Indexdefrag takes a long time because the command is run based on a small interaction block (transactional block).
http://www.bkjia.com/PHPjc/631135.html www.bkjia.com true http://www.bkjia.com/PHPjc/631135.html techarticle 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 tools to optimize database index usage, this article ...
