May 24, 2010 at 3:20 pm
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
May 24, 2010 at 3:54 pm
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.
May 25, 2010 at 6:30 am
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
May 25, 2010 at 8:33 am
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
Change is inevitable... Change for the better is not.
May 25, 2010 at 9:03 am
How is it caused by the bad code???
May 25, 2010 at 9:04 am
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
May 25, 2010 at 9:10 am
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
May 25, 2010 at 9:18 am
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
May 25, 2010 at 9:23 am
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
May 25, 2010 at 11:09 am
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
Change is inevitable... Change for the better is not.
May 25, 2010 at 11:14 am
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
May 26, 2010 at 7:44 am
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
June 8, 2010 at 2:23 pm
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
June 8, 2010 at 7:06 pm
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
Change is inevitable... Change for the better is not.
June 9, 2010 at 1:46 pm
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 CompileIf 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