Shulou(Shulou.com)06/01 Report--

This article to share with you is about the optimization of SQL Server index skills is what kind of, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some gains, not much to say, follow Xiaobian to see it.

The biggest factor affecting database performance is the index. Because of the complexity of the problem, I can only speak briefly on it, but there are several good books available on the subject. I'll only discuss two SQL Server indexes here, clustered and nonclustered. When considering what type of index to build, you should consider the data types and columns that hold that data. Similarly, you must consider the types of queries your database is likely to use and the most frequently used query types.

Type of index

If columns hold highly correlated data and are often accessed sequentially, it is best to use clustered indexes because SQL Server physically reorders columns in ascending (default) or descending order so that the queried data can be found quickly. Similarly, it is best to use clustered indexes for these columns when the search control is within a certain range. This is because there is only one clustered index on each table due to physically rearranging the data.

In contrast to the above, if columns contain data that is less relevant, you can use a nonculstered index. You can use up to 249 nonclustered indexes in a table--although I can't imagine a practical application using that many indexes.

When tables use primary keys, SQL Server automatically creates a unique cluster index for columns (s) containing that key by default. Obviously, indexing these columns (s) uniquely implies uniqueness of the primary key. When establishing a foreign key relationship, it is a good idea to establish a nonclustered index on the foreign key cloumn if you intend to use it frequently. If the table has clustered indexes, it maintains relationships between data pages in a linked list. Conversely, if the table has no clustered index, SQL Server saves the data pages in a stack.

data page

When an index is created, SQL Server creates datapages, which are pointers to speed up searches. When an index is created, its corresponding fill factor is set. The fill factor is set to indicate the percentage of data pages in the index. Over time, updates to the database consume existing free space, which causes pages to be split. The consequence of page splitting is that it degrades the performance of the index, so queries using that index result in fragmentation of the data store. When an index is created, the fill factor for that index is set, so 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 operation of the current database, please use caution in important occasions). DBCC INDEXDEFRAG and DBCC DBREINDEX are two commands that clean up clustered and non-clustered index fragments. INDEXDEFRAG is an online operation (that is, it does not block other table actions such as queries), while DBREINDEX physically rebuilds the index. In most cases, rebuilding an index will do a better job of defragmentation, but this advantage comes at the expense of blocking other actions currently taking place on the table where the index is located. INDEXDEFRAG takes a long time when large fragment indexes are present because it runs on small transactional blocks.

fill factor

When you perform any of these steps, the database engine can return indexed data more efficiently. The topic of fill factors is beyond the scope of this article, but I would caution you about tables that are intended to be indexed using fill factors.

SQL Server dynamically selects which index to use when executing a query. To do this, SQL Server decides which index to use based on the statistics distributed over that keyword on each index. It is important to note that these statistics used by SQL Server may be "out of date" and need to be updated through daily database activities such as inserting, deleting, and updating tables. You can view the status of statistics by executing DBCC SHOWCONTIG. When you think statistics are "out of date," you can execute UPDATE STATISTICS for the table, and SQL Server refreshes the information about the index.

Establish database maintenance plan

SQL Server provides a tool to simplify and automate database maintenance. This tool, called the Database Maintenance Plan Wizard (DMPW), also includes index optimization. If you run this wizard, you'll see statistics about indexes in the database that work as logs and are updated periodically, thus reducing the effort of rebuilding indexes manually. If you don't want to automatically refresh index statistics periodically, you can also choose Reorganize Data and Data Pages in DMPW, which will stop old indexes and rebuild them at a specific fill factor.

The above is what kind of skills are used to optimize SQL Server indexes. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.