Sql-server – How to plan bring down the space used by databases to as low as possible

compressiondatabase-designsql serversql server 2014

Per our security breaks and guidelines when a database grow too big that it cant meet the requirements where it cannot be restored under a given RTO we need to plan on achieving same:-

Based on your experiences i would need some guidance and help here:-

Some insights on the database in question –

Total space –> Right click database-properties -> size = 10TB

What seems free in above –> Right click database-properties -> space
Available = 5 TB

This database is an OLTP and critical one as it goes through lot of DUI operations throughout with heavy usage of TempDB

I have listed out TOP 10 tables with space and index usage:-

All top 5 as pulled are

  • partitioned table with billion and million rows over 700 partitions
    in single table

  • Above tables have PAGE compression enabled

  • Above tables have less than 3 indexes in most of them but compression
    at index level shows NONE

For backup we do use compression with third party tool like idera to reduce size yet it crosses the threshold. Backup size is approx 1.9 TB for full backups. We do have diff and tran log backups as well.

I am little hesitant to go that shrinking route as it may impact performance but please help me with any ideas but else can be done to bring the space down?

Can we split few large table to their own separate database? If yes what is the catch?

Much appreciate all your help

Best Answer

I'll echo SqlWorldWide on the point about data types. The article they linked has a few good examples, but there are a lot more less-obvious areas to check too.

I've written a script sp_sizeoptimiser to help automatically identify a lot of them. Even if you don't use it, the areas it covers are worth looking into:

Data Types

  • Time based data types
  • Unspecified VARCHAR length
  • Mad VARCHAR Max
  • NVARCHAR data type
  • FLOAT and REAL data types
  • BIGINT as IDENTITY
  • NUMERIC or DECIMAL with 0 scale
  • Enum columns not implemented as foreign key

Architecture

  • Default fill factor
  • Number of indexes
  • Inefficient indexes
  • Sparse columns
  • Heap tables

Details for each of these is in the documentation linked above.

Preferably, I'd run this on a non-production copy of the database if possible, however it doesn't do any heavy lifting under the hood, so should be OK to run on production as well.

Related Question