469,326 Members | 1,320 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

A light focus on SQL Profiler

Using SQL Profiler:

(Ref: http://msdn2.microsoft.com/en-us/library/ms181091.aspx)

Sometimes it’s very beneficial to use sql profiler.
Let’s have an idea about that and how to use that by taking some scenarios.

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the SQL Server Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly.

Using SQL Server Profiler
SQL Server Profiler shows how SQL Server resolves queries internally. This allows administrators to see exactly what Transact-SQL statements or Multi-Dimensional Expressions are submitted to the server and how the server accesses the database or cube to return result sets.

Using SQL Server Profiler, you can do the following:
• Create a trace that is based on a reusable template
• Watch the trace results as the trace runs
• Store the trace results in a table
• Start, stop, pause, and modify the trace results as necessary
• Replay the trace results
Use SQL Server Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process, and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

How to: Create a Trace (SQL Server Profiler)
Now lets describes how to use SQL Server Profiler to create a trace.
To create a Trace :
1. On the File menu, click New Trace, and connect to an instance of SQL Server.
The Trace Properties dialog box appears.

The Trace Properties dialog box fails to appear, and the trace begins instead, if Start tracing immediately after making connection is selected. To turn off this setting, on the Tools menu, click Options, and clear the Start tracing immediately after making connection check box.
2. In the Trace name box, type a name for the trace.
3. In the Use the template list, select a trace template on which to base the trace, or select Blank if you do not want to use a template.
4. To save the trace results, do one of the following:
o Click Save to file to capture the trace to a file. Specify a value for Set maximum file size. The default value is 5 megabytes (MB).
Optionally, select Enable file rollover to automatically create new files when the maximum file size is reached. You can also optionally select Server processes trace data, which causes the service that is running the trace to process trace data instead of the client application. When the server processes trace data, no events are skipped even under stress conditions, but server performance may be affected.
o Click Save to table to capture the trace to a database table.
Optionally, click Set maximum rows, and specify a value.

When you do not save the trace results to a file or table, you can view the trace while SQL Server Profiler is open. However, you lose the trace results after you stop the trace and close SQL Server Profiler. To avoid losing the trace results in this way, click Save on the File menu to save the results before you close SQL Server Profiler.
5. Optionally, select the Enable trace stop time check box, and specify a stop date and time.
6. Click Run to start the trace.
Dec 12 '07 #1
0 4648

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Jeff Mair | last post: by
8 posts views Thread by patrickshroads | last post: by
9 posts views Thread by Franco Gustavo | last post: by
3 posts views Thread by Maansi Gupta | last post: by
8 posts views Thread by Bryan | last post: by
1 post views Thread by ofirmgr | last post: by
3 posts views Thread by Sam Samson | last post: by
2 posts views Thread by Paul Ritchie | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.