Auto update statistics

garak

I've modify the post and remove the false information.

Waldo

What false information?

Thanks, Stryk, for the informative post.
It was more or less the reasoning I was following, allthough I didn't know it could result into errors (never encountered it).
I did delete the already created auto stats, and set up the daily statitistic update exactly the way you mentioned ... .
So, nice to get confirmation .

One question though. Why the "Auto. Update Stats Async" = ON ?

kriki

[Topic moved from SQL General forum to SQL Performance forum]

colin_leversuch-roberts

you see the worry I have is that already there are a number of myths within your posts - auto stats work at column level, not table level. Auto update happens at predefined times as well documented by microsoft, sorry don't have the link to hand, you can still create the stats and update them aschronously. Sadly the conclusion that Navision is so different is way off the mark - most business applications don't come from a sql server and usually not a rdbms background, I've been tuning third party applications for 14 years on sql server and come what may the basic principles are still the same be it ERP, CRM, financials, trading systems etc. etc.
It's interesting though and I find it quite fascinating.

colin_leversuch-roberts

Just as an update to auto update and auto create stats being enabled.
As I am used to working in highly controlled environments all changes have to be tested and monitored. Since I turned on auto create stats just over 1000 have been created on my navision database, 900 odd were created the first day. My nightly job to update stats shows no change to run time ( between 40 and 55 mins ), profiling shows no eveidence of auto update stats or create stats events, except in tempdb, of any significance.
I should point out that I always apply a blanket update stats; what might not be known is this command only updates those stats that need updating; so no extra overhead.
I'm also monitoring the procedure cache to check for cached plans for auto stats - these would be present if frequent events were occurring - I've not found any yet.
You might want to add to your maintenence, dbcc updateusage(0), this is also an important command to help the optimiser.
The results I've had back show no adverse effects, and although it's truly subjective, users have been asking what we've done to the system because it appears to be running so much better - however as I say this is always subjective and I suspect holidays have probably lowered numbers of users. Database throughput, transactions/sec, seem to be about the same as before - oh yes I monitor my server and database absolutley, I have hourly stats for every working day and a third party monitoring tool which does similar - so I can truly see how my navision database engine is working, or not, maybe you can all do the same, yes?

stryk

How to clean Auto. Stats?

Well, this is the part of some TSQL how I do it:
Display Stats:

select [id], object_name([id]) as [tabname], [name], isnull(indexproperty(id, name, N'IsStatistics'),0) as [stats], isnull(indexproperty(id, name, N'IsAutoStatistics'),0) as [auto]
    from sysindexes 
    where ((indexproperty(id, name, N'IsStatistics') = 1) or (indexproperty(id, name, N'IsAutoStatistics') = 1)) and (isnull(objectproperty([id], N'IsUserTable'),0) = 1)

Delete Stats:

declare @id int, @name varchar(128), @statement nvarchar(1000)
declare stat_cur cursor fast_forward for select [id], [name] from sysindexes where ((indexproperty(id, name, N'IsStatistics') = 1) or (indexproperty(id, name, N'IsAutoStatistics') = 1)) and (isnull(objectproperty([id], N'IsUserTable'),0) = 1)
open stat_cur
fetch next from stat_cur into @id, @name
  while @@fetch_status = 0 begin         
    set @statement = 'DROP STATISTICS [' + object_name(@id) + '].[' + @name + ']'    
    begin transaction
      --print @statement
      exec sp_executesql @statement
    commit transaction
    fetch next from stat_cur into @id, @name
  end
close stat_cur
deallocate stat_cur

This kills ALL statistics! Afterwards I run a sp_updatestats and sp_createstats 'indexonly' (as posted previously).

Why "Auto. Update Stats. Async"?

Well, I was told this is a "smoother" algorithm than "Auto. Update Stats.". But reading some stuff on MSDN I have to admit this is crap :oops:
See http://msdn2.microsoft.com/en-us/library/ms188124.aspx
So I revise my statement and would then recommend:

"Auto. Update Stats Async" = OFF

Marije_Brummel

colin leversuch-roberts wrote:

You might want to add to your maintenence, dbcc updateusage(0), this is also an important command to help the optimiser.

Is your database a SQL2000 or SQL2005?

I am always into learning new stuff but this is what Technet sais:

DBCC UPDATEUSAGE (Transact-SQL)

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure. In SQL Server 2005, these values are always maintained correctly. Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 may contain invalid counts. We recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.

Marije_Brummel

colin leversuch-roberts wrote:

I'm also monitoring the procedure cache to check for cached plans for auto stats - these would be present if frequent events were occurring - I've not found any yet.

Can you please elaborate more on this topic? How can you get usefull information from this for Navision?

We have seen major issues which seem to be related to procedure cache but are difficult to solve or even explain.

Thanx.

colin_leversuch-roberts

This is specifically sql server 2005 32bit enterprise edition. I have a series of queries I use to monitor sql server, some of these are written into reporting services, for sql 2000 I usually write reports into html pages using web assistant. I can post code - what exactly are you interested in and sql 2000 or sql 2005 as queries are different? I've been digging into the internals of sql server since version 6.0 so I've accumulated a stack of information - I've also have the benefit of training from the likes of Kalen Delaney, Kimberley Tripp and other sql guru's
There's much much more data available from within sql 2005. I also use a tool called sql diagnostic manager http://www.idera.com/Products/SQLdm/?S= ... EAodtjB7SQ

you can get a free download which is fully operational for about 14 days