Link to home
Start Free TrialLog in
Avatar of crcsupport
crcsupportFlag for United States of America

asked on

SQL, Updating Statistics take too long for 60GB database

Windows 2012, SQL 2008 STD.
I have a database of 60GB size, not too big.
I am trying to rebuild index and update statistics using SQL SMSS Maintenance Plan.
Rebuilding index went smooth, took less than 2 hours.
Now the next step is to update statistics.
I chose 'Column Only ' (because RebuildIndex does updating statistics on indexes as I read) and Full Scan option (because I saw a few report, there's not much difference between Fullscan and more than 25% sample rate).
The update statistics just take too long. Right now it's been running 4 hours, still keep going.
I read online, some guys having much bigger database like more than 500GB takes only 40 minutes or around.
My database takes too longer comparing to what I read online.
Some suggests to create clustered index, but I'm not allowed to change the database structure.

Why does my database take too long updating statistics? I can't  find answers. Is there anyway to identify and fix the problem?
SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of crcsupport

ASKER

The server has 32GB RAM and one virtual machine runs on the server which has only 6GB ram.
So 24GB RAM available to be used by the SQL server.
When I started the rebuildindex, it had 7GB RAM available. I don't think RAM is issue here.
Also the server is HP Proliant G8, 2X6 core CPU.

Backup takes only 11minutes.

What does this mean
"You should also consider setting auto stats updates to be async rather than sync, so that they occur in the background rather than delaying a query."

Auto stat update in enabled on the database. I don't see option regarding sync vs async.

I also examined each tables, the biggest table which has 50M records have 25 auto statistics.
As a test, I just ran one auto statistics updated, it took 8 mins! lol
8 * 25=200mins.

What should I do with this auto stats? This database has been in use for 15 years. And when I read about the auto stats online, some guy suggests to drop the all auto stats and start from scratch.

I'm tempted to do it because the table is only having history and log which is not critical.

Is it OK to drop the auto stats starting with _WA?
By the way, even if I do fullscan on the statistics, if Auto Update Statistics is enabled, the sample rate will drop from 100% to 1-20% anyway. Isn't it?
So, why should I do update statistics with fullscan when I do maintenance weekend? Why do people do it and why is it the default option in SMSS Maintenance Plan?