statistics

Last Post 19 Sep 2008 11:53 AM by TRACEYSQL. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
TRACEYSQL
New Member
New Member

--
18 Sep 2008 08:21 AM
I am running this procedure on database


create PROCEDURE dbo.SP_UpdateStatsALL
AS

Set Nocount on
Declare db Cursor For
Select name from master.dbo.sysdatabases where name = 'APPLDB'
--not in ('master','TempDB', 'msdb', 'model')

Declare @dbname varchar(60)
Declare @execmd nvarchar(150)

Open db
Fetch Next from db into @dbname
While @@Fetch_status=0
begin
if @dbname is null
Begin
Print 'null Value'
end
else
Begin
PRINT '###########################################################################'
PRINT 'Update Statistics in ' + @dbname
SELECT @execmd = 'USE ' + @dbname + ' EXEC sp_updatestats'
EXEC(@execmd)
PRINT ''
End
Fetch Next from db into @dbname
end
Close db
Deallocate db

GO


When i go check

SELECT object_name(i.object_id) as Table_Name
, i.name as Index_Name
, i.index_id, o.name
, STATS_DATE(i.object_id, i.index_id) as Statistics_Date
FROM sys.objects o
INNER JOIN sys.indexes i
ON o.object_id = i.object_id
order by Table_Name, i.index_id ;
GO

(Some of the statistics dates are not updated).


Any suggestions.

Should i just use UPDATE STATISTICS
TRACEYSQL
New Member
New Member

--
19 Sep 2008 09:03 AM
Thanks - Once question when you do a index rebuild this actually does update the statitistics.

The question is i cannot use the update statistics with FULLSCAN takes way too long on server.

With sp_updatestats (what is the percentage it uses).
When the reindex runs what % is this using.

How do you pick a good % to run and monitor if this is adequate.

I do not want to run the update statistics 'table' with sample xx percentage and just undo everything that the reindex just did or is it better to run sp_udpatestats.

The reindex will only do if frag > 30% which means some tables may not be fragmentated but statistics are out.

Cheers
TRACEYSQL
New Member
New Member

--
19 Sep 2008 11:53 AM
I think i got this to the nail.

Index - yes does do the full scan.

So its only basically during the day if there a lot of inserts/updates going on would you really need to do an UPDATES STATISICS WITH FULL .

Hmm perhaps i write a procedure that looks at the last stats updates and do FULL SCAN on those tables during the day.


Now my reindex rebuild is
EXEC ('ALTER INDEX ' + @indname + ' ON ' + @tabname + ' REBUILD WITH(PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON)')

The STATISTICS_NORECOMPUTE IS OFF -- When i read this it talking about Distribution statistics.
Is this STATICTICS or another set of statistics - shouldn't i have this ON for the reindex rebuild to go
update all the statistics.



STATISTICS_NORECOMPUTE = { ON | OFF }
Specifies whether distribution statistics are recomputed. The default is OFF.

ON
Out-of-date statistics are not automatically recomputed.

OFF
Automatic statistics updating are enabled.

To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or execute UPDATE STATISTICS without the NORECOMPUTE clause.

You are not authorized to post a reply.

Acceptable Use Policy