Poor performance over linked server SQL Server 2012

I am having an issue with performance since changing our Linked server connection to SQL 2012.  The query in 2008R2 ran in 9 seconds and now it takes around 10 minutes.  When I trace the query on the 2 servers, the statement is completely different.

CREATE TABLE [dbo].[PS_OCC_ADDRESS_S](
	[EMPLID] [varchar](11) NULL,
	[ADDRESS_TYPE] [varchar](4) NULL,
	[OCC_ADDR_TYP_DESCR] [varchar](30) NULL,
	[ADDRESS1] [varchar](55) NULL,
	[ADDRESS2] [varchar](55) NULL,
	[ADDRESS3] [varchar](55) NULL,
	[CITY] [varchar](30) NULL,
	[STATE] [varchar](6) NULL,
	[POSTAL] [varchar](12) NULL,
	[COUNTY] [varchar](30) NULL,
	[COUNTRY] [varchar](3) NULL,
	[DESCR] [varchar](30) NULL,
	[FERPA] [varchar](1) NULL,
	[LASTUPDDTTM] [varchar](75) NULL,
	[LASTUPDOPRID] [varchar](30) NULL
) ON [PRIMARY]

Statement:
SELECT EmplId, Address1, Address2, Address3, City, State = substring(State,1,2), Zip = substring(Postal,1,10),  County, Country 
--INTO tmp_Addr 
FROM [Sql03].[DWHCRPT].[dbo].[PS_OCC_ADDRESS_S]
WHERE (Address_Type = 'PERM') and (EmplID IN (

	SELECT UserID As EmplID FROM Collegium.dbo.Users
	UNION
	SELECT EmplID As EmplID FROM Emap.dbo.Applications
	UNION
	SELECT ID As EmplID FROM HS_Program_Application.dbo.Applications

))
Any suggestions would be appreciated

April 30th, 2015 9:52am

Just a quick thought, try to rebuild indexes and update stats on linked server.

Are you facing same issues with other queries too? if not then update stats can do the trick fo

Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 10:03am

I have attempted that and it didn't make any difference.  When I run the query, the CPU spikes on the remote server but I checked all indexes and they look good.
(@P1 nvarchar(50))SELECT "Tbl1005"."EMPLID" "Col1036","Tbl1005"."ADDRESS1" "Col1038","Tbl1005"."ADDRESS2" "Col1039","Tbl1005"."ADDRESS3" "Col1040","Tbl1005"."CITY" "Col1041","Tbl1005"."STATE" "Col1042","Tbl1005"."POSTAL" "Col1043","Tbl1005"."COUNTY" "Col1044","Tbl1005"."COUNTRY" "Col1045" FROM "DWHCRPT"."dbo"."PS_OCC_ADDRESS_S" "Tbl1005" WHERE "Tbl1005"."ADDRESS_TYPE"='PERM' AND CONVERT(nvarchar(11),"Tbl1005"."EMPLID",0)=@P1
This is what is running on the remote server.
April 30th, 2015 10:17am

I am having an issue with performance since changing our Linked server connection to SQL 2012.  The query in 2008R2 ran in 9 seconds and now it takes around 10 minutes.  When I trace the query on the 2 servers, the statement is completely different.

CREATE TABLE [dbo].[PS_OCC_ADDRESS_S](
	[EMPLID] [varchar](11) NULL,
	[ADDRESS_TYPE] [varchar](4) NULL,
	[OCC_ADDR_TYP_DESCR] [varchar](30) NULL,
	[ADDRESS1] [varchar](55) NULL,
	[ADDRESS2] [varchar](55) NULL,
	[ADDRESS3] [varchar](55) NULL,
	[CITY] [varchar](30) NULL,
	[STATE] [varchar](6) NULL,
	[POSTAL] [varchar](12) NULL,
	[COUNTY] [varchar](30) NULL,
	[COUNTRY] [varchar](3) NULL,
	[DESCR] [varchar](30) NULL,
	[FERPA] [varchar](1) NULL,
	[LASTUPDDTTM] [varchar](75) NULL,
	[LASTUPDOPRID] [varchar](30) NULL
) ON [PRIMARY]

Statement:
SELECT EmplId, Address1, Address2, Address3, City, State = substring(State,1,2), Zip = substring(Postal,1,10),  County, Country 
--INTO tmp_Addr 
FROM [Sql03].[DWHCRPT].[dbo].[PS_OCC_ADDRESS_S]
WHERE (Address_Type = 'PERM') and (EmplID IN (

	SELECT UserID As EmplID FROM Collegium.dbo.Users
	UNION
	SELECT EmplID As EmplID FROM Emap.dbo.Applications
	UNION
	SELECT ID As EmplID FROM HS_Program_Application.dbo.Applications

))
Any suggestions would be appreciated

SELECT UserID As EmplID FROM Collegium.dbo.Users
into #temp1
UNION
SELECT EmplID As EmplID FROM Emap.dbo.Applications
UNION
SELECT ID As EmplID FROM HS_Program_Application.dbo.Applications

....WHERE (Address_Type = 'PERM') and EmplID IN (select * from #temp1)

Still it does not resolve 9 secs over 10 minutes. Good luck.

Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 10:32am

I'm at a complete loss.....  I removed the NONCLUSTERED index on PS_OCC_ADDRESS_S.EMPLID and it ran in 6 seconds....
April 30th, 2015 10:58am

You created a table, so I am assuming it is empty, then u selected from another table (same name) and nothing about non-clustered index. Something is missing...

Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 11:08am

I added the DDL so everyone would know the table structure.  

The table isn't empty and contains several million rows, which had a non clustered index on the EMPLID column. Once the nonclustered index was removed, the query time is 6 seconds.

April 30th, 2015 11:10am

No - you added just the basic table structure.  Performance is all about the indexes. And removing this index might improve this particular query, but it can also impact other operations negatively.  One test does not prove your theory - perhaps this caused something else to occur.  And evaluating performance requires examining the execution plans - making random changes and guessing is just that: guessing.
Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 11:26am

And it's nice how every column in the table is nullable.  Someone needs to spend time on defining a schema correctly. Even better is that a datetime value (presumably, based on the column name - lastupddttm) is stored as a string.  There are many lessons to be learned, even if you are stuck with this structure.
April 30th, 2015 11:29am

I have been reviewing the execution plans to identify why the query runs fine on SQL 2008R2 but not on SQL 2012.

Thanks for the advice!

Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 11:52am

I added the indexes back and was able to find the issue.  The local table had an ID that was set as nvarchar(50) and the ID of the remote instance has an ID that is VARCHAR(11).  Added a CAST statement and returns the data in 8 seconds.

--IF EXISTS(SELECT table_name FROM information_schema.tables WHERE table_name = 'tmp_Addr' and table_schema = 'dbo')
--DROP TABLE dbo.tmp_Addr

SELECT EmplId, Address1, Address2, Address3, City, State = substring(State,1,2), Zip = substring(Postal,1,10),  County, Country 
--INTO tmp_Addr 
FROM [SQL03].[DWHCRPT].[dbo].[PS_OCC_ADDRESS_S]
WHERE (Address_Type = 'PERM') and (EmplID IN (

	SELECT CAST(UserID AS VARCHAR(11)) As EmplID FROM Collegium.dbo.Users
	UNION
	SELECT CAST(EmplID As VARCHAR(11)) AS EmplID FROM Emap.dbo.Applications
	UNION
	SELECT CAST(OSUID AS VARCHAR(11)) As EmplID FROM HS_Program_Application.dbo.Applications

))

April 30th, 2015 2:03pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics