September 1, 2016 at 4:19 pm
How to find out which big tables and indexes are not compressed.
Is there a standard report for this at the instance level? or db level?
or anyone have a script for this? Please advise. Thanks.
Report criteria
Any table with data > 800 MB not compressed
Any index with disk usage > 80 MB not compressed
Regards,
SQLisAwe5oMe.
September 1, 2016 at 5:00 pm
I found this query but I only need tables/indexes bigger than below requirement
Any table with data > 800 MB not compressed
Any index with disk usage > 80 MB not compressed
Any idea, how to modify the script to get that?
SELECT DISTINCT
SERVERPROPERTY('servername') [instance]
,DB_NAME() [database]
,QUOTENAME(OBJECT_SCHEMA_NAME(sp.object_id)) +'.'+QUOTENAME(Object_name(sp.object_id))
,ix.name [index_name]
,sp.data_compression
,sp.data_compression_desc
FROM sys.partitions SP
LEFT OUTER JOIN sys.indexes IX
ON sp.object_id = ix.object_id
and sp.index_id = ix.index_id
WHERE sp.data_compression <> 1
ORDER BY 2;
Regards,
SQLisAwe5oMe.
September 2, 2016 at 8:28 am
Don't have time right now to fully test/refine this, but it should be close at least:
SELECT DISTINCT
SERVERPROPERTY('servername') [instance]
,DB_NAME() [database]
,QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) +'.'+QUOTENAME(Object_name(p.object_id))
,p.data_compression
,p.data_compression_desc
FROM sys.partitions p
INNER JOIN (
SELECT container_id, SUM(total_pages) AS total_pages, SUM(used_pages) AS used_pages
FROM sys.allocation_units
WHERE type IN (1, 3)
GROUP BY container_id
) AS au ON au.container_id = p.partition_id
WHERE p.object_id > 100 AND
((p.index_id = 0 AND used_pages >= 800 * 128) OR
(p.index_id <> 0 AND used_pages >= 80 * 128))
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 2, 2016 at 9:56 am
yes, this is good, thanks Scott.
Regards,
SQLisAwe5oMe.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply