SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 table size growing everyday?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sql_server_dba
Posting Yak Master

160 Posts

Posted - 05/06/2011 :  00:46:41  Show Profile  Reply with Quote
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

Australia
73 Posts

Posted - 05/06/2011 :  02:31:28  Show Profile  Reply with Quote
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
Go to Top of Page

sql_server_dba
Posting Yak Master

160 Posts

Posted - 05/06/2011 :  08:25:16  Show Profile  Reply with Quote
Yes!! The table do have clustered index.

The table is of 500GB. May be the index_physic_stats
needs lit of resources which is difficult on our server.

Doesn't Update statistics after the delete operation take care of the
Fragmentation?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/06/2011 :  08:46:36  Show Profile  Visit russell's Homepage  Reply with Quote
No, update statistics only updates statistics

Have a look at ALTER INDEX (both rebuild and reorganize options) in BOL

Go to Top of Page

sql_server_dba
Posting Yak Master

160 Posts

Posted - 05/06/2011 :  09:11:59  Show Profile  Reply with Quote
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?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/06/2011 :  09:15:39  Show Profile  Visit russell's Homepage  Reply with Quote
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?
Go to Top of Page

sql_server_dba
Posting Yak Master

160 Posts

Posted - 05/06/2011 :  19:15:08  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000