Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_server_dba
Posting Yak Master
167 Posts |
 Posted - 2011-05-06 : 00:46:41
|
| Experts...i got stuck with my huge table here.any help is highly appreciated.i have a table of 500GB with 50 million records.I have 20 columns in the table and 2 columns are of text data type. i am having hardtime with the size of the table...everyday i delete 1 million records and run the update statistics and later during the day it inserts one more million records. So now, my problem is the datasize of the table is 50GB, index size is 20GB and the text size is 420GB. so now, my question is when ever i delete data and update the statistics, does the text data space release from the table?if Yes, then why does my table is growing 1GB on everyday?Thanks in Advance. |
|
|
dshelton
Yak Posting Veteran
73 Posts |
Posted - 2011-05-06 : 02:31:28
|
| This could be caused by table fragmentation, do you have a clustered index on this table?Run sys.dm_db_index_physical_stats against this table and return your results.David |
 |
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2011-05-06 : 08:25:16
|
| Yes!! The table do have clustered index.The table is of 500GB. May be the index_physic_statsneeds lit of resources which is difficult on our server.Doesn't Update statistics after the delete operation take care of the Fragmentation? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-05-06 : 08:46:36
|
No, update statistics only updates statistics  Have a look at ALTER INDEX (both rebuild and reorganize options) in BOL |
|
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2011-05-06 : 09:11:59
|
| so does this mean, when ever i delete and update statistics, the fragmentation will be the same and some more fragmentation will be added to it? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-05-06 : 09:15:39
|
| Statistics have nothing to do with fragmentation.So basically, yes. Deleting and adding rows will very likely cause index fragmentation.How much fragmentation are you seeing? |
|
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2011-05-06 : 19:15:08
|
| i havent ran the physical_stats query since it was huge table and i dont have that many resources...but my guess is around 250-300GB. |
|
|
|
|
|
|
table size growing everyday?