I have a question regarding updating statistics for a primary key.
Background: An update statistics with fullscan is sometimes taking 30 minutes - the table is 80 million rows, with only 4 columns. The table is truncated, and then 80 million rows inserted all in one go.
Now why the update stats is taking that long is another question (I have no idea - any thoughts?), but my question is; Since you can't disable the "not automatically recompute statistics" option for a primary key, and you would think it would be imperitive for the stats to be kept up to date for a PK for inserts.... does this mean the stats would be kept up to date? and an update stat with fullscan isn't required?
Hope someone can help 
Thanks
James
If you have not changed anything statistics will be automatically updated by SQL Server after a number of modifications have been made to the table. SQL Server 2005 updates the counter that checks the number of modifications for BULK INSERT too. When autostats kicks in only a sample of the data is used to calculate them.
Doing a full scan requires a lot of I/O so with 80 million rows it might be slow if your disk subsystem is not fast enough..
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
sql
No comments:
Post a Comment