SQL Server Maintenance Database

1. Emptying the cache
Function Description: When viewing the execution plan, you should clear the cache first. Otherwise, it's possible that the schedule or query time you see is not necessarily real, because SQL uses the buffer data

DBCC dropcleanbuffers DBCC Freeproccache

2. Rebuilding indexes, organizing index fragmentation
Function Description: When you find the scan density line, the ratio of the best count to the actual count is already severely misaligned, the logical scan fragment accounts for a very large percentage, and the average number of bytes available per page is very large, it means that your index needs to be re-organized.
Index establishment of the analysis table:

DBCC showcontig ('TableName')

The results of the implementation are as follows:

To perform the Rebuild Index command:

DBCC Dbreindex ('TableName')

Execute the Parse Table Index command again:

DBCC showcontig ('TableName')  

The results of the implementation are as follows:

3. UPDATE STATISTICS

Analysis Note: When an index is created, the optimizer creates statistics to the table or view on which the index column is located, and in addition, if the auto_create_statistics option is set on , the optimizer creates a single-column statistic. In time it does not appear on the desired column of the query. If you feel that some query performance is problematic, check all predicates, and if these columns are missing statistics, you can add them manually, and sometimesDTA (Database Tuning Advisor) also advises you to create statistics. in general, before query compilation, if synchronization update statistics is turned on,SQL Server will cause update statistics to occur if statistics are found to be out of date, then your query will use real-time statistics. This action blocks the query, knows that the update is complete, but does not keep the queries, and it updates the statistics so that the next time you run the query, you can use the more recent statistics. by default, only members of the three roles that are creators of the sysadmin/db_owner/object have permission to create and update statistics.

Update Statistics GYPLDFL1

4. Rebuilding index fragmentation for the entire library

Analysis: Because of the excessive insert, modify and delete operations on the table, the index page is divided into multiple pieces to form an index fragment, if the index fragmentation is serious, the scan index time will be longer, even cause the index is not available, so the data retrieval operation slowed down. Check Index Fragmentation:

SELECT object_name(dt.object_id), Si.name, dt.avg_fragmentation_in_percent, Dt.avg_page_space_used_in_percent from      (SELECT object_id, index_id, Avg_fragmentation_in_percent, avg_page_space_used_in_percent  fromSys.dm_db_index_physical_stats (db_id(),NULL,NULL,NULL,'detailed')        WHEREindex_id<> 0       )  asDTINNER JOINsys.indexes si onSi.object_id =Dt.object_id   andsi.index_id=dt.index_id

Execution Result:

(1) When will the index be reorganized?

Check the Externalfragmentation section
When the value of avg_fragmentation_in_percent is between 10 and 15

Check the Internalfragmentation section
When the value of avg_page_space_used_in_percent is between 60 and 75

(2) When do I rebuild the index?
Check the Externalfragmentation section
When the value of avg_fragmentation_in_percent is greater than 15
Check the Internalfragmentation section
When the value of avg_page_space_used_in_percent is less than 60

Generate the appropriate SQL statement:

SELECT 'ALTER INDEX [' +Ix.name+ '] on [' +S.name+ ']. [' +T.name+ '] ' +           Case  whenPs.avg_fragmentation_in_percent>  the                Then 'REBUILD'         ELSE 'REORGANIZE'         END +             Case  whenPc.partition_count> 1                Then 'PARTITION =' + CAST(Ps.partition_number as nvarchar(MAX))         ELSE "'        END, Avg_fragmentation_in_percent fromSys.indexes asIXINNER JOINSys.tables T onT.object_id =Ix.object_id       INNER JOINSys.schemas s ont.schema_id=s.schema_idINNER JOIN(SELECT object_id, Index_id,avg_fragmentation_in_percent, Partition_number fromSys.dm_db_index_physical_stats (db_id(),NULL,NULL,NULL,NULL)) PS onT.object_id =Ps.object_id   andix.index_id=ps.index_idINNER JOIN(SELECT object_id, index_id,COUNT(DISTINCTPartition_number) asPartition_count fromsys.partitionsGROUP  by object_id, index_id) PC onT.object_id =Pc.object_id   andix.index_id=pc.index_idWHEREPs.avg_fragmentation_in_percent> Ten   andIx.name is  not NULL  

Execution Result:

Execute the generated SQL statement:

ALTER INDEX [PK__GD_MOVEB__1489BC61D65FF2AA]  on [dbo].[Gd_movebarcode_detail]REBUILDGoALTER INDEX [Pk_branchstylealldata]  on [dbo].[Branchstylealldata]REORGANIZEGoALTER INDEX [pk_pubbranchlocation_1]  on [dbo].[pubbranchlocation]REBUILDGo

5. Rebuilding the entire library's statistics

Exec sp_updatestats;

6. View SQL statement execution time, CPU usage

SET STATISTICSIo onSET STATISTICSTime onGo---The SQL statement you want to testSelect *  fromU_tagwhereQty=(Select Max(qty) fromu_bag)GoSET STATISTICSProfileOFFSET STATISTICSIoOFFSET STATISTICSTimeOFF

SQL Server Maintenance Database