Methods for rebuilding indexes of SQL Sever database _mssql

I. Query ideas

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

 select creation_time N ' statement compile time ', last_execution_time n ' Last Execution time ', total_physical_reads N ' total number of physical reads ', Total_logical_reads/execution_count n ' per logical read times ', Total_logical_reads n ' logical read total times ', total_logical_writes N ' logical write total times ', execution_count n ' execution times ', total_worker_time/1000 n ' Total CPU time used by Ms ', total_elapsed_time/1000 N ' Total time spent MS ', (Tota L_elapsed_time/execution_count)/1000 N ' Average time ms ', SUBSTRING (St.text, (QS.STATEMENT_START_OFFSET/2) + 1, (case Statement_end_offset WHEN-1 THEN datalength (st.text) ELSE Qs.statement_end_offsetend-qs.statement_start_offset)/2) +  1 N ' execute statement ' from Sys.dm_exec_query_stats as Qs CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) St where SUBSTRING (St.text, (QS.STATEMENT_START_OFFSET/2) + 1 (case statement_end_offset WHEN-1 THEN datalength (st.text) ELSE qs.statement_end_of 
Fsetend-qs.statement_start_offset)/2) + 1) Not like '%fetch% ' order by Total_elapsed_time/execution_count DESC; 

2. List the amount of data for each table in the database, and require the operations staff to know enough about the business to see what tables are the most queried, and to view "disk usage on the previous table":

3. To view the situation of table fragmentation, you can use the command

DBCC Showcontig

You can see that the table scan density is only 33.52% (the best state is 100%, each table page is full of data), far below the best count, which means that the table's utilization is very low, originally scanned a page can produce results, now may need to scan three pages, increased query time , while logical fragmentation and fragmentation are many (generally thought to be optimized for more than 30%), which means that the same page has very little data and a lot of fragmentation, consuming too much database resources.
4. Based on your understanding of the business, find the most query table, compare his data, query time, and the degree of fragmentation can determine whether the table needs to defragment, rebuild the index to improve database performance.
The statement that rebuilds the index is:

use[database name]
ALTER INDEX all on [table name] REBUILD;

After rebuilding, the same table nwme_company_index, the result of querying table fragmentation again is as follows:

You can see that the density has changed to 96.9%, and the logical fragments are almost gone.

5. Now you can look at the defragmentation, whether the query performance is really optimized, run the command listed in the 1th again to see that most of the query statements used by the average time decreased by nearly half:

You can now go to the foreground to actually experience the optimized effect.