數據壓縮：自動評估

来源:http://www.cnblogs.com/Joe-T/archive/2016/07/14/5670514.html
-Advertisement-
Play Games

在前一篇博文數據壓縮簡要的基礎上，我希望把數據壓縮評估自動化。於是有了這篇博文。 白皮書推薦對符合如下條件的大型表和索引使用頁壓縮： 表或索引的掃描操作占到所有操作的75%及以上時 表或索引的更新操作占到所有操作的20%及以下時 註意，這是白皮書中的結論和建議，只做參考，最為最佳實踐的考慮點之一。 ...


在前一篇博文數據壓縮簡要的基礎上，我希望把數據壓縮評估自動化。於是有了這篇博文。

白皮書推薦對符合如下條件的大型表和索引使用頁壓縮：

  • 表或索引的掃描操作占到所有操作的75%及以上時
  • 表或索引的更新操作占到所有操作的20%及以下時

註意，這是白皮書中的結論和建議，只做參考，最為最佳實踐的考慮點之一。

 

此腳本的原始作者是Louis Li。但是它的腳本有一些限制，我在這此基礎上做了修改：

  • 輔助表由用戶表改成臨時表
  • 只分析頁數大於1000的分區
  • 判斷範圍擴大到所有的表和索引，而不只是堆和聚集索引
  • 判斷粒度改成分區級別。
  • 增加各分區使用空間的統計
  • 修改生成語句，增加提高性能的選項: MAXDOP=8,SORT_IN_TEMPDB=ON
  • 修改過濾條件。原來只分析Scan大於75%的分區，這樣流水日誌類型的表(S~=0%,U~=0%)會被過濾掉。改成(S>75%或者Update<20%)的。

下麵腳本會找出符合以下條件的對象並生成相應的壓縮數據腳本。

1. 掃描當前資料庫的所有索引，找出同時符合下麵條件的索引：

  • 索引的頁數超過1000
  • 索引的SELECT操作在所有操作中的占比高於75%或者索引的UPDATE操作在所有操作中的占比小於20%

註意此處的粒度是基於分區的。所以如果表和索行，做了分區會在分區級別上做出判斷。

2. 對於被上一步找出的索引，分別評估頁和行壓縮能節省的空間(用百分比表示)。

3. 對比行和頁壓縮的數據，進行推薦。對於沒有UPDATE操作或者頁壓縮節省的空間比行壓縮多10%，則推薦頁壓縮。其餘索引都推薦行壓縮。

4. 腳本的結果分為兩部分，第一部分是推薦的壓縮的索引，第二部分是推薦壓縮的方式和相應腳本。

 

--Collect all index stats

if object_id('tempdb..#index_estimates') is not null

  drop table #index_estimates

go

create table #index_estimates

(

    database_name sysname not null,

    [schema_name] sysname not null,

    table_name sysname not null,

    index_id int not null,

    partition_number int not null,

    update_pct decimal(5,2) not null,

    select_pct decimal(5,2) not null,

    used_size_kb int not null,

    constraint pk_index_estimates primary key (database_name,[schema_name],table_name,index_id,partition_number)

)

;

go

insert into #index_estimates

select

    db_name() as database_name,

    schema_name(t.schema_id) as [schema_name],

    t.name,

    i.index_id,

    p.partition_number,

    i.leaf_update_count * 100.0 / (i.leaf_delete_count + i.leaf_insert_count + i.leaf_update_count + i.range_scan_count + i.singleton_lookup_count + i.leaf_page_merge_count) as UpdatePct,

    i.range_scan_count * 100.0 / (i.leaf_delete_count + i.leaf_insert_count + i.leaf_update_count + i.range_scan_count + i.singleton_lookup_count + i.leaf_page_merge_count) as SelectPct

    ,p.used_page_count*8 as used_size_kb

from 

    sys.dm_db_index_operational_stats(db_id(),null,null,null) i

    inner join sys.tables t on i.object_id = t.object_id

    inner join sys.dm_db_partition_stats p 

    on i.object_id = p.object_id and i.index_id=p.index_id and i.partition_number=p.partition_number

where

    i.leaf_delete_count + i.leaf_insert_count + i.leaf_update_count + i.range_scan_count + i.singleton_lookup_count + i.leaf_page_merge_count > 0

    and p.used_page_count >= 1000  -- only consider tables contain more than 1000 pages

    --and i.index_id<2 --only consider heap and clustered index

    and 

    (

    (i.range_scan_count / (i.leaf_delete_count + i.leaf_insert_count + i.leaf_update_count + i.range_scan_count + i.singleton_lookup_count + i.leaf_page_merge_count) > .75 

    or 

    (i.range_scan_count/ (i.leaf_delete_count + i.leaf_insert_count + i.leaf_update_count + i.range_scan_count + i.singleton_lookup_count + i.leaf_page_merge_count))< .2

    ))

order by

    t.name,

    i.index_id

go

--show data compression candidates

select * from #index_estimates;

--Prepare 2 intermediate tables for row compression and page compression estimates

if OBJECT_ID('tempdb..#page_compression_estimates') is not null 

  drop table #page_compression_estimates;

go

create table #page_compression_estimates

([object_name] sysname not null,

[schema_name] sysname not null,

index_id int not null,

partition_number int not null,

[size_with_current_compression_setting(KB)] bigint not null,

[size_with_requested_compression_setting(KB)] bigint not null,

[sample_size_with_current_compression_setting(KB)] bigint not null,

[sample_size_with_requested_compression_setting(KB)] bigint not null,

constraint pk_page_compression_estimates primary key ([object_name],[schema_name],index_id,partition_number)

);

go

if OBJECT_ID('tempdb..#row_compression_estimates') is not null 

   drop table #row_compression_estimates;

go

create table #row_compression_estimates

([object_name] sysname not null,

[schema_name] sysname not null,

index_id int not null,

partition_number int not null,

[size_with_current_compression_setting(KB)] bigint not null,

[size_with_requested_compression_setting(KB)] bigint not null,

[sample_size_with_current_compression_setting(KB)] bigint not null,

[sample_size_with_requested_compression_setting(KB)] bigint not null,

constraint pk_row_compression_estimates primary key ([object_name],[schema_name],index_id,partition_number)

);

go

--Use cursor and dynamic sql to get estimates  9:18 on my laptop

declare @script_template nvarchar(max) = 'insert ###compression_mode##_compression_estimates exec sp_estimate_data_compression_savings ''##schema_name##'',''##table_name##'',##index_id##,##partition_number##,''##compression_mode##''';

declare @executable_script nvarchar(max);

declare @schema sysname, @table sysname, @index_id smallint ,@partition_number smallint,@compression_mode nvarchar(20);

declare cur cursor fast_forward for 

select

    i.[schema_name],

    i.[table_name],

    i.index_id,

    i.partition_number,

    em.estimate_mode

from

    #index_estimates i cross join (values('row'),('page')) AS em(estimate_mode)

group by

    i.[schema_name],

    i.[table_name],

    em.estimate_mode,

    i.index_id,

    i.partition_number;

open cur;

fetch next from cur into @schema, @table,@index_id,@partition_number, @compression_mode;

while (@@FETCH_STATUS=0)

begin

    set @executable_script = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@script_template,'##schema_name##',@schema),'##table_name##',@table),'##compression_mode##',@compression_mode),'##index_id##',@index_id),'##partition_number##',@partition_number);

    print @executable_script;

    exec(@executable_script);

    fetch next from cur into @schema,@table,@index_id,@partition_number, @compression_mode;

end

close cur;

deallocate cur;

--Show estimates and proposed data compression 

with all_estimates as (

select

    '[' + i.schema_name + '].[' + i.table_name + ']' as table_name,

    case 

        when i.index_id > 0 then '[' + idx.name + ']'

        else null

    end as index_name,

    i.partition_number,

    i.select_pct,

    i.update_pct,

    case 

        when r.[size_with_current_compression_setting(KB)] > 0 then 

            100  - r.[size_with_requested_compression_setting(KB)] * 100.0 / r.[size_with_current_compression_setting(KB)] 

        else

            0.0

    end as row_compression_saving_pct,

    case 

        when p.[size_with_current_compression_setting(KB)] > 0 then

            100  - p.[size_with_requested_compression_setting(KB)] * 100.0 / p.[size_with_current_compression_setting(KB)] 

        else    

            0.0

    end as page_compression_saving_pct,

    (case when ps.name is null then 0 else 1 end)  as is_partitioned

from

    #index_estimates i

    inner join #row_compression_estimates r on i.schema_name = r.schema_name and i.table_name = r.object_name and i.index_id = r.index_id

    inner join #page_compression_estimates p on i.schema_name = p.schema_name and i.table_name = p.object_name and i.index_id = p.index_id

    inner join sys.indexes idx on i.index_id = idx.index_id and object_name(idx.object_id) = i.table_name

    left  join sys.partition_schemes ps on idx.data_space_id=ps.data_space_id

), 

recommend_compression as (

select

    table_name,

    index_name,

    select_pct,

    update_pct,

    row_compression_saving_pct,

    page_compression_saving_pct,

    partition_number,

    is_partitioned,

    case 

        when update_pct = 0 then 'Page'

        when update_pct >= 20 then 'Row'

        when update_pct > 0 and update_pct < 20 and page_compression_saving_pct - row_compression_saving_pct < 10 then 'Row'

        else 'Page'

    end as recommended_data_compression

from

    all_estimates

where

    row_compression_saving_pct > 0

    and page_compression_saving_pct > 0

)

select

    table_name,

    index_name,

    select_pct,

    update_pct,

    cast(row_compression_saving_pct as decimal(5,2)) as row_compression_saving_pct,

    cast(page_compression_saving_pct as decimal(5,2)) as page_compression_saving_pct,

    recommended_data_compression,

    case 

        when index_name is null and is_partitioned =0 then

            'ALTER TABLE ' + table_name + ' REBUILD WITH  ( data_compression = ' + recommended_data_compression + ',MAXDOP=8)' 

        when index_name is null and is_partitioned =1 then

            'ALTER TABLE ' + table_name + ' REBUILD PARTITION='+CAST(partition_number AS VARCHAR(2))+' WITH  ( data_compression = ' + recommended_data_compression + ',MAXDOP=8)' 

        when index_name is not null and is_partitioned =0 then

            'ALTER INDEX ' + index_name + ' ON ' + table_name + ' REBUILD WITH  (data_compression = ' + recommended_data_compression + ',MAXDOP=8,SORT_IN_TEMPDB=ON)' 

        when index_name is not null and is_partitioned =1 then 

            'ALTER INDEX ' + index_name + ' ON ' + table_name + ' REBUILD PARTITION='+CAST(partition_number AS VARCHAR(2))+' WITH  ( data_compression = ' + recommended_data_compression + ',MAXDOP=8,SORT_IN_TEMPDB=ON)'   

    end collate database_default as [statement] 

from

    recommend_compression

order by

    table_name

--Clean up

drop table #index_estimates;

drop table #page_compression_estimates;

drop table #row_compression_estimates;

GO
Evaluate Data Compression

 

註意:

這個腳本的分析時長由要分析對象的數量和數據量決定。可能你會發現，這個跟在SSMS中的Storage-Compression中評估值有一些差別。兩種方式都使用的是sp_estimate_data_compression_savings,但是SSMS中不會指定@index_id參數，所以它評估的表中或者分區中所有對象的總合，這對於多個索引的表是非常不准確的。

 

總結:

1. 此腳本，我在很多生產環境中已經使用，均表現正常。但是如果你使用此腳本，請認真評估再使用。

2. 數據壓縮還會跟複製，AlwaysOn，列存儲等相互影響，這又是另一個故事了。

3. 數據壓縮不會壓縮行外的LOB數據。如果要壓縮只能在程式端壓縮，或者使用FileStream+壓縮捲。SQL Server 2016提供了新的函數COMPRESS/DECOMPRESS來壓縮單個數據，但不是用來解決行外LOB壓縮問題的。


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

-Advertisement-
Play Games
更多相關文章
  • 【轉】iOS屏幕適配
    一、iOS屏幕適配發展歷程 設備適配技術 4及以前（iPad未出） 直接用代碼計算 有了iPad autoResizing 有不同屏幕的iPhone後 autoLayout 有更多不同屏幕的iPhone後 sizeClass 二、各個技術的特性 1、 直接用代碼計算 由於屏幕的大小都一樣，只有橫豎屏 ...
  • launchImage啟動圖片尺寸(設置)
    首先需要一個專有的啟動圖片（沒有則新建） 其次決定適配的系統及橫豎屏 查看相應圖片的指定尺寸 按照尺寸添加圖片 OK。 如果尺寸不對或者沒有為相關的機型進行適配，就會導致該機型顯示時存在上下黑邊的情況。 ...
  • 【代碼筆記】一個tableView,兩個section
    一，效果圖。 二，工程圖。 三，代碼。 RootViewController.h RootViewController.m ...
  • 設計移動App的十大技巧
    編寫一款Android或iOS應用也許很容易，但是若想設計的成功卻不是一件簡單的事，用戶界面對於一款移動應用的成功是至關重要的。也許你會說，為何界面那麼糙的Flappy Bird可以大紅大紫，可那畢竟是特例，製作精良設計美觀的應用才是移動界的主流。對此，如何設計一個更好的應用，這裡有十個技巧。 技巧 ...
  • Java 正則表達式詳解
    一、正則表達式基礎知識 我們先從簡單的開始。假設你要搜索一個包含字元“cat”的字元串，搜索用的正則表達式就是“cat”。如果搜索對大小寫不敏感，單詞“catalog”、“Catherine”、“sophisticated”都可以匹配。也就是說： 1.1 句點符號 假設你在玩英文拼字游戲，想要找出三 ...
  • 最新的 cocoapods 安裝與使用（2016.7）
    cocoapods簡介： cocoapods簡介： cocoapods 是iOS的類庫管理工具，可以讓開發者很方便集成各種第三方庫，而不用去網站上一個個下載，再一個個文件夾的拖進項目中，還得添加相關的系統依賴庫。只需要安裝好cocoapods，然後編寫好Podfile，最好在終端輸入一行命令，就可以 ...
  • oracle 資料庫Cmd命令導入導出
    imp 導入資料庫： 1.直接導入數據表： imp username/passwork@orcl file=d:/AA.dmp eg: imp 用戶名/密碼@orcl file=d:/dmp存放的路徑 exp導出資料庫： 1.導出多張數據表： exp username/passwork@orcl f ...
  • SQL Server2014 SP2關鍵特性
    SQL Server2014 SP2關鍵特性 轉載自：https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-2014-service-pack-2-is-now-available/ 根據SQL Server客戶的和SQL社區的反饋，SQL2 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類