CODEWITHSUNDEEP
sql-serversql-server-2012
Mehdi Souregi
Mehdi Souregi

Reputation: 3265

Reducing disk space of sql database

I got a database that have 2TB of data, and i wanna reduce it to 500Go by dropping some rows and removing some useless columns, but i have other ideas of optimizations, and i need an answer of some questions before.

My database got one .mdf file, and 9 other .ndf file and each file has an initiale size of 100Go.

  1. Should I reduce the initiale size of each .ndf file to 50Go? can this operation affect my data?
  2. Dropping an index help to reduce space?

PS : My Database contains only one single table, that has one clustered index and two other non clustered indexes,

If you have any other ideas of optimizations, it would be very helpful

Upvotes: 1

Views: 1432

Answers (1)

pacreely
pacreely

Reputation: 1931

Before droping any indexes run these two views.

sys.dm_db_index_usage_stats

sys.dm_db_index_operational_stats

They will let you know if any of them are being used to support queries. The last thing you want is to remove an index and start seeing full table scans on a 2TB table.

If you can't split up the table into a relational model then try these for starters.

  1. Check your data types.

-Can you replace NVARCHAR with VARCHAR or NCHAR with CHAR? (they take up half the space)

-Does your table experience a lot of Updates or a lot of Inserts (above view will tell you this)? If there are very few updates then consider changing CHAR fields to VARCHAR fields. Heavy updates can cause page splits and result in poor Page fullness.

-Check that columns only storing a Date with no time are not declared as Datetime

-Check value ranges in numeric fields i.e. try and use Smallint instead of Int.

  1. Look at the activity on the table, update & insert behaviour. If the activity means very few Pages are re-arranged then consider increasing your Fill Factor.

  2. Look at the Plan Cache, get an idea of how the table is being queried, if the bulk of queries focus on a specific portion of the table then implement a Filtered Index.

  3. Is your Clustered Index Unique? If not then SQL creates a "hidden extra Integer column" that creates uniqueness under the bonnet.

Upvotes: 1

Related Questions