Hi,

I'm executing this query over linked server:

INSERT INTO dbo.myTable(col1, col2,col3)
EXEC [linkedServer].[myDatabase].[dbo].[p_myStoredProcedure]

It takes very long time at night. Procedure now executes in less than second, while query now runs in 2 seconds.
The number of rows is about 100000.

How can I find out where is the problem?

Interesting is that similar query with 10 times greater load executes couple of seconds:

INSERT INTO dbo.myTable2(col1, col2, col3)
SELECT col1, col2, col3 FROM [linkedServer].[myDatabase].[dbo].[myView]

Is there some difference if I execute query as INSERT with SELECT statement vs INSERT with EXECUTE stored procedure?
That is one difference i can notice between this 2 queries.

Maybe SQL is sleeping at night and that is the reason of long running time :)

br, Simon

I did not quite follow the execution times. Which was the slowest INSERT- EXEC or INSERT-SELECT?

As for SQL Server sleeping at night, do you see any difference in execution time between daytime and nighttime?

Difference between daytime and nighttime may be due to reindexing jobs that has flushed data from cache. It may also be due to a SAN which is hammered by a bunch of servers running backup.