-1

I have a SQL Server Database, In two weeks in each four months many insert operation is executed to the database and in this time the database experience very slow performance, it takes a lot of time for a single and simple query. I faced this problem two times and solved the issue by update the statistic of the database using sp_updatestats procedure.

**What is the main reason behind this performance hit??

closed as too broad by Mr.Brownstone, Joe Obbish, Colin 't Hart, McNets, mustaccio Dec 23 '18 at 14:46

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Welcome to DBA.SE! As it stands, this is too broad to be able to provide you with a useful answer. Can you provide more detail? What have you tried, what did your investigations show, what queries did you run to determine that statistics need to be updated? – Mr.Brownstone Dec 22 '18 at 14:57
3

The SQL Server query optimizer uses statistics to create good query plans. When your statistics get out of date, the optimizer can make very bad choices about how to run queries.

Inserting or updating lots of rows can easily cause statistics to get out of date, because there will be new and different data in the table(s) that the original statistics didn't consider.

SQL Server will update statistics automatically when you hit certain thresholds, see Statistics - AUTO_UPDATE_STATISTICS Option.

However, if you have that option turned off, or if you're just not hitting the threshold, or if the sampling rate for the automatic stats update isn't good enough, then you'll have this problem.

One solution is to enhance whatever process does all of these inserts to manually update stats (as you mentioned in your post).

If you're on a new enough version (SQL Server 2016 SP1 CU4 or SQL server 2017 CU1), you could also set the PERSIST_SAMPLE_PERCENT option to make sure that future auto stats updates use a high enough sampling rate for your needs. See here for more information on that: Enhancement: New keyword is added to CREATE and UPDATE STATISTICS statements to persist sampling rate for future statistics updates in SQL Server

0

I would recommend implementing something like a weekly maintenance job that would also update statistics. https://ola.hallengren.com is a very common and well known solution.

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