SQL索引碎片的產生,處理過程。

来源:https://www.cnblogs.com/adsoft/archive/2019/11/28/11950693.html

本文參考 https://www.cnblogs.com/CareySon/archive/2011/12/22/2297568.html https://www.jb51.net/softjc/126055.html https://docs.microsoft.com/zh-cn/sql/rel ...


本文參考

https://www.cnblogs.com/CareySon/archive/2011/12/22/2297568.html

https://www.jb51.net/softjc/126055.html

https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15

本文需要對“索引”和MSSQL中數據的“存儲方式”有一定瞭解。

軟體經常在使用一段時間過後會無緣無故卡頓,這是因為在資料庫(MSSQL)頻繁的插入和更新的操作過程中會產生分頁,在分頁的過程中產生碎片導致的。所以,對於碎片需要定時的處理。基本上所有的辦法都是基於對索引的重建和整理,只是方式不同。

  1. 刪除索引並重建
  2. 使用DROP_EXISTING語句重建索引
  3. 使用ALTER INDEX REBUILD語句重建索引
  4. 使用ALTER INDEX REORGANIZE

以上方式各有優缺點,下麵存儲過程主要使用3,4

先看一個整理碎片的存儲過程,然後採用作業的方式定時執行。

Create PROCEDURE [dbo].[proc_rebuild_index]
    @ret    INT OUTPUT
AS
SET NOCOUNT ON
BEGIN
    DECLARE @fldDefragFragment INT = 10;
    DECLARE @fldRebuildFragment INT = 30;
    DECLARE @fldMinPageCount INT = 1000;
    DECLARE @fldTable VARCHAR(256);
    DECLARE @fldIndex VARCHAR(256);
    DECLARE @fldPercent INT;
    DECLARE @Sql       VARCHAR(256);
    declare @DBID  int;
    BEGIN TRY
        SET @ret = -1;
        set @DBID = db_id();
        -- 獲取索引碎片狀況
        DECLARE curIndex CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
            SELECT 
                 TBL.NAME TABLE_NAME
                ,IDX.NAME INDEX_NAME
                ,AVGP.AVG_FRAGMENTATION_IN_PERCENT
            FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(@DBID, NULL,NULL, NULL, 'LIMITED') AS AVGP 
            INNER JOIN SYS.INDEXES AS IDX 
             ON AVGP.OBJECT_ID = IDX.OBJECT_ID 
            AND AVGP.INDEX_ID = IDX.INDEX_ID 
            INNER JOIN SYS.TABLES AS TBL 
             ON AVGP.OBJECT_ID = TBL.OBJECT_ID
            INNER JOIN SYS.DM_DB_PARTITION_STATS PS
             ON AVGP.OBJECT_ID = PS.OBJECT_ID
            AND AVGP.INDEX_ID = PS.INDEX_ID 
            WHERE
                AVGP.INDEX_ID >= 1 
            AND AVGP.AVG_FRAGMENTATION_IN_PERCENT >= @fldDefragFragment
            AND PS.RESERVED_PAGE_COUNT >= @fldMinPageCount;
        -- 打開游標
        OPEN curIndex;
        -- 獲取游標
        FETCH NEXT FROM curIndex
        INTO @fldTable,@fldIndex,@fldPercent;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                --碎片率大於30,重建索引
                IF @fldPercent >= @fldRebuildFragment
                    BEGIN
                        SET @Sql = 'ALTER INDEX ' + @fldIndex + ' ON ' + @fldTable + ' REBUILD';
                        EXEC(@Sql);
                    END
                ELSE
                --碎片率小於30,重組索引
                    BEGIN
                        SET @Sql = 'ALTER INDEX ' + @fldIndex + ' ON ' + @fldTable + ' REORGANIZE';
                        EXEC(@Sql);
                    END
                -- 獲取游標
                FETCH NEXT FROM curIndex
                INTO @fldTable,@fldIndex,@fldPercent;
            END
        -- 關閉游標
        CLOSE curIndex;
        DEALLOCATE curIndex;
        SET @ret = 0;
    END TRY
    BEGIN CATCH
        SET @ret = -1;
        DECLARE @ErrorMessage    nvarchar(4000);
        DECLARE @ErrorSeverity    int;
        DECLARE @ErrorState        int;
        SELECT
              @ErrorMessage = ERROR_MESSAGE()
            , @ErrorSeverity  = ERROR_SEVERITY()
            , @ErrorState = ERROR_STATE();
        RAISERROR( @ErrorMessage, @ErrorSeverity, @ErrorState);
        RETURN;
    END CATCH;
END

下麵直觀的看一下碎片產生的過程

--創建測試表
if object_id('test') is not null 
  drop table test
go
create table test
(
  col1 int, 
  col2 char(985),
  col3 varchar(10)
)
Go
--創建聚焦索引
create CLUSTERED index cix on test(col1);
go
--插入數據
declare @var int 
set @var=100
while (@var<900) 
begin
  insert into test(col1, col2, col3) 
  values (@var, 'xxx', '')
  set @var=@var+100
end;
--查看頁存儲情況
select page_count, avg_page_space_used_in_percent, record_count,
       avg_record_size_in_bytes, avg_fragmentation_in_percent, fragment_count,
       * from [master].sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('test'), null, null, 'sampled')

 

--然後做更新操作後,繼續查看頁存儲情況。

update test set col3='更新測試' where col1=100

--再次插入數據後查看頁存儲情況
declare @var int 
set @var=100
while (@var<900) 
begin
  insert into test(col1, col2, col3) 
  values (@var, '插入測試', '')
  set @var=@var+100
end;

 

--下麵看下對碎片整理之前和之後的IO
set statistics io on 
select * from test
alter index cix on test rebuild
select * from test 
set statistics io off

 

 明顯的邏輯讀取減少了。從而提高了性能

 

 

 

 


您的分享是我們最大的動力!

更多相關文章
  • 推薦sql.js——一款純js的sqlite工具。 一、關於sql.js sql.js(https://github.com/kripken/sql.js)通過使用Emscripten編譯SQLite C代碼,將SQLite移植到Webassembly。 它使用存儲在記憶體中的虛擬資料庫文件,因此不會 ...
  • 剛安裝mysql後想通過navicat來連接mysql,發現報錯 1251這個錯誤,不慌。這個很簡單。 首先通過cmd進入mysql。 然後修改密碼規則 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你 ...
  • 增加 語法: db.collectionName.insert({json對象}); 刪除 語法: db.collection.remove(查詢條件, num); 第二個參數是整數型,代表刪除的個數;預設是0(刪除全部文檔) 修改 語法: db.collection.update(查詢條件,新值) ...
  • 一、Redis簡介 Redis是一款基於key-value的高性能NoSQL資料庫,開源免費,遵守BSD協議。支持string(字元串) 、 hash(哈希) 、list(列表) 、 set(集合) 、 zset(有序集合)等數據結構,除此之外還提供了鍵過期、發佈訂閱、Lua腳本、事務、流水線(Pi ...
  • 索引的正常使用對於軟體的性能至關重要。 可以通過DMV,DMF檢查缺失索引情況。 --獲取缺失索引語句。 SELECT top 100 mid.index_handle, equality_columns, inequality_columns, included_columns, statemen ...
  • 一、加入依賴 com.github.spt-oss spring-boot-starter-data-redis 2.0.7.0 redis依賴二、添加redis.properties配置文件# REDIS (RedisProperties)# Redis資料庫索引(預設為0)spring.redi... ...
  • 註意,本方法是適用於同一區域網下的遠程連接 註意,本方法是適用於同一區域網下的遠程連接 註意,本方法是適用於同一區域網下的遠程連接 首先需要修改mysql資料庫的相關配置,將user表中的host改為%(因為mysql預設的是只能本地訪問,也就是說只能localhost訪問,若需其他人訪問,則要修改 ...
  • 1、首先關閉虛擬機點擊編輯虛擬機設置 2、點擊想要擴容的硬碟點擊擴容 3、增加容量 輸入想增加的容量,因為我本身是30G寫到35G是加了5G不是增加30G.(此處為了演示只增加5G) 4、開啟虛擬機 查看虛擬機當前磁碟掛載情況 fdisk -l 5、選擇磁碟 fdisk /dev/sda 6、查看磁 ...
一周排行
  • 微信公眾號: "Dotnet9" ,網站: "Dotnet9" ,問題或建議: "請網站留言" , 如果對您有所幫助: "歡迎贊賞" 。 .NET CORE(C ) WPF 抽屜式菜單 閱讀導航 1. 本文背景 2. 代碼實現 3. 本文參考 4. 源碼 1. 本文背景 使用簡單動畫實現抽屜式菜單 ...
  • 在上面abp(net core)+easyui+efcore實現倉儲管理系統——ABP WebAPI與EasyUI結合增刪改查之八(三十四) 文章的學習之後。我們通過前面的八篇文章已經學習了通過WebAPI介面與控制器去實現新增、刪除與修改功能。接下來,我們要在控制器中實現查詢功能。 ...
  • 1.選中項目-->屬性-->生成-->選中 XML文檔文件(xml路徑和該項目相同) 2.選擇生成序列化程式集:自動/開 ...
  • 本筆記摘抄自:https://www.cnblogs.com/PatrickLiu/p/7640873.html,記錄一下學習過程以備後續查用。 一、引言 從今天開始我們開始講結構型設計模式,結構型設計模式有如下幾種:適配器模式、橋接模式、裝飾模式、組合模式、外觀模式、享元模式、代理模式。 創建型設 ...
  • C 中 ConfigureAwait 相關答疑FAQ 在前段時間經常看到園子里有一些文章討論到 ConfigureAwait,剛好今天在微軟官方博客看到了 "Stephen Toub" 前不久的一篇答疑 ConfigureAwait 的一篇文章,想翻譯過來。 原文地址:https://devblog ...
  • 想要實現二維數組中根據某個欄位排序,一般可以通過數組迴圈對比的方式實現。這裡介紹一種更簡單的方法,直接通過PHP函數實現。array_multisort() :可以用來一次對多個數組進行排序,或者根據某一維或多維對多維數組進行排序。詳細介紹可參考PHP手冊:https://www.php.net/m ...
  • 常用的軟體: 播放器: cloundMusic(網易雲音樂) https://music.163.com/#/download PotPlayer(一款強大的視頻播放器) https://daumpotplayer.com/download/ ACDsee(ACDsee圖片編輯器免費版) https ...
  • 發現問題 在一次偶然中,在爬取某個網站時,老方法,打開調試工具查看請求方式,請求攔截,是否是非同步載入,不亦樂乎,當我以為這個網站非常簡單的時候,發現二級網頁的地址和源碼不對應 Ajax非同步載入?源碼也是這樣的 而且這些鏈接直... ...
  • 準備年後要跳槽,所以最近一直再看面試題,並且把收集到的面試題整理了以下發到博客上,希望對大家有所幫助。 首先是集合類的面試題 1. HashMap 排序題,上機題。 已知一個 HashMap<Integer,User>集合, User 有 name(String)和 age(int)屬性。請寫一個方 ...
  • JVM體繫結構圖 Native Interface(本地介面) Java本地介面(Java Native Interface (JNI))允許運行在Java虛擬機(Java Virtual Machine (JVM))上的代碼調用本地程式和類庫,或者被它們調用,這些程式和類庫可以是其它語言編寫的,比 ...
x