scheduling SQL Profiler

Hi all,
Is that possible to schedule a SQL Profiler session (without to keep a
windows session) ? If yes, how ?
Thank's.
Arnaud
You can use PSSDIAG utility to run profiler and it has scheduling capability
http://support.microsoft.com/default...b;en-us;830232
To learn how to use this tool
http://msdn.microsoft.com/library/de...v_12072004.asp
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Arnaud Demol wrote:
> Hi all,
> Is that possible to schedule a SQL Profiler session (without to keep a
> windows session) ? If yes, how ?
> Thank's.
> Arnaud
|||Arnaud Demol wrote:
> Hi all,
> Is that possible to schedule a SQL Profiler session (without to keep a
> windows session) ? If yes, how ?
> Thank's.
> Arnaud
You'll have to create a server-side trace to do this and avoid the
Profiler client. You can create the trace from Profiler and script it
out from the File Script Trace menu option. Once you have the trace
script, read about sp-trace_setstatus in BOL because you'll need to
learn how to stop a trace as well. You can then create a job in the SQL
Server Agent to execute the trace whenever you want. Stopping it is a
manual process since you'll need to know the trace handle in order to do
it. You could create a second job from the first one once you get the
handle for the trace.
You'll also need to make sure you set Profiler up to have the server
manage the trace and save the results to a file before you script out
the T-SQL.
You'll also need to look up sp_trace_gettable which allows you to read
trace files and optionally import them into a table in the database.
It will take some work to get it done, but once you understand the
process, you should have no trouble doing this on the regular basis.
David Gugick
Imceda Software
www.imceda.com
|||Sure...you can do that. They are called server side traces.
The specifics of how to run these depend upon what version
of SQL Server you are using.
With SQL Server 2000, You can start by using Profiler to
create a trace file which has your events, filters, etc.
that you want to trace. You will want to setup this trace to
be saved to a file. After you specify what you want to
trace, you can go up to the menu in Profiler to File and
select Script Trace to create a trace file definition. You
can then control and schedule the trace using the system
stored procedures that using the naming convention
sp_trace_xxxxx. You can find more information on these
stored procedures in books online. You can also find more
information in the following article:
http://support.microsoft.com/?id=822853
Towards the end of the article, there are links for
additional information.
If you are using SQL Server 7, the extended stored
procedures for controlling the traces are named
xp_trace_xxxxx.
Another difference between the two versions is that in SQL
Server 7, you can have server side traces saved directly to
a table (in 2000 you can't). But you would want to save to a
file anyway as it decreased the overhead for SQL Server.
-Sue
On Thu, 20 Jan 2005 04:15:02 -0800, Arnaud Demol <Arnaud
Demol@.discussions.microsoft.com> wrote:

>Hi all,
>Is that possible to schedule a SQL Profiler session (without to keep a
>windows session) ? If yes, how ?
>Thank's.
>Arnaud
sql

Scheduled Profiler

I need to get performance stats off a number of live servers. The stats need to cover the time from 07:00 to 19:00 every day.

I'm after things like CPU, Memory and IO, but also I want things like transactions and stored procedures.

Can profiler provide this kind of information, and can I schedule the jobs?

The systems are all running on Windows server 2003 and SQL Server 2005.

Thanks

Gary

Hi,

You should schedule a t-sql task with SQL Server Agent.

Use the sp_trace_create to create a trace, sp_trace_setevent to set up your events for monitoring and sp_trace_setstatus to start, stop or pause a trace.

I hope it helps.

Regards,

Janos

|||

Scheduling server traces is what you would want to do as Janos posted. Rather than trying to set up all of the statements and events yourself, it's much easier to just use Profiler to create the script file. Create the trace you want in Profiler and then go to the menu to File, Export and then select Script Trace Definition. This will create the server trace file that you can schedule in SQL Agent. You can then open these files in profiler, import them into a table for analysis, etc. It's the same file format that Profiler uses.

-Sue

|||

refer these links

http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm

http://msdn2.microsoft.com/en-us/library/ms191006.aspx

Madhu

schedueling proflier

How can I schedule the profiler to log each day from 14:00 to 14:20?
Carl K
http://www.personnelware.com/carl/resume.html
Hi Carl.
I'm assuming you're on SQL 2000 here..
You use the Profiler's scripting capabilities to generate a server side
trace, then schedule a call to sp_trace_setstatus to start / stop the trace
at 14:00 / 14:20 via the sql agent.
Regards,
Greg Linwood
SQL Server MVP
"Carl Karsten" <carl@.personnelware.com> wrote in message
news:Oj4oUZSLEHA.1312@.TK2MSFTNGP12.phx.gbl...
> How can I schedule the profiler to log each day from 14:00 to 14:20?
> Carl K
> --
> http://www.personnelware.com/carl/resume.html
|||yes: sql 2000.
I see that I will need to run the trace "on the server" - how much extra load
does this create? currently the server's CPU is at 60-80%. I don't care if it
takes it up to 95, but when it hits 100 is when the support lines go nuts, so we
don't want that to happen ;)
Thanks for getting me this far.
Carl K
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:e3d0o0SLEHA.1388@.TK2MSFTNGP09.phx.gbl...
| Hi Carl.
|
| I'm assuming you're on SQL 2000 here..
|
| You use the Profiler's scripting capabilities to generate a server side
| trace, then schedule a call to sp_trace_setstatus to start / stop the trace
| at 14:00 / 14:20 via the sql agent.
|
| Regards,
| Greg Linwood
| SQL Server MVP
|
| "Carl Karsten" <carl@.personnelware.com> wrote in message
| news:Oj4oUZSLEHA.1312@.TK2MSFTNGP12.phx.gbl...
| > How can I schedule the profiler to log each day from 14:00 to 14:20?
| >
| > Carl K
| > --
| > http://www.personnelware.com/carl/resume.html
|
|
|||Hi Carl.
An understanding of the SQL Profiler architecture will help you understand
the impact of tracing SQL Server using Profiler.
SQL Profiler uses a client / server architecture. SQL Profiler is a GUI tool
that attaches to a server component called SQL Trace to perform it's work.
When you start SQL Profiler, it connects to SQL Trace & begins traces by
requesting SQL Trace to create listener queues. Profiler then attaches
itself to those queues, draining output for display on the GUI.
Running SQL Profiler on the server's console has a large overhead because
the server is performing not only the SQL Trace server component, but also
all the GUI work, rendering the queue output in the GUI grid, using
considerable CPU & memory as well as causing lots of context switching by
forcing the server CPUs to both run the database process & the GUI process.
You can run the SQL Trace component on its own though, without the overhead
of the Profiler GUI by scripting the server commands from Profiler. To do
this, you set up your trace configuration (events, columns & filters),
script it from Profiler to a file, close Profiler & run the script against
the SQL Server in the QA. It outputs results to a .trc file which you can
then view via the Profiler on another machine at a later date. You might
even schedule a job to ftp the .trc files after the trace is closed (eg
after 14:20).
Using the SQL Trace has only a minimal impact on the server's resources,
mostly because it doesn't cause the context switching required to update the
SQL Profiler GUI, distracting the CPUs from running the SQL Server process'
threads. How much is minimal? It depends on how many events & data columns
you're capturing and how restrictive your filters are. Generally though,
assuming you're only capturing a few commands & a few columns, you probably
won't notice any performance degradation at all. If you smother the trace by
capturing ALL events, ALL data columns, not filtering at all & displaying in
real time on the Profiler GUI, well you should expect a noticable
performance degradation.
Lastly - I have a personal philosophy on this issue that all servers should
be traced, at least to some degree either via SQL Trace, Windows Perfmon or
some other tool because otherwise, how will you know how much utilisation
you're actually getting from your server at all? My take on this is that
even the busiest production boxes should be traced / perfmon'd and that they
will be should be factored into the original capacity plan. This is often an
important part of managing SLAs.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Carl Karsten" <carl@.personnelware.com> wrote in message
news:uoMxJtULEHA.3300@.TK2MSFTNGP10.phx.gbl...
> yes: sql 2000.
> I see that I will need to run the trace "on the server" - how much extra
load
> does this create? currently the server's CPU is at 60-80%. I don't care
if it
> takes it up to 95, but when it hits 100 is when the support lines go nuts,
so we
> don't want that to happen ;)
> Thanks for getting me this far.
> Carl K
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:e3d0o0SLEHA.1388@.TK2MSFTNGP09.phx.gbl...
> | Hi Carl.
> |
> | I'm assuming you're on SQL 2000 here..
> |
> | You use the Profiler's scripting capabilities to generate a server side
> | trace, then schedule a call to sp_trace_setstatus to start / stop the
trace
> | at 14:00 / 14:20 via the sql agent.
> |
> | Regards,
> | Greg Linwood
> | SQL Server MVP
> |
> | "Carl Karsten" <carl@.personnelware.com> wrote in message
> | news:Oj4oUZSLEHA.1312@.TK2MSFTNGP12.phx.gbl...
> | > How can I schedule the profiler to log each day from 14:00 to 14:20?
> | >
> | > Carl K
> | > --
> | > http://www.personnelware.com/carl/resume.html
> |
> |
>