Stored proc execution times different on two machines

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

asked Jan 23 '12 at 03:10 AM in Default

rkfaulkner
23 ● 1 ● 1 ● 1

(comments are locked)

3 answers:

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.

answered Jan 23 '12 at 03:16 AM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

(comments are locked)

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.

answered Jan 24 '12 at 01:39 AM

Zahid
91 ● 8 ● 9 ● 11

(comments are locked)

Is the version of SQL the same?

You should check the execution plans to see if there are differences.

Other things apart from indexes/stats jump to mind:

parallelism
network i/o

answered Jan 23 '12 at 03:15 AM

Kev Riley ♦♦
46.1k ● 39 ● 43 ● 69

+1 you have beet me by few secs. :-)

Jan 23 '12 at 03:18 AM Pavel Pawlowski

Pavel / Kev ... many thanks for getting back so quickly. I had previously ran execution plans on this but not checked the "actual" execution plan which advised an index was required.

Added this in and it now runs in under 5 secs so excellent!

I am still a little confused though as the index only shows as "missing" when I run it on the server version and not when i run it locally?! Both versions are the same 10.0.55 and although the server is running other things I wouldn't say it was heavily loaded. In terms of parallelism both machines are set to "automatically" use processors and both machines show CPUs 0-3 as enabled. Once again many thanks!!

Jan 23 '12 at 03:50 AM rkfaulkner

The parallelism depends on machines itself and also how the Cost Threshold for Parallelism is set on the server. The actual degree of parallelism can be checked in the query plan.

Related to the index missing index on server. If the amount of data differs, than on the server a different query plan can be chose and that particular plan can profit from the index. It really depends on the data and query plans which the server chooses.

Jan 23 '12 at 03:57 AM Pavel Pawlowski

(comments are locked)