Hi.
Didn't know how to exactly title this.
We have and issue that we have pinpointed and I need some explanation.
We are running an sp like so:
declare @date as datetime
set @date=convert(datetime,convert(varchar,getdate()-1,101))
DELETE FROM ZZ_EAI_SourceData_VM WHERE COLLECTED_DATE = @Date and cinema='01'
INSERT INTO ZZ_EAI_SourceData_VM
(
etc,etc..
)
SELECT
etc,etc..
------ Linked server ---
from RENSRV.VISTA.DBO.ZZ_EAI_SourceData
----------------------------
LEFT JOIN VISTAVM.DBO.tblStock VS
where COLLECTED_DATE = @Date
....etc
Now as you can see, the date declaration is inside the sp.
this will take about a minute.
If we do it like this:
ALTER PROCEDURE [dbo].[ZZ_Insert_SourceData_03]
@Date as DateTime
AS
BEGIN
DELETE FROM ZZ_EAI_SourceData_VM WHERE COLLECTED_DATE = @Date and cinema='01'
INSERT INTO ZZ_EAI_SourceData_VM
(
etc,etc..
)
SELECT
etc,etc..
------ Linked server ---
from RENSRV.VISTA.DBO.ZZ_EAI_SourceData
----------------------------
LEFT JOIN VISTAVM.DBO.tblStock VS
where COLLECTED_DATE = @Date
....etc
When calling this from a job step it will take 10 minutes!
So obviously there is something wrong when calling the date value from an exoteric job step rather than having it hard coded inside the sp.
Can anyone advise of any steps we might take (for example use update statistics on run) that will resolved this?
Another note is that we used to have this run with the outside value on an SQL2000 server without problems.
the problem started with the migration to 2008 R2. I don't know if it just flushed the query plan and the problem might existed on SQL 2000 also but I'm just mentioning it.
You can try with OPTION RECOMPILE inside your sp or WITH RECOMPILE in line with your exec statement, just to test if it is problem with the cached plan
I'm thinking maybe there is a problem when using a linked server, else it does not really make sense if you use inside parameters or outside parameters(?).
It is possible that you are running under different security rights:
one is under your's user rights and one use sql agent
first have enough rights to see statistics (for Linked serv) and generate a proper plan
and for job step, the sql agent don't . ddl_admin is the minimal required to see statistics. (this is solved in SQL2012 SP1)
I'm not sure how to check that but for now I do not run the job step, I'm executing the stored procedure from the same server that I am running the query.
So i do an " execute ZZ_Insert_SourceData_03 @date WITH RECOMPILE"
and after I run the first "fast" query from the same place.
I also tried to set statistics profile on and off, in can that that was the problem but, nothing...
I'm also thinking, is there a possibility that the @date value is not interpreted correctly on the stored procedure?
I mean having the date value outside the stored procedure messing with the interpretation of the execution plan?
OK.
So I hard cored the date inside the stored procedure and it will still be slow , so this is not a date problem.
I'm think it has something to do on how the stored procedure calls the linked server(?)
I am including the execution plan.
What really hits the eye is that the remote query cost is 97% on the fast t-SQL and the same cost is on 16% on the sproc.
OK.
After reading, I guess stepson may have a point on security rights.
I cannot try this as I am not sysadmin on this DB but supposedly giving admin rights will bypass an error that is through internally (but continues the query) and will remove the execution plan completely, thus make it slow.
I can't test that however and even so, we cannot give admin rights.
Hi.
Can you verify something on the security rights?
I have the server that is running the main sp from and on the linked servers, i see that it has the desired link server set with:
Local Login ="sa" Impersonate "checked" . I log to the main server as "sa" (guess I was an admin and never noticed it ) and I run the sproc from the admin log in.
Would that be sufficient to say that I have enough rights or do I need to set something on the linked server also?
The linked server does not have any link back to the main server.
) and I run the sproc from the admin log in.