SQL 2000 SP4 + 2040 hotfix, x64 OS, 32-bit SQL Server
SQL Server 2005, SP2, X64 OS, 32-bit SQL Server
Most production databases are located on Box A but we have a few that I've gotten moved over to Box B. Queries to from Box B to Box A have worked fine but they aren't really what I would consider large.
I've since begun to test this with our development version which works fine on SQL 2000 when it doesn't do remote queries.
A query that takes less than 3 seconds (most of the time instant) when run locally, takes upwards of 30 seconds when using four part names in the views that the stored proc calls.
After clearing the waits and running this guy I get the following:
LAZYWRITER_SLEEP 30343 *
SQLTRACE_BUFFER_FLUSH 32000 *
* I realize these two are usually ignored.
I will focus on the costliest part of the query since it's so long.
Here's the Execution plan:
|--Table Insert(OBJECT@PostTable), SET[PI] = @FT.[PI] as [f].[PI],[CCode] = [Expr1010],[CreDate] = [BoxA].[ADHC].[dbo].[PA].[CreDate],[AI] = [BoxA].[ADHC].[dbo].[PA].[AI]))
|--Top(ROWCOUNT est 0)
|--Merge Join(Inner Join, MANY-TO-MANY MERGE[f].[PI])=([BoxA].[ADHC].[dbo].[PA].[PI]), RESIDUAL[BoxA].[ADHC].[dbo].[PA].[PI]=@FT.[PI] as [f].[PI]))
|--Sort(ORDER BY[f].[PI] ASC))
| |--Table Scan(OBJECT@FT AS [f]))
|--Sort(ORDER BY[BoxA].[ADHC].[dbo].[PA].[PI] ASC))
|--Remote Query(SOURCEBoxA), QUERYSELECT "Tbl1009"."AI" "Col1025","Tbl1009"."PI" "Col1026","Tbl1009"."CCode" "Col1034","Tbl1009"."CreDate" "Col1036" FROM "ADHC"."dbo"."PA" "Tbl1009" WHERE ("Tbl1009"."CreDate" IS NOT NULL) AND "Tbl1009"."EndDate" IS NULL))
I wish I could provide tons of sample code but since it's sensitive data I cannot.
One other thing that I will mention is that there are different lengths on the datatype for a few columns.
Box A may have a column with varchar(50) and the developer may use varchar(11). In the above example Box A has a VarChar(4) on the CONVERT_IMPLICIT(varchar(2). Could that difference really cause 27 seconds of duration?
Update: I just ran perfmon on the Network Interface and there are no problems there.
, Database AdministratorA hodgepodge of Information Technology and LifeLinkedIn ProfileMy Twitter