SQL Server Performance

exec sp_reset_connection

Discussion in 'T-SQL Performance Tuning for Developers' started by anilsaritha, Sep 15, 2006.

  1. anilsaritha New Member

    Dear Gurus

    Below is the trace capture of a save action of one of my forms
    This 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
  2. harsh_athalye New Member

    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"
  3. satya Moderator

  4. allwinm New Member

    Hi, plz check your Backend procedure and include "set nocount on". Your ASP page will work now.Regards,Allwin

Share This Page