MS-SQL 觀察目前Update及Scan比例和壓縮

 觀察目前Update及Scan比例:

SELECT

    o.name AS [Table_Name]

    ,x.name AS [Index_Name]

    ,i.partition_number AS [Partition]

    ,i.index_id AS [Index_ID]

    ,x.type_desc AS [Index_Type]

    ,i.leaf_update_count * 100.0 /

    (i.range_scan_count + i.leaf_insert_count

    + i.leaf_delete_count + i.leaf_update_count

    + i.leaf_page_merge_count + i.singleton_lookup_count

    ) AS [Percent_Update]

    ,i.range_scan_count * 100.0 /

    (i.range_scan_count + i.leaf_insert_count

    + i.leaf_delete_count + i.leaf_update_count

    + i.leaf_page_merge_count + i.singleton_lookup_count

    ) AS [Percent_Scan]

FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) i

JOIN sys.objects o

    ON o.object_id = i.object_id

JOIN sys.indexes x

    ON x.object_id = i.object_id

         AND x.index_id = i.index_id

WHERE (i.range_scan_count + i.leaf_insert_count

+ i.leaf_delete_count + leaf_update_count

+ i.leaf_page_merge_count + i.singleton_lookup_count) != 0

AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1

ORDER BY [Percent_Update] ASC

壓縮語法:

語法也很簡單，可以針對資料表或索引分別啟用壓縮:

資料表

ALTER TABLE TableName REBUILD PARTITION = ALL

WITH (DATA_COMPRESSION = PAGE)

索引

ALTER INDEX IndexName ON TableName REBUILD PARTITION = ALL 

   WITH (DATA_COMPRESSION = PAGE)