High Transactions per second

  • I see a lot of high transactions/sec on one of the instance(around 2000/sec).....How do i reduce the transactions/sec???

    Any help would be appreciated

    Regards,

    Sandeep

  • You could introduce bottlenecks to slow down the process.

    You could move the database to slower hardware.

    You could change how you define a transaction in your application.

    You could remove all indexes from the database.

    Most people want to inclrease the number of TPS.

    Converting oxygen into carbon dioxide, since 1955.
  • I agree with Steve. Why on earth would you want to reduce the number of transactions per second? This is indicative of a high load on the system. You should be working to ensure that it can maintain that load and support more. Can you explain further what problem you're attempting to solve?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (5/25/2010)


    Why on earth would you want to reduce the number of transactions per second? This is indicative of a high load on the system. You should be working to ensure that it can maintain that load and support more. Can you explain further what problem you're attempting to solve?

    Because sometimes that high load is artificially caused by bad code. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • How is it caused by the bad code???

  • Jeff Moden (5/25/2010)


    Grant Fritchey (5/25/2010)


    Why on earth would you want to reduce the number of transactions per second? This is indicative of a high load on the system. You should be working to ensure that it can maintain that load and support more. Can you explain further what problem you're attempting to solve?

    Because sometimes that high load is artificially caused by bad code. 😉

    Of course, but simply saying, "hey, I've got high transactions" sure isn't a complete picture. Maybe he's supporting NASDAQ, in which case, it makes sense. If it's the local vet that is getting 2000 transactions/minute, there might be a HUGE problem.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Sandy2704 (5/25/2010)


    How is it caused by the bad code???

    Just to give one example, our developers wrote a piece of code that made every call to the database, three times. So our transactions, and load, and contention, etc., were three times as high as they should have been.

    The thing is, just saying that you have X number of transactions doesn't answer the question, how many transactions should you have? What is your system doing? What makes you think that just the number of transactions is a problem?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Trasactions in our application generally range between 7 to 40 /sec......The application is not huge.....I am using a third party tool called precise i3(It gives me alert messages whenever it crosses the pre-defined threshold value).....

    from the 7 days High transactions/sec happened around 2 to 3 times

  • Sandy2704 (5/25/2010)


    Trasactions in our application generally range between 7 to 40 /sec......The application is not huge.....I am using a third party tool called precise i3(It gives me alert messages whenever it crosses the pre-defined threshold value).....

    from the 7 days High transactions/sec happened around 2 to 3 times

    So, the next step is try to identify why you're getting transactions and where they're coming from. Once you know where & when you can work on fixing the problem. You could try setting up a trace to capture calls to the server. Instead, I'd suggest using DMV's. First, I'd look at sys.dm_exec_query_stats. This will allow you to see if any of the queries in cache have huge execution counts. It's possible though that the queries have aged out of cache. If so, then the key would be to run sys.dm_exec_requests during one of the events so that you can see what's being called, from where. Immediately after an event you can go back to sys.dm_exec_query_stats to see what caused most of the traffic.

    Once it's identified, you have to figure out where it's coming from and why.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (5/25/2010)


    Jeff Moden (5/25/2010)


    Grant Fritchey (5/25/2010)


    Why on earth would you want to reduce the number of transactions per second? This is indicative of a high load on the system. You should be working to ensure that it can maintain that load and support more. Can you explain further what problem you're attempting to solve?

    Because sometimes that high load is artificially caused by bad code. 😉

    Of course, but simply saying, "hey, I've got high transactions" sure isn't a complete picture. Maybe he's supporting NASDAQ, in which case, it makes sense. If it's the local vet that is getting 2000 transactions/minute, there might be a HUGE problem.

    Sorry... I was in a hurry and didn't post the full answer. I completely agree with you. A high number of transactions could be just normal usage or they could be symptoms. I also agree that Sandy needs to find out which it is.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (5/25/2010)


    Grant Fritchey (5/25/2010)


    Jeff Moden (5/25/2010)


    Grant Fritchey (5/25/2010)


    Why on earth would you want to reduce the number of transactions per second? This is indicative of a high load on the system. You should be working to ensure that it can maintain that load and support more. Can you explain further what problem you're attempting to solve?

    Because sometimes that high load is artificially caused by bad code. 😉

    Of course, but simply saying, "hey, I've got high transactions" sure isn't a complete picture. Maybe he's supporting NASDAQ, in which case, it makes sense. If it's the local vet that is getting 2000 transactions/minute, there might be a HUGE problem.

    Sorry... I was in a hurry and didn't post the full answer. I completely agree with you. A high number of transactions could be just normal usage or they could be symptoms. I also agree that Sandy needs to find out which it is.

    No worries. You're one of the very last people I'd start a fight with.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Sandy2704 (5/25/2010)


    Trasactions in our application generally range between 7 to 40 /sec......The application is not huge.....I am using a third party tool called precise i3(It gives me alert messages whenever it crosses the pre-defined threshold value).....

    from the 7 days High transactions/sec happened around 2 to 3 times

    Sandy, this is the second time I have seen a post from you about your monitoring system throwing an alert that you didn't know what to do with. In both instances the alert was bogus to begin with.

    I HIGHLY recommend you get a professional on board for a few days to a few weeks to a) help you configure useful and meaningful alerts b) turn off useless ones and c) mentor you on what you might want to do if you hit these alerts.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • On further investigating the High transacttions/sec i noticed it is due to high number of SQL Compilations/sec......It seems some of the statements don't have the Actual Plan and so everytime that particular statement is fired it has to Compile

  • Sandy2704 (6/8/2010)


    On further investigating the High transacttions/sec i noticed it is due to high number of SQL Compilations/sec......It seems some of the statements don't have the Actual Plan and so everytime that particular statement is fired it has to Compile

    If that's true, it's probably time to look for GUI code that has a lot of embedded SQL or possibly at ORM code.

    I agree with what Kevin said... you should probably get a little on-site help from someone like Kevin on this problem. As a side bar, Kevin is very much a "Ninja" on problems like this.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (6/8/2010)


    Sandy2704 (6/8/2010)


    On further investigating the High transacttions/sec i noticed it is due to high number of SQL Compilations/sec......It seems some of the statements don't have the Actual Plan and so everytime that particular statement is fired it has to Compile

    If that's true, it's probably time to look for GUI code that has a lot of embedded SQL or possibly at ORM code.

    I agree with what Kevin said... you should probably get a little on-site help from someone like Kevin on this problem. As a side bar, Kevin is very much a "Ninja" on problems like this.

    Thanks for the props Jeff! BTW, are you at TechEd this week?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply