June 24, 2010 at 3:51 am
Dave,
saw your status online. I tried the Server side trace in test server. I have saved the trace script ouput (as .trc) file. From that, how can i get the total Transactions on a day (or on a particular time period). Is using trace function to select the data the only way?
Its open for all to answer. since dave had answered initially, addressed him in this post
June 24, 2010 at 4:00 am
As far as i know ,probably wrong, but fn_trace_gettable is the only way to read .trc files. The trace file wont tell you how many transactions there have been , for that you need to summarise the perfmon data.
The select statement did not show a transaction count as a select does not need one.
Do not confuse statements with transactions and assume that the count of both should be equal.
June 24, 2010 at 4:16 am
Dave,
right now, a bit confused. "The trace file wont tell you how many transactions there have been , for that you need to summarise the perfmon data"... any idea how to go about it...
Also, if its not a big task, can you clarify "statements" & "transactions"? Even a high level view may also sufficce
June 24, 2010 at 5:40 am
Still waiting for an reply... am near to the finish line..
June 24, 2010 at 6:00 am
balasach82 (6/24/2010)
Still waiting for an reply...
Patience. We're all unpaid volunteers with our own jobs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 24, 2010 at 7:50 am
Yep, understand you Gail. Forums like these are Godsend (as long as its free 😀 🙂 )
Its just that i cant wait to see the results and its implementation... think u know the feeling... 😀
June 24, 2010 at 8:16 am
balasach82 (6/24/2010)
Dave,right now, a bit confused. "The trace file wont tell you how many transactions there have been , for that you need to summarise the perfmon data"... any idea how to go about it...
Also, if its not a big task, can you clarify "statements" & "transactions"? Even a high level view may also sufficce
It really depends on how you've set up the trace and how your system is calling stuff (procedures or ad hoc sql) and how you're maintaining transactions (through the client, through the procedures/sql, combination of both). Getting a count of transactions, and when I say transactions I mean transactions as defined in the BOL, you're still better off getting them through performance counters. But, I think, based on what you're saying, you don't actually want transactions. What you want to know is the number of calls that are made to the database, regardless of transaction. That's why you want trace events and that's why you want to marry the information to the procedures themselves.
With this in mind, collect the data using trace events as has been described. And, as has been described you'll need to load the data from the trace file into a table using ::fn_trace_gettable. After you get it into a table, you'll want to run queries against it to generate aggregate information just like it was any other data. The only trick is you'll have to clean up the textdata field to eliminate or mask parameters so that you get true aggregates. This isn't the easiest of tasks, so you might want to look into a third party tool (sql benchmark pro, there are others). I think this is what you're trying to do. There are articles out there detailing this process greater than I have. I'd check Gail's articles over on Simple-Talk (linked above I think).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 24, 2010 at 8:37 am
BEGIN TRANSACTION
UPDATE SomeTable Set SomeColumn = @AVariable
INSERT INTO AnotherTable (StrCol)
SELECT SomeColumn FROM SomeTable
SELECT SomeColumn, OtherColumn FROM AnotherTable
COMMIT TRANSACTION
One transaction, three statements.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 28, 2010 at 6:34 am
Why "clean up the textdata field"? The data from fn_trace_gettable has all the required parameters...but also has fields where there's nothing but NULL .. What is that NULL?
June 28, 2010 at 7:13 am
The text data field shows the full query, including parameters passed. Take these two calls as an example:
EXEC spMyProc @parm1 = 42, @parm2 = 'Optional Parameter'
EXEC spMyProc @parm1 = 42
Now, you want to see how many times the procedure spMyProc was called. Can you group by the values to see the count of two? No, because the parameters are different. What if it looks like this:
EXEC spMyProc @parm1 = 42, @parm2 = 'Optional Parameter'
EXEC spMyProc @parm1 = 42, @parm2 = 'My Unique Optional Parameter'
Again, you can't get an aggregate count of the procedure call. So, there are third party tools out there that can either strip parameters off, or simply change all parameter values so that you get this:
EXEC spMyProc @parm1 = ?, @parm2 = ?
EXEC spMyProc @parm1 = ?, @Parm2 = ?
Then you can aggregate, but only where all the parameters match. You see, it gets complicated. It's a great tool, but there's work involved.
The places where the are NULL values are events that don't put anything into the texdata column.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 28, 2010 at 7:18 am
Thanks....Thanks....Thanks.
Thats a great way of explaining. Now I understand. Its complicated to get the query's count(which query ran how many times).
Still having one doubt.. that fileds with NULL values.. how is it generated.... Is it any system/sql process...
June 28, 2010 at 7:30 am
balasach82 (6/28/2010)
Thanks....Thanks....Thanks.Thats a great way of explaining. Now I understand. Its complicated to get the query's count(which query ran how many times).
Still having one doubt.. that fileds with NULL values.. how is it generated.... Is it any system/sql process...
The null textdata is not from a stored procedure call or query. It's from some other event that you're collecting that doesn't have any information to put into the textdata column.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 28, 2010 at 7:34 am
so it maybe a background process...right?.. Also can I omit such records for my calculation, since they are not a transaction in strict sense.
June 28, 2010 at 7:37 am
No, it's from some different event that your trace is picking up. Check what events you have the trace listening for.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 28, 2010 at 7:42 am
balasach82 (6/28/2010)
so it maybe a background process...right?.. Also can I omit such records for my calculation, since they are not a transaction in strict sense.
Not knowing the events you've defined, I can't tell you what it is. I can tell you what it isn't. It isn't a query as defined by an RPC Completed or SQL Batch Completed event. Those will both put data into the textdata field. So yes, I would ignore it in trying to count the number of calls to the database. Note, I didn't say transactions. As Gail noted above, a transaction can consist of multiple procedure calls. If you want a count of transactions, defined here, then you should use other means of measuring, as we talked about earlier in the thread.
I'm just trying to be clear so that you get exactly what it is that you need and aren't surprised by any results.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply