In sys.dm_exec_query_stats total_worker_time
is:
Total amount of CPU time, in microseconds, that was consumed by
executions of this plan since it was compiled.
So it is the total CPU time for all executions of the cached plan. The execution_count
column gives the number of times the complete plan has executed, and there is also information about the lowest and highest values recorded. The figures from the DMV are in microseconds, whereas STATISTICS TIME
output shows milliseconds. Finally, the MAXDOP
hint only applies to the portion of the query that runs locally.
I noticed you are running SQL Server 2005 SP2. There were all sorts of timing issues with that (now unsupported) release, for some detail, see:
SQL Server 2005 SP2 will introduce new messages to the error log related to timing activities.
Consider moving to 2005 SP4.
The performance problem actually had to do with the LEFT OUTER JOIN
tables. If I changed them to INNER JOIN
, or if I excluded their data from the SELECT
columns, the query ran fine.
What I ended up doing was creating a View
on the linked server containing all the data I wanted from it, then simply joining to it from the primary server with the #tmpIds
table.
I didn't think this would work since I thought joining everything and pulling it down to the second server before filtering was the same as what I was doing now, and would lead to the same performance problem, but surprisingly that doesn't appear to be the case.
CREATE VIEW MyView
AS
SELECT T1.Id, T2.ColA, ...
FROM Table1 as T1
INNER JOIN Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Table6 as T6 ON T5.Id = T6.Id
GO
and
INSERT INTO #tmpTable (Id, ...)
SELECT T1.Id, T1.ColA, ...
FROM Server.Database.dbo.MyView as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id
All the joined columns were correctly indexed, however according to this answer
Even though there may be indexing on tables on the remote server, SQL may not be able to take advantage of them while it can build a local query plan that does take advantages of indexing.
And this one
Let the linked server do as much as possible.
It is impossible for SQL Server to optimise a query on a linked server, even another SQL Server
so I am guessing that the query plan used for the query was not using the Indices defined, and SQL Server was generating a poor query plan for the LEFT OUTER JOIN
tables.
Best Answer
When executing a query over Linked Server the SQL Server session will enter an OLEDB wait while waiting for results, and that time will not be accounted for as CPU_time. The linked server does not return wait stats.