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 [email protected]. 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.
debra burgess Asked: Can you, in email, discuss any impact a memory manager software has on SQL server? And can you state which traces you would use to determine memory problems with an application?
Answered: Tracing is probably not the best tool for this. I would look at the System Monitor, which has numerous memory counters. There are events that show you when memory grows or shrinks, but tying that to a particular application could be tricky.
Ron Sirvent Asked: These SPs are available in SQL 2000 but how to accomplish the similar server-side tracing in SQL 7 by using other SPs or Ex-SPs? Best practices?
Answered: SQL Server 7 has a set of procedures that start with xp_trace*. You can take a trace that you defined in SQL Server 2000 profiler, and script it as both a SQL Server 2000 script and a SQL Server 7 script to compare the differences. The mechanisms of choosing which events and data, from the profiler, are very similar between the versions; just the procs run behind the scenes are very different.
Chris Becker Asked: Can you script a trace that's created in SQLProfiler tool?
Answered: Yes, this option is available from the file menu, with the 'script trace' option.
Ron Sirvent Asked: Is it possible to define a trace thru Profiler and then change/enhance it programmatically?
Answered: I'm not exactly sure what you mean by programmatically. The sp_trace_setevent procedure allows you to add new event/data values to the trace if it is stopped, or you can set an event/data combination to off, so it won't be captured any more.
Virender Ajmani Asked: In Sql Server 7.0, are there any issues with xp_trace_setquedestination? I can never get this to work in Sql Server 7.0
Answered: I am not aware of any, and I couldn't find anything in the Knowledgebase. You might consider posting this question on the peer support forum on the SQL Server Magazine web site.
Chuck Boyce Asked: is blackbox on sql 7?
Answered: In SQL Server 7 blackbox is set up completely differently, not using the procedures that create 'regular' traces. There is a special procedure that can be executed by a system administrator, called xp_trace_setqueryhistory. This procedure starts automatically saving the last 100 queries in a buffer, called a 'ring' buffer, because when it gets filled, it loops around and starts overwriting itself. There are three ways to have the contents of this buffer written to a .trc file that can be read with the Profiler:
- The system generates an error of severity greater than 17. (You cannot force this by using RAISERROR, even if you specify level 18 or above. It has to be a server initiated error.) The file will be called blackbox.trc.
- The server crashes unexpectedly. The file will be called blackbox.trc.
- You run the procedure xp_trace_flushqueryhistory and specify a file name. Check the Books Online for details.
Note that SQL Server always appends to the blackbox.trc file. If you encounter a situation in which frequent failures occur, this file will continue to grow. You must periodically delete this file or remove all its contents (perhaps after copying its contents to another location) to keep it from growing indefinitely.
Ron Sirvent Asked: Will black box work when server crashes or when server is rebooted or both?
Answered: Yes, the black box file will be available when SQL Server stops for any reason.
Bill Buzzard Asked: Black box easier way to setup?
Answered: It can't get much easier than it already is in SQL Server 2000. You can use the procedure I supplied in the scripts for the webinar.
James J Leddy Asked: Trace shows stored proc gets moved in and out of cache intermittently (seen by watching cachehit -vs- cachemiss, cacheinsert) ... I see the problem but what do I do to fix it? Can you tell SQL Server to keep a procedure in cache so it doesn't re-create the execution plan etc..., or do you just have to throw more memory at it (we've got 256mb)???
Answered: SQL Server has a cache management algorithm that keeps plans in cache based on how expensive they are to recreate and how often they are used. A plan that is frequently recreated may be one that is not very expensive. Are you sure that the recompilation is actually causing a performance problem? There may be nothing to 'fix'.
One thing you could do to test it is set up a test environment with the same data, and just run this proc so you know you will always have enough memory and it will never need to recompile. Create a second version of the proc with the WITH RECOMPILE option so that it will ALWAYS recompile. Set up a loop to run both versions of the proc over and over and compare the total durations.
If it does turn out that recompiling really is a problem, there is no way to force a plan to stay in cache. Read the KB article on how to troubleshoot stored procedure recompiles to see if you can change the procedure in any way. Your very last choice would be adding more memory to your system.
Shirley Landers Asked: Any limit to number of items that can be autostarted?
Answered: Not really; any procedure can be marked as autostart, so the only limit is the limit on the total number of objects that can be created. However, if you have hundreds of autostart procs, it will take a long time for SQL Server to start up!
Janice Parkinson Asked: Can you find an existing connection that is using up too much CPU?
Answered: First you have to define what you mean by 'too much'. However, SQL Server tracing may not be the best tool for this. It doesn't accumulate statistics in real time. While the Profiler, by default, will show existing connections when it starts up, and you can then capture all the work those connections are doing, it shows CPU on an event-by-event basis. After you were through tracing, you could use the function fn_trace_gettable to copy the data to a table. From the table, you can then analyze the data, including getting the sum of the CPU for each connection to see who is using the most.
However, a better tool might be the sp_who2 stored procedure, which shows the cumulative CPU usage for each current connection.
Mike Patrick Asked: Is there a way to tell how much CPU the tracing is taking and if someone else is running one?
Answered: To see if other traces are running, you can use the function fn_trace_getinfo, which I demonstrated in the webinar. If an output row with a property of 1 has a value of an odd number, then that trace is being writing to Profiler, otherwise it is a server side trace or a black box.
The procedure sp_who2 will show you which processes are originating from Profiler (look in the Program Name column), and their total accumulated CPU time. There is no easy way that I can think of now to find which rows in the sp_who2 output correspond to which traces but this is definitely something worth finding out. I'll see what I can get from the dev team, and update this archive if I get any more information.
Chuck Boyce Asked: any books on using trace that you'd recommend?
Answered: There are none that have as much information as I would like, but check out the SQL Server 2000 Resource Kit, and the SQL Server 2000 Performance Tuning Technical Reference, both from Microsoft Press.
Keith Tam Asked: When will MS add the feature of replaying NT Auth apps?
Answered: Just like WHY questions, I'm not privy to all the news about WHEN. Write to [email protected] to let them know if this is important to you; there is still time to get new features into the next version.
Art Remnet Asked: what tools do you suggest for tuning SQL statements
Answered: If by tuning, you mean how to get them to perform better, then the Index Tuning Wizasrd is a good way to start. If you mean actually rewriting the query into more efficient SQL, then there are no automated tools that I know of. You have to just get lots of experience, and learn all you can about the SQL language. You can read Joe Celko's book on "SQL for Smarties", but remember he is not T-SQL specific. He only uses ANSI SQL. For SQL Server specific techniques, read Ben-Gan and Moreau on "Advanced TSQL for SQL Server 2000".
Scott Lezberg Asked: What is the best way to trace a Particular SQL Server Authenticated User?
Answered: There is nothing special about this; you can just put the user name in the filter for LoginName. Go to the Filters table in the trace definition screen, find and expand LoginName, and under that, find and expand 'Like'. Type in the user name you want to filter on.
Kevin Hayward Asked: Can you set a trace to fire off when a specific users logs into SQL Server
Answered: Great question… it just might lead to a whole article in TSQL Solutions newsletter!
You can use tracing to fire out another trace, but you have to do it through Profiler, because server side traces will not write to a table in real-time. The profiler trace should capture all "Audit Login" events and write to a table. You need to run your trace once for practice, and create the table, let's call it TraceLogins. Then when the table has been created you can run sp_help on it to get the column names and definition. The following example will write all SQL Server authenticated logins, other than 'sa', to a history table. However, instead of the trigger writing to the history table, you could just check for LoginName being a particular value, and then call a procedure which starts another trace you have already defined, maybe first checking to make sure it is not already running.
So, I just did this. I created a trace to capture only "Audit Login" Events, and captured the LoginName, HostName (client machine), ApplicationName and start time, and write to a table called TraceLogins. Sp_help showed me the column definitions for TraceLogins, so I was able to create a History table using this definition:
create table LoginHistory
(LoginName nvarchar(256),
HostName nvarchar(256),
ApplicationName nvarchar(256),
StartTime datetime)
I then created a trigger on the TraceLogins table, to write to LoginHistory whenever any non-sa, SQL Server authenticated user logged in. You can change the IF statements in the trigger to put any conditions you want, but note that in the captured trace table, Event Class is a number, so you have to check the docs to see what number corresponds to which Event. I found that the 'Audit Logins' event genenerated two rows every time someone logged in, and one of the rows had a NULL for HostName, so I included a check in the trigger to include the row where HostName was null.
Here is my trigger:
CREATE TRIGGER capture_logins
on TraceLogins for insert
as
if (select EventClass from inserted) != 14
-- 14 is the Audit Login Event
OR (select LoginName from inserted) = 'sa'
OR (select HostName from inserted) is null
OR (select LoginName from inserted) like '%\%'
-- a '\' means the login is Windows Authenticated
RETURN
INSERT into LoginHistory(LoginName, HostName,
ApplicationName, StartTime)
SELECT LoginName, HostName,
ApplicationName, StartTime
FROM inserted
You can use this as a template for a trigger for other events that may occur. I have not tested the overhead of using this, but one downside is that the trace must always be running using Profiler, not a server side trace, and I have not found a way to have Profiler start up a trace automatically when SQL Server starts up.
James J Leddy Asked: What's the next webinar in this series?
Answered: The next webinar will be on Thursday, May 9. I should have the topic finalized in a week or so, but it will probably be something on analyzing showplan output. I just found out that the webinar software will be upgraded so that my demos will be captured in the archive. This will be important for the graphical showplan, so I need to confirm that the new software will be available before the topic is finalized.
Chris Becker Asked: How do you monitor the buffer on the client if the client is doing the trace processing?
Answered: I'm not sure exactly what you're asking here. If you want to see how much work the Profiler client is doing you can look at the output from sp_who2, and it will show processes that have a Program Name of 'Profiler'.
Ron Sirvent Asked: regarding "outside of server tracing" can you set tracing to use (run) other sets of CPUs rather then taking a performance of the server it is tracing.
Answered: No, because the heavy work of Profiler is actually looking at the kernel work that SQL Server is doing, so it has to be running right where the server itself is running.
Virender Ajmani Asked: when you capture showplan all event it has information in Binary Data column. I just want to read that. I was using readtext ...
Answered: The docs say that the Binary Data column is just the estimated cost of the plan, but somehow that doesn't seem right. If it was just the cost, you shouldn't need readtext, you should be able to just convert to another numeric datatype. But I think there is something more going on, and I will research this further.
Monica Roberts Asked: what do you think about the product Log Explorer? It is being sold by Lumigent
Answered: I have a copy of Log Explorer that I just begun to play with, so it's too early to have a real opinion of my own. However, some other SQL Server consultants, whose opinions I highly respect, say it's a tool you don't want to be without.
Bill Buzzard Asked: How do I tie oracle into the SQL trace profile?
Answered: SQL Trace is designed to work only with SQL Server as it relies heavily on the internal workings of that product and is tied directly to the source code.
James J Leddy Asked: Will you be offering followon WEBINARs on how to analyze, in more detail, what trace file is showing you and what to do to fix the problem (slow running proc, slow running query , etc..)?
Answered: Probably not in this series, but SQL Server Magazine and I are discussing future plans for when this first series of 4 webinars is completed.
Marina Medvedev Asked: Do you know of another tool from another vendor that does similar activities as the Trace/Profiler.
Answered: No other tool could give you this level of detail, because SQL Server Tracing has hooks into the actual source code.
Relevant KnowledgeBase articles:
You can access the Knowledgebase from the site http://support.Microsoft.com.
Q214799 – INF: SQL Profiler From Command Line Parameters to Temp File Usage
Q279033 – INF: Identifying Cascading Referential Integrity in SQL Profiler
Q258990 – INF: Trace in SQL Server by Using Extended Stored Procedures
Q237932 – BUG: The function fn_trace_gettable cannot read rollover files generated by SQL Profiler
Q270599 – INF: How to Programmatically Load Trace Files into Tables
Q270629 – BUG: Profiler trace files not generated when files exist
Q282749 – BUG: Deadlock Information Reported with SQL Server 2000 Profiler Is Incorrect
Q243586 – INF: Troubleshooting Stored Procedure Recompilation
