Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Database performance very poor after a moths use

Author  Topic 

d99mj
Starting Member

5 Posts

Posted - 2014-10-20 : 09:16:05
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 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?

Best regards Markus.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-20 : 10:25:53
Sounds like your db is becoming fragmented. Let me ask you: What column is the clustered index on? Also, are new rows inserted in the same order as the clustered index?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-20 : 11:57:22
To me it sounds like you are missing indexes.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

d99mj
Starting Member

5 Posts

Posted - 2014-10-20 : 18:35:52
Hi!

Thanks for your replies!

The clustered index is on ProductID column in that table. Only ten rows have been inserted since the start and the total number of rows are 545.
The database itself contains a lot of tables, all with indexes. I have not designed the database myself, it comes with nopCommerce, a free ecommerce software.
The script only updates the names, stockqtys, prices etc. for all products and inserts new Products when available. It uses the build in functions in nopcommerce to update and add products so I have not written the sql statements myself eather. Is there a way to check how frgmented the database is or must you do this table by table? (Example of a script I can run against the database to ckeck this?).

Regards Markus
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-20 : 18:37:46
We'll need to see the execution plans for the problem queries. Stating that tables have indexes is not sufficient to troubleshoot this issue. I would also suspect blocking as the culprit.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

d99mj
Starting Member

5 Posts

Posted - 2014-10-22 : 17:54:36
Hi!

I have now found the problem. 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 :)

Regards Markus
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-22 : 17:56:46


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -