sp_recpompile after UPDATE Statistics?

Question

sp_recpompile after UPDATE Statistics?

BrerSQL

SSCommitted

Points: 1672
More actions
December 19, 2008 at 10:22 am

#216734

Simple question which I thought would be straight forward.
Is it necessary to perform an sp_recompile after performing batch UPDATE STATISTICS operations.
I am old school and I have always performed sp_recompile's after my update statistics operations....habit from my Sybase days.
I have read that this is no longer necessary but yet I still persist to do so.
I have recently taken over responsibilities for a new server and have found that on Monday's certain procedures need to be recompiled due to poor performing queries. Low and behold, I find that they are performing their UPDATE STATISTICS operations over the weekend. This seems awfully suspicious to me.
I have scoured the WEB but I have not found and definitive answer to this question.
I am about to go OLD SCHOOL and implement sp_recompiles but I thought I would pose the question to see if anyone can help.
Things to note, auto create statistics is ON but auto update statistics and update statistics asynchronously is off.
thank you in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 1

If running sp_recompile corrects the problem, I would hazard a guess that they are doing something else significant after the stats are updated. Those changes won't cause the stats to rebuild because auto-update is off, hence the optimiser generates bad plans. Easy way to check is to pull off the execution plans for some of the queries and look especially for cardinality differences in estimated and actual rows.

BrerSQL SSCommitted Points: 1672 More actions · Answer 2

ok, then you are stating that an sp_recompile is NOT needed after the batch 'Update Statistics'.

That makes sense since there is no major data manipulation going on that would cause a plan to be recompiled...only statistics being updated.

But that still does not answer the core question...If statistics are updated in a batch mode like this, is a sp_recompile required in order to ensure that you have the most up to date query plans?

Also....

I dug a little deeper and found that they are also performing index defrags:

...SELECT @sql = 'ALTER INDEX ' + @indexname + ' ON ' + @table + ' REORGANIZE'

exec (@sql) ...

On Saturday they perform defrags on selective tables.

On Sunday they perform DBCC CHECKDB and Update Statistics.

Could a defrag require a sp_recompile?

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 3

No, sp_recompile should never be required after stats are updated or after a defrag. SQL should automatically detect when stats are stale and recompile as needed, and the defrag only affects the location of the data which is irrelevant to the compiler.

If you are sure that there are no data changes that could cause this, you probably need to compare execution plans before and after.

noeld SSC Guru Points: 96590 More actions · Answer 4

Also UPDATE STATISTICS on very large tables could be a double-egde sword if there is data skewness ... You may want do a FULLSCAN for those cases.

* Noel

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Eric Butler (12/19/2008)
Simple question which I thought would be straight forward.
Is it necessary to perform an sp_recompile after performing batch UPDATE STATISTICS operations.

No. A stats update invalidates all plans based on that table. The proc will recompile on its next run.

I have recently taken over responsibilities for a new server and have found that on Monday's certain procedures need to be recompiled due to poor performing queries.

It could be parameter sniffing. If the first run is with non-standard parameters it could cause the problem. Can you post one of the procs and (perhaps) both the bad exec plan and the new one after the recompile.

You can get the plans from one of the DMVs

select * from sys.dm_exec_query_stats qs cross apply sys.dm_exec_query_plan(qs.plan_handle)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

BrerSQL SSCommitted Points: 1672 More actions · Answer 6

That was exactly where I was headed next.

Thank you for this information.

I will abandon my fruitless plans of performing the sp_recompile's.

It may take some time to gather up some examples but I will post it on this link once I get it.

thanks again for the replies

BrerSQL SSCommitted Points: 1672 More actions · Answer 7

thank you Gail...you were spot on correct.

Parameter sniffing was the problem.

I re-wrote the procedures and we have not seen the problem since.