Post reply

SQL Profiler usage suggestions for server load testing?

  • 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

  • May 18, 2005 at 11:08 am

    #559661

    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

  • May 18, 2005 at 11:48 am

    #559672

    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

     

  • May 19, 2005 at 4:45 am

    #559803

    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

     

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

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