Table size is Huge!!!
-
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?
-
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.
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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?
-
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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
TDE is enabled, do you think page compression will work?
-
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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
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
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 -
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) -
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)...
-
Thank you guys for your time 🙂
-
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) -
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
-
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply