locked
Database performance is very poor after a month of use RRS feed

  • Question

  • Hi!

    I am quite new to SQL server and have a question regarding database speed. The database (about 50 MB big) was very fast in the beginning. Now, a month later, the speed is a lot worse. I run a script every hour that updates specific fields in some tables (about 500 rows) and in the beginning the script took about 2 minutes and now it neary takes 28 minutes. I have restored an early copy of the database and when I run the script against the copy the performance is back with a runtime of 2 minutes so nothing is wrong with the server (sql server 2014). I have tried to run 'exec sp_updtestats' with no difference in performance and also tried to rebuild and reorganize the indexes with no luck... Someone friendly :) here that can help me with this big problem and how I can find what is wrong with the production database? it seems that the script gradually caused the decreased performance... :(

    Best Regards Markus

    Monday, October 20, 2014 1:24 PM

Answers

  • Hi!

    I have now found the problem thanks to SQL Profiler and your help :). It was caused by a bug in the script that used the built in functions in the ecommerce software to update products but it didn't reused the deliverydates why 545 deliverydates were added every hour and together with bad indexes on that table it slowed down the script and site a lot. I rewrote the script to use sql queries against the databas directly instead of the build in functions and the script now takes 6 seconds :)

    What is a good Maintenance plan for the sql server? The databases are set to SIMPLE recovery model and are backed up every night so backup is not needed in this job. If I run the maintenance plan wizard, is it good to incude all these in the following order: 'Check database integiry', 'Shrink database', 'Reorganize Index', 'Rebuild Index', 'Update Statistics', 'Clean Up History', Maintenance Cleanup Task' for all user databases?

    Skip somone?
    Reorder them?
    Is every night a goog interval?
    Anything else that needs to be done?

    It takes less than a minute to run this job against the production database so execution time is no probem here...

    Regards Markus

    Wednesday, October 22, 2014 10:09 PM

All replies

  • From the information you have posted, it seems that the script is not very efficiently written and/or there are appropriate indexes missing. As a starting point, could you post you script?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, October 20, 2014 9:57 PM
  • Hi!

    The script is written in c# and uses the built in functions in nopCommerce (a free ecommerce software) to update and insert new products (so I have not written the sql statements myself). Every hour the script is run and updates the names, the prices, deliverydates, stockqtys etc. If there are any new products they will be added to. The database contains a lot of tables all with indexes... Only 10 products have been added, the table Product now contains 545 rows and in the beginning it contained 535 products so Another 10 products should not make any difference in speed at all and I can see the the run time as incresed from 2 to 3 to 4 to 5 minutes up to 28 minutes now during about 5 weeks. I can notice that the entire site is slower and the cpu works harder but  'exec sp_updtestats' makes no difference to run against the database (is it supposed to take about 2 seconds to run this command?).

    Regards Markus

    Monday, October 20, 2014 10:42 PM
  •  I run a script every hour that updates specific fields in some tables (about 500 rows) and in the beginning the script took about 2 minutes and now it neary takes 28 minutes.

    REBUILD indexes on a periodic basis like every weekend or each month end.

    Optimization: http://www.sqlusa.com/articles/query-optimization/

    You should also implement a scheduled backup plan. What is the recovery model? SIMPLE? FULL?




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014





    • Edited by Kalman Toth Tuesday, October 21, 2014 12:56 AM
    Tuesday, October 21, 2014 12:53 AM
  • Can you post here an execution plan of the query?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, October 21, 2014 5:28 AM
    Answerer
  • Sounds like this nopCommerce thing is not written with top performance in mind.

    Since the database is so small, maybe you could upload a zipped backup together with the C# program and instructions how to run it somewhere. (Google Drive, Dropbox etc.) However, there is no guarantee that I or anyone else will able to give you a complete solution - it is unlikely that any of us will volunteer to rewrite nopCommerce. (And my experience is that when you have disasters of this magnitude, the code is often rotten from inside and out.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, October 21, 2014 7:26 AM
  • You have only one option strongly suggest to run SQL Profiler and analyze their findings to get some idea.

    Also try to tune scripts and create important indexes against the queries. And also remove un-necessary indexes. Remember your database designing really matters. Some days back, I have solved a issue with help of mention techniques. Good Luck !!!

    Tuesday, October 21, 2014 8:22 AM
  • Hi!

    I have now found the problem thanks to SQL Profiler and your help :). It was caused by a bug in the script that used the built in functions in the ecommerce software to update products but it didn't reused the deliverydates why 545 deliverydates were added every hour and together with bad indexes on that table it slowed down the script and site a lot. I rewrote the script to use sql queries against the databas directly instead of the build in functions and the script now takes 6 seconds :)

    What is a good Maintenance plan for the sql server? The databases are set to SIMPLE recovery model and are backed up every night so backup is not needed in this job. If I run the maintenance plan wizard, is it good to incude all these in the following order: 'Check database integiry', 'Shrink database', 'Reorganize Index', 'Rebuild Index', 'Update Statistics', 'Clean Up History', Maintenance Cleanup Task' for all user databases?

    Skip somone?
    Reorder them?
    Is every night a goog interval?
    Anything else that needs to be done?

    It takes less than a minute to run this job against the production database so execution time is no probem here...

    Regards Markus

    Wednesday, October 22, 2014 10:09 PM
  • Great to hear that you were able to fix the issue yourself!

    You should definitely take out "Shrink Database" from the maintenance plan. That option is completely evil.

    You also only need one of Rebuild and reoranize index. For your small database, go with REINDEX.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 23, 2014 11:59 AM