How To Use A Sql Profiler For Tracing?

[dotnetrockz]

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring T-SQL Statements of Database Engine. We can save and reuse the state at a later point of time.[list]
[*][b]We can do the following using SQL Server Profiler [/b]
[list]
[*]Create a trace
[*]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
[/list]
[/list][list]
[*]Use SQL Server Profiler to monitor only the events in which you are interested.
[/list]
[b]Menu Path:[/b] Start | All Programs | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler.
The following screen will come:
[img]http://www.codeproject.com/KB/dotnet/SQLServerProfiler/pic1.jpg[/img]
Figure 1.0
Click on <Connect> Button. New Properties Screen will come:
[img]http://www.codeproject.com/KB/dotnet/SQLServerProfiler/properties.jpg[/img]
Figure 1.1
It has two selection tabs:[list]
[*][b]General: [/b]It is used for general setting for Trace Database Engine.
[*][b]Event:[/b] It is used to add or remove some selected event for monitor.
[/list]
In General Section (as given in Figure 1.1), it is divided into four sections.
[b]Section 1: [/b]In this section, you have to just specify the name of your trace, Trace provider name and server name are predefined and based upon your SQL Server.
And it is not editable.
[b]Section 2: [/b]It is the template section. You can choose different type of Templates based upon your requirements. It is the configuration for trace. By default, it is "Standard (Default)" templates. Others templates are T-SQL, T-SQL Duration, T-SQL Reply, T-SQL SPs, etc. You can create your own custom Templates by selecting different Events and Event Class. It is saved as "[i].tdf[/i]" Extension.
[b]Section 3: [/b]This section is related to save your trace. Either as File ([i].trc[/i]) or in a database. as table. While clicking on Save to file check box, File save dialog box should open and you can save that file (with[i] .trc[/i] extension).
If you check the [i]"Save to Table", [/i]it will connect with your server and ask you to which database you want to save that trace table information.
[img]http://www.codeproject.com/KB/dotnet/SQLServerProfiler/savetotable.jpg[/img]
Figure 1.2
[b]Section 4: [/b]You can stop your trace on a particular time. Check the "[i]Enable trace stop time" [/i]checkbox and give the time at which you want to stop track, SQL Server will automatically stop trace on that time.
Now Move To "[i]Event Section[/i]" Tab.
Now we need to know some definition with respect to SQL Server Profiler.
[b] What is an Event?[/b]

An Event is an action or operation that is performed in your SQL Server 2005 Database Engine.
Some examples of Events are:[list]
[*]
[list]
[*]Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements.
[*]User login and logout
[*]Execution of Stored procedures
[*]Operation with cursor
[/list]
[/list]
SQL Server profiler is able to trace all of these events and all these events are categories on a particular Event class.
[b] What is an Event Class?[/b]

Event class is a type of event that can be traced.
Some examples are:[list]
[*]SQL: BatchCompleted
[*]SQL: Batch Starting
[*]Audit Login
[*]Audit Logout
[*]Lock: Acquired
[*]Lock: Released
[/list]
Now you can select events from this screen:
[img]http://www.codeproject.com/KB/dotnet/SQLServerProfiler/events.jpg[/img]
Figure 1.3
In section 1, we can select the proper check box based upon our requirement, section 2 will display the details of Selected events and Events class. If you check in the check box of section 3, you will get all the list of Events and Columns in Section 1.
Section 4 is something like customization. Just click on the "Column Filter Button". In this section, you can specify some condition (like or Not like).
[img]http://www.codeproject.com/KB/dotnet/SQLServerProfiler/Filter1.jpg[/img]
Figure 1.4
By clicking on "[i]Organize Column[/i]" button, you can change the sequence of order of selected events.
Now Click on the "[i]Run[/i]" Button, then Trace window will come:
[img]http://www.codeproject.com/KB/dotnet/SQLServerProfiler/trace1.jpg[/img]
[img]http://www.codeproject.com/KB/dotnet/SQLServerProfiler/trace2.jpg[/img]
Figure 1.5
Using these windows, you will get the detailed time duration of a query and all other events information that you have selected.
You can save this result and use it in future. Or you can extract a particular query from the trace, just right click and click on "E[i]xtract Event Data"[/i]. And save this as a SQL Script.
[b] Reply in SQL Server Profiler[/b]

SQL Server profiler has a Reply facility which has the ability to save a trace and replay it later.
Replay is useful to troubleshoot an application. Trace replay supports debugging by using Toggle Breakpoint and the Run to Cursor options on the SQL Server Profiler Replay menu.
Anything changed in SQL Server Management Studio will be traced by the SQL Profiler. So it can basically be used for database performance check. We also have "SQL Server Performance Monitor" to monitor the System and Server performance too.

[dotnetrockz]

Using SQL Profiler to Capture Stored Procedure Call from Application





[b]SQL Profiler[/b] is a high-quality tool to figure out different database issues, like “[i]Which are costliest queries running[/i]”, “[i]Which quires acquired exclusive locks[/i]”, “[i]Which indexes are missing[/i]” and so on. But in development environment and on production when solving a problem, developers like to use SQL Profiler to get exact procedure call which is being generated by front end application.
Worst practice is, that developers like to use existing built in templates for this purpose and normally use default one i.e. [b]STANDARD[/b]. If you are also using SQL Profiler for this procedure call purpose, then selecting [i]STANDARD [/i]trace template is not a good choice, as on production server it affects its performance and even on development server it returns much more extra information.
Good practice is, if you have not created your own template then always select [b]TUNING[/b].

[url="http://3.bp.blogspot.com/-E0IvFJvb59w/Tpxm7dSRn5I/AAAAAAAAAPI/3DjWtQKnz_g/s1600/TraceTuning1.gif"][img]http://3.bp.blogspot.com/-E0IvFJvb59w/Tpxm7dSRn5I/AAAAAAAAAPI/3DjWtQKnz_g/s640/TraceTuning1.gif[/img][/url]


It also has some extra information so when you only need to catch procedure calls generated from your application then click on “[u]Event Selection[/u]” tab and keep only “[u]RPC:Completed[/u]” event. You need not to select “Sp: stmt Completed” as you just need to capture “execute procedure calls” and not all the statement inside this procedure. You can also omit “[u]SP: Batch Completed[/u]” as we need calls that are only generated from application. If you also need to capture calls from SSMS then you can keep it.

[url="http://4.bp.blogspot.com/-v8UGIxlozU8/Tpxm8X5AvUI/AAAAAAAAAPQ/8eTuukbv0nI/s1600/TraceTuning2.gif"][img]http://4.bp.blogspot.com/-v8UGIxlozU8/Tpxm8X5AvUI/AAAAAAAAAPQ/8eTuukbv0nI/s640/TraceTuning2.gif[/img][/url]


To avoid extra work pressure on server and to get your required results only, you must also apply filters on “[u]Database Name[/u]” and “[u]Text[/u]”

[url="http://2.bp.blogspot.com/-WWWkMAkKMic/Tpxm9JNQdxI/AAAAAAAAAPY/eVvN36O5Eb4/s1600/TraceTuningFilter.gif"][img]http://2.bp.blogspot.com/-WWWkMAkKMic/Tpxm9JNQdxI/AAAAAAAAAPY/eVvN36O5Eb4/s320/TraceTuningFilter.gif[/img][/url]
Use % sign, just like you use in LIKE operator.

[url="http://2.bp.blogspot.com/-kYL_b5lvyjg/Tpxm9yX3f_I/AAAAAAAAAPg/yoUQyGCs5hk/s1600/TraceTuningFilter2.gif"][img]http://2.bp.blogspot.com/-kYL_b5lvyjg/Tpxm9yX3f_I/AAAAAAAAAPg/yoUQyGCs5hk/s320/TraceTuningFilter2.gif[/img][/url]
Now run your trace and you will find your required results quickly and clearly, even without putting extra work load on database server.

[Guest]

Gp...kaani maree high level lo undi

[dotnetrockz]

[quote name='Guest' timestamp='1352411453' post='1302765875']
Gp...kaani maree high level lo undi
[/quote]

2nd psot chudu adhi easy gane undhi.....

[deals2buy]

SQL Server vaallaki oka thread undi ga..akkada post chey bhayya same....

btw mee apice lo 2012 using aa?

[dotnetrockz]

nenu cheyyaledhu online nunchi copy/paste...

[vissu]

Good one

Bhayya Sql server material vunte plz pm cheyava

[deals2buy]

[quote name='Nissan' timestamp='1352411667' post='1302765898']
nenu cheyyaledhu online nunchi copy/paste...
[/quote]
ok ok..aa thread lo update chesa le
nuvvu SQL server Developer or DBA?
[quote name='vissu' timestamp='1352411769' post='1302765913']
Good one

Bhayya Sql server material vunte plz pm cheyava
[/quote]
nuvvu naaku oracle material isthanu ani enni rojulu aindi? [img]https://lh6.googleusercontent.com/-rxfAR3VXs2M/TmBTtWJEJHI/AAAAAAAAD9s/fqbpjvNUAsk/s150/Brahmi-3.gif[/img]

[dotnetrockz]

[quote name='vissu' timestamp='1352411769' post='1302765913']
Good one

Bhayya Sql server material vunte plz pm cheyava
[/quote]

material em ledhu bhayya


nuvvu sql server developer aithe [url="http://www.1keydata.com/sql/sql.html"]http://www.1keydata.com/sql/sql.html[/url] lo side ki purple color lo unna cases anni practice cheyyi management studio lo and sample databases download chesuko practice ki like adventureworks.....

or else Aamphat ane user ni adugu he works on sql....he can suggest something..

[Guest]

[quote name='Nissan' timestamp='1352411516' post='1302765884']


2nd psot chudu adhi easy gane undhi.....
[/quote]

Yeah..this one is useful..Gp

[vissu]

[quote name='Nissan' timestamp='1352412067' post='1302765936']


material em ledhu bhayya


nuvvu sql server developer aithe [url="http://www.1keydata.com/sql/sql.html"]http://www.1keydata.com/sql/sql.html[/url] lo side ki purple color lo unna cases anni practice cheyyi management studio lo and sample databases download chesuko practice ki like adventureworks.....

or else Aamphat ane user ni adugu he works on sql....he can suggest something..
[/quote]

Thanks Bhayya
Actual ga nadhe oracle background nd trying to learn sql server 2008

[vissu]

[quote name='deals2buy' timestamp='1352411885' post='1302765921']

ok ok..aa thread lo update chesa le
nuvvu SQL server Developer or DBA?

nuvvu naaku oracle material isthanu ani enni rojulu aindi? [img]https://lh6.googleusercontent.com/-rxfAR3VXs2M/TmBTtWJEJHI/AAAAAAAAD9s/fqbpjvNUAsk/s150/Brahmi-3.gif[/img]
[/quote]

Nee

Mail cheyamanna but nuvvu cheyaledhu

[deals2buy]

[quote name='vissu' timestamp='1352412515' post='1302765986']
Nee

Mail cheyamanna but nuvvu cheyaledhu
[/quote]
[img]https://lh4.googleusercontent.com/-ts_jwcB7ztc/T1gM2Vy-DNI/AAAAAAAAGNg/HgdGkXa5KBo/s150/Brahmi-5.gif[/img]mail id ivvakunda ela chestaru vayya?

[stewiegriffin]

Profiler lo each query enni CPU's use cheysthondi thelsuovaccha..??
max DOP setting change cheyataniki ee info kaavali..

[ChampakDas]

help ki emo ani vachina...anyway GP