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. (http://
www.sqlmag.com/Article/ArticleID/26380/sql_server_26380.html)
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, even though both sets of databases have identical
properties.
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.)
I have searched high and low and been unable to find any references to
this particular situation. Any insight would be much appreciated.
Jerry Foster
CTO
Plexus Systems
www.plex.com
jfos@.plex.com
On Mon, 06 Aug 2007 15:28:11 -0700, Jerry <jfos@.plex.com> wrote:
>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.)
>I have searched high and low and been unable to find any references to
>this particular situation. Any insight would be much appreciated.
1. Don't know, very interested. I do know that 2005 breaks big SPs
into many smaller compilation units than did 2K, but I would not have
expected these to be visible as separate trans.
Actually, that may be a question - what if you count SP begins on both
systems?
2. Ghost traditionally does space recovery on tables that lack
clustered keys and have had deletes, and is generally suppressed in
profiler - or at least I've never seen it there.
3. The transaction number sounds bogus as now reported, I'd look for
something else, batches, SPs, etc.
In general, coming up with a baselining algorithm for SQLServer
performance management, has always been a challenge, and is best done
differently depending on local applications concerns.
Interested to see what else you learn about this.
Josh
|||From bol:
Ghost rows
The number of rows that are marked as deleted but not yet removed.
These rows will be removed by a "clean-up thread, when the server is not
busy".
This value does not include rows that are being retained due to an
outstanding
snapshot isolation transaction.
I guess you've discovered what they call the clean-up. You probably have no
control over it. Consider it an MS (microsoft surprise
www.beyondsql.blogspot.com
|||From bol:
Ghost rows
The number of rows that are marked as deleted but not yet removed.
These rows will be removed by a "clean-up thread, when the server is not
busy".
This value does not include rows that are being retained due to an
outstanding
snapshot isolation transaction.
I guess you've discovered what they call the clean-up. You probably have no
control over it. Consider it an MS (microsoft surprise
www.beyondsql.blogspot.com
|||In addition to the other posts:
I think that (at least in 7.0 and 2000) it was ghost cleanup that did shrink. So perhaps some of
your databases has autoshrink?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jerry" <jfos@.plex.com> wrote in message
news:1186439291.965094.120600@.z24g2000prh.googlegr oups.com...
> 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. (http://
> www.sqlmag.com/Article/ArticleID/26380/sql_server_26380.html)
> 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, even though both sets of databases have identical
> properties.
> 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.)
> I have searched high and low and been unable to find any references to
> this particular situation. Any insight would be much appreciated.
> Jerry Foster
> CTO
> Plexus Systems
> www.plex.com
> jfos@.plex.com
>
sql
No comments:
Post a Comment