none
AUTO UPDATE STATS on temp tables RRS feed

  • Question

  • Hi All,

    We've lot of real time complex reports running on the replication of the production database.
    The Stored Procs used in the reports creates some temp tables and plays around with those.

    When I analyzed, I found that there are plenty of UPDATE STATS events against those temp tables. Also, these SPs are among the top ones in the SP:Recompile.

    Need help in the followings.

    1. How to avoid update stats on temp tables? I'm not comfortable changing Auto Update Stats to False for tempdb, is it OK to do this with tempdb?

    2. What can I do to reduce the recompilation of these SPs?

    Any help in this regard will be helpful.

    Thanks & Regards
    Mayur Kashikar

    Tuesday, October 9, 2012 1:02 PM

Answers

All replies

  • Its not a good idea to change the auto update stats  option of tempdb to false. So the question to how to disable a stats update on some tables. You can use table variables instead of #table. table variables does not have stats.. or convert your #tabel to user table and use sp-autostats sp to disable stats update or rewrite your code to avoid #tables.. 

    if multiple people access the same report at the same time then 2nd option will not be appropriate..also disabling stats update effect the cardinality estimation hence it will affect the query plan.. 

    Please read

     http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker




    • Edited by SimpleSQL Tuesday, October 9, 2012 1:41 PM
    Tuesday, October 9, 2012 1:17 PM
  • Instead of temp tables use #temp tables which is a temp table and drops after the session is closed.

    SQL Champ
    Database Consultants NY

    Wednesday, October 10, 2012 3:26 PM
  • Hi Mayur Kashikar,

                                 1.) NORECOMPUTE  - http://msdn.microsoft.com/en-us/library/ms187348.aspx

    Disable the automatic statistics update option, AUTO_UPDATE_STATISTICS, for the specified statistics. If this option is specified, the query optimizer completes this statistics update and disables future updates.

    Also ,you can try :

    SP_AUTOSTATS 'temp_table_name', 'OFF'

      For more information on statistics - http://sathyadb.blogspot.in/2012/08/sql-server-statistics.html

                                   2.) Use KEEPFIXED PLAN to avoid SP recompilation - http://support.microsoft.com/kb/276220

       Hope below links will help you more on understanding re-compilation 

                http://msdn.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

                http://msdn.microsoft.com/en-us/library/ms190439.aspx

                       Hope your problem will be solved :)

    Thanks & Regards,

    Sathya


    sathyas


    Wednesday, October 10, 2012 4:49 PM
    Moderator
  • Thanks Sathya!

    I also came across the same thing and implemented the KEEPFIXED PLAN option. This has certainly solved my problem of auto update stats of temp tables. However, now I have noticed another issue, which I'm not sure is a side effect of this or not but happened only post this implementation! The issue is that the Page Life Expectancy (PLE) has dropped drastically post the implementation of KEEPFIXED PLAN in my 4 such SPs!!! Till now I couldn't find if the drop in PLE is related to KEEPFIXED PLAN but this has happened only post applying this hint.

    Regards
    Mayur Kashikar

    Friday, October 12, 2012 8:23 PM
  • Hi Mayur Kashikar,

    Page Life Expectancy is number of seconds a page will stay in the buffer pool without references.

    --To find PLE value for an object

    SELECT [object_name], [counter_name], [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%objectname%' AND [counter_name] = 'Page life expectancy'

    I found similar discussion on drop in Page Life Expectancy (PLE) - http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/71814032-cd8d-4802-80de-7fb2bee80f41

    memory pressure, missing indexes, or a cache flush could be the factor which might affect Page Life Expectancy (PLE)

    Hope you can get it out with info provided :)

    Thanks,

    Sathya


    sathyas

    • Marked as answer by Mayur Kashikar Tuesday, October 16, 2012 6:09 PM
    Saturday, October 13, 2012 2:40 AM
    Moderator
  • Thanks Sathya!

    I think, the MSDN article you've shared should solve my problem. It talks about the physical reads happening on the server. This sounds sensible, as the PLE is very likely to drop if the physical reads increase. I'm analyzing the data further to make life better for the server and myself...:) I'm marking the KEEPFIXED PLAN suggestion as an answer to the situation I started this thread for. Thanks again...

    Tuesday, October 16, 2012 6:08 PM