Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ThreePea
Yak Posting Veteran
83 Posts |
 Posted - 2007-08-06 : 17:49:16
|
Our Transactions/sec counter jumped quite a bit when we moved to SQL Server 2005. The move coincided with increased load so we didn't think anything of it until recently. Upon further review, the counter just seems too high.There was an article in SQL Server magazine a few years ago by Brian Moran where he states, "Transactions/sec doesn't measure activity unless it's inside a transaction. Batch Requests/sec measures all batches you send to the server even if they don't participate in a transaction." He goes on to say that Transactions/sec will be skewed lower because it is a subset of Batch Requests/sec. ([url]http://www.sqlmag.com/Article/ArticleID/26380/sql_server_26380.html[/url])The article was written for SQL Server 2000. We conducted tests in 2000 and found what he said to be right on the money. SELECT statements increased Batch Requests/sec, but not Transactions/sec. UPDATE/INSERT/DELETE statements increased both in lockstep. Makes perfect sense so far.We conducted the same tests in 2005 and found a radically different story. While SELECT statements behaved the same, UPDATE/INSERT/DELETE statements showed Transactions/sec skyrocket 2-10x more than Batch Requests/sec for the duration of the statement. In other words, a single transaction submitted by our application fires off exponentially more transactions than the one we submitted. I was unable to pinpoint exactly what these "hidden" transactions were actually doing. Is this something that occurred in 2000 but simply wasn't reported? Or is it new behavior in 2005?While trying to answer these questions we noticed a second strange behavior in 2005. When no queries are being executed the Transactions/sec counter still jumps every six seconds like clockwork. And these phantom transactions number in the thousands. We tried to use profiler to capture what SQL was being executed, but nothing shows up in any SQL Statement or Batch event. However, when we turned on the SQLTransaction event we found it, sort of. An object called GhostCleanupTask runs every six seconds causing thousands of transactions. We don't know exactly what it is doing, but we noticed that it ran consistently on some databases, but never on other databases. Both sets of databases are identical and in use.So, all of this investigation leads me with three final questions.- 1. What is behind all the extra transactions caught by perfmon when I submit a single transaction?
- 2. What is GhostCleanupTask and why does it take so many transactions? (And why does it only run on certain databases?)
- 3. If a potential customer asks for our Transactions/sec count, is it accurate to give them the big number, knowing that our application is only actually submitting a fraction of that? On the other hand, the system apparently is actually doing that many transactions. (For instance, on our production server during peak, Batch Requests/sec is about 4,000, while Transactions/sec hits 26,000.
Any insight would be much appreciated.3P |
|
|
ThreePea
Yak Posting Veteran
83 Posts |
|
|
|
|
|
|
|
Transactions/sec behaves much differently in 2005