Request for count and "volume" of transactions

Question

Request for count and "volume" of transactions

agerard 65681

Ten Centuries

Points: 1027
More actions
March 18, 2019 at 9:17 am

#371748

I am being asked to report on the number of transactions, and the "volume" of transactions for a particular set of queries being run against a database on a SQL 2008 server. The question as it was worded: "What is the average daily and monthly volume for each transaction/query?"
I can easily measure the transaction count, but I am assuming what is meant by "volume" is the total number of bytes of read IO involved in these transactions. I can't find a sql profiler trace column or a DM table that provides this information, the best I can find is Include Client Statistics in SSMS which returns a transaction count and number of bytes, but I believe that byte count is the number of bytes of whatever is written to the query window, and these queries all read from tables in one DB and write to tables in another DB.
Anyone know any tricks I don't?
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

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

agerard 65681 Ten Centuries Points: 1027 More actions · Answer 1

Is that what the "read" column on a sql profiler trace is showing? Appears it may be... sorry for the dumb question then! 😉

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

agerard 65681 - Monday, March 18, 2019 10:10 AM
Is that what the "read" column on a sql profiler trace is showing? Appears it may be... sorry for the dumb question then! 😉

The read column is in pages. Each page is 8KB. If you divide the number of pages by 128.0, it'll return the "volume" in MegaBytes.

--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)

agerard 65681 Ten Centuries Points: 1027 More actions · Answer 3

Thanks much for the answer, Jeff! But interestingly, that seems low. There is one query against a table which is about 700MB in size and where there is no covering index and so the profiler returned about 700,000,000 bytes. If we divide by 128, the number of megabytes returned seems impossibly small...