SP Recompile causing local transaction to be distributed transaction
-
Hi
I have a SP that access another SQL Server over Linked Servers. The SP syncronizes tables from both servers. as there are some validations and data transformations required, I have to use cursors. (just can't be avoided). SP uses #tables for all dirty manipulations.
The SP pulls data from linked server, loops through fetched data (curosr) and fetches few more table's data as required (inside a cursor while loop). It also does some manipulations (inside a curso loop). None of the this activity is done in a TRANSACTION. Now once all data is ready in #tables, I am inserting it into Main tables WITHIN A SINGLE LOCAL TRANSACTION, either commits it or rollbacks it as per @@error. no where inside a transaction, I am reffereing to the Linked server.
The SP functions well. Now I am facting some error messages on irregularly inside a cursor
Server: Msg 8501, Level 16, State 1, Procedure TestProc, Line 184
MSDTC on server 'MJIE-1223' is unavailable.
Server: Msg 7391, Level 16, State 1, Procedure TestProc, Line 184
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].
On tracing with profiler, I found following events at the end of stored procedure execution.
SP:StmtStarting : This contains a statement with local transaction
SP:CacheRemove
SP:Recompile : SP Name
SP:CacheMiss
SP:CacheMiss
My Interpretation of this trace is : SP is getting Recompiled within a transaction (and the cursor). This somehow seems to refer to the Queries to linked server (which are outside of the transactions and hence attempts to promote the transaction to distributed transaction.
I have also set following in my SP
SET NOCOUNT ON
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET REMOTE_PROC_TRANSACTIONS OFF
Can anyone explain / suggest a workaround ?
-
Can you post you SP code so we can get a better idea of what you are doing. Also if your Proc name begins "sp_" you will get a CacheMiss everytime, renam it and see if it performs better.
-
The actual proc Name does not begin with sp_
and for my test enviornment I renamed procedure to TestProc
Proc code is too long and lengthy which I would make the posting look bad.
Also the major trouble/question that i have been going through is
does a SP recompile in between a cursor causes statement outside to be re-executed ?
that seems to be the case with my procedure. this is some sort of indication how proc is organized. The line number that i get in error message line marked in BOLD
create proc testProc
as
SET NOCOUNT ON
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET REMOTE_PROC_TRANSACTIONS OFF
declare @i int -- and all other @var declarations
create 6 #tables
select @i = count(*) from SER_1.db.dbo.tab1 -- this is prob statement
insert #table1
select col1 from SER_1.db.dbo.tab1
if @i <> @@rowcount
begin
raiseerror ( ' ..... ' )
return
end
DECLARE c_User CURSOR FOR
SELECT col1
FROM #table1
ORDER BY col1 DESC
open c_user
fecth next c_user into @col1
WHILE @@FETCH_STATUS = 0
BEGIN
insert #table2
select col2, col3 from SER_1.db.dbo.tab2
where col2 = @col1
/* a lot of inserts and updates as per requirements */
/* once everything is cooked up */
BEGIN TRAN
insert tab1
select * from tab1
select @IntError = @@error
if @IntError <> 0 GOTO finalstep
/*and 7 more inserts */
finalstep:
if @IntError/no <> 0
rollback tran
else
commit tran
delete all #tables
fecth next c_user into @col1
END
close c_user
deallocate c_user
-
G'day,
I'm deliberately avoiding the transaction context part of this as I'm curious to see what the gurus have to say on it, although fundamentally I'd comment that you can't do anything to a database without being part of a transaction of some sort.
On beating the stored proc recompilation front, though, Ken Henderson gives some excellent insight into this in his "Guru's Guide" stored proc book. Basically, usage of temp tables under various circumstances will force sp recompiles ( eg modification of ddl and extensive data changes ) which is on of the reasons "temp tables are evil" <bg>. If you try declaring your #tables as table variables, this significantly reduces the recompilation susceptibility although it does hamper you in that you can't ( unless one of the gurus is about to pleasantly surprise me ) insert the results of a stored procedure execute into a table variable.
As an alternative, one of the things I am experimenting with in those situations where there just is no alternative to temporary tables I break my sp's into "high-compile likelihood" components and "low-compile likelihood" components on the basis that this gives me a chance to shift my "expensive recompile" portions of code out into less volatile sp's. Again, Ken provides some great tips on managing your temp table contexts, visibility and lifetime in this sort of situation.
Hope this helps,
Mark
-
I spent a lot of time with the SP and Profiler.
During my tests I added a Query HINT to all statement refering to #tables.
i.e. OPTION ( KEEPFIXED PLAN )
This has reduced SP recompilation to ZERO.
However, I am not able to answer myself aboout this Question.
Does SP recompile while it is already running ? ( I saw profiler, answer is YES)
Does SP recompile cause entire plan to be rexecuted ?
( Not sure, as error message indicated it is doing so. but data and print messages inside SP suggest NO )
This is a really Strange behaviour which I am not able to digest .
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply