Re: schedueling proflier
From: Greg Linwood (g_linwoodQhotmail.com)
Date: 04/29/04
- Next message: Michael, Cheng [MSFT]: "RE: SQL Agent Jobs Hang in Executing State"
- Previous message: Aaron Bertrand [MVP]: "Re: Recovery of database 'abcdef'..."
- In reply to: Carl Karsten: "Re: schedueling proflier"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 29 Apr 2004 15:09:40 +1000
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
> |
> |
>
- Next message: Michael, Cheng [MSFT]: "RE: SQL Agent Jobs Hang in Executing State"
- Previous message: Aaron Bertrand [MVP]: "Re: Recovery of database 'abcdef'..."
- In reply to: Carl Karsten: "Re: schedueling proflier"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
