January 3, 2005 at 1:31 pm
I was tring to get the performance stats like CPU time per transaction, IO per transaction, etc. I tried SQL Profiler, but the event "SQL Transaction" tracks all transaction begin/commit/rollback and doesn't capture the resource used by the transaction. Is there any other ways of tracing the per transaction stats in SQL Server?
Thanks, Stan
January 3, 2005 at 6:33 pm
I don't have a answer to your specific question, but here is what I've done in the past:
Note there's a difference between a SQL "statement", "batch", and a "transaction". My focus was on reducing the time for specific SQL DML (sel/ins/upd/del) with the thought that transaction performance will follow statement improvements. So, with that in mind,
I set up a trace (output to a db table) on "batch completion", recording the start/end datetime, statements, and various performance columns. Then I wrote a simple parse routine to parse the sql syntax for each batch so I could identify and group queries/mods, then it's a simple datediff to find which take the longest and focus on them.
I did write a more extensive set of scripts that analyzed concurrent processes to see what else was going on simultaneous with long running statements, but that's too complicated to go into here.
hope this helps.
January 4, 2005 at 9:20 am
Thank you, John, for sharing your helpful experience!
I am trying to construct an analytic performance model for a database to guide the capacity planning under several predicted growth scenarios. Sounds like you chose "statement" instead of "transaction" as the basic unit to represent the system performance. However, the challenge of it is if there is a clear classification of the transactions (e.g., buyer bids, seller listing products) in your system, it makes more sense to predicte the capacity growth in terms of different types of transactions. Also if "transaction" is the basic unit, the statment mixture is modeled more accurately, while "statement"-based model assumes unexpected statement mixture. Anyway, your suggestion is a very good alternative to choose from. But just out of my curiosity, is SQL Server capable to do the per-transaction performance stats? Or is there a counterpart of Oracle Statspack in SQL Server? Thanks! -Stan
January 4, 2005 at 9:24 am
Oh, another thing is when modeling the response time, it makes more sense to use transaction-based performance model so that it is easier to bridge the performance model and the service level agreement, because the clients/users are more interested in the response time of a certain type of transaction but not statement. -Stan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply