SQL Server维护数据库

浏览数：21 / 时间：2015年06月12日

1.清空缓存
功能说明:在查看执行计划的时候，应该先清除缓存。否则有可能你看到的计划或查询时间不一定是真实的，因为SQL会利用缓存区的数据

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

2.重建索引，整理索引碎片
功能说明: 当你发现扫描密度行，最佳计数和实际计数的比例已经严重失调，逻辑扫描碎片占了非常大的百分比，每页平均可用字节数非常大时，就说明你的索引需要重新整理一下了。
分析表的索引建立情况：

DBCC showcontig(‘TableName‘)

执行结果如下:

执行重建索引命令:

DBCC DBREINDEX(‘TableName‘‘)

再次执行分析表索引命令:

DBCC showcontig(‘TableName‘)

执行结果如下:

技术分享

3.更新统计信息

分析说明:当索引创建时，优化器会创建统计信息到索引列所在的表或者视图上，除此之外，如果对Auto_Create_Statistics选项设置了ON，优化器会创建一个单列统计信息，及时它没有出现在查询的所需列上。如果你觉得一些查询性能有问题，检查所有谓词，如果这些列缺失了统计信息，你可以手动增加，有时候，DTA（数据库优化顾问）也会建议你创建统计信息。一般情况下，在查询编译之前，如果开启了同步更新统计信息，SQLServer如果发现统计信息过时，会引发更新统计信息的操作，然后你的查询就会使用上实时的统计信息。而这个操作会阻塞查询，知道更新结束，但是不会保留这些查询，它会更新统计信息以便下次运行查询的时候可以使用上较新的统计信息。默认情况下，只有sysadmin/db_owner/对象的创建者这三种角色的成员才有权限创建和更新统计信息。

update statistics GYPLDFL1

4.重建整个库的索引碎片

分析说明:由于表上有过度地插入、修改和删除操作，索引页被分成多块就形成了索引碎片，如果索引碎片严重，那扫描索引的时间就会变长，甚至导致索引不可用，因此数据检索操作就慢下来了。检查索引碎片:

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   
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, ‘DETAILED‘)    
    WHERE index_id <> 0   
    ) AS dt 
    INNER JOIN sys.indexes si   
    ON si.object_id = dt.object_id  AND  si.index_id  = dt.index_id

执行结果:

技术分享

(1)什么时候该索引重组?

检查 Externalfragmentation 部分
当avg_fragmentation_in_percent 的值介于 10 到 15 之间

检查 Internalfragmentation 部分
当avg_page_space_used_in_percent 的值介于 60 到 75 之间

(2)什么时候重建索引?
检查 Externalfragmentation 部分
当avg_fragmentation_in_percent 的值大于 15
检查 Internalfragmentation 部分
当avg_page_space_used_in_percent 的值小于 60

生成相应的SQL语句:

SELECT ‘ALTER INDEX [‘ + ix.name + ‘] ON [‘ + s.name + ‘].[‘ + t.name + ‘] ‘ +   
       CASE WHEN ps.avg_fragmentation_in_percent > 15   
            THEN ‘REBUILD‘  
       ELSE ‘REORGANIZE‘  
       END +   
  
       CASE WHEN pc.partition_count > 1   
            THEN ‘ PARTITION = ‘ + CAST(ps.partition_number AS nvarchar(MAX))   
      ELSE ‘‘  
      END, avg_fragmentation_in_percent   
FROM sys.indexes AS ix   
    INNER JOIN sys.tables t  ON  t.object_id = ix.object_id   
    INNER JOIN sys.schemas s ON  t.schema_id = s.schema_id   
    INNER JOIN  (SELECT object_id,index_id,avg_fragmentation_in_percent,   
                        partition_number   
                 FROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)   
                 ) ps  ON t.object_id = ps.object_id  AND  ix.index_id = ps.index_id   
    INNER JOIN  (SELECT object_id,index_id,COUNT(DISTINCT partition_number) AS partition_count   
                 FROM  sys.partitions   
                 GROUP BY object_id,index_id 
                ) pc  ON t.object_id = pc.object_id  AND ix.index_id= pc.index_id      
WHERE ps.avg_fragmentation_in_percent > 10  AND ix.name IS NOT NULL

执行结果:

技术分享

执行生成的SQL语句:

ALTER INDEX [PK__gd_moveb__1489BC61D65FF2AA] ON [dbo].[gd_movebarcode_detail] REBUILD
go
ALTER INDEX [PK_branchstylealldata] ON [dbo].[branchstylealldata] REORGANIZE
go
ALTER INDEX [PK_PubBranchLocation_1] ON [dbo].[PubBranchLocation] REBUILD
go

5.重建整个库的统计信息

Exec sp_updatestats;

6.查看SQL语句执行时间,CPU占用情况

SET STATISTICS io ON
SET STATISTICS time ON
go
---你要测试的sql语句
select * from u_tag where qty =(
select max(qty) from u_bag)
go
SET STATISTICS profile OFF
SET STATISTICS io OFF
SET STATISTICS time OFF