question

Sharma avatar image
Sharma asked

How to implement policy management for check database compression?

How to implement policy management to check database compression enable or not for multiple databases instance?
sql-server-2008compressionenterprise-editionpolicy-based-management
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sharma avatar image
Sharma answered
Use below script to check database compression on instance? Create Table #Compression_Details(TableName VARCHAR(500),IsCompressed INT, Compressed_Desc VARCHAR(1000),DBNAME VARCHAR(500)) DECLARE @SQLString VARCHAR(MAX) DECLARE @DBIn VARCHAR(500) DECLARE Check_Compression CURSOR FOR SELECT NAME FROM sys.databases WHERE database_id >4 OPEN Check_Compression FETCH NEXT FROM Check_Compression INTO @DBIn WHILE @@FETCH_STATUS =0 BEGIN SET @DBIn = '['+ @DBIn+']' SET @SQLString = 'USE ' + @DBIn + '; INSERT INTO #Compression_Details(TableName,IsCompressed,Compressed_Desc,DBNAME) Select T.name,P.data_compression,P.data_compression_desc,DB_NAME() FROM sys.partitions P JOIN sys.tables T ON (P.object_id = T.object_id) JOIN sys.schemas S ON (T.schema_id = S.schema_id) WHERE P.data_compression <>0' EXEC(@SQLString) PRINT (@DBIn) FETCH NEXT FROM Check_Compression INTO @DBIn END CLOSE Check_Compression DEALLOCATE Check_Compression SELECT * FROM #Compression_Details DROP TABLE #Compression_Details
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered
I personally would use powershell. Not got a script to hand myself, but [this][1] site looks to do what you want. This does it at the table level. Not checked that the script works, but it seems fine on "face value" [1]: http://artemervits.blogspot.co.uk/2011/01/identify-uncompressed-tables-in-sql.html
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SirSQL avatar image
SirSQL answered
You don't actually perform compression at the database level, it's always done at the table level. I also probably wouldn't set a policy as regards compression level. Depending upon the datatypes used, the variable values of that data and your performance requirements you may not want to use compression at all. You should really be establishing that need on a per table basis and checking it against your workload and server performance (ensuring that you start off with a good baseline). I know that this does completely cover your question, I'm just a little concerned that you are headed down a road that you should not be going down.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
I totally agree with @SirSQL. You should re-consider such policy. But I guess this can be done by choosing the **table** facet. Following what could be the condition script ExecuteSql('Numeric', 'select COUNT(*) FROM sys.partitions P JOIN sys.tables T ON (P.object_id = T.object_id) JOIN sys.schemas S ON (T.schema_id = S.schema_id) WHERE T.name = @@ObjectName AND S.name = @@SchemaName AND P.data_compression = 0 AND P.index_id IN (0,1)') and should be evaluated against value "0". Please note that I have added the filter AND P.index_id IN (0,1) to omit the secondary indexes. If you want the indexes to be evaluated as well, then remove that filter. By default, the policy would be for every table/ every database for your instance.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.