none
Every time i create database, do i need to update statitics?

    Pergunta

  • Hi,

    Every time i created database, we observed the queries slow down. When we update stats for all tables, it works fine.

    It has auto update stats:true

    But unless untill we explicitly execute update stats, the database wont work fine. Why this weired behaviour by database.

    Regards,

    Navin


    Navin.D http://dnavin.wordpress.com

    segunda-feira, 4 de junho de 2012 15:40

Respostas

  • When you just created a database is has no tables and no data. So something was done between the creation of the database and when you experience the slowness. I.e., without knowing more about your situation, we can't comment.

    Also, are you saying that it is enough with one update of statistics and the database is forever "fast" from that? If so, you have found a very simple solution to your problem.

    If you need to run regular update statistics, then you probably have data skew meaning auto-update statistics isn't enough, you need to schedule a regular job for updating statistics.


    Tibor Karaszi, SQL Server MVP | web | blog

    segunda-feira, 4 de junho de 2012 16:24
    Moderador
  • Every time i created database, we observed the queries slow down. When we update stats for all tables, it works fine.

    It has auto update stats:true

    But unless untill we explicitly execute update stats, the database wont work fine. Why this weired behaviour by database.

    >>It might be reason that improper  optmization job(reindex/update statisits) set your applations against database if the database is small size then it wont cause anything to turn on the update statisitics how ever incase if the large DML runs against on the objects and you do not have the proper optimization job then it may cause the performance issue.

    (even leaving the auto update stats set to true is an good option how ever it will work certain criteria set,but if the table/database grows in size and number of DML operation gets huge then it might cause performance degradtion when if the auto stats set true against the database).

    as in your case you said that "But unless untill we explicitly execute update stats, the database wont work fine. Why this weired behaviour by database".

    ->it seems that DML operations performing frequently, tha caused the issue to query to get slowness, you can do like this you can update the statisics for the selected tables during the non business hours everyday) then check the performance.

    schedule an weekly rebuild index job to ensure about the optmization improtance..


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    terça-feira, 5 de junho de 2012 10:51

Todas as Respostas

  • When you just created a database is has no tables and no data. So something was done between the creation of the database and when you experience the slowness. I.e., without knowing more about your situation, we can't comment.

    Also, are you saying that it is enough with one update of statistics and the database is forever "fast" from that? If so, you have found a very simple solution to your problem.

    If you need to run regular update statistics, then you probably have data skew meaning auto-update statistics isn't enough, you need to schedule a regular job for updating statistics.


    Tibor Karaszi, SQL Server MVP | web | blog

    segunda-feira, 4 de junho de 2012 16:24
    Moderador
  • Every time i created database, we observed the queries slow down. When we update stats for all tables, it works fine.

    It has auto update stats:true

    But unless untill we explicitly execute update stats, the database wont work fine. Why this weired behaviour by database.

    >>It might be reason that improper  optmization job(reindex/update statisits) set your applations against database if the database is small size then it wont cause anything to turn on the update statisitics how ever incase if the large DML runs against on the objects and you do not have the proper optimization job then it may cause the performance issue.

    (even leaving the auto update stats set to true is an good option how ever it will work certain criteria set,but if the table/database grows in size and number of DML operation gets huge then it might cause performance degradtion when if the auto stats set true against the database).

    as in your case you said that "But unless untill we explicitly execute update stats, the database wont work fine. Why this weired behaviour by database".

    ->it seems that DML operations performing frequently, tha caused the issue to query to get slowness, you can do like this you can update the statisics for the selected tables during the non business hours everyday) then check the performance.

    schedule an weekly rebuild index job to ensure about the optmization improtance..


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    terça-feira, 5 de junho de 2012 10:51
  • Hello Navind,

    Sounds to me that you can take advantage of maintenance plans. With them you can schedule statistic updates, also you can browse among other maintenance tasks like reindexing.

    See this link http://msdn.microsoft.com/en-us/library/ms187658.aspx

    Try to identify how often to schedule them, since I don't know the detail of the operations you are dealing with I really can't give an advice other than to test from bigger time spans to little time spans, what I mean is not to immediately set this maintenance plan to run daily, test it first setting it weekly or even monthly, if you see you still get the issue, then start to decrease the time span until you reach a proper solution. This is because running statistic updates too often can affect certain queries, it all depends on the transactions you are handling.

    AJ

    sexta-feira, 8 de junho de 2012 04:02