SQL Server Magazine

To access the demonstration scripts from Kalen Delaney’s SQL Server Magazine LIVE! Webinar "SQL Server Internals for Troubleshooting and Tuning: Part 2—Tracing Your SQL Server Applications," January 25, 2002, just click Download the Code.

Kalen Delaney, Webinar #2

Questions and Answers:

Marina Medvedev Asked: Can we capture unlogged transactions?

Answered: The tracing mechanism is not related to logging at all. You can capture statements as they are submitted, whether or not the changed data is logged is completely irrelevant to the trace.

James J Leddy Asked: Can you have a trace START at a specific time?

Answered: If the trace is defined using the sp_trace* procedures, you can include those procedure calls as the text of a job with a step of type Transact-SQL. You can just paste the sp_trace* procedure calls in the job step definition box, or create a procedure, and have the TSQL job step call the procedure. Schedule the job to start when you need it to, and make sure SQL Agent is running.

Virender Ajmani Asked: Why does Msft restrict the usage of Profiler to users having sa privileges. Even granting execute privileges to profiler extended stored procedures does not allow the user to get into profiler

Answered: I'm sorry I can't answer most WHY questions. I know that Microsoft is aware of the impact that this restriction has. The best workaround is to do your tracing on a test server with a simulated workload running the same apps that are run in production.

James J Leddy Asked: Is the save file on the SQL Server itself or can it be anywhere?

Answered: The file can be any drive that can be accessed. If you are defining the trace from Profiler, and have the option checked to have the server process the trace data, it means that the Profiler client will process the data, and the trace file must be in a location that the client can write to. If you check the box to have the server process the trace data, or you are creating the trace using sp_trace* procedures, then the file must be in a location that the server can write to. The account the server runs under must have write permissions, and the file should be specified using a UNC name, not a mapped drive letter.

Ron Sirvent Asked: Is it possible to run a trace automatically every time SQL Server starts? Please note that the trace is defined thru Profiler not with stored procs like sp_trace_xxx.

Answered: Any trace you define in profiler can be defined using stored procs, by using the 'script trace' option from the profiler. Once you have converted your trace to a script with the trace stored procedures, you can embed those procedure calls in a stored procedure, and mark the procedure to have the 'autostart' property. See the Books Online for the 'sp_procoption' procedure on how to define a trace as 'autostart'.

Arindam Sen Asked: when the data is captured in a table, why is that we cannot see all the details about all the sql that was executed

Answered: I don't know what data you're referring to that can't be seen.

Bill Buzzard Asked: Can poor SQL that is captured be tuned using this tool?

Answered: Profiler is not a tuning tool. It is an analysis tool, so you can analyze the impact that your tuning efforts have.

Jude Asked: Is there a way to view the value of variables in a trace?

Answered: If you capture all your SQL statements you'll be able to see where the variables are assigned values. But if you really want to watch them as they change, you can either replay the trace with the show results option, or use the TSQL Debugger from the Query Analyzer.

James J Leddy Asked: So you can capture trace into file via SQL procs and then use PROFILER to decipher it?

Answered: Yes, the profiler doesn't care how the trace was defined. Actually, even if you define a trace using profiler, behind the scenes the sp_trace* procedures are executed to capture the data. So the data in the trace file is always in the same format, and any saved trace file (.trc) can be opened and analyzed using the Profiler tool.

Ron Sirvent Asked: Currently C2 Audit mode option comes with 200M default file roll-over size. This option is unchangeable. Will it be changeable in the next SQL Server releases?

Answered: There is very little public information about the next version available yet. In fact, it is still so early, that the features are not all defined yet. You can make something happen by writing to sqlwish@microsoft.com. The devs DO read all the mail that comes in and they take your requests seriously. (Obviously, I'm not saying that they grant every wish, but they do consider them.)

John Budaj Asked: Do you have a list of standard columns and events you would start with if you were just starting out to monitor activity?

Answered: I included some in the presentation, and you can check the slides. Also, the supplied templates in Profiler are excellent.