Hi Folks,

First off all, sorry for my English. I hope you understand me.

We always heard that the database statistics must be constantly updated to the Query Optimizer generate the best execution plan for our querys. For this, we should enable the ‘Auto Update Statistics’ option in our databases.

However, with this option enabled if our table has more than 500 rows (almost our table have), the statistics in this table will be updated only when we have “500 + 20% of the table size” of changes in the table. The worst is that can happen in a wrong time and create more cost to your production environment.

In my Job, I have a table that is widely used and it has 70 million of rows, If I wait for the statistics to be updated automatically (ignoring the index statistics that are updated with a REBUILD for example), my statistics would be updated when I reach “500 + 20% * 70,000,000 = 14,000,500” of changes. In my case, it would take a very long time and querys with bad plans could be generated.

So, with the objective to minimize this problems, I decided to update my statistics when I reach 0.5% of changes on my table (and not 20% as is the default). I set this value because the UPDATE STATISTICS WITH FULLSCAN operation is very expensive and I don’t have a window to update all my statistics daily. Then, my table will be an update statistics when I get “70,000,000 * 0,005 = 350,000” of changes.

As I already have a window to indexes maintenance and dozens of routines running at dawn, I get in the VPN at 22:40 and I ran an UPDATE STATISTICS at this time to analyze the impact it would have in my database that is 7 x 24. So, I decided to use a window 22:40 to 23:50 to perform this procedure.

In each database that I want to run the update statistics, I created the procedure below. Then I did a job that runs daily at 22:40 with one step for each database.

CREATE PROCEDURE [dbo].[stpUpdate_Stats] As
BEGIN

SET NOCOUNT ON

— To go out the routine when the window finish
IF GETDATE()> dateadd(mi,+50,dateadd(hh,+23,cast(floor(cast(getdate()as float))as datetime)))– Time > 23:50
RETURN

Create table #Update_Stats(
Id_Stats int identity(1,1),
Ds_Comand varchar(4000),
Nr_Rows int)

;WITH Size_Tables AS (
SELECT obj.name, prt.rows
FROM sys.objects obj
JOIN sys.indexes idx on obj.object_id= idx.object_id
JOIN sys.partitions prt on obj.object_id= prt.object_id
JOIN sys.allocation_units alloc on alloc.container_id= prt.partition_id
WHERE obj.type= ‘U’ AND idx.index_id IN (0, 1)and prt.rows> 1000
GROUP BY obj.name, prt.rows)

insert into #Update_Stats(Ds_Comand,Nr_Rows)
SELECT ‘UPDATE STATISTICS ‘ + B.name+ ‘ ‘ + A.name+ ‘ WITH FULLSCAN’, D.rows
FROM sys.stats A
join sys.sysobjects B on A.object_id = B.id
join sys.sysindexes C on C.id = B.id and A.name= C.Name
JOIN Size_Tables D on  B.name= D.Name
WHERE  C.rowmodctr > 100
and C.rowmodctr> D.rows*.005
and substring( B.name,1,3) not in (‘sys’,’dtp’)
ORDER BY D.rows

declare @Loop int, @Comand nvarchar(4000)
set @Loop = 1

while exists(select top 1 null from #Update_Stats)
begin
IF GETDATE()> dateadd(mi,+50,dateadd(hh,+23,cast(floor(cast(getdate()as float))as datetime)))– Time > 23:50
BEGIN
BREAK — To go out the routine when the window finish
END

select @Comand = Ds_Comand
from #Update_Stats
where Id_Stats = @Loop

EXECUTE sp_executesql @Comand

delete from #Update_Stats
where Id_Stats = @Loop

set @Loop= @Loop + 1
end
END

If someone has another suggestions for an update statistics routine, please feel free to leave a comment.

Remember, if you have a large window, you can update all statistics daily and the Query Optimizer will thank you a lot.

Enjoy,

Fabrício França Lima

MCITP – Database Administrator