April 29, 2015 at 12:28 pm
I have a trace that I create daily which tracks all procs which run for 3 seconds or longer. I periodically review the trace to find calls which commonly timeout or run long. Recently, I found something I don't understand.
My environment is distributed, and the problem I'm seeing involves a linked server call. On Server A, a stored procedure is called which includes a linked server call to another server, Server B. I see both calls- the original proc call on Server A, and the linked server call on Server B- in my trace as the duration is usually 5-10 seconds.
What I can understand is this: the proc call on server A finishes successfully (Error=0-Success in the trace), but the linked server call finishes with Error=2-Abort. There is no try-catch happening or anything like that anywhere in the proc. The proc called on Server A does a select joining tables on the linked server using 4 part naming. All of the tables being joined exist on server B, so we're not actually joining "across" the linked server (this is a potential performance problem, I know, but I don't see how that could cause this problem) and the query over to server B is a translated query like:
declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,NULL,N'SELECT "Tbl1011"."A_real_columname" "Col1236","Tbl1011"."another_real_column" "Col1237","Tbl1011"."Third column" "Col1238","Tbl1011"."fourth_column_name" "Col1239","Tbl1011"."fifthcolumnname" "Col1240","Tbl1011"."yet_another_obfuscated_column" "Col1242" FROM "DATABASENAME"."Owner"."tablename" "Tbl1011" ORDER BY "Col1237" ASC'
select @p1
The problem is not a timeout- DTC and linked server timeout values are several minutes, and the query only runs for a few seconds.
Any thoughts are appreciated
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply