Store Proc runs in 3 secs in SSMS takes 3 minutes when ran from Agent

Question

Store Proc runs in 3 secs in SSMS takes 3 minutes when ran from Agent

ben.rosato

SSCommitted

Points: 1929
More actions
May 2, 2012 at 10:25 am

#255732

This one has me sort of stumped. When I profile this not only does the time increase but so does cpu and logical reads. Reads go from 16,000 to 44 million.:w00t:

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Thomas Stringer SSCarpal Tunnel Points: 4038 More actions · Answer 1

Could be a result of bad parameter sniffing. Without more information as to what the stored procedure does, the calls that are made to it (i.e. parameters), and execution plans though it's just going to be guessing at this point. Please post more details.

Twitter: @SQLife
Email: sqlsalt(at)outlook(dot)com

ben.rosato SSCommitted Points: 1929 More actions · Answer 2

Thomas Stringer (5/2/2012)
Could be a result of bad parameter sniffing. Without more information as to what the stored procedure does, the calls that are made to it (i.e. parameters), and execution plans though it's just going to be guessing at this point. Please post more details.

No parameters. Just stored procedure call from SSMS vs same call from agent job.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

The problem here is that we can't see what you see. What little you have provided only allows us to take wild shots in the dark and hope the answer fits your problem.

Read the second article I reference below in my signature block. Follow the instructions and we should be able to help you.

ben.rosato SSCommitted Points: 1929 More actions · Answer 4

Lynn Pettis (5/2/2012)
The problem here is that we can't see what you see. What little you have provided only allows us to take wild shots in the dark and hope the answer fits your problem.
Read the second article I reference below in my signature block. Follow the instructions and we should be able to help you.

This really comes down to a different query plan being generated when I run this from an agent job. Here the part of the query doing the bulk of the work and the 2 plans that are generated. I did forget to mention this is a linked server query. Can't really post the DDL because there are too many tables.

ben.rosato SSCommitted Points: 1929 More actions · Answer 5

There has to be something that sql agent is doing or isn't doing that would cause a different query plan to be generated.

Lowell SSC Guru Points: 323518 More actions · Answer 6

as mentioned, without specifics, wer can only guess;

a different process smay have different ANSI settings, like ANSI NULLS, etc, which can affect the query(whatever it does).

it could be the database context is not what you think it is.

it could be something related to permissions.

it could be that it is thrashing around in tempdb, and that's what takes too long.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 7

doesn't the slow plan say it is copying 3.8 million rows @ 115 gig via a table spooll from the linked server?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ben.rosato SSCommitted Points: 1929 More actions · Answer 8

Lowell (5/2/2012)
doesn't the slow plan say it is copying 3.8 million rows @ 115 gig via a table spooll from the linked server?

Indeed it does. Actual is 0 rows.

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 9

As others mentioned, we can only guess.

From your query, why you use "UNION" and "DISTINCT" at the same time? You can use "UNION ALL" and "DISTINCT". It may save you a little bit time.

Since a linked server is involved, is the remote login a db_ddladmin role in the remote database? If not, it can't use the stats on the remote tables.

(I guess your linked server uses "Be made using the login's current security context" since you did have the issue when you ran it from SSMS by yourself.)

-------

Edit: .....you did not have the issue....

ben.rosato SSCommitted Points: 1929 More actions · Answer 10

Wildcat (5/2/2012)
As others mentioned, we can only guess.
From your query, why you use "UNION" and "DISTINCT" at the same time? You can use "UNION ALL" and "DISTINCT". It may save you a little bit time.
Since a linked server is involved, is the remote login a db_ddladmin role in the remote database? If not, it can't use the stats on the remote tables.
(I guess your linked server uses "Be made using the login's current security context" since you did have the issue when you ran it from SSMS by yourself.)
-------
Edit: .....you did not have the issue....

Linked server uses "Be made using this security context". The login on the remote server is not db_ddladmin.

ben.rosato SSCommitted Points: 1929 More actions · Answer 11

Wildcat (5/2/2012)
As others mentioned, we can only guess.
From your query, why you use "UNION" and "DISTINCT" at the same time? You can use "UNION ALL" and "DISTINCT". It may save you a little bit time.
Since a linked server is involved, is the remote login a db_ddladmin role in the remote database? If not, it can't use the stats on the remote tables.
(I guess your linked server uses "Be made using the login's current security context" since you did have the issue when you ran it from SSMS by yourself.)
-------
Edit: .....you did not have the issue....

Dude!, It was the distinct. Don't need it. Union alone does the trick.