3
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.
Note:
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.
Caution:
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.
