Online Tutorials & Training Materials | STechies.com
Register Login

Update Statistics on MS SQL Server

|| 0

Update Statistics on MS SQL Server
Stechies

 

If anyone has had problems with the Auto Update Statistics on SQL Server like we have, I've come up with a solution (other than running it manually)

Here is an SP that you can implement that will generate a temp table and populate it with tables that have not had updated stats and have had more than 500 000 rows updated.

**********************************************************************

USE [***DBName***]
GO
/****** Object:  StoredProcedure [dbo].[usp_RefreshStats]    Script Date: 05/30/2008 13:03:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_RefreshStats] @Freq char(1)= 'W'
AS
BEGIN
 
SET NOCOUNT ON;
declare @cnt int
declare @max int
declare @tsql varchar(400)
declare @tblName varchar (200)
declare @Frequancy varchar(10)

Create Table #t (id int identity ,TableName varchar(200))


 Insert into #t(TableName)
 select distinct left(name,charindex('~',name)-1) name
 from ED1.dbo.sysindexes
 where name like '%~%' and rowmodctr > '500000'
 order by name

 
   set @cnt = 1
   select @max = max(id) from #t

WHILE @cnt <= @max

BEGIN
    Select @tblName = TableName from #t where id=@cnt

-- modify this line here to change exactly WHAT you want to run

    Set @tsql = 'UPDATE STATISTICS ED1.dbo.' + @tblName + ' with sample 13 percent '

-- remove "--" from Print to view what it's doing and insert "--" before Exec to stop it from running against the database.
     --print @tsql
     Exec(@tsql)
 
    set @cnt=@cnt +1

END
 
END

***********************************************************************

 

Hope this helps you as much as it's helped us!!

Please feel free to drop me a line if there is anything that I can help with :-)

 


Related Articles