NAV/SQL Performance - My Two Cents

Jörg Stryk (MVP - Dynamics NAV) about Performance Optimization & Troubleshooting with MS Dynamics NAV and MS SQL Server

Well, we all know that "Index Statistics" are crucial for optimized query processing in SQL Server, that we shouldn't use "Auto. Create Stats" or "Auto. Update Stats", that we should use "Jobs" or "Maintenance Plans" to keep them up to date, etc. – but sometimes it is not that easy …

 

Preface

"Index Statistics" – short "stats" – contain statistical information about field values, precisely their "density", which simplified tells how many different values per field are available (check with DBCC SHOW_STATISTICS). These stats are mandatory to process query optimal, as they are used to choose the best index for a query. Missing or insufficient stats will lead to wrong "decisions" of the SQL engine (Query Optimizer), actually resulting in poor performance.

Statistics could be generated on every field (or combination of fields), but the important ones are those related to indexes.

 

Out of the Box

… the DB properties "Auto. Create Stats" and "Auto. Update Stats" are enabled. Thus, SQL Server generates the statistics which it "thinks" are necessary – and updates them whenever it "thinks" it is necessary. This means, that one could be never sure, if indeed all required stats are available, and they are sufficiently maintained.

As in NAV it possible for every user to apply filters on almost every table field, and due to the fact that NAV mostly send SELECT * (asterisk = all fields) SQL Server will sooner or later automatically create a statistic for nearly every column in a table! Hence, during time a remarkable overhead of stats is generated, and as the stats are also automatically updated write transaction would perform slower day by day.

 

What we want

To get the stats we – actually the Db – want to have is sufficient index stats, where we know what is generated and when! Thus, the first step is to disable the "Auto." stuff in the DB options. The second step is to clean up the previously created auto stats.

List auto stats:

select [id], [object_name] = object_name([id]), [name] from sysindexes

where (indexproperty([id], [name], N'IsStatistics') = 1)

and (indexproperty([id], [name], N'IsAutoStatistics') = 1) -- Filter on Auto. Stats

and (isnull(objectproperty([id], N'IsUserTable'),0) = 1)

order by object_name([id])

 

This will display a list of stats named _WA_Sys_... .

Drop auto stats:

 

set statistics io off

set nocount off

 

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)

and (indexproperty([id], [name], N'IsAutoStatistics') = 1) -- Filter on Auto. Stats

and (isnull(objectproperty([id], N'IsUserTable'),0) = 1)

order by object_name([id])

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

 

The third step is to generate the required index stats:

exec sp_updatestats

go

exec sp_createstats 'indexonly'

 

This will generate/update only statistics related to indexes and columns which are part of an index! Ideally, these two procedures should be executed daily – plus a weekly full statistics rebuild, by e.g. using the "Update Statistics" task of the "Maintenance Plan" feature. Display stats with

select [id], [object_name] = object_name([id]), [name] from sysindexes

where (indexproperty([id], [name], N'IsStatistics') = 1)

and (indexproperty([id], [name], N'IsAutoStatistics') = 0) -- Filter on User Stats

and (isnull(objectproperty([id], N'IsUserTable'),0) = 1)

order by object_name([id])

 

The statistics are named like the corresponding index (e.g. "$1") or the index column (e.g. "Item No_").

 

So far, so good – so what?

Well, actually now we have the stats we want and need to have. But these stats could also cause trouble – in very few cases: In NAV (C/SIDE Object Designer), if you change the definition of a field which is part of an index – thus we have our stats on it -, e.g. changing the name or data-type (also property "SQL DataType"), an error will raised, telling that the ALTER TABLE ALTER COLUMN command failed due to a related statistic.

This happens, because neither SQL nor C/SIDE are dropping those "user statistics" before altering the column in SQL – only "auto. stats" would be automatically deleted (refer to "Books Online" about ALTER TABLE … ALTER COLUMN)!

Hence, if this happens it is necessary to remove the "user stats" before altering the objects :

set statistics io off

set nocount off

 

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)

and (indexproperty([id], [name], N'IsAutoStatistics') = 0) -- Filter on User Stats

and (isnull(objectproperty([id], N'IsUserTable'),0) = 1)

order by object_name([id])

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, the required stats have to be re-created using the sp_createstats 'indexonly' procedure.

 

Again, this should happen rarely, as you won't change data type or index columns frequently …