Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL, Updating Statistics take too long for 60GB database

Posted on 2016-10-19
5
Medium Priority
?
503 Views
Last Modified: 2016-10-22
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?
0
Comment
Question by:crcsupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 35

Assisted Solution

by:ste5an
ste5an earned 500 total points
ID: 41851597
Rebuilding index went smooth, took less than 2 hours.
is under most circumstance too much time for index rebuilds.

Check the database IO paths. Check the health of your disks. Are these rebuilds done online? Are they done in a maintenance window or are user active and blocking these rebuilds?
0
 
LVL 43

Assisted Solution

by:pcelba
pcelba earned 500 total points
ID: 41851782
Execute the Resource Monitor on your server and look what resources are short. Namely focus on CPU loading and disk queue. Also RAM usage can be important.

BTW, how much takes the database backup?
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 41852400
A full scan could take a long time depending on how wide (how many total bytes) the rows are.  

Rather than using a Maintenance Plan (MP), you might want to update stats yourself and specify a specific percentage, say 33, rather than doing a full scan, and specify all stats for one table at the same time.  Since I don't use MPs, I suspect they might do each stats update separately, even in the same table; again, I do not know that for sure, it's just a guess, since I never use a MP.

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.
0
 
LVL 1

Author Comment

by:crcsupport
ID: 41852431
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?
0
 
LVL 1

Author Comment

by:crcsupport
ID: 41852512
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?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question