1

I have table Reg_Stats table without any indexes. Statistics are not updating automatically but I have enabled the autoupdate statistics (is_auto_create_stats_on, is_auto_update_stats_on are both are on).

My second problem: once I updated statistics manually, it is showing correctly but in execution plan it is not showing properly.

enter image description here

Improve this question
7
  • The plan you see might have been in cache before you updated statistics. There are a couple of cases where the plan is not evicted from the cache when statistics are updated. One is If you have "Auto Update Statistics = False" the other is if you have not inserted/updated anything in the table since the last time you updated statistics. (Tested on SQL Server 2014). Test it by modifying one row, update statistics and then execute the query.
    – Mikael Eriksson
    Commented Nov 19, 2015 at 7:08
  • @MIkael Eriksson I have taken execution plan after updating the statics. if i update the inserted rows then execution plan showing correctly but if i inserted new rows execution plan not showing properly and i am inserting 500+20% rows but staticcs not updating automaticaly.
    – Neminath
    Commented Nov 19, 2015 at 7:11
  • How and when autoupdate statistics kick in sounds like a different question than the one you have here is it not? The plan shows an estimate of one row from the scan operator where the manually updated statistics shows 1500 something rows. The plan is recompiled and correct when you modify the table (one row is enough) and then manually update statistics right?.
    – Mikael Eriksson
    Commented Nov 19, 2015 at 7:21
  • 1) In case of execution plan -what i am trying to say is , as you said i have updated some rows then execution plan showing correctly but when i insert fresh record execution plan not showing properly. 2) in case of statics- it is not updating until i update manualy.
    – Neminath
    Commented Nov 19, 2015 at 7:27
  • Yes, I understand that but that is not what your question is about. You asked why the execution plan was wrong. In comments you are asking when auto update statistics happens. A totally different question.
    – Mikael Eriksson
    Commented Nov 19, 2015 at 7:31

1 Answer 1

Reset to default
1

Statistics are being updated when the following number of rows have been affected in the said table : 20% * (table row count) + 500, unless the table contains less than 6 rows (in this case it is updated every time 6 rows have been affected), or less than 500 (same rule applies).

Now for the plan you displayed, it looks very weird. I can't repro it either. Can you tell us how yo updated the statistics please ?

@++ ;)

Improve this answer
1
  • I have used below 2 options to update statics 1) UPDATE STATISTICS Reg_Stats ( _WA_Sys_00000001_5B1CF803) 2) UPDATE STATISTICS Reg_Stats And 1st time i have inserted 2 rows then i have inserted 500 rows again i have inserted 500+20% rows but statics are not updating untill i do it manually.
    – Neminath
    Commented Nov 19, 2015 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.