Update statistics with large databases

Question

Update statistics with large databases

ramana3327

SSCoach

Points: 19722
More actions
February 5, 2015 at 5:56 am

#300858

Hi,
currently our database size is around 350G. It will grow up to 1.5 TB
We have the
Auto create statistics option :True,
auto update statistics option :True,
auto update statistics asynchronously option : False
at database level
we have a weekly job, update statistics running very long time. It is created through maintenance plan using the option full scan.
Previously they tested with sampling but instead of full scan running with the sampling effected the queries.
Is there option to avoid the long time job duration.
If we didn't run the statistics manually what will happen? Please let me know how do you maintain statistics with large databases

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

If you don't update your statistics the optimizer will make bad choices for your query plans. This can seriously negatively impact performance. So, large database or not, you have to keep statistics up to date just as much (or even more so) than a small database.

First, a strong recommendation to set TraceFlag 2371 on in your system. This changes how auto update statistics works on large tables.

After that, if you do have stats that need to be updated occasionally using a full scan, you'll just have to find the times when these stats are least used in order to update them.

You also might test using ASYNC updates on your statistics. It can work well in some circumstances.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ramana3327 SSCoach Points: 19722 More actions · Answer 2

How to find the trace flag 2371 is enabled or not in our environment?

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

Here you go.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ramana3327 SSCoach Points: 19722 More actions · Answer 4

ramana3327

SSCoach

Points: 19722

February 5, 2015 at 1:19 pm

#1775871

Thanks you.

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 5

Hi Grant,

i tried to run the below command on the sql i got all the output value as 0

DBCC TRACESTATUS (2371);

Output :

TraceFlagStatusGlobalSession
2371000

What does this means. Please advice me.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

It means that traceflag is not enabled on your system.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 7

thanks grant and please suggest me if it not enable will it affect performance. How drastically it affect the performance?

ramana3327 SSCoach Points: 19722 More actions · Answer 8

In my case also DBCC TRACESTATUS (2371) is not showing any thing.

ramana3327 SSCoach Points: 19722 More actions · Answer 9

Are there any performance impacts by enabiling trace flag 2371

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 10

Yes, there is a performance impact but not the way some would think. It can be much worse.

When would you expect auto-updates of statistics to occur? Yeah... that's right. When you've added or modified a lot of rows. When does that normally happen? At night when batch jobs are running... or when that one user during the day makes that one entry that, like the proverbial final straw, breaks the camel's back.

The real problem here is large tables and rebuilding stats on large tables. There are a couple of ways to handle all of this but I've found that the largest of tables are typically some form of history or audit table, which can also include things like InvoiceDetail (for example) tables that have mostly static, temporal data in them.

So, with all that in mind, what are your largest two or 3 tables and what are they used for? More to come once we find that out.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 11

KGJ-Dev (2/5/2015)
thanks grant and please suggest me if it not enable will it affect performance. How drastically it affect the performance?

I have not seen it have a negative impact on any system yet. Like with any change to your system, you should test it, monitor it, and be prepared to back it out. However, I do still advocate for it.

Better statistics will enhance performance on the majority of systems, not hurt them. You should see a positive impact from this. But, test it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning