RS First Dynamics NAV Blog


...from NAV 3.60 to NAV 2013
Archivio Posts
Anno 2015

Anno 2014

Anno 2013

Anno 2012

Anno 2011

Anno 2010

Anno 2009

Anno 2008

Anno 2007

SQL CLEAN AUTO STATS & CREATE NEW

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 ...

exec sp_updatestats
go

Create Stats
exec sp_createstats 'indexonly'
go

CLEAN AUTO STATS
How to clean Auto Stats
(KILL ALL STATISTICS)

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

AFTER > Scheduled Job
sp_updatestats and sp_createstats 'indexonly' (as posted previously)

fonte Mibuso

Categoria: Sql Server ALL
mercoledì, 21 set 2011 Ore. 09.48

Messaggi collegati


Statistiche
  • Views Home Page: 344.110
  • Views Posts: 722.013
  • Views Gallerie: 0
  • n° Posts: 345
  • n° Commenti: 0
Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003