Stored proc execution times different on two machines
Hi I have a stored proc running on SQL 2008. If i run it on our server it takes over 2 minutes to execute. However if I take a copy of the database and run it locally on my laptop it runs in under 20 seconds? I've checked all the obvious things such as indexes / stats / free space etc and cant find anything obvious. Hope someone can help! Regards Rob
Did you checked query plans? Are both the same? If not then the difference in query plan can cause this. Degree parallelization is also one of important things which can have impact on the query execution times. (Higher parallelization doesn't always mean shorter execution times). Also Depends how high is the load on the server. If you have heavily loaded server responding to higher number of request, the execution times can be longer than on your machine, which is not loaded at all. Also internal data fragmentation can have impact. So as I wrote, fist thing is to compare query plans whether they are the same or not and start from here.
I would also suggest you to check the execution plan. check the cost. It is not mandatory to have same execution plan for these two different machine. Also need to know the physical machines configuration. Is both the machine I/O same? On the server there could be some other load and could be network issue. Is the database on the same machine you running the SP? Index fragmentation could be a reason as well. Therefore the best way to analyse is execution plan.