Promote SQL Server speed collation index fragmentation _mssql

By experience, this is an index fragmentation problem. Check the index fragment DBCC SHOWCONTIG (table) and get the following results:

DBCC Showcontig is scanning the ' A ' table ...
Table: ' A ' (884198200); index id:1, Database id:13
A TABLE-level scan has been performed.
-Scan number of pages ... ....: 3127..................
-The number of extents scanned ... ...: 403. .................
-Number of extents switches ....: 1615..............., ...
-The average number of pages on each expansion area ...: 7.8........
-Scan density [best value: Actual value] ... ...: 24.20%[391:1616.......
-Logical scan Fragmentation ... ...: 68.02%. ...................
-Extents Scan fragmentation ....: 38.46%................
-The average number of bytes available on each page ..., ...: 2073.2........
-Average page density (full) ....: 74.39%........, ...., ...
DBCC execution completed. If DBCC prints an error message, contact your system administrator.


From the top we can see that the logical scan fragmentation and the extents scan fragments are very large, you really need to handle the index fragmentation.

There are generally two ways to solve this, one is to defragment the index with DBCC INDEXDEFRAG, and the other is to rebuild the index with DBCC DBREINDEX. Each has its advantages and disadvantages. The quote from Microsoft is as follows:
The DBCC indexdefrag command is an online operation, so the index is available only if the command is running. And you can break the operation without losing the completed work. The disadvantage of this approach is that there is no valid drop/re-create operation of the clustered index in the organization of the data.

Recreating the clustered index will rearrange the data so that the data page fills up. The level of fullness can be configured using the FILLFACTOR option. The disadvantage of this approach is that the index is offline during the drop/recreate cycle and the operation is atomic. If you break the index creation, the index is not re-created.

In other words, to achieve good results, you have to use the Rebuild index, so you decide to rebuild the index.
DBCC dbreindex (table, index name, fill factor)
The first argument can be either a table name or a table ID.
The second argument, if it is ', represents all indexes that affect the table.
The third parameter, the fill factor, is the degree of data padding for the index page. If 100, which means that each index page is full, the select is the most efficient, but in the future when you want to insert the index, you have to move all the pages behind, very inefficient. If 0, the previous fill factor value is used.

DBCC Dbreindex (A, ', 100)
Re-test query speed, fast.