Tuesday, March 20, 2012

TSQL to identify Compression Type on Indexes within a SQL 2008 or greater database

I came across a need to identify which indexes within a SQL 2008 R2 database that have Compression enabled and more importantly those that do not. I could not find a specific query to do this online but have come up with the following.

This query will not only report the Indexes and their compression type for User Tables it will also generate the statements to enable PAGE or ROW compression, or remove the compression from the table.

SELECT sys.schemas.name AS [SchemaName]
, sys.objects.name AS [TableName]
, ISNULL(sys.indexes.name, '') as [IndexName]
, SUM(sys.partitions.Rows) AS [RowCount]
, sys.partitions.data_compression_desc AS [CompressionType]
, 'ALTER INDEX ['+ISNULL(sys.indexes.name, '')+'] ON ['+sys.schemas.name+'].['+sys.objects.name+'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)' as [PAGECompressionTSQL]
, 'ALTER INDEX ['+ISNULL(sys.indexes.name, '')+'] ON ['+sys.schemas.name+'].['+sys.objects.name+'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)' as [ROWCompressionTSQL]
, 'ALTER INDEX ['+ISNULL(sys.indexes.name, '')+'] ON ['+sys.schemas.name+'].['+sys.objects.name+'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)' as [REMOVECompressionTSQL]
FROM sys.partitions
INNER JOIN sys.objects ON sys.partitions.object_id = sys.objects.object_id
AND sys.objects.type = 'U' --USER_TABLE
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.objects.schema_id
LEFT JOIN sys.indexes ON sys.partitions.object_id = sys.indexes.object_id
AND sys.partitions.index_id = sys.indexes.index_id
WHERE sys.partitions.index_id >= 0 --ignore the partitions from the non-clustered index if any
AND OBJECT_NAME(sys.partitions.object_id) LIKE '%' --Enter the table name here to restrict the results
GROUP BY sys.schemas.name
, sys.objects.name
, ISNULL(sys.indexes.name, '')
, data_compression_desc
ORDER BY data_compression_desc
, SUM(sys.partitions.Rows) DESC OPTION (RECOMPILE);

4 comments:

  1. Great script is there away to use this for Table compression not index

    ReplyDelete
    Replies
    1. Table compression is just compression of the Clustered index. So the above scripts should provide the information you are seeking.

      Delete
  2. Great script! This combined with my quicky script (below) to find DB's with Enterprise features provided me with exactly what I needed to easily find and remove compression so we could downgrade from Enterprise to Standard. Thank you!

    EXEC sp_msforeachdb 'use [?]
    BEGIN
    DECLARE @dbname varchar(25)
    SET @dbname= ''?''
    END

    DECLARE @tsql nvarchar(1000)
    SET @tsql = ''SELECT DB_NAME() dbName, feature_name, feature_id FROM sys.dm_db_persisted_sku_features''
    EXEC (@tsql)'

    ReplyDelete
  3. đồng tâm
    game mu
    cho thuê nhà trọ
    cho thuê phòng trọ
    nhac san cuc manh
    số điện thoại tư vấn pháp luật miễn phí
    văn phòng luật
    tổng đài tư vấn pháp luật
    dịch vụ thành lập công ty trọn gói
    nước cờ trên bàn thương lượng
    mbp
    erg
    nghịch lý
    chi square test
    nghệ thuật nói chuyện
    coase
    thuyết kỳ vọng
    chiến thắng con quỷ trong bạn
    cân bằng nash

    Tính tới quan hệ trong huyện. Chu Tinh Văn và Khâu Nguyên Phong có quan hệ mật thiết với mình. Nhưng hai người bọn họ chỉ giới hạn trong hệ thống công an. Khâu Nguyên Phong mặc dù có quan hệ mật thiết với Mao Đạo Lâm, nhưng cùng lắm chỉ đề xuất một chút. Bây giờ rất khó chắc Mao Đạo Lâm đã giúp hắn như lần trước.

    Cù Vận Bạch? Người phụ nữ này có thể làm Bí thư đảng ủy Ban quản lý Khu Khai Phát thì phải có quan hệ rất rộng. Lô Vệ Hồng cùng Mao Đạo Lâm đối xử khá tốt với Cù Vận Bạch. Nghe nói Cù Vận Bạch điều tới làm Chủ nhiệm Khu Khai Phát là do Mao Đạo Lâm mạnh mẽ đề cử, điều này gây tranh cãi trong Hội nghị thường ủy, cuối cùng Lô Vệ Hồng đã phải vỗ bàn ủng hộ.

    Triệu Quốc Đống có thể nhìn ra Cù Vận Bạch muốn mình thay cô làm Chủ nhiệm.

    Thật lòng mà nói hơn nửa năm công tác thì hai bên đều khá hài lòng với nhau. Nhưng Triệu Quốc Đống có thể làm Chủ nhiệm Ban quản lý hay không lại không do cô quyết định. Cô dù có quan hệ tốt với Lô Vệ Hồng và Mao Đạo Lâm thì chỉ là riêng cô. Mà lãnh đạo phải suy nghĩ rất nhiều thứ.

    Chẳng qua ngồi chờ chết không phải tính cách của Thái Chánh Dương. Dù thành hay không hắn cũng phải thử một phen, vận dụng tất cả lực lượng có thể mà thử. Dù thất bại thì cũng được yên lòng.

    - Cù tỷ, bây giờ đã là tháng bảy, nửa năm đã qua đi, chúng ta có phải làm lễ tổng kết không, cũng tiện báo cáo công việc với lãnh đạo huyện?
    Triệu Quốc Đống vào văn phòng Cù Vận Bạch, hắn ngửi ngửi hoa cắm trên bàn rồi nói:
    - Cù tỷ, chị để lọ hoa ở đây có phải là muốn người ta vào là so sánh chị với hoa không?

    - Cậu đừng có nịnh Cù tỷ này chứ. Không chừng cậu ở mặt người phụ nữ khác lại nịnh như vậy.

    Sau tối đó, Cù Vận Bạch cũng dần thích ứng với tác phong làm việc của Triệu Quốc Đống. Mặc dù nói chuyện hơi loạn một chút, có đôi khi thích trêu đùa một chút nhưng lại có thể làm sôi nổi không khí. Hơn nữa đối phương cũng không có ý đồ đặc biệt nên Cù Vận Bạch càng lúc càng thích Triệu Quốc Đống.

    ReplyDelete