October 20, 2014 at 8:24 am
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.
October 20, 2014 at 8:39 am
How big is the database now? You said it was 50MB before, what is it now?
What does the query actually do?
How many rows are in the table now? How many when you started?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2014 at 3:40 pm
Well, there's nothing inherent in SQL Server that would result in the system running slower just because of the time passed. You've already hit on what is usually the biggest problem, statistics. Now, you've updated your stats with sp_updatestats, but that's just a sampled update. You could try running a manual statistics update on each of the tables in question:
UPDATE STATISTICS <TableName> WITH FULL SCAN
But, that's only going to help if it's the stats. Your query was running in 2 minutes when everything started. That's not too fast. If it's doing scans against the table and it took 2 minutes when you started and it's still doing scans when your database is big, it could just be that the growth has lead to the slowdown. Have you checked the execution plans for the queries?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 20, 2014 at 4:51 pm
Hi!
Thanks for the replies! 🙂
The database comes with nopCommerce (a free ecommerce software) written in c# and the script updates the Product table were the names, prices, deliverydates, prices, stockqtys etc. are updated. It is run once an hour. From the start the database contained about 535 products and only 10 have been added so toalt rows now in the product table is 545. The script uses the built in functions in nopCommerce for updates and inserts so I have not written the queries myself.
If I change and use the old backup of the database the run time of the script is back to 2 minutes why there must be somthing that has happened to the production database... Tha database size now is only 8 MB bigger so not much difference there eather...
Regards Markus
October 21, 2014 at 6:16 am
That's pretty small data growth. Check the execution plans for the query from the old database as compared to the new one. See if they're different.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 22, 2014 at 4:14 pm
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 🙂
Thanks again for all your help! 🙂
Regards Markus
October 23, 2014 at 1:18 am
Glad you solved it and thanks or posting the follow up. It's always good to know what the problem really was and the solution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 23, 2014 at 1:27 am
3party software. Can't live with it, can't live without it.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply