Shulou(Shulou.com)05/31 Report--

This article introduces how to improve the speed of SQLServe, the content is very detailed, interested friends can use for reference, I hope it can be helpful to you.

How to improve the speed of SQLServer

Check the index fragment DBCCSHOWCONTIG (table) and get the following results:

DBCCSHOWCONTIG is scanning the'A 'table. Table:'A' (884198200); index ID:1, database ID:13 has performed a TABLE-level scan. -number of pages scanned.: 3127-number of extents scanned.: 403-number of extents switches. ...: 1615-average number of pages per expansion area.: 7.8-scan density [best value: actual value].: 24.20% [391R 1616]-logical scan fragments. .: 68.02%-extended disk scan fragment..: 38.46%-average number of bytes available per page.: 2073.2-average page density (complete). .: 74.39%DBCC execution completed.

As we can see from the above, both the logical scan fragment and the extended disk scan fragment are very large, so it is really necessary to deal with the index fragment.

Generally, there are two ways to solve the problem, one is to use DBCCINDEXDEFRAG to defragment the index, and the other is to use DBCCDBREINDEX to rebuild the index. Each has its own advantages and disadvantages. The exact words to call Microsoft are as follows:

How to improve the speed of SQLServer

The DBCCINDEXDEFRAG command is an online operation, so the index is available only if the command is running. And the operation can be interrupted without losing the completed work. The disadvantage of this approach is that it is not as effective as the drop / recreation operation of the clustered index in reorganizing the data.

Recreating the clustered index reorganizes the data, resulting in a full data page. The degree of filling can be configured using the FILLFACTOR option. The disadvantage of this approach is that the index is offline during the delete / recreation cycle and the operation is atomic. If index creation is interrupted, the index will not be recreated.

In other words, if you want to get good results, you still have to rebuild the index, so you decide to rebuild the index.

DBCCDBREINDEX (table, index name, fill factor)

The first parameter can be either the table name or the table ID.

The second parameter, if'', affects all indexes of the table.

The third parameter, the fill factor, is the degree of data fill of the index page. If it is 100, it means that every index page is full, and select is the most efficient at this time, but when you want to insert the index later, you have to move all the pages behind it, which is very inefficient. If 0, the previous fill factor value is used.

DBCCDBREINDEX (A ~ (th))

Retest the speed and find that the speed is already very fast.

On how to improve the speed of SQLServe to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.