SQL Server Statistics

Business Central/NAV Technical Forum
1

Hi,

We have been looking at doing some performance tuning on SQL Server and have had a play with the Tuning Engine in SQL Server 2005 in our test environment. We ran the trace for tuning and put the workload to the test and it suggested the creation of a load of new statistics and one or two keys, which the developers can now evaluate. We did have autstats on for the live environment and it seems to have created a load of statistics which may not be relevant at all. A few questions now which various bit of reference material (e.g. http://www.developer.com/db/article.php/3622881) have not been able to clear up, which hopefully one of the experts here can help with.

If we turn off autostats we will have a step in our maintenance plan to update statistics every night but…

Will we need to run the tuninig engine from time to time to identify stats that are missing, the maintenance plan seems to only update existing stats?

How do we get rid of rubbish stats that exist in our DB as a result of having had autostats on? DBCC SHOW_STATISTICS as a manual job on each table would take ages, does anybody have a script that identifies and removes useless stats or is it irrelevant anyway if autostats are off?

Regards

Meint

2

Hi Meint,

Well, the “Auto. Create Stats” and also “Auto. Update Stats” could be a real pain; I recommend to disable it. THe sufficiently maintain the required statistics you could implement a SQL Agent Job (or TSQL task for the MP) doing this:

use [MyNAVdb]
go
exec sp_updatestats
go
exec sp_createstats ‘indexonly’

To get rid of the already existing auto-stats, you could exec this script (part of the NAV/SQL Performance Toolbox)

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

(Afterwards you have to run the sp_updatestats/sp_createstats thing)

This would update the existing stats and create missing index statistics. Rgarding the “tuning engine”: I do not recommend to use it, as the recommendations are oftenly somewhat “starnge” and actually sub-optimal. I recommend to monitor the system frequently with the SQL Profiler to learn about “bad queries” and fix those individually …

Regards,

Jörg

3

Hi Jörg,

Many thanks for that, that fills in the gaps very nicely. I have done quite a lot of research on this now and it seems that the general SQL Server community recommendations don’t always agree with the views of the Navision community, e.g. the use of autostats seems to generate quite a lot of debate. Unless we find any real reason to disagree we will stick with what seems to work for the people in the Navision field.

Regards

Meint

4

Well, the issue is, that the “general SQL Server commuity” hardly knows about the strange (stupid?) things NAV does with the SQL Server [6]

I’d like to give you some “real” reasons for disabling this “Auto. Stats” stuff:

With “Auto. Create Stats” SQL Server will - sooner or later -create a statsistic for every single field/column in a table, regardless if it is part of an index or not. This is due to the matter, that C/SIDE always queries a “SELECT *” (all) and gives the user tha capability to filter on any field of this table.

This generates a remarkable overhead, getting worse when these statistics are updated due to “Auto. Update Stats”. With this option SQL Server decides when to update which could mean never or at a time when it interferses with user transactions. The more records are in a table, the longer takes the update, the worse gets the performance.

Thus, creation and update of stats is costly, and actually you want to spend these “costs” outside the running business.

But its getting worse: In SQL Server there can be 250 related objects per table (as indexes, constraints, … and stats). With “Auto. Create Stats” it is possible to reach this limit - especially on wide tables (= many fields) with lots of indexes. If this happens - I encountered this already on a customer’s site - you cannot create another index on this table - neither from C/SIDE nor SQL site -; an error will be raised!

Also, if you try to delete a field from a table (after “emptying” the values) you could encounter a SQL error, telling there are “related objects” to this column, as C/SIDE is not able to drop these statistics automatically.

This is why I recommend to disable the “Auto. Stats” features, as they sooner or later will cause trouble.

P.S.: An exception is when importing lots of data into a table (e.g. data migration). Oftenly there is a mixture of importing data and business logic based on this data. There it might be feasible to enable the “Auto. Stats”. But after data-migration it shoulb be disabled again; and the stats being cleaned up.

5

Something I would like to add:

In SQL server 2k5, if you create statistics, and it is kept on index which has a field that you then want to delete, you have to delete the statistics as well as all of the data in the object.

A little off topic, but I couldn’t find another good place to post this…

6

I know it’s not a very new post, but I would like to know if the same still applies when we talk NAV 2009 R2 and SQL Server 2008?