March 18, 2013 at 6:13 am
I have a select query which brings back 35k rows. it runs in approx 5 seconds.
I moved this to a new server and it runs for around an hour before i give up and kill the process.
I put in select top 35000 .......... at the start of the query and i can get it to run in just under 4 minutes.
The query runs across two servers to fetch the data using a left outer join to pull back everything from server 1 regardless of a match on server 2
Where do i start looking to find the bottle neck ?
I've attached the plan.
March 18, 2013 at 6:40 am
Can you also post the actual execution plan for the query from the server where it works well?
March 18, 2013 at 6:47 am
I think check linkserver properties on old server vs new server.
March 18, 2013 at 6:52 am
I have uploaded the 'working ' plan.
On the developer server and the old production server the link between the servers was a dedicated 'sa' sql logon. On the new box the link between the servers is a dedicated SQL Data_Reader logon as the range of people accising this box will be higher and not always from within the company
March 18, 2013 at 6:54 am
You're running into this: http://www.benjaminnevarez.com/2011/05/optimizer-statistics-on-linked-servers/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2013 at 7:00 am
Don't have time to compare them that I need but we are looking at two completely different query plans. Also, the troublesome query is a select and the other is an insert query. Not sure how this impacts what is going on.
March 18, 2013 at 7:03 am
sorry , i blanked out the insert part of the statement when i was trying to get the results back to try and work out why it was taking such a vastly different amout of time to pull the data through. The select part of the statement is the same on both sides
March 18, 2013 at 7:26 am
Nested loops prefetch bail-out?
Try concatenating on the remote side and casting the result to something sensible. You might also benefit from using two OPENQUERY queries (to guarantee remote execution of the whole query), one for each remote, and running the results into temp tables.
SELECT
'SQLCRM01 : ' + isnull(HC.Client_code,'')as 'source_db',--[CSS_SummaryFakeKey]
/*(cast(A.LISTING_SRC_ID as varchar) + '/' + cast(A.LISTING_ID as varchar)) AS 'source_id',--[Name_Id]*/
A.LISTING_ID,
'',--[AddressID]
'',--[CaseId]
/*(cast(A.LISTING_SRC_ID as varchar) + '/' + cast(A.LISTING_ID as varchar)) AS 'source_id',--[ClientDIWOR]*/
A.LISTING_ID,
'SQLCRM01',--SourceDB
isnull(HC.Client_Code,'******'),--clientcode
substring(DISPLAY_NM,1,100) as 'DISPLAY_NM',--surname
DISPLAY_NM as 'DISPLAY_NM'--company name
,c.[address]--linked address
,''--linked comms
,1--iscompany 1 = 'O' 0 = 'I'
,HP.Employee_name--client partner
,null--lastupdatedts
,''--lastupdatedrunid
,''--nulllasttime
FROM SQLCRM01.[InterAction].dbo.INT_AUX_LISTING A
left join SQLCRM01.[InterAction].dbo.INT_AUX_LST_ADDR B on B.LISTING_ID = A.LISTING_ID and B.LISTING_SRC_ID = A.LISTING_SRC_ID
left join (
SELECT
ADDRESS_ID,
[address] = CAST(ISNULL(C.LINE1,'|') +
ISNULL('|' + C.LINE2,'|') +
ISNULL('|' + C.LINE3,'|') +
ISNULL('|' + C.CITY,'|') +
ISNULL('|' + C.POSTAL_CD,'|') +
ISNULL('|' + C.[STATE],'|') +
ISNULL('|' + C.COUNTRY,'|') AS VARCHAR(256)) -- or something big enough
FROM SQLCRM01.[InterAction].dbo.INT_AUX_ADDRESS
) C on C.ADDRESS_ID = B.ADDRESS_ID
--left join SQLCRM01.[InterAction].dbo.INT_AUX_LST_CUSTOM R on R.LISTING_ID = A.LISTING_ID and R.LISTING_SRC_ID = A.LISTING_SRC_ID and R.DIRECTORY_ID = '-10008' and R.LST_CUSTOM_DEF_ID = '1185'
--left outer join SQLCRM01.[InterAction].dbo.INT_AUX_LST_CUSTOM R2 on R2.LISTING_ID = A.LISTING_ID and R2.LISTING_SRC_ID = A.LISTING_SRC_ID and R2.DIRECTORY_ID = '-10008' and R2.LST_CUSTOM_DEF_ID = '1186'
left outer join sqlpms01.cmsnet.dbo.HBM_NAME HN on (cast(A.LISTING_SRC_ID as varchar) + '/' + cast(A.LISTING_ID as varchar)) = HN._INTERACTION_ID
left outer join sqlpms01.cmsnet.dbo.HBM_CLIENT HC on HN.name_uno = HC.name_uno
left outer join SQLPMS01.cmsnet.dbo.HBM_PERSNL HP on HC.RESP_EMPL_UNO = HP.EMPL_UNO
where DISPLAY_NM = A.COMPANY_NM and A.LISTING_TYP_IND = 1 and (A.OWN_DIR_ID = -1 or A.OWN_DIR_ID = '120')
--and R.STRING_VALUE is not NULL
order by A.COMPANY_NM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 18, 2013 at 9:06 am
Linked servers now using dbo level account and speed is back to sub 5 seconds.
I will however need to re think the whole approach as for security reasons I really dont want anything other than read access to the linked data.
thanks for your help
March 20, 2013 at 4:00 pm
Martin Stephenson (3/18/2013)
Linked servers now using dbo level account and speed is back to sub 5 seconds.I will however need to re think the whole approach as for security reasons I really dont want anything other than read access to the linked data.
thanks for your help
Try the OPENQUERY approach ChrisM@Work mentioned, one per remote server.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply