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.
| 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? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-10-20 : 11:57:22
|
| To me it sounds like you are missing indexes.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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 |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Database performance very poor after a moths use
Tara Kizer