x

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
more ▼

asked Jan 23, 2012 at 03:10 AM in Default

rkfaulkner gravatar image

rkfaulkner
23 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

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.
more ▼

answered Jan 23, 2012 at 03:16 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Jan 24, 2012 at 01:39 AM

Zahid gravatar image

Zahid
91 9 10 11

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Jan 23, 2012 at 03:15 AM

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

+1 you have beet me by few secs. :-)
Jan 23, 2012 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, 2012 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, 2012 at 03:57 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x415
x11

asked: Jan 23, 2012 at 03:10 AM

Seen: 1110 times

Last Updated: Jan 23, 2012 at 03:10 AM