SQL Server Performance Forum – Threads Archive
exec sp_reset_connection
Dear Gurus Below is the trace capture of a save action of one of my formsThis save action takes 11 seconds at our office and it takes 20 seconds at the client place when called through the application.Ours is a core banking application.this bank has got around 43 branches.
When the below trace capture is executed in the Query analyzer it is over within a second.
We are using Asp.Net with Code behind as vb.net.Now i am assigned with the task of performance tuning.
In this trace capture i am finding lots of "exec sp_reset_connection",
Is this really a cause of concern?
Why there is a difference in time between the QA execution and application execution?. Kindly guide me exec sp_reset_connection
go
exec sp_procedure_params_rowset @procedure_name = N’SAM_sIsMnuValid’
go
exec SAM_sIsMnuValid @Sam_User = ‘rprabu’, @BrId = ‘8’, @MnuID = ‘LS85′, @DtTime = ’15-09-2006 14:33:40.9222’, @Role = ‘CLRK’
go
exec sp_reset_connection
go
exec sp_procedure_params_rowset @procedure_name = N’SAM_sChildMnu’
go
exec SAM_sChildMnu @P_UserId = ‘rprabu’, @P_MenuID = ‘LM85’, @P_RoleId = ‘CLRK’, @P_Action = ‘P’
go
exec sp_reset_connection
go
exec sp_procedure_params_rowset @procedure_name = N’SAM_sSelPrdBalnType’
go
exec SAM_sSelPrdBalnType @p_PrdID = ’14’
go
exec sp_reset_connection
go
exec sp_procedure_params_rowset @procedure_name = N’SAM_sSelOprtnPrdID’
go
exec SAM_sSelOprtnPrdID @p_OprtnID = ’65’, @p_MnuID = ”
go
exec sp_reset_connection
go
exec sp_procedure_params_rowset @procedure_name = N’SAM_sSelOprsSetlmntOptns’
go
exec SAM_sSelOprsSetlmntOptns @p_OprtnID = ’65’, @p_ScrID = ‘LM85’, @p_BrID = ‘8’
go
exec sp_reset_connection
go
exec sp_procedure_params_rowset @procedure_name = N’SAM_sSelOptnFrmUIMap’
go
exec SAM_sSelOptnFrmUIMap @p_ScrID = ‘LM85′, @p_OprtnID = ’65’, @p_SetlmntID = ‘BCSH’
go
exec sp_reset_connection
go
exec sp_procedure_params_rowset @procedure_name = N’SAM_sSelEntryAllowed’
go
exec SAM_sSelEntryAllowed @p_ScrID = ‘LM85′, @p_OprtnID = ’65’, @p_SetlmntID = ‘BCSH’
go
exec sp_reset_connection
go
exec sp_procedure_params_rowset @procedure_name = N’SAM_sGetChrgs’
go
exec SAM_sGetChrgs @p_MnuID = ‘LM85′, @p_OprID = ’65’, @p_SettlmntID = ‘BCSH’, @p_BrID = ‘8’, @p_PrdID = ’14’, @p_AcctID = ‘14074’, @p_BusnDt = ’06-09-2006′
go
exec sp_reset_connection
go
exec sp_procedure_params_rowset @procedure_name = N’SAM_sSelOprsOptions’
go
exec SAM_sSelOprsOptions @p_OprID = ’65’
go
exec sp_reset_connection
go
exec sp_procedure_params_rowset @procedure_name = N’SAM_sGetGridXML’
go
exec SAM_sGetGridXML @p_OprtnID = ’65’, @p_MnuID = ‘LM85’
go
exec sp_reset_connection
go
exec sp_procedure_params_rowset @procedure_name = N’SAM_sSelWOAcct’
go
exec SAM_sSelWOAcct @p_OprtnID = ’65’, @p_MnuID = ‘LM85’
go
exec sp_reset_connection
go
exec sp_procedure_params_rowset @procedure_name = N’SAM_sInsAuditLog’
go
exec SAM_sInsAuditLog @AccessType = ‘Screen’, @UserId = ‘rprabu’, @ResID = ‘LM85′, @OprnTime = ’15-09-2006 14:33:41.0003’, @Oprn = ‘Print’
go
exec sp_reset_connection
go
exec sp_procedure_params_rowset @procedure_name = N’SAM_sBRControlSP’
go
exec SAM_sBRControlSP @P_ScrId = ‘LM85’, @P_XMLString = ‘<MAINTAG><HEADER’, @P_Operation = ‘P’, @P_UniqueId = ‘LM85rprabu15-09-2006 14:33:41.0159435’
go
exec sp_procedure_params_rowset @procedure_name = N’SAM_sSelOprtnType’
go
exec SAM_sSelOprtnType @p_OprtnID = ’65’
go
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION
go
exec SeqInit_GetCurrentValue @Purpose = N’IGRP’, @Location = N’8′
go
exec SeqInit_SetNextVal_FRW @NVal = N’78’, @Purpose = N’IGRP’, @Location = N’8′
go
exec SAM_sCHKAPPRVTXN @p_ScrID = N’LM85′, @p_OprtnID = N'<NewDataSet><OprXML><OprtnID>65</OprtnID></OprXML></NewDataSet>’, @p_SetlmntID = N”, @p_UIMapID = N”, @p_BrID = N’8′
go
exec SAM_sGetPrdCatgy @p_PrdID = N’14’
go
exec SeqInit_GetCurrentValue @Purpose = N’LONTXN’, @Location = N’8′
go
exec SeqInit_SetNextVal_FRW @NVal = N’67’, @Purpose = N’LONTXN’, @Location = N’8′
go
exec SAM_sSelOppOprsOptions @p_OprID = N’65’, @p_SetlmntID = N’BCSH’
go
exec SAM_sSelOprtnType @p_OprtnID = N’65’
go
exec SAM_sGetCshExBOID @p_OprtnID = N’65’
go
exec SAM_sSelIDHAcctDet @p_LdgrType = 1, @p_PrdID = N’14’
go
exec SAM_sSelIDHAcctDet @p_LdgrType = 1, @p_PrdID = N’14’
go
exec SAM_sSelEntryAllowed @p_ScrID = N’LM85′, @p_OprtnID = N’65’, @p_SetlmntID = N’BCSH’
go
exec SAM_sGetAcctRVER @p_StrAcctXML = N'<Ledger><NewDataSet>
<HeaderLdgr BrID="8" PrdID="14" AcctID="14074" OprBrID="8" TxnID="0624967" FASTxnID="" InsGrpID="78" JVNo=" " GrdRecStatus="I" OprType="Collection" OprID="65" TxnSrc="M" BusnsDt="06-09-2006" CrtdDt="15-09-2006" CrtdBy="rprabu" CyFK="INR" LstApprvdBy="Auto" LstApprvdDt="15-09-2006" Approve="Y" Reject="N" SendToEdit="N" MnLdgrInsGrpID="0" MnLdgrInsGrpSlNo="0" FrmIDH="N" Ldgr="LON" RVersion="" HdrUnqID="" />
<DetailLdgr OptnType="Penal Interest" OptnTypeID="6" OptnName="Penal Interest" OptnID="061.001" TxnType="C" TxnIDSubSlNo="1" SetlmntID="BCSH" SetlmntOptnName="By Cash" Amount="38.00" TxnID="0624967" FASTxnID="" ContraOprtnID=" " GrdRecStatus="I" TokenNo="0" TxnBrID="8" TxnPrdID="602" TxnACNo="14" TxnJVNo=" " TxnChqNo=" " TxnChqDt=" " TxnDraweeBnk=" " TxnDraweeBrn=" " TxnInFavourof=" " ValueDt="06-09-2006" AvailDt="06-09-2006" Narration="_x0020_" Realised="" RealisedDt="" HdrUnqID="1" InsGrpSlNo="1" MnLdgrInsGrpID="0" MnLdgrInsGrpSlNo="0" Ldgr="LON" />
<DetailLdgr OptnType="Interest" OptnTypeID="2" OptnName="Interest" OptnID="060.001" TxnType="C" TxnIDSubSlNo="2" SetlmntID="BCSH" SetlmntOptnName="By Cash" Amount="62.00" TxnID="0624967" FASTxnID="" ContraOprtnID=" " GrdRecStatus="I" TokenNo="0" TxnBrID="8" TxnPrdID="602" TxnACNo="14" TxnJVNo=" " TxnChqNo=" " TxnChqDt=" " TxnDraweeBnk=" " TxnDraweeBrn=" " TxnInFavourof=" " ValueDt="06-09-2006" AvailDt="06-09-2006" Narration="_x0020_" Realised="" RealisedDt="" HdrUnqID="1" InsGrpSlNo="1" MnLdgrInsGrpID="0" MnLdgrInsGrpSlNo="0" Ldgr="LON" />
</NewDataSet></Ledger>’
go
exec SAM_sBRControlSP @P_ScrId = N’65’, @P_XMLString = N'<NewLdgrDS><Ledger><NewDataSet>
<HeaderLdgr BrID="8" PrdID="14" AcctID="14074" OprBrID="8" TxnID="0624967" FASTxnID="" InsGrpID="78" JVNo=" " GrdRecStatus="I" OprType="Collection" OprID="65" TxnSrc="M" BusnsDt="06-09-2006" CrtdDt="15-09-2006" CrtdBy="rprabu" CyFK="INR" LstApprvdBy="Auto" LstApprvdDt="15-09-2006" Approve="Y" Reject="N" SendToEdit="N" MnLdgrInsGrpID="0" MnLdgrInsGrpSlNo="0" FrmIDH="N" Ldgr="LON" RVersion="46363586" HdrUnqID="" />
<DetailLdgr OptnType="Penal Interest" OptnTypeID="6" OptnName="Penal Interest" OptnID="061.001" TxnType="C" TxnIDSubSlNo="1" SetlmntID="BCSH" SetlmntOptnName="By Cash" Amount="38.00" TxnID="0624967" FASTxnID="" ContraOprtnID=" " GrdRecStatus="I" TokenNo="0" TxnBrID="8" TxnPrdID="602" TxnACNo="14" TxnJVNo=" " TxnChqNo=" " TxnChqDt=" " TxnDraweeBnk=" " TxnDraweeBrn=" " TxnInFavourof=" " ValueDt="06-09-2006" AvailDt="06-09-2006" Narration="_x0020_" Realised="" RealisedDt="" HdrUnqID="1" InsGrpSlNo="1" MnLdgrInsGrpID="0" MnLdgrInsGrpSlNo="0" Ldgr="LON" />
<DetailLdgr OptnType="Interest" OptnTypeID="2" OptnName="Interest" OptnID="060.001" TxnType="C" TxnIDSubSlNo="2" SetlmntID="BCSH" SetlmntOptnName="By Cash" Amount="62.00" TxnID="0624967" FASTxnID="" ContraOprtnID=" " GrdRecStatus="I" TokenNo="0" TxnBrID="8" TxnPrdID="602" TxnACNo="14" TxnJVNo=" " TxnChqNo=" " TxnChqDt=" " TxnDraweeBnk=" " TxnDraweeBrn=" " TxnInFavourof=" " ValueDt="06-09-2006" AvailDt="06-09-2006" Narration="_x0020_" Realised="" RealisedDt="" HdrUnqID="1" InsGrpSlNo="1" MnLdgrInsGrpID="0" MnLdgrInsGrpSlNo="0" Ldgr="LON" />
</NewDataSet><Apprptn>
<ApprRec OptnTypeID="1" OptnType="Principal" OverDueAmt="0" OptnAmt="0" PendAmt="0" BrID="8" PrdID="14" AcctID="14074" GLHeadName="JEWEL_LOAN" MainHead="Y" OSAmt="7500.00" GLHeadIDCD="C" Purpose="DEM" CurrDueAmt="0" CurrPendingAmt="0" PrevDueAmt="0" PrevPendingAmt="0"/><ApprRec OptnTypeID="2" OptnType="INTEREST" OverDueAmt="923.0000" OptnAmt="62" PendAmt="861" BrID="8" PrdID="14" AcctID="14074" GLHeadName="INT_RECD_ON_JEWEL_LOAN" MainHead="N" OSAmt="0" GLHeadIDCD="C" Purpose="DEM" CurrDueAmt="0" CurrPendingAmt="0" PrevDueAmt="923.00" PrevPendingAmt="861"/><ApprRec OptnTypeID="6" OptnType="PENAL INTEREST" OverDueAmt="38.0000" OptnAmt="38" PendAmt="0" BrID="8" PrdID="14" AcctID="14074" GLHeadName="P_INT_RECD_ON_JL" MainHead="N" OSAmt="0" GLHeadIDCD="C" Purpose="DEM" CurrDueAmt="0" CurrPendingAmt="0" PrevDueAmt="38.00" PrevPendingAmt="0"/><ApprRec OptnTypeID="4" OptnType="Notice Charges MI" OverDueAmt="0" OptnAmt="0" PendAmt="0" BrID="8" PrdID="14" AcctID="14074" GLHeadName="NOTICE_CHARGES_MI" MainHead="N" OSAmt="0" GLHeadIDCD="C" Purpose="DEM" CurrDueAmt="0" CurrPendingAmt="0" PrevDueAmt="0" PrevPendingAmt="0"/><ApprRec OptnTypeID="10" OptnType="Notice Charges EC" OverDueAmt="0" OptnAmt="0" PendAmt="0" BrID="8" PrdID="14" AcctID="14074" GLHeadName="NOTICE_CHARGES_EC" MainHead="N" OSAmt="0" GLHeadIDCD="C" Purpose="DEM" CurrDueAmt="0" CurrPendingAmt="0" PrevDueAmt="0" PrevPendingAmt="0"/><ApprRec OptnTypeID="59" OptnType="Postage & Telegraph" OverDueAmt="0" OptnAmt="0" PendAmt="0" BrID="8" PrdID="14" AcctID="14074" GLHeadName="POSTAGE_AND_TELEGRAPH" MainHead="N" OSAmt="0" GLHeadIDCD="C" Purpose="DEM" CurrDueAmt="0" CurrPendingAmt="0" PrevDueAmt="0" PrevPendingAmt="0"/><ApprRec OptnTypeID="3" OptnType="Advertisement Charges" OverDueAmt="0" OptnAmt="0" PendAmt="0" BrID="8" PrdID="14" AcctID="14074" GLHeadName="ADVERTISEMENT_CHARGES" MainHead="N" OSAmt="0" GLHeadIDCD="C" Purpose="DEM" CurrDueAmt="0" CurrPendingAmt="0" PrevDueAmt="0" PrevPendingAmt="0"/></Apprptn>
</Ledger></NewLdgrDS>’, @P_Operation = N’P’, @P_UniqueId = N’LM85rprabu15-09-2006 14:33:41.0159435′
go
exec SAM_sSelIDHAcctDet @p_LdgrType = 1, @p_PrdID = N’14’
go
exec SAM_sSelEntryAllowed @p_ScrID = N’LM85′, @p_OprtnID = N’65’, @p_SetlmntID = N’BCSH’
go
exec SAM_sSelOprtnType @p_OprtnID = N’65’
go
exec SeqInit_GetCurrentValue @Purpose = N’IDHTXN’, @Location = N’8′
go
exec SeqInit_SetNextVal_FRW @NVal = N’17’, @Purpose = N’IDHTXN’, @Location = N’8′
go
exec SAM_sGetCshExBOID @p_OprtnID = N’65’
go
exec SAM_sInstLdgrPost @p_LdgrXML = N'<NewLdgrDS><Ledger><NewDataSet>
<HeaderLdgr BrID="8" PrdID="602" AcctID="14" OprBrID="8" TxnID="0624917" FASTxnID="" Baln="N" TmpVch="Y" IsExists="N" GrdRecStatus="I" InsGrpID="78" JVNo=" " OprType="Collection" OprID="65" TxnSrc="M" BusnsDt="06-09-2006" CrtdDt="15-09-2006" CrtdBy="rprabu" CyFK="INR" LstApprvdBy="Auto" LstApprvdDt="15-09-2006" Approve="Y" Reject="N" SendToEdit="N" MnLdgrInsGrpID="78" MnLdgrInsGrpSlNo="0" FrmIDH="N" Ldgr="IDH" RVersion="" SunRevBrId="" SunRevBusnsDt="" SunRevTxnId="0" SunReversed="N" />
<DetailLdgr OptnType="Penal Interest" OptnTypeID="6" OptnName="Penal Interest" OptnID="061.001" TxnType="C" TxnIDSubSlNo="1" SetlmntID="BCSH" SetlmntOptnName="By Cash" Amount="38.00" TxnID="0624917" FASTxnID="" GrdRecStatus="I" TokenNo="0" ContraOprtnID="65" TxnBrID="8" TxnPrdID="14" TxnACNo="14074" TxnJVNo=" " TxnChqNo=" " TxnChqDt=" " TxnDraweeBnk=" " TxnDraweeBrn=" " TxnInFavourof=" " ValueDt="06-09-2006" AvailDt="06-09-2006" Narration="_x005F_x0020_" InstPost="N" Realised="" RealisedDt="" HdrUnqID="1" AcctSlNo="1" InsGrpSlNo="2" MnLdgrInsGrpID="0" MnLdgrInsGrpSlNo="0" Ldgr="IDH" />
<DetailLdgr OptnType="Interest" OptnTypeID="2" OptnName="Interest" OptnID="060.001" TxnType="C" TxnIDSubSlNo="2" SetlmntID="BCSH" SetlmntOptnName="By Cash" Amount="62.00" TxnID="0624917" FASTxnID="" GrdRecStatus="I" TokenNo="0" ContraOprtnID="65" TxnBrID="8" TxnPrdID="14" TxnACNo="14074" TxnJVNo=" " TxnChqNo=" " TxnChqDt=" " TxnDraweeBnk=" " TxnDraweeBrn=" " TxnInFavourof=" " ValueDt="06-09-2006" AvailDt="06-09-2006" Narration="_x005F_x0020_" InstPost="N" Realised="" RealisedDt="" HdrUnqID="1" AcctSlNo="1" InsGrpSlNo="2" MnLdgrInsGrpID="0" MnLdgrInsGrpSlNo="0" Ldgr="IDH" />
</NewDataSet></Ledger></NewLdgrDS>’, @p_DELLdgrXML = N'<NewLdgrDS><Ledger><NewDataSet><HeaderLdgr></HeaderLdgr></NewDataSet></Ledger></NewLdgrDS>’, @p_AllowOverride = N’N’
go
exec IBT_IsIBTOprtn @P_OprtnId = N’ ‘
go
COMMIT TRANSACTION
go
exec sp_procedure_params_rowset @procedure_name = N’LON_sGrtRecForChalln’
go
exec LON_sGrtRecForChalln @p_OprID = ’65’, @p_BusnsXML = ‘<NewDataSet>
‘
go
exec sp_procedure_params_rowset @procedure_name = N’RptW_sRptParamByOrder’
go
exec RptW_sRptParamByOrder @P_RptCode = ‘CHP’
go
exec sp_reset_connection
go
exec sp_procedure_params_rowset @procedure_name = N’RptW_sReportMappingDetail’
go
exec RptW_sReportMappingDetail @P_ReportCode = ‘CHP’, @P_Option = default
go
exec sp_reset_connection
go
exec sp_procedure_params_rowset @procedure_name = N’SAM_sSelPrdBalnType’
go
exec SAM_sSelPrdBalnType @p_PrdID = ’14’
go
Deep Regards
Anil Kottayam
Never Give Up
There is nothing to worry about sp_reset_connection. It is a lightweight sp used for connection pooling I suppose. If you want to find out more what sp_reset_connection does, check out this link: http://www.sqldev.net/misc/sp_reset_connection.htm "Nothing is Impossible"
http://msdn.microsoft.com/msdnmag/issues/05/01/ASPNETPerformance/
http://msdn.microsoft.com/msdnmag/issues/06/07/WebAppFollies/
http://www.codeproject.com/useritems/ASPNET_Best_Practices.asp
http://www.odetocode.com/Articles/237.aspx Fyi on keeping the application performance. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Hi, plz check your Backend procedure and include "set nocount on". Your ASP page will work now.Regards,Allwin
]]>