SQL Server Performance Forum – Threads Archive

monitoring transactions

Hey guys, I’m very happy to become a member of this forum. I’m actually new with SQL 2000 and trying to speed up by reading Books online. Anyways, I need some advice or recommendations on what to do. Assuming we have a stand-alone client/server application. This application does have 5 different transaction types. I would like to monitor each transaction types and also monitor each transactions per user. With the data that I will be capturing, I will be able to plot it in a graph. I already have the graphing capability. It’s already working. So, maybe you can give your experiences or advice on how to do this kind of monitoring. An example output would we like this:
Assuming we were to monitor how each user took to do the transaction. username starttime stoptime trantype responsetime(ms)
user1 10:17:01.000 10:17:05.000 withdraw 4
user2 10:17:01.000 10:17:07.000 deposit 6
user1 10:17:03.000 10:17:10.000 balance 7
user3 10:17:04.000 10:17:09.000 deposit 5 On the above table, it shows us 3 different transactions. Maybe with your suggestion, I can use NorthWind as my test db. Any help will be greatly appreciated. Neil

The way to monitoring transactions is using Profiler.
With Profiler you can capture SQL’s, RPC, etc.
Also you can add some columns, like user, duration, or filter by application.
Luis Martin
Moderator
SQL-Server-Performance.com

So using profiler, we can differtiate each incoming transactions?

note that a ‘transaction’ has a different meaning in the database world: ie: a complete or atomic set of operations. In your case I presume a trasnaction is simply a different record into a table, or a different stored procedure. Either way profiler can monitor this although I would not recommend this as a long term solution. Perhaps you might want to create an audit table whereby each time an WITHDRAW stored prcoedure is called it logs it to an audit table…. you are using stored procedures and not ad-hoc SQL statements… right ? <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ />

Yes, you are correct. They are just different records.
Can you guide me on how I can create an audit table? Some applications are using ad-hoc and some applications are using stored procedure. I’ve been trying to understand profiler and a little confused on what to really include in my trace. If it’s ok, can you tell me steps on how I can trace traffic using northwind database? And also, can you guide me on what to put on the last TAB(i think it’s the pattern matching window). This is the window when we create a new trace. Thanks again.

And also, can you explain to me what WITHDRAW stored procedure mean?

I suggest to begin with template: SQLProfilerStandard. You should add events like: Store Procedures–> SP<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />mtmStarted, SP<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />mtmCompleted. Transaction–> All events. Last Tab:Filters, is usefull to filter want you want, dependig of columns you add. Example: You want to trace only transaction with duration over 100 miliseconds, so you should set 100 in duration filter. Luis Martin Moderator SQL-Server-Performance.com

Great. I will give it a kick <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Thanks.

Well since there are ad-hoc SQL Statements in addition to stored procedures, I would recommend creating triggers on important tables which would insert any changes in data to another table. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

]]>