SQL Server維護資料庫

標籤：

1.清空緩衝
功能說明:在查看執行計畫的時候，應該先清除緩衝。否則有可能你看到的計劃或查詢時間不一定是真實的，因為SQL會利用緩衝區的資料

DBCC DROPCLEANBUFFERSDBCC 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] REBUILDgoALTER INDEX [PK_branchstylealldata] ON [dbo].[branchstylealldata] REORGANIZEgoALTER INDEX [PK_PubBranchLocation_1] ON [dbo].[PubBranchLocation] REBUILDgo

5.重建整個庫的統計資訊

Exec sp_updatestats;

6.查看SQL語句執行時間,CPU佔用情況

SET STATISTICS io ONSET STATISTICS time ONgo---你要測試的sql語句select * from u_tag where qty =(select max(qty) from u_bag)goSET STATISTICS profile OFFSET STATISTICS io OFFSET STATISTICS time OFF

 

SQL Server維護資料庫