Methods of rebuilding indexes of SQL Sever database

I. Query ideas

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

?

1234567891011121314151617181920212223

SELECT creation_time N‘语句编译时间‘,last_execution_time N‘上次执行时间‘,total_physical_reads N‘物理读取总次数‘,total_logical_reads/execution_count N‘每次逻辑读次数‘,total_logical_reads N‘逻辑读取总次数‘,total_logical_writes N‘逻辑写入总次数‘, execution_count N‘执行次数‘, total_worker_time/1000 N‘所用的CPU总时间ms‘, total_elapsed_time/1000 N‘总花费时间ms‘, (total_elapsed_time / execution_count)/1000 N‘平均时间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‘执行语句‘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 amount of data for each table in the database, and you need operations personnel to understand the business enough to know which tables are the most queries, and you can see "disk usage in the previous table":

3. To view the 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, that is, the utilization of this table is very low, originally scanned a page can produce results, now may need to scan three pages, increase the query time , and logical fragments and fragmentation are many (generally think more than 30% needs to be optimized), that is, the same page, the data is very small and fragmented, consuming too much database resources. 4. Based on your understanding of the business, find the table with the most queries, 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 the table fragmentation again is as follows:

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

5. Now you can look at the defragmentation, whether it is really optimized for query performance, run the 1th list of commands again to see that the average time spent by most query statements has dropped by nearly half:

Now you can go to the foreground to actually experience the optimized effect.

Methods of rebuilding indexes of SQL Sever database