SQL SEVER database re-indexing method, sqlsever re-Indexing

SQL SEVER database re-indexing method, sqlsever re-Indexing

I. query ideas

1. To determine the slow query speed of the database, you can use the following statement to list the average time, total time, CPU time, and other information of the query statement.

SELECT creation_time N 'Statement compilation time', last_execution_time N 'last execution time', total_physical_reads N 'Total number of physical reads ', total_logical_reads/execution_count n' Number of logical reads per time ', total_logical_reads N 'Total number of logical reads ', total_logical_writes N 'Total number of logical writes', execution_count N 'execution times', total_worker_time/1000 n' total CPU time ms ', total_elapsed_time/1000 n'total time ms ', (total_elapsed_time/execution_count)/1000 n'average time ms', SUBSTRING (st. text, (qs. statement_start_offset/2) + 1, (CASE statement_end_offsetWHEN-1 then datalength (st. text) ELSE qs. statement_end_offsetEND-qs. statement_start_offset)/2) + 1) n' execution statement 'from sys. dm_exec_query_stats AS qsCROSS APPLY sys. dm_exec_ SQL _text (qs. SQL _handle) stwhere SUBSTRING (st. text, (qs. statement_start_offset/2) + 1, (CASE statement_end_offsetWHEN-1 then datalength (st. text) ELSE qs. statement_end_offsetEND-qs. statement_start_offset)/2) + 1) not like '% fetch %' order by total_elapsed_time/execution_count DESC;

2. List the data volume of each table in the database. O & M personnel must be familiar with the business and know which tables have the most queries. You can view the "disk usage of the preceding table ":

3. To view table fragments, run the following command:

DBCC SHOWCONTIG

We can see that the scanning density of this table is only 33.52% (the best state is 100%, and each table page is filled with data), which is far lower than the best count, that is, the utilization of this table is very low, the results can be obtained after scanning one page. Now, you may need to scan three pages to increase the query time. However, there are many logical fragments and partition fragments (which are generally considered to be optimized when the number of segments exceeds 30% ), that is to say, on the same page, there is very little data and a lot of fragmentation, occupying too much database resources.
4. based on your understanding of the business, you can find the most queried table, compare its data, query time, and fragmentation level to determine whether the table needs to be fragmented and re-indexed, to improve database performance.
The statement for re-indexing is:

Use [database name]
Alter index all on [Table name] REBUILD;

After reconstruction, the same table NWME_Company_Index is queried again. The result of the query is as follows:

We can see that the density has changed to 96.9%, and the logic fragmentation is almost useless.

5. now you can check whether the query performance has been optimized after the fragments are sorted. Run the command listed at the first point again to check whether the query performance has been optimized, the average time used by most query statements is reduced by nearly half:

Now you can go to the front-end to experience the optimized results.