April 14, 2016 at 9:11 pm
I support an ERP product using SQL Server as the backend.
One of my clients has 2 different databases, same table structure, same indexes, same everything except the data. Same server, same instance.
I have written a stored procedure which takes 22 seconds on a reasonably big database.
The identical stored proc ran over a smaller database takes 27 minutes. Seeing as the server hardware is identical, the database design is identical, the SP code is identical I have no idea why the stored proc running over a smaller database would take so long.
I figure if the SP was poorly coded it would be slow in both databases. If the hardware was to blame (not enough RAM, disk space etc) then it would be slow in both databases. If the data it needed to churn was vastly bigger then sure, but it is actually smaller.
I'm thinking I may do a index REBUILD and update stats, but failing that, other ideas?
Cheers
Danster
April 14, 2016 at 11:42 pm
First thing to start with - look at the execution plans taken in both environments.
It will show you what's so different there.
_____________
Code for TallyGenerator
April 15, 2016 at 12:28 am
Good idea, but about 10 minutes ago I sorted it out.
Just needed to run sp_updatestats.
All good after that.
Call this a case closed.
April 15, 2016 at 2:05 am
Nice one! I was going to suggest updating the statistics. Had a very similar problem recently. Now I update the stats on a schedule using the Ola Hallangren scripts.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply