Auto update statistics

garak

Hi, one question.

Has you activate this option or have you create an job, that every morning, like 4o clock, with sp_updatestats the statistics updates.

Whats you meaning and properties on DB?

Regards

Waldo

What we do:
- all auto options off
- daily statistics update (index only is enough)
- daily index rebuild

Actually, we use the SQLPerform Maintenance tool that does everything for you ... .

garak

Waldo ;-)

Ok, what i do. Auto create stas is on, auto update stats off.
an job sp_updatestats every morning 04:00, and reorgindex every sunday 04:00.

mhm, every rebuild index :-k

Are thy big databases with many writes and actuall needed reads, like some G/L or Cost Infomations / Analyses :?:

Regards

Waldo

Actually, when you rebuild your indexes, make sure that your DOP is maximum ... then it should be possible to rebuild every night on big databases.

If not, there is a script to do it table per table... somewhere on this forum.

garak

DOP :?:

Today i rebuild index for all tales evry sunday.
But i'm on supier. to create an job for hot tables , like some entries, to reog / rebuild that runs every day.

ALTER INDEX ALL ON Production.Product

REBUILD WITH ....

or Alter Index Reorgenize ..

I mean, that must be perfomanter for analyses on this tables, because the server must not read so many pages.

Waldo

DOP = Degree of Parallellism = how many CPUs can be used for one process... .

colin_leversuch-roberts

I don't want to appear to be getting at anyone but the advice to turn off auto create and auto update stats must be the most stupid recommendation for SQL Server installs.
My client was told to turn this off, in all the time I've worked with the versions of sql server that support auto stats I've only ever turned this off on read only databases - the average navision sql database is small and I imagine the average server has way more power than really needed the chances of auto stats update causing a problems is so remote it's not true; so my client was advised to turn this off - I've since turned it back on - other posts have concluded that the indexing for navision on sql server is not as good as it could be, auto stats will help. in three days after re-enabling this setting nearly 1000 stats had been created, in the same period there was no indication of any auto stats updates - once a table gets over a million rows the real chance of an auto update stats event kicking in is pretty remote.
The end result - well some users have remarked the system is running faster - that may be subjective of course. I have a 1TB database with many tables in the 100 million rows and a lot of users.
In sql 2005 you can set update stats to run asynchronously ( background ) The auto stats is one of the myths surrounding sql server that has been taken out of context over the years.
Leave these options enabled and also update your stats every night, preferably after postings system updates etc. etc. ideal is last thing before your users start work again.
btw. How many users who give advice about this setting or actually change it know how to monitor to see if the alledged effects of auto stats is actually affecting your system?

Marije_Brummel

In SQL 2000 it was admitted my Microsoft that the updatestatistics do not work.

For 2005 I do not know but what I do know is that for most ERP systems the statistics do not change every hour or so.

If you profile a system you see statman kicking in a lot, so statistics cost performance.

Waldo

colin leversuch-roberts wrote:

I don't want to appear to be getting at anyone but the advice to turn off auto create and auto update stats must be the most stupid recommendation for SQL Server installs.

Well ... I know who it's coming from :roll: .

garak

interesting statements.
@Waldo: And from where it comes:?:


For our engl friends ;-)
New Wembley stadium. First football game.
England vs. Germany
1:2 ;-)

Wembley -> the lounge of the german football

colin_leversuch-roberts

don't do football so comment is sadly wasted!!! just shows our footballers are over hyped, over paid and can't play football.
Apart from use with Biztalk I'd be interested to see any microsoft KB which says auto stats don't work. I have a number of SQL Server MVP's as friends - they struggled to understand why anyone would recommend this route without very extensive diagnostics. So you can see auto stats fire now and again in profiler - I see all sorts of statements in profiler - I ask again on what basis do you decide the auto stats are bad - I figure it's got to have become an urban myth for navision - But hey, here you are complaining about poor performance and turning off one of the features of sql server which can aid performance. It's akin to turning off traction control on your car. Auto stats certainly work in sql 2000.

thaug

I do remember recently seeing something to this effect, that it is better to keep auto update stats on. I don't recall where I saw it, but it was somewhat eye opening, hence I remember it.

Although this is not what I saw, I found this article that is relevant to the discussion however:
http://www.microsoft.com/technet/prodte ... stats.mspx

In particular, this bit under Best Practices is most interesting:

For a large majority of SQL Server installations, the most important best practice is to use auto create and auto update statistics database-wide.

garak

oh colin ;-) our footballers over hyped? Ok, but not over paid. Look to beck's, he was absolutly terrible .....

But whatever, we win ;-)

So, back to the root ......

Mhm, some people say this, other this ...

it's better to activate the auto update stats option but its good when you create an job to update stats schedul.

colin_leversuch-roberts

sorry probably lost in transalation - English footballers = overhyped, overpaid and rubbish - I agree about Becks - rubbish!

DenSter

I did not do the research myself, but I know for a fact that the people who recommend this researched this did. They came to the conclusion that FOR NAV DATABASES, NOT FOR SQL SERVER IN GENERAL, it is better to turn off the auto options.

The part that many people forget to mention though, is that this then needs to be complemented by regular maintenance on the statistics, but on the index fields only. Now as for why, I really don't know. All that I do know is that very authorative people, who put the best NAV minds together with the best SQL Server minds, did the research and came to the conclusion that for NAV database, the best performance comes when you keep statistics on index fields only.

I know for a fact that Microsoft did extensive NAV benchmark testing on SQL Server in the UK, in Denmark, in the US, and in Germany. I have spoken personally with people who were present at the benchmark tests in Germany, and they confirmed that during those tests, having too many statistics actually decreased performance.

Personally, I trust the judgement of the above people, and I will continue to take their advice, and continue to turn off the auto options.

DenSter

@ Colin: you have to understand that your TB database is the exception to the rule. On that scale, there are probably all sorts of things going on that most other NAV implementations don't see.

I wish you'd be more open to what the NAV people have to say. Many folks here work on many NAV databases, and have first hand experience of INCREASED performance after turning off the auto options. Granted it is in combination with other measures at the same time, but it HAS made a positive impact on performance.

This discussion is useful though, I'm now open to try turning the auto statistics option back on for particular customers and see if it makes a positive impact.

Waldo

For me, it will be off by default.
I'll keep this thread in mind (off course), but I doubt that I'll ever turn it on.

Marije_Brummel

I am definately NOT a SQL mvp, but I see a lot of Navision on SQL and my knowledge is limited to having that working fast.

I also know that any discussion with a SQL expert without Navision knowledge is something very interesting.

Yes, Navision on SQL is different. Nav only does simple read and write statements to the server, it does not do any business logic on the server. The Navision datamodel and transaction model were designed for the Native server, not for SQL.

We just have to do with what we have to make the installations hum.

DenSter

Waldo wrote:

For me, it will be off by default.
I'll keep this thread in mind (off course), but I doubt that I'll ever turn it on.

Yes my thoughts exactly. It's off until I have tried everything else. When I reach the point of "I dunno what to do next" I'll give it a shot.

stryk

My two cents:

With "Auto. Create Stats" the SQL Server could generate a statistic for every field, depending on it's usage - even though this field is not part of an index. During time, this could generate an enormous "overhead" which is simply not required. But as these stats are also updated, it will take longer the more stats exist. Also, these stats could cause trouble with C/SIDE development, e.g. if you try to delete a field, you could get an error if a statistic is related to this field. Another error could be, that with SQL 2000 you could only have max. 250 (+-?) related objects per table. I had the case where a table had abouat 240 (!!!) statistics and 10 indexes, so the problem was that we could not create another index!

If "Auto. Update Stats" is enabled, SQL Server performs this updated when it "thinks" it's appropriate. This could be never, or at a time where it interferes with user transactions, then decreasing performance.

And that's not a "SQL tale", this is fact!

My recommendation is:

+ "Auto. Create Stats" OFF
+ "Auto. Update Stats" OFF
+ "Auto. Update Stats Async." ON (2005)
+ If "Auto. Create Stats" was enabled, then delete all "Auto Stats"
+ Maintain statistics daily using

exec sp_updatestats
go
exec sp_createstats 'indexonly'

This will grant to have those statsistics the SQL Server really needs - no more, no less.

NavStudent

how do you delete all the auto stats?

garak

//delete, because false informations

NavStudent

those are all sift tables. What are you talking about?

I would never touch those tables. :evil:

NavStudent

Everything comes up with a price. There are some pros and cons of using that option and choice should be made very carefully otherwise it can seriously hurt performance. For example, the AUTO_UPDATE_STATISTICS option, when turned on (which it is by default for a new database), tells the Query Optimizer to automatically update the index and column statistics as data changes. It is vital because data changes can affect Optimizer decision in selecting optimum query plan. Statistics are automatically updated when the statistics used in a query execution plan are outdated. Statistics are considered outdated when 20% or more rows in a table have changed.

So what it means to me is when Optimizer consider statistics need to be updated it does it right there and then recompile the query to use updated statistics and create new execution plan and execute the query. Most of the time this process is not noticeable but in some scenarios it can hinder query performance and might cause query timeouts.

For large tables that have many indexes and statistics on columns it can take significant amount of time. Query has to wait during that time to be recompiled using new statistics and then time to execute that query plan. This can cause serious delay and can happen on periodic bases for example query usually takes 2 second but some time it takes more the 10 seconds. And believe me it will take you some time before realizing that what’s going on.

David_Singleton

stryk wrote:

My two cents:

With "Auto. Create Stats" the SQL Server could generate a statistic for every field, depending on it's usage - even though this field is not part of an index. During time, this could generate an enormous "overhead" which is simply not required. But as these stats are also updated, it will take longer the more stats exist. Also, these stats could cause trouble with C/SIDE development, e.g. if you try to delete a field, you could get an error if a statistic is related to this field. Another error could be, that with SQL 2000 you could only have max. 250 (+-?) related objects per table. I had the case where a table had abouat 240 (!!!) statistics and 10 indexes, so the problem was that we could not create another index!

If "Auto. Update Stats" is enabled, SQL Server performs this updated when it "thinks" it's appropriate. This could be never, or at a time where it interferes with user transactions, then decreasing performance.

And that's not a "SQL tale", this is fact!

My recommendation is:

+ "Auto. Create Stats" OFF
+ "Auto. Update Stats" OFF
+ "Auto. Update Stats Async." ON (2005)
+ If "Auto. Create Stats" was enabled, then delete all "Auto Stats"
+ Maintain statistics daily using

exec sp_updatestats
go
exec sp_createstats 'indexonly'

This will grant to have those statsistics the SQL Server really needs - no more, no less.

=D>

Thanks for explaining WHY.

DenSter

garak wrote:

the stats are stored in tables. the name aof this tables are: Companyname$NavisionTableNo$KeyNo

For example table Value Entry:

MyCompany$5802$0 ... MyCompany$5802$XX in these tables the stats for the selectet key are storerd, also empty sift records.

NO DON"T TOUCH THOSE TABLES :shock:

These are the SIFT tables, they do not store any stats, at least not directly in the tables visible in the SQL Server table designer.

DenSter

stryk wrote:

My two cents:

Thanks Jorg, that's about how I understand it. I didn't want to get too technical and have someone try to discredit the whole story on one minute technicality.

garak

sorry, the post are not written by me, its was an post of my student (like som other post). :evil:

I'll change my pw and he must create his own login

Was stryk wrote, was an very good explain. \:D/

David_Singleton

garak wrote:

the stats are stored in tables. the name aof this tables are: Companyname$NavisionTableNo$KeyNo

For example table Value Entry:

MyCompany$5802$0 ... MyCompany$5802$XX in these tables the stats for the selectet key are storerd, also empty sift records.

to delete the records in these tables, use "delete" or, if you will not log the delete statement, use "truncate". but when you are manuell delete these entries, you must create new records :!:

If you doesn't create the stats, the perfomance of the database is bad, because the server can't make an index seek an must do an full table scan.
These cost perfomance.

Regards

Garak, are you serious ! :^o :^o

What happens if someone reads this and follows your advise.

DenSter

garak wrote:

sorry, the post are not written by me, its was an post of my student (like som other post). :evil:

I'll change my pw and he must create his own login

Was stryk wrote, was an very good explain. \:D/

Could you please edit the post and remove the false information? If anyone follows that 'advice' they could cause major issues.

David_Singleton

garak wrote:

sorry, the post are not written by me, its was an post of my student (like som other post). ...

That's a handy way to explain things away :-k