As soon as I saw Bert Wagner (t|b) post his T-SQL Tuesday topic last week I knew this was going to be a great one. I’m really looking forward to reading about everyone’s favourite code snippets so thanks Bert for hosting and choosing a fantastic subject!
A lot of the code I can’t live without is either downloaded from the community (e.g. sp_whoisactive, sp_indexinfo, sp_blitz), or very specific to my workplace so I’m going to share some code that I’ve been meaning to blog about.
I’ve been using this at work recently and it also relates to the presentation I gave at the ONSSUG June meeting around data compression. The beginnings of this script originated online as I dug into learning about the DMVs that related to objects and compression and then customized for what I needed.
If you run the below as is it will provide basic information about all objects in your database, except those in the ‘sys’ schema, along with their current size and compression level.
(This is also available in my GitHub Tips and Scripts Repo)
Now this T-SQL is great for a quick look at one database, but what if I want to run this script against every database in my environment? Well I popped over to PowerShell, fired up dbatools and ran the following:
Bad news, there was no Get-DbaDbCompression, there were commands for compressing objects (Set-DbaDbCompression) and for getting suggested compression setting based on the Tiger Teams best practices (Test-DbaDbCompression), but nothing to just return the current compression status of the objects.
What’s more exciting than just using the greatest PowerShell module ever created? Making it better by contributing! So I made sure I had the latest development branch synced up and got to work writing Get-DbaDbCompression. This has now been merged into the main branch and is therefore available in the Powershell gallery, so if your dbatools module is up to date you can now run the following to get the same information as above from one database:
Or go crazy and run it against a bunch of servers.
I hope this post might come in handy for anyone who is curious about data compression in their environments. Both the T-SQL and PowerShell versions provide not just the current compression setting but the size of the object too. Useful if you are about to apply compression and would like a before and after comparison to see how much space you saved.