dynamicsusers: Index Statistics

[Blog bot]

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 …



Подробнее... http://dynamicsuser.net/blogs/stryk/archiv...statistics.aspx