Since most databases continue to grow and you may have increased the propensity of an index to cause major page splits and the cost of disk space is relatively cheap and free space does not take up space in backups nor usually any extra memory, what incredible savings or efficiency do you think there is to shrinking a database file?
The only, perhaps, potential "savings" is whatever disk costs there are on the cloud (again, I believe those are relatively inexpensive) or enabling you to restore to a smaller footprint on-prem box because a restore will allocate the previously reserved free space.
Basically our goal is to reduce storage cost...so willl that fulfilled by using row/page compression. Or do i need to take any extra care here. We are using azure sql vm..which stores data on disk ..and definitely this storage is costly...so plan is to identify heavy sized table and compress.....to make some.free space.....so that what i am saying is that free space i am not findig while checkong db size...though it is visible inside sp spaceused table.
Yes, after initial data compression, you must shrink the file to have SQL Server release space back to the drive. And that can cause performance issues because of the way SHRINK works. Very frustrating.
If the tables are really large, and you can have secondary (non-primary) data files, the cleanest method is often to compress the large table(s) into a new, separate filegroup. When all the large indexes have moved, then you shrink the files in the original filegroup(s). Shrink FILE BY FILE, NEVER shrink the entire db, like this:
DBCC SHRINKFILE (1, ...)
Sometimes rebuilding the indexes remaining in the original filegroup before shrinking the files will reduce overhead due to shrinking.
Either way, since the SHRINKFILE can cause read performance issues, after the shrink you will need to see if remaining index(es) in those file(s) need rebuilt, if performance on those indexes is important to you.
Shrinking files to get space back is one of the more painful tasks to do in SQL Server.
"It Depends". It has to be the right kind of maintenance and even Microsoft no longer recommends either the very old 10/30 or the not quite as old 5/30 methods. Recommendations about maintaining statistics have taken the place of maintaining indexes based on logical fragmentation.
Here's the "new documentation" that MS came out with starting on 20 April 2021. To summarize, they no longer recommend blinding following any panacea...
Paul Randal also explained "where the numbers came from" way back in 2009...
The final statement in that article by Paul says it bluntly....
"In the meantime, take those numbers with a pinch of salt and don’t treat them as absolute."
p.s. While Page compression can have some advantages, the underlying Row compression turns many types of "fixed width" datatypes into variable width, which can lead to a whole lot more fragmentation due to "ExpAnsive Updates" and the Page compression causes index rebuilds to take twice as long as they used to. Just something else to consider because "It Depends" and nothing is a panacea.
Is there any other way around to reduce sql server table storage cost or reduce the size of sql server table on cloud which is already havinh page compression on it.
Yes... as of SQL Server 2016, you have the COMPRESS and DECOMPRESS functions.
Here are the MS links...
And here are a couple of links that explain (a whole lot better than the MS docs) how to use the and what some of the ramifications are...
My recommendation is to, if at all possible, test on a copy of the table in a non-production environment because a whole lot depends on the actual content of the column(s) for such compression. And also keep in mind that such columns cannot be indexed (although I cannot understand why someone would want to index such a column).
We use COMPRESS() on JSON strings before storing them. We don't need to parse the JSON, just store it and rarely retrieve it, until it is old enough to throw out, so COMPRESS() is a real winner.