I found a huge SQL server profiler tuning trace on a production server, can I do anything usefull with it?

  • A fellow employee quit the job a week ago and I have been assigned to watch over a (small) production SQL server he was administering. When I logged on a couple of days ago I found that SQL profiler has been running for two months straight saving its traces to a file (actually 2 files because it rolled over after 500MB). This server runs a small legacy app with about 20 users and nobody ever complained for the app's performance.

    Now I am not a DBA per se, I am a developer with a average grasp of DBAing, so my question to you more experienced guys is this: Can I do anything usefull with this trace, such as feeding it to Microsoft's Database Tuning Advisor? or should I just ignore it and delete it? After running for two straight months it has captured so much data that I feel I can do something usefull with it....

  • d viz - Tuesday, February 7, 2017 12:58 PM

    A fellow employee quit the job a week ago and I have been assigned to watch over a (small) production SQL server he was administering. When I logged on a couple of days ago I found that SQL profiler has been running for two months straight saving its traces to a file (actually 2 files because it rolled over after 500MB). This server runs a small legacy app with about 20 users and nobody ever complained for the app's performance.

    Now I am not a DBA per se, I am a developer with a average grasp of DBAing, so my question to you more experienced guys is this: Can I do anything usefull with this trace, such as feeding it to Microsoft's Database Tuning Advisor? or should I just ignore it and delete it? After running for two straight months it has captured so much data that I feel I can do something usefull with it....

    How useful it is depends on what was being traced for what reasons. So it's likely unknown. For myself, I'd probably import the trace files into a table in a non-production environment and query and see what I can find. Just out of curiosity. If you wanted to go down that path, you can find an examples of using select into and fn_trace_get_table to do that in this article:
    sys.fn_trace_gettable (Transact-SQL)

    You can join that table to sys.trace_events to see the event names:
    SELECT te.name as EventClassName, tr.*
    FROM YourTableNameForTrace tr
    INNER JOIN sys.trace_events te
    ON tr.EventClass = te.trace_event_id

    Sue

  • you can use this trace to create a baseline may be using PAL (performance analysis tool) and use it for future problems.  here is the reference how to use it https://www.youtube.com/watch?v=8fxEs3pPmNI

  • A substantial profiler trace, logging a lot of data for which you're unsure what to do with; that may cause more problems than it helps. It can consume CPU or even cause blocking. When they asked you to watch over the server, did they mention any specific issues to be vigilant for?

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • I agree with Sue_H. You can load the SQL profiler traces to a table in a non-production environment and query it. You may find info on stored procedures that are captured in the trace files such as: Long running stored procs, Top SP in terms of CPU, Reads, writes , etc.

  • Thank you all for your answers,

    I managed to contact the ex-DBA and got a bit of rational for this trace. The app that uses the DB is an old VB6/ADO that is no longer being maintained and it is used as-is. Since there are very few users and not a lot of data (total size of the DB is around 500 MB) he wanted to experiment with the performance impact of SQL profiler on a Production machine with a non critical application. He also wanted a long running trace so the data was representative of the actions performed. The trace is based on the "Tuning profile" of SQL Server Profiler.

    He was going to do what Sue_H suggested: Load the trace to a table and try to improve performance for the application since he didn't have access to the source code of the app. He also wanted to experiment and evaluate the DTA suggestions vs common sense tuning :-).

    He actually never got around to do so and admitted forgetting to shut the trace down. He was actually delighed to hear that so much data was gathered without any users complaining.

    Since we do have a test server available,  I will probably go down the same path and treat this as a learning excersize. follwing Sue's advice. It is a nice oppurtunity to get a bit more competent with SQL server under the hood.

    Thank you all for your suggestions!

Viewing 6 posts - 1 through 5 (of 5 total)

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