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.
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?
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.
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.
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.
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.
@ 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.
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.
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.
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.
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.
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.
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.
Comments
- 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 ... .
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
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
If not, there is a script to do it table per table... somewhere on this forum.
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
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.
I mean, that must be perfomanter for analyses on this tables, because the server must not read so many pages.
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
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?
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.
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
@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
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.
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:
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.
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.
RIS Plus, LLC
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.
RIS Plus, LLC
I'll keep this thread in mind (off course), but I doubt that I'll ever turn it on.
Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
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.
RIS Plus, LLC
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
This will grant to have those statsistics the SQL Server really needs - no more, no less.
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool
I would never touch those tables. :evil:
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.
=D>
Thanks for explaining WHY.
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.
RIS Plus, LLC
RIS Plus, LLC
I'll change my pw and he must create his own login
Was stryk wrote, was an very good explain. \:D/
Garak, are you serious ! :^o :^o
What happens if someone reads this and follows your advise.
RIS Plus, LLC
That's a handy way to explain things away :-k