SQL Server Performance

Will transaction reduces sql performance

Discussion in 'General DBA Questions' started by kraghunath, Dec 12, 2006.

  1. kraghunath New Member

    Hi All,

    I have a scenario where i have to use ado.net transaction. This transaction losts for almost 10min. During the transaction period none of users will be accessing the application. I am using many temporary tables in stored procedures which participate in the transaction. I would like to know after the ADO.Net transaction is complete will there be any hit on sql - server peformance.

    When the sql server and applicaiton server(IIS) is on the same machine ( test enverionment) after the transaction is completed my system is giving me a message saying virtual memory is low blah blah...

    Can you kindly let me know the answer and reason.

    Thanks,
    Raghunath K

  2. ranjitjain New Member

    It will be good if you put your transaction handling routine inside stored procedure instead of ado.net transaction.
    From client application you call only stored procedure and in the SP
    you can keep transaction as minimal as possible,
    because SQL server needs to wait for any transaction to be accomplished.
  3. dineshasanka Moderator

    it seems you have huge transaction, which is the reason for the less performance

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  4. satya Moderator

    What are the settings of Memory on SQL server?
    Do you see any issues with scheduled jobs or blocking during this transaction process?
    As its a test machine try to reduce the transaction size for better 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.
  5. FrankKalis Moderator

    It would certainly be helpful, if you could post some of your code.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de

Share This Page