473,703 Members | 2,600 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

A light focus on SQL Profiler

3 New Member
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.
Dec 12 '07 #1
0 5049

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

Similar topics

0
1884
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 Virtual PC to run a Win2K Server OS with SQL Server 2000 (sp3 i think). Whenever I start up SQL Profiler - even immediately after a bootup, if I try to pause, stop, or exit out of profiler, it hangs. The strange part is that it seems to keep on tracing even though it won't respond anymore. I...
8
2831
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 machine I get back values in the reads column. I took a query that profiler reported as having 0 reads and ran in in query analyzer wtih STATISTICS IO on and confirmed that there are in fact reads: Table 'tt_cawardalloc'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 1. Table...
0
1654
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 it works only for 2002 and 2003. Is there a profiler that looks like DevPartner Profiler (gives the same information: shows clearly which methods/statements takes the most time in total etc and free!). Or will DevPartner make a version for 2005 soon?
9
3273
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 Borland and AQTime. My big problem is, my application is a big application, and many times I have many EventHandlers associated to an object, then this object never is free from the memory. AQTime for .Net can tell me how many references of EventHandler the object
3
1686
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 Previously with Visual Studio 6.0 I was using the profiler in the following way 1) Build the project with link setting- Enable Profiling as checked. 2) I had created a custom batch file for profiling my application and was
8
2143
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? I know that we can inherit from a TraceListener class to write to files, databases, etc..., but I would really like to attach to running .NET processes and get the results without any logging and instead capture the real-time results when troubleshooting.
1
3176
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 sp_trace_create @P1 output, 1, NULL, NULL, NULL which means @tracefile = NULL so where from the profiler read the results?!
3
3681
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 service that tracks the process' memory but I need to "get inside" and look a bit deeper and I don't have a clue where to start ... a commercial memory profiler is not an option due the nature of the environment the application is deployed in .. My app runs fine for weeks (in 8 locations) but...
2
2341
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 took the longest to execute and b) lines of code that allocated the most unreleased memory. I have looked around and am unable to find a good tool to easily achieve these statistics for my C# Winforms application. Possible reasons are that: 1) I am having difficulty finding the existing...
0
8756
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8667
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8961
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7861
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4429
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4687
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3122
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2446
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2063
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.