Query all table data and index compression

Question

Does anyone happen to have a generic SQL statement that'll list all of the tables and indexes in a database, along with their current compression setting, for each partition?

Thanks.

EDIT: This is as far as I got in my attempt to query tables, but I'm not sure the join is correct (I'm getting duplicates, which seems to be caused by the presence of indexes)

SELECT [t].[name], [p].[partition_number], [p].[data_compression_desc]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]

Ratchford

You could have written one in the time you've waited for an answer. :-) You know about sys.partitions, sys.indexes, etc. right? – Quotient

I don't know, I'm a fast typer. I had a go, but tbh I'm not confident of the relationships between the tables you mentioned. – Ratchford

You should include what you tried in your question; it shows to others that you've put in some effort and aren't just asking for code (or asking for someone to write your code for you). – Quotient

Answer 1

I thought I'd share my final query. This'll give two result sets, the first of data compression for heaps and clustered indexes, and the second of index compression for non-clustered indexes.

SELECT [t].[name] AS [Table], [p].[partition_number] AS [Partition],
    [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
WHERE [p].[index_id] in (0,1)

SELECT [t].[name] AS [Table], [i].[name] AS [Index],  
    [p].[partition_number] AS [Partition],
    [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id]
WHERE [p].[index_id] > 1

Ratchford

For a great explanation of this answer see this answer: https://mcmap.net/q/542742/-query-all-table-data-and-index-compression – Caravansary

It does not list PK for me? Is this normal? – Tomi

Answer 2

While I think while the final queries posted by Barguast may work, there is still a problem with them/something not explained well enough.

Basically an index_id of 0 is a heap, 1 is a clustered index and 2 is everything else (non-clustered indexes).

The problem with the above queries is that the query for the data will not work if the table is a heap (even though there is data in the table). Also the query for the indexes works because you specify the index_Id = 2 and there is dupes due to not joining the index_id between sys.indexes and sys.partitions. If you join on those then there will not be duplicates in the result set and you can do the much more understandable index_id not in (0,1).

Anyways fixed queries are below. I also added index name to the first query (note this field will be null if the table is a heap). Also note that you don't have to specify the join for index_id on the first query, because the where specifies (0,1) and there can only be one of those (in other words you could add it if you like but it doesn't make a difference).

-- Data (table) compression (heap or clustered index)
SELECT [t].[name] AS [Table], 
       [i].[name] AS [Index],
       [p].[partition_number] AS [Partition],
       [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] 
     ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] 
     ON [i].[object_id] = [p].[object_id]
WHERE [p].[index_id] in (0,1)

-- Index compression (non-clustered index)
SELECT [t].[name] AS [Table], 
       [i].[name] AS [Index],  
       [p].[partition_number] AS [Partition],
       [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] 
     ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] 
     ON [i].[object_id] = [p].[object_id] AND i.index_id = p.index_id
WHERE [p].[index_id] not in (0,1)

December

Answer 3

These answers are all decent and work. Since I embellished it a bit for my work, I figured is was about time for to contribute back a bit. This query adds the schema from Jason's answer (which I needed). and it also sorts out some of the join issues and combines the results into a pretty simple summary.

-- Returns user tables and indexes in a DB and their Compression state
select s.name [Schema], t.name [Table], i.name [Index], p.data_compression_desc Compression
     , case when p.index_id in (0, 1) then 'Table' else 'Index' end CompressionObject
  from sys.tables t
  join sys.schemas s on t.schema_id = s.schema_id
  join sys.indexes i on t.object_id = i.object_id
  join sys.partitions p on (i.object_id = p.object_id and i.index_id = p.index_id)
where t.type = 'U'
order by 1, 2, p.index_id, 3

I used this as a "work list" to generate scripts to compress everything since I just lift-shift the db into an Azure VM and wanting to reduce IOPS to improve perf. Hope this helps somebody out there.

Sejm

Answer 4

This should do the job, test it for a small subset to be sure it gives you what you need

SELECT DISTINCT s.name [Schema], t.name [Table], i.name [Index Name], p.partition_number, p.data_compression_desc
-- uncommenting the below line will give you dupes
--, p.index_id
FROM sys.schemas s
INNER JOIN sys.tables t
    ON s.schema_id = t.schema_id
    INNER JOIN sys.indexes i
        ON t.object_id = i.object_id
    INNER JOIN sys.partitions p
        ON t.object_id = p.object_id
ORDER BY s.name, t.name

The reason you are probably getting dupes is because you have multiple partition records per table, e.g. multiple index_id, see this MSDN article for clarification on what the index_id's mean. Adding a DISTINCT should solve the problem of the dupes

Oilcloth

Answer 5

I prepared below SQL for myself. Also it can be useful for you. Here you are.

SELECT [t].[name] AS [Table_Name],
       '-' AS Index_Name,
       p.rows AS [Total_Records],
       (8 * SUM(a.used_pages))/1024   AS 'Size_MB',
       8 * SUM(a.used_pages)    AS 'Size_KB',
       p.data_compression AS "Data_Compression",
       [p].[data_compression_desc] AS [Data_Compression_Desc],
       p.xml_compression AS "XML_Compression", 
       p.xml_compression_desc AS "XML_Compression_Desc"
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE [p].[index_id] in (0,1)
AND t.name in ('Type_Your_Table_Name')
GROUP BY t.[name], p.rows, p.data_compression, p.data_compression_desc, p.xml_compression, p.xml_compression_desc
UNION ALL
SELECT [t].[name] AS [Table_Name], 
       [i].[name] AS [Index_Name],  
       '-' AS "Total_Records",
       (8 * SUM(a.used_pages))/1024   AS 'Size_MB',
       8 * SUM(a.used_pages)    AS 'Size_KB',
       p.data_compression AS "Data_Compression",
       [p].[data_compression_desc] AS [Data_Compression_Desc],
       p.xml_compression AS "XML_Compression", 
       p.xml_compression_desc AS "XML_Compression_Desc"
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id]
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE [p].[index_id] > 1
AND t.name in ('Type_Your_Table_Name')
GROUP BY t.name, i.name, p.data_compression, p.data_compression_desc, p.xml_compression, p.xml_compression_desc

Planoconvex

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community? – Hakodate

Answer 6

Combined into a single query, this shows schema, table and index names:

SELECT
    OBJECT_SCHEMA_NAME(t.object_id) AS Schemaname,
    t.name AS TableName, 
    i.name AS IndexName,  
    CASE 
        WHEN p.index_id = 0 then 'Heap'
        WHEN p.index_id = 1 then 'Clustered'
        ELSE 'Non-Clustered'
    END as Type,
    p.partition_number AS [Partition],
    p.data_compression_desc AS [Compression]
FROM 
    sys.partitions p
    JOIN sys.tables t ON t.object_id = p.object_id
    LEFT JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id;

There's also this: Script out all indexes in a database

Crepe