Auto Update Statistics
-
Hi,
I've got a confusing situation here. SQL Server decided to perform an auto update just after I had done a manual update and I have no idea why.
I have built a process to perform statistics updates (FULLSCAN) based on a number of variables. This is necessary due to the fact that the auto update produces less than desireable stats on very large tables, the sample rate is far too low.
This is the situation:
My job started on Saturday evening. At 22:20 it processed a table containing 512,506,480 rows and it took 20 minutes to complete. On Sunday morning at 08:30 an auto update happened on 2 out of the 8 indexes on the table. At the time the table still contained 512,506,480 rows, so 0 inserts had occurred. We also extract data from DMV's and load a performance warehouse at regular intervals. I used that data to find out that 0 updates had occurred between Saturday evening and Sunday morning.
So in short, SQL Server decided to update a statistic that was less than 10 hours old, that had 0 inserts and had 0 updates. The new stats sampled 1,210,590 rows, a sample rate of 0.24%.
In the future I will be running these with NORECOMPUTE, but can anyone explain this behaviour.
Thanks
-
hmm ... 08:30 ... now that really would be a coincidence, wouldn't it.
Are you sure that hasn't been an sp_updatestats or so that got launched (e.g. using sqlagent) ?
Did you capture "auto update events" ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
ALZDBA (7/25/2011)
hmm ... 08:30 ... now that really would be a coincidence, wouldn't it.Are you sure that hasn't been an sp_updatestats or so that got launched (e.g. using sqlagent) ?
Did you capture "auto update events" ?
Good question, but it doesn't seem to be a scheduled update. There is no job running at that time, or any other time, performing an UPDATE STATISTICS. There are also no maintenance plans on that server.
I had a look at the jobs running at that time and there is one that kicks off at 08:30 and queries the tables concerned.
-
Sean Pearce (7/25/2011)
ALZDBA (7/25/2011)
hmm ... 08:30 ... now that really would be a coincidence, wouldn't it.Are you sure that hasn't been an sp_updatestats or so that got launched (e.g. using sqlagent) ?
Did you capture "auto update events" ?
Good question, but it doesn't seem to be a scheduled update. There is no job running at that time, or any other time, performing an UPDATE STATISTICS. There are also no maintenance plans on that server.
I had a look at the jobs running at that time and there is one that kicks off at 08:30 and queries the tables concerned.
Can you post the code that is running at 8:30?
-
Ninja's_RGR'us (7/25/2011)
Sean Pearce (7/25/2011)
ALZDBA (7/25/2011)
hmm ... 08:30 ... now that really would be a coincidence, wouldn't it.Are you sure that hasn't been an sp_updatestats or so that got launched (e.g. using sqlagent) ?
Did you capture "auto update events" ?
Good question, but it doesn't seem to be a scheduled update. There is no job running at that time, or any other time, performing an UPDATE STATISTICS. There are also no maintenance plans on that server.
I had a look at the jobs running at that time and there is one that kicks off at 08:30 and queries the tables concerned.
Can you post the code that is running at 8:30?
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECTacc.CURRENCY_CODE,
acc.COMPANY_CODE,
acc.name,
ae.*
INTO#incorrect_entries
FROMtbl_account acc WITH(NOLOCK)
INNER JOINtbl_account_entry ae WITH(NOLOCK)
ON ae.account_id = acc.id
WHEREacc.CURRENCY_CODE <> ae.CURRENCY_CODE
UPDATEtbl_account_entry
SETcurrency_code = acc_CURRENCY_CODE
FROMtbl_account_entry ae
INNER JOIN#incorrect_entries err
ON ae.id = err.id
The 3 indexes I'm having a problem with are on tbl_account (1) and tbl_account_entry (2).
-
How many rows are getting updated by this?
This doesn't sound like you're not changing anything...
-
Can you also elaborate on the indexes and the stats for which you noticed the auto update ?
(ddl / stats columns ?)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Sean Pearce (7/25/2011)
Ninja's_RGR'us (7/25/2011)
How many rows are getting updated by this?This doesn't sound like you're not changing anything...
The query is to fix incorrect entries, and on this occassion there were no incorrect entries found.
I would guess that it might be possible that the update stats step is saved in the query plan (which should be reused at every run). I have no idea how to check or test for that other than a full trace and re-run the query.
-
ALZDBA (7/25/2011)
Can you also elaborate on the indexes and the stats for which you noticed the auto update ?(ddl / stats columns ?)
dbo.TBL_ACCOUNT
_WA_Sys_TYPE_ID_078C1F06
TYPE_ID, ID
CREATE NONCLUSTERED INDEX [IX1]ON [TBL_ACCOUNT_ENTRY]([TRANSACTION_ID],[CURRENCY_CODE])
INCLUDE ([ID],[VERSION],[AMOUNT],[DATE],[DRCR],[DESCRIPTION],[VALUE_DATE],[ACCOUNT_ID],[OP_ENTRY_ID],[TYPE_ID])
CREATE NONCLUSTERED INDEX [IX2]
ON [TBL_ACCOUNT_ENTRY] ([TRANSACTION_ID],[ACCOUNT_ID])
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply