Table size is Huge!!!

Question

Table size is Huge!!!

Tac11

SSCertifiable

Points: 6980
More actions
August 5, 2014 at 8:16 am

#288739

I have a database around 600 GB out of that A table is 398 GB, index size is 3 GB free space arund 5 GB. What are options do I have to make the table (database) smaller?

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Sarah Wagner Hall of Fame Points: 3178 More actions · Answer 1

Disk space is pretty cheap now a days, why make it smaller? Is archiving a possibility? You can play with compression, but I wouldn't just try it in production plus it depends on what data you are storing.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Delete data?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tac11 SSCertifiable Points: 6980 More actions · Answer 3

thanks for your time. One more question, how can I track what causing GB growth? Is there any script I can run to track table size growth/index size?

Sean Pearce SSCoach Points: 15999 More actions · Answer 4

Dropping A table will give the most gain.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 5

If you have Enterprise SQL Server you can turn on compression

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Tac11 SSCertifiable Points: 6980 More actions · Answer 6

TDE is enabled, do you think page compression will work?

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 7

smtzac (8/5/2014)
TDE is enabled, do you think page compression will work?

Possibly. If I remember correctly (and we're going off my memory, so Katy bar the door), they compress before they encrypt in terms of storage processing. But a little searching on the interwebs is likely to come up with a definitive answer.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

smtzac (8/5/2014)
thanks for your time. One more question, how can I track what causing GB growth? Is there any script I can run to track table size growth/index size?

Sure, use this script

http://bit.ly/tablespace

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 9

smtzac (8/5/2014)
I have a database around 600 GB out of that A table is 398 GB, index size is 3 GB free space arund 5 GB. What are options do I have to make the table (database) smaller?

Is the table an "audit" table? That would explain a lot...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 10

Here are a few options to make that single table "smaller":

If the table contains a rather large number of columns (e.g. 50 or more) you might consider "vertical partitioning", meaning to add a second table with the same primary key and splitt the columns (if columns can be grouped logically, even better...).

If the size is due to a large number of rows, you might consider "horizontal partitioning",e.g. by moving "old data" to one or more archive table(s), e.g. one per year.

Finally, evaluate the data type used for the column definitions and if it's defined for the size needed. For example, a NCHAR(255) to store either "Yes" or "No" could be reduced to VARCHAR(3)...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Tac11 SSCertifiable Points: 6980 More actions · Answer 11

Tac11

SSCertifiable

Points: 6980

August 6, 2014 at 12:08 pm

#1735739

Thank you guys for your time 🙂

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 12

smtzac (8/6/2014)
Thank you guys for your time 🙂

You still haven't identified whether or not it's an audit table. If it is, a whole 'nuther world can be opened to solve the problem of "huge" tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 13

I'm surprised nobody has discussed normalization... or did I miss something? It would be good if @smtzac could script the CREATE TABLE and tell us a bit about it (how many rows, what is it's purpose etc). Is it a transactional table or a flat data dump? Also what version od SQL Server is it on?

...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

ScottPletcher SSC Guru Points: 101118 More actions · Answer 14

LutzM (8/6/2014)
Here are a few options to make that single table "smaller":
If the table contains a rather large number of columns (e.g. 50 or more) you might consider "vertical partitioning", meaning to add a second table with the same primary key and splitt the columns (if columns can be grouped logically, even better...).
If the size is due to a large number of rows, you might consider "horizontal partitioning",e.g. by moving "old data" to one or more archive table(s), e.g. one per year.
Finally, evaluate the data type used for the column definitions and if it's defined for the size needed. For example, a NCHAR(255) to store either "Yes" or "No" could be reduced to VARCHAR(3)...

Excellent points overall.

But a Yes/No column should be changed to char(3), not varchar(3) (barring lots of NULL values).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".