question

Colin Wilson avatar image
Colin Wilson asked

Same procedure taking different times to run from the website and management studio

I have an odd situation.

We are running SQL Server 2008 and have a stored procedure that times out every time when being called from the website.

I have run profiler against the website and captured the precise call being made. This is giving me a duration of 29992.

When I copy the text of the procedure being called and run exactly the same code using the Management Studio, I get a duration of 3278. I have even used the website login to ensure that I use the same credentials - long shot I know, but I thought I'd try.

What is more, I can simulate this every time without fail, but I cannot understand why exactly the same call is taking such a long time when called from the web server?!?

If anyone can help it would be greatly appreciated.

stored-proceduresprofilertimeout-expired
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Steinar avatar image
Steinar answered

You should capture the query plan used when run from the web server(using profiler), and compare it with the one used from SSMP. Most likely they are different.

Then you should investigate why. Is it using WITH RECOMPILE? Are you using the same connection settings from the wseb server as from ssmp? (can be captured using profiler). Different settings might generate different plans.

If you still can't find the cause, you could consider using hints to keep the plan the same.

If the query plans ARE the same, then we need to investigare further.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
+1 - given it's easily re-producible, profiler is your first port of call for sure
1 Like 1 ·
Fatherjack avatar image
Fatherjack answered

I have something similar here. An access application is taking 45s+ to run a query that executes in < 2s via SSMS. I dont have anything to add beyond what Steinar has mentioned but will be watching the responses you get with interest incase they give me some ideas. My problem is going to be how to get the Access app to run the query differently as its compiled ...

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

Check the ANSI settings for the connection from both sources. Chances are, it's different. That can cause different execution plans and different performance metrics.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

Also look at the amount of data being returned. If the network to the web server is slower than the network you ran the SSMS query on, then you could be waiting for data to be returned.

More likely to be execution plans like most have already said, but I have seen network load do this too.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.