SQL Server and Distributed Transaction Tuning

Users complained that a monthly financial report would no longer run SQLServer Reporting Services, (SSRS.)   Upon investigation, it was found that this was a stored procedure that ran from one Annex database, sourcing from another and outer joins to a SQLServer database on a remote server through a linked server configuration.  In attempts to run the report,  my SQL Server Profile traces on both the source SQL Server and the remote SQLServer resulted in consistent sp_reset_connection results from the source and no activity in the remote.

 

I ran my trusty and favorite script to tell me what processes were taking the most resources and quickly realized with statement was the cause:

SELECT  TOP 1000

[Object_Name] = object_name(st.objectid),

creation_time, last_execution_time, total_cpu_time = total_worker_time / 1000,

avg_cpu_time = (total_worker_time / execution_count) / 1000,

min_cpu_time = min_worker_time / 1000, max_cpu_time = max_worker_time / 1000,

last_cpu_time = last_worker_time / 1000, total_time_elapsed = total_elapsed_time / 1000 ,

avg_time_elapsed = (total_elapsed_time / execution_count) / 1000,

min_time_elapsed = min_elapsed_time / 1000, max_time_elapsed = max_elapsed_time / 1000,

avg_physical_reads = total_physical_reads / execution_count,

avg_logical_reads = total_logical_reads / execution_count,

execution_count,

SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset

END

-- qs.statement_start_offset

) /2) + 1) as statement_text

FROM sys.dm_exec_query_stats qs

CROSS APPLY

sys.dm_exec_sql_text(qs.sql_handle) st

WHERE Object_Name(st.objectid) IS NOT NULL

--AND DB_NAME(st.dbid) = '
ORDER BY   total_cpu_time DESC

db_name(st.dbid), total_worker_time / execution_count  DESC

 

Now the important data from here is that the report came up at the top promptly, as it was an challenge to be sure:

Object_Name	execution_time	total_cpu_time	min_cpu_time	max_cpu_time	last_cpu_time	avg_logical_reads
Rpt_ClientRevenueDownloadKa	33:15.1	1040436	507989	532447	532447	4701439

Note that the min and max CPU times are very consistent and the average logical reads are out of this world!  The other nice thing about this report, is that it will tell you exactly, the code that is causing the issue.  You have the choice then of copying the code out of the final column and running and explain plan to see what is the issue or executing a test to see the problem.  The code in question was a distributed query, so an explain plan often does not show what a test of the query would, so I ran the one statement, which did return to the remote server, but with frustrating results:

What you see in the diagram above is both a choice by the server to parallelize the query, which is flooding the server with both waits on parallel and network IO.   The query did end up returning, but only after approximately 10 minutes.

Something is coming across the network to run against the remote server and after verifying statistics, etc. on the remote, I returned to the source server and looked at the query.

The query required a two step temp table to be created before joining to the tables across the linked server.  The issue here, is that this data was flooding both the network as well as the remote database.  The following, without the temp table, completes very quickly:

SELECT

--ISNULL(atv.NAME, 'UNKWN') AS name,

ISNULL(parentCrmA.NAME, 'UNKWN') AS parent,

month(t.DT1) AS pmonth, YEAR(t.DT2) AS pyear,

RIGHT('0' + CONVERT(VARCHAR(3), MONTH(t.DT2)) + CONVERT(VARCHAR(5), YEAR(t.DT2)), 6) AS fdte,

t.DT2 AS pdate, t.P_AMT*(-1) AS amt,

'rev_amt' AS r_amt, 'adj_amt' AS a_amt,

a.GLA_NO AS GLNo, --atv.crmVert AS vertical,

crmA.OID_NAME AS oidname, s.S_NAME AS spc,

c.C_NAME AS Cname, crmA.NC_NAME AS c_cname,

crmA.I_CD AS i_cd

--INTO      #FINData ß2

^nd

 step temp table commented out, along with its columns…

FROM        R_SERVER.FIN_app.dbo.tglTbl t

INNER JOIN  R_SERVER.FIN_app.dbo.tglAtbl a ON t.GLAKey = a.GLAKey

LEFT OUTER JOIN   R_SERVER.FIN_app.dbo.tglItbl i ON t.TNo = i.TNo

LEFT OUTER JOIN   R_SERVER.FIN_app.dbo.tglCtbl c ON i.CKey = c.CKey

--LEFT OUTER JOIN @AccountToVerticalMap atv ON c.CID = atv.cName COLLATE SQL_Latin1_General_CP1_CI_AS

LEFT OUTER JOIN   R_SERVER.FIN_app.dbo.tglStbl s ON i.PSKey = s.SPKey

LEFT OUTER JOIN   R_SERVER.FIN_app.dbo.tglARtbl ar ON t.ARKey = ar.ARKey

LEFT OUTER JOIN   S_SERVER_DB.dbo.Atbl crmA ON c.CID = crmA.Name COLLATE SQL_Latin1_General_CP1_CI_AS AND crmA.DeletionS_CD = 0 AND crmA.S_CD = 0

LEFT OUTER JOIN   S_SERVER_DB.dbo.Atbl parentCrmA ON cA.PA_ID= parentCA.A_IDAND parentCA.DS_CD = 0 AND parentCrmA.S_CD = 0

WHERE a.GLANo between 4000 and 4999

:03 seconds, in fact… J 

The IO issue is the code creates the two temp objects, then attempts to send this data across the network to the linked server and onto the remote database to process the request.  As this is a monthly report, you have to ask yourself, is a temp table really required??  Not in this instance and it is actually the bottleneck to performance.

 

ORIGINAL:

with cAName(MemName)

AS (SELECT      c.CID

FROM  R_SERVER.FIN_app.dbo.tglCtbl c

WHERE c.CID is not null

UNION ALL

SELECT      ar.ARCode

FROM  R_SERVER.FIN_app.dbo.tglARtbl ar

WHERE ar.ARCode is not null

)

INSERT      INTO @ActVertMap

SELECT      DISTINCT

a.Name AS Name,

a.New_Vertical AS Vert

FROM  S_SERVER_DB.dbo.Atbl a

LEFT OUTER JOIN cAName c ON a.Name = c.Name COLLATE Latin1_General_CI_AS

WHERE a.Name is not null

order by a.Name

 

SIMPLE  REWRITE:

drop table cacct_wrk

go

SELECT      c.CID  as "MemName" into cacct_wrk

FROM  R_SERVER.FIN_app.dbo.tglCtbl c

WHERE c.CID is not null

UNION ALL

SELECT      ar.ARCode

FROM  R_SERVER.FIN_app.dbo.tglARtbl ar

WHERE ar.ARCode is not null

go

drop table ActVertMap_wrk

go

SELECT         DISTINCT

a.Name AS mapName,

a.New_Vertical AS mapVert

into ActVertMap_wrk

FROM  S_SERVER_DB.dbo.Atbl a

LEFT OUTER JOIN cacct_tst c ON a.Name = c.MemName COLLATE Latin1_General_CI_AS

WHERE a.Name is not null

order by a.Name

go

 

FINAL REPORTING QUERY REWRITE:

SELECT

ISNULL(atv.NAME, 'UNKWN') AS name,

ISNULL(parentCrmA.NAME, 'UNKWN') AS parent,

month(t.DT1) AS pmonth, YEAR(t.DT2) AS pyear,

RIGHT('0' + CONVERT(VARCHAR(3), MONTH(t.DT2)) + CONVERT(VARCHAR(5), YEAR(t.PDate)), 6) AS fdte,

t.DT2 AS pdate, t.P_AMT*(-1) AS amt,

'r_amt' AS r_amt, 'a_amt' AS a_amt,

a.GL_NO AS GLANo, atv.M_VERT AS vertical,

cA.O_ID AS oidname, s.S_NAME AS spc,

c.C_NAME AS FIN_cname, cA.NC_NAME AS ccname,

cA.I_CD AS icode --INTO      #FINData

FROM        R_SERVER.FIN_app.dbo.tglTbl t

INNER JOIN  R_SERVER.FIN_app.dbo.tglAtbl a ON t.GLAKey = a.GLAKey

LEFT OUTER JOIN   R_SERVER.FIN_app.dbo.tglItbl i ON t.TNo = i.TNo

LEFT OUTER JOIN   R_SERVER.FIN_app.dbo.tglCtbl c ON i.CKey = c.CKey

LEFT OUTER JOIN   ActVertMap_wrk atv ON c.CID = atv.mName
COLLATE SQL_Latin1_General_CP1_CI_AS

LEFT OUTER JOIN   R_SERVER.FIN_app.dbo.tglStbl s ON i.PSKey = s.SPKey

LEFT OUTER JOIN   R_SERVER.FIN_app.dbo.tglARtbl ar ON t.ARKey = ar.ARKey

LEFT OUTER JOIN   S_SERVER_DB.dbo.Atbl cA ON c.CID = cA.Name COLLATE SQL_Latin1_General_CP1_CI_AS AND cA.DeletionS_CD = 0 AND cA.S_CD = 0

LEFT OUTER JOIN   S_SERVER_DB.dbo.Atbl parentCA ON crmA.P_ID= parentCA.A_ID AND parentCA.DeletionS_CD = 0 AND parentCA.S_CD = 0

WHERE a.GLANo between 4000 and 4999

And this returns in .03 seconds!! 😀

 

Using the correct feature for the goal and the use of the code is important.  Where using a temporary object might be a great option for local processing, the limitations of the optimizer in any platform to utilize that temporary object must be taken into consideration.  This was a complex process involving three databases that with growth, resulted in poor performance.