472,338 Members | 1,767 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,338 developers and data experts.

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 4866

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

Similar topics

by: Jeff Mair | last post by:
Hello all, I haven't been using profiler for a terribly long time, and I've come across something that I just can't figure out. I'm running MS...
by: patrickshroads | last post by:
I am running a profiler trace against a database and noticed that the reads column always shows 0. When running the same trace against another...
by: DraguVaso | last post by:
Hi, I'm using for a while DevPartner Profiler (http://www.compuware.com/products/devpartner/resources/profiler/profiler.as p), but unfortunately...
by: Franco Gustavo | last post by:
Hi, All What's is the best .net profiler for Debug an application. I have to buy one but I don't know which one!! I tried some of them like...
by: Maansi Gupta | last post by:
Platform - Windows 2000 Professional , VC .NET 7.1 Hello, I have recently ported my source code from Visual Studio 6.0 to .NET 7.1 ...
by: Bryan | last post by:
Does anyone have an example of an application that can connect to a running process and capture Trace.WriteLine calls like in SQL Server Profiler? ...
by: ofirmgr | last post by:
im trying to understand how the profiler works. so i started 2 profilers,one listen to another and I saw the profiler is running: exec...
by: Sam Samson | last post by:
Can any-one recommend some good sites detailing how to code a simple memory profiler? How do they "look under the hood"? I built an uber simple...
by: Paul Ritchie | last post by:
A few years ago I used a Profiler (I forget the name) on my Delphi code at the time and got fantastic results showing both: a) lines of code that...
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.