December 15, 2008 at 9:19 pm
i used TSQL_SPs template to trace the sps of two databases..i made some changes in the template as the file size for huge....i am very confused about few events....in the AUDIT:LOGOUT event class i see the CPU event has a very big number..why is that....i need to analyze and give report saying so and so SP is taking long time...how do i do dat..there are so many events....for SP:STMNT in the event class CPU there is no value wat does this mean..can anyone suggest me an article or white paper where i can find good information about TSQL_SPs template...please suggest.....
December 16, 2008 at 6:37 am
OK. First off, don't collect StmtComplete events as part of a general data collection process. They are for targeted troubleshooting only. Second, I wouldn't worry overly much about the logout event because it just shows a connection closing and with connection pooling, etc., that doesn't necessarily equate to meaningful data. For general performance data collection, I get rpc complete, query complete and deadlock events. That's pretty much all you need.
Do a search here at SSC for profiler scripts and you'll get all kinds of them.
I wrote an introduction to performance [/url]tuning a while back. It might be useful.
"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
December 16, 2008 at 7:37 am
thank you...there are like more than 24k rows in the trace file..how can i conclude that so and so SP is taking more time?...should i go through the entire list?..and also how can i conclude the so and so SP in been exec many no of times..
December 16, 2008 at 8:44 am
You can load the file into a database and then run queries against it. That's how I've been doing it for years. That way you can get aggregates and see which query runs the longest in total or which one has the max run time or which one uses the most cpu in total or the most cpu for a single execution... Once you move the data into a table, querying makes it easy. You can use this function, fn_trace_gettable function, like a table and query against it. For details check the books online.
To really drill down on the querying you will need to clean up the textdata column so that it only shows the procedures or queries run and doesn't include parameter values, but there are multiple methods for doing that available. Again do a search on the site here.
"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
December 16, 2008 at 9:42 am
thanks again for being prompt...do u mean to say that i will need to filter the events so that i do not get the text data...and then transfer into a table...yeah that will be a good idea..and once it is in the table i can just query and find it out right?...thanks..
December 16, 2008 at 10:37 am
iqtedar (12/16/2008)
thanks again for being prompt...do u mean to say that i will need to filter the events so that i do not get the text data...and then transfer into a table...yeah that will be a good idea..and once it is in the table i can just query and find it out right?...thanks..
Very close. You're almost there. I'm not saying that you should filter OUT the texdata, but that you'll need to clean the textdata so that you eliminate parameters. There are all kinds of scripts and utilities out there to do it. But you need to clean out the extraneous garbage so that you're dealing with clean sets of data in order to pull your reports.
"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
December 16, 2008 at 12:57 pm
ok we did a trace and it worked out..thanks...FYI..we just want to find which procs are hitting the db ..thanks..
December 16, 2008 at 1:02 pm
Today you just want to find out which procs are hitting. Tomorrow you're going to realize that you can begin to run aggregates and groupings to put out more meaningful reports on which procedures and queries are running slow. When that comes around, you will need to clean up the textdata because:
EXEC spr_MyProc @Id = 42
does not equal
EXEC spr_MyProc @Id = 52
and you're going to want to know that spr_MyProc was executed twice for a cumulative run time of X, etc. Without at least cleaning out the parameter values, you won't be able to do that.
"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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply