SQL Profiler usage suggestions for server load testing?

Question

SQL Profiler usage suggestions for server load testing?

Johnny Ed

Old Hand

Points: 326
More actions
May 18, 2005 at 10:07 am

#164623
Hi all,
Our users have complained about poor performing queries, disconnections from the application under load (reported via the SQL Server ODBC DSN), and general performance sluggishness, as is their right.
So I organsied a performance stress test, and have just captured some data after running several sample loads through the front end reporting tool (BO).
I have run a test using the SQLProfilerStandard template, excluding system IDs and this hasnt captured disconnects, locks or anything really that useful other than BatchCompleted SQL statements. This only seems useful for trying to identify long running SQL (although it doesnt seem to capture the full statement ). This is obviously due to my poor choice of template.
I have written a script that captures all SQL Perf Counters to a table for later analysis, but I also need to run Profiler on the server to capture useful events ( which is a slighty weaker side to my tuning )
What other Events or Data Columns would people recommend I capture (and what will it help me identify) ?
Any useful tips or links would be appreciated,
Many thanks in advance,
J.
```
Events:
EventClass
TextData
DatabaseID
NTUserName
ClientProcessID
ApplicationName
LoginName
SPID
Duration
StartTime
Reads
Writes
CPU
```
```
Data Columns:
Audit Logon
Audit Logout
Exisitng Connection
RPC:Completed
SQL:BatchCompleted
```

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

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

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 1

Have a look at this http://msdn.microsoft.com/SQL/default.aspx?pull=/library/en-us/dnsqlpro04/html/sp04j1.asp should give you some ideas

ramses2nd Hall of Fame Points: 3720 More actions · Answer 2

I have been using the suggestion of the following website. The nice thing is that you can download the trace file and stored procedures used to interogate the data profiled.

http://vyaskn.tripod.com/analyzing_profiler_output.htm

Johnny Ed Old Hand Points: 326 More actions · Answer 3

Thanks guys, both very useful posts. Im running with Craig's link right now.

For those who are also reading this thread, here are some useful descriptions of the standard Profiler templates.

http://www.extremeexperts.com/sql/articles/TraceTemplate.aspx