does auto update stats ?
-
I have a question about the auto create stats and auto update stats option on a user database. I have both turned on - I manually run an update stats at regular intervals.
Should I be able to see that the auto update stats is actually working ? How can I tell if these two options actually do what I expect them to do?
SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = 'mytable' AND o.id = i.id
The above script will show the info I'm interested in for a particular table - during the working 24 hour period shouldn't I see evidence in the shown data of the stats being updated?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Create stats occurrs based on the significant number of times a column appears in submitted queries sorry don't have the threshold.
Update stats is based on the number of data changes that occurr it is based on a percentage of change but again I don't have that right here and cannot remmber right off but I have been in several discussion in the past about this on the forums for this site.
-
I did have a search before posting the question .. think I've probably answered my own question by further research across a number of my servers and databases, I might have an additional issue of an update stats ( possibly in one of my least liked methods - a sysmaint plan ) not actually updating or running. I had problems initially finding any updated stats in the last week on a couple of prod databases.
Certainly the auto update does not work quite as often as I might have expected
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
The basic algorithm for auto update statistics is:
- If the row for a table is less than six and the table is in the tempdb database, auto update with every six modifications to the table.
- If the row for a table is greater than 6, but less than or equal to 500, update status every 500 modifications.
- If the row for a table is greater than 500, update statistics when (500 + 20 percent of the table) changes have occurred.
For table variables, row changes does not trigger auto update statistics.
-
Interesting, many thanks.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
i want to ask ..
what is best ... to check auto update statistics in DB properties .. or to make statistices update by yourself (by maintance plan for example) ?
Alamir Mohamed
Alamir_mohamed@yahoo.com -
I have always updated stats as a scheduled job on production databases every night, using sp_updatestats, as part of general housekeeping. I was prompted into my initial post as I'd not re-enabled the update stats job on a database after a software release and was interested in the various results shown by looking at the stats. ( the database has auto create and auto update stats enabled )
I usually include stat updates within etl routines having encountered various performance problems over the years.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I always manually update the stats on the production servers. I do not want SQL Server deciding to add to the overhead, I have enough problems.
Quand on parle du loup, on en voit la queue
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply