Re: Running profiler on busy 8-cpu Sql Server

From: Brian Moran (brian_at_solidqualitylearning.com)
Date: 03/25/04 

Date: Thu, 25 Mar 2004 08:54:35 -0500

Couldn't agree more...

I always start with a light trace and add more detail as I go along to
ensure it's not having a significant impact.... that's probably the most
important advice of all... 

-- 
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23QFbVjVEEHA.3256@TK2MSFTNGP09.phx.gbl...
> Yes, I'll add that my post is intended to encourage a better understanding
> of the Profiler rather than discounting it's use out of hand on busy
> servers. I also regularly use it on VERY busy servers nearly always
without
> incident.
>
> I think it's also worth saying that you should introduce Profiler
gradually
> on a busy server - eg, if you're capturing long running queries, select
just
> the essential colulmns / events & set the duration filter high and work
> backwards until you get data. Don't just jump in with all events / columns
&
> no filters as you're sure to clog the system that way.
>
> So, Profiler's ok to use on busy servers - just use it with some care &
> don't make the classic mistake of performing blanket profiling everything.
> Develop an understanding of how it works, what it's capturing & try to use
> it strategically / effectively.
>
> Regards,
> Greg Linwood
> SQL Server MVP
>
> "Brian Moran" <brian@solidqualitylearning.com> wrote in message
> news:OX%23of1UEEHA.2408@TK2MSFTNGP10.phx.gbl...
> > I agree with Greg's comments. Here are some additional thoughts...
> >
> > * I regularly run Profiler on VERy busy servers. I've done it on servers
> > where customers have told me 'theres no way to run Profiler, our server
is
> > way too busy, we've tried it and it's killed our server'
> > * Last week I was tracing on an 8X with over 1000 batches per second....
> > granted that's not outrageoulsy high... but it's not light either.
> > * tricks to remember include:
> > - avoid using the GUI.
> > - NEVER use the GUI if you're also going to specify a 'server processes
> > trace data'. Trace what THAT instance of Profiler does and you'll see it
> > starts TWO traces... one for hte GUI and one for the file that is being
> > written too.. yikes! <g>
> > - ALWAYS include a max cap on the trace size so the trace quickly stops
if
> > you've stared a 'killer' trace.
> > - You WILL hit the max cap size VERY quickly on a busy server if you're
> > tracing too much.
> > - round trips events (rpc:completed and sql:batchcompleted) are usually
> > reasonable to grab everything even on a busy server...
> > - unfortunately, the 'stmtcompleted' events are often very handy...
> > - UDF's will KILL you if you're tracing them... I've generated 100M of
> trace
> > data in a few seconds on moderately busy servers with lots of UDFs...
> > - you can get the trace down to a manageable size by:
> > * filtering our commands that have duration and CPU = 0.
> > * setting nest level = 1 or 0. That will prevent you from drilling down
> into
> > nested procs and you won't see the UDF's. However, you will be able to
see
> > top line stmts in procs and it's usually enough to track down a lot of
> > problems on a busy server....
> >
> > Let me know if you have any other questions. I love Profiler and have
> gotten
> > handy at working around it's quirks to let me use it safely in almost
any
> > production envrionment.
> >
> > -- 
> >
> > Brian Moran
> > Principal Mentor
> > Solid Quality Learning
> > SQL Server MVP
> > http://www.solidqualitylearning.com
> >
> >
> > "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> > news:edxz$2SEEHA.3696@TK2MSFTNGP10.phx.gbl...
> > > Hi Dan.
> > >
> > > SQL Profiler puts load on a SQL Server machine in a few ways, so there
> are
> > a
> > > few options you have as to how you go about minimalising its
> interference
> > > with SQL Server.
> > >
> > > First, it's a GUI which means that when you run it directly on a busy
> > > server's console, that server not only has to keep up with the
> background
> > > processing that SQL Server's doing, it also has to constantly switch
> > > contexts to update the GUI. Not only does the Profiler GUI's screen
need
> > to
> > > be rendered, it also has to format all of the data columns which are
> > > actually collected on separate "rows" into individual rows, so there's
a
> > lot
> > > of work going on in the background in the GUI. This in itself can
cause
> a
> > > pronounced performance hit on a SQL Server. So, running the Profiler
GUI
> > on
> > > a separate machine can mitigate this problem, assuming it's the
> > bottleneck.
> > >
> > > Then there's the back-end component of SQL Profiler - "SQL Trace"
which
> > SQL
> > > Server has to run. This is the part that actually captures the data
from
> > SQL
> > > Server & places it on queues that the Profiler GUI picks up. The more
> > events
> > > / data columns & filters you define in a profiler trace, the more work
> SQL
> > > Trace has to do. Adding fewer data columns can help minimalise it's
> work.
> > > Make sure you're only collecting the events / columns you actually
need
> &
> > > not gathering all columns / events for the heck of it..
> > >
> > > Generally, when Profiling a busy production server, the least
intrusive
> > > method will be to use the back end SQL Trace components alone, writing
> the
> > > output to a local file on the server, then viewing that file later in
> the
> > > Profiler GUI. This is performed with sp_trace_create on SQL2K
(different
> > on
> > > SQL70) and the Profiler GUI can even script these stored procs using
> File
> > /
> > > Save As / SQL Script..
> > >
> > > The only caveat is that when writing to a local file, if you're trying
> to
> > > capture a lot of Profiler data and the system's disk system is already
> > > heavily loaded, it may be better to write the data to a network share,
> > > assuming the NIC isn't overloaded.. Hence my earlier comments - there
> are
> > a
> > > few options you can choose from when profiling heavy loaded servers &
it
> > > pays to read up on the Profiler architecture a bit. I recommend
"Inside
> > SQL
> > > Server" - MS Press as there's an excellent section dedicated in that
> back
> > > (toward the back) on this topic..
> > >
> > > Additionally, you might want to have a look on
> > www.sqlserver-performance.com
> > > as it's sure to have a few arcitles on this topic.
> > >
> > > HTH
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Dan Dapkus" <anonymous@discussions.microsoft.com> wrote in message
> > > news:D99F34A3-9A04-4C5A-BE3E-A453EDA42866@microsoft.com...
> > > > Beginning the process of tuning a client's database and web
> application,
> > I
> > > started a Profiler trace on their busy (peak 70 - 80 % cpu
utilization)
> > > 8-cpu 8gb RAM SQL Server, and the SQL Server promptly became
> > non-responsive.
> > > Only a reboot brought it back to life.
> > > >
> > > > Microsoft MSDN support told me that Profiler can place a fairly
heavy
> > > burden on a busy SQL Server - unfortunately, the busier the SQL Server
> is,
> > > the greater the burden Profiler places on it.  In general, all queries
> > must
> > > be serialized in order to allow Profiler to gather information about
> them.
> > > I now believe that Profiler is basically unusable on a production
> > > multi-processor SQL Server due to the risk associated with the
increased
> > > load imposed by Profiler.
> > > >
> > > > My question is: how does one go about gathering "real-life"
> information
> > > about a busy, multi-processor SQL Server (without running it into the
> > > ground)?  All I really need is the Sql Text and the reads generated by
> > each
> > > query sent to the SQL Server, but I don't know a way to get that
> > information
> > > other than Profiler.
> > > >
> > > > Thanks for any suggestions.
> > > > Dan
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: URGENT deadlock question
    ... > Profiler to view a trace, SQL Server dumps rowsets across the network to ... > SQL Server doesn't feel like it can keep up with all the activity. ... > server-side trace to a trace file guarantees you'll see everything. ...
    (microsoft.public.sqlserver.server)
  • Re: Running profiler on busy 8-cpu Sql Server
    ... * I regularly run Profiler on VERy busy servers. ... where customers have told me 'theres no way to run Profiler, our server is ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: URGENT deadlock question
    ... >> network to the Profiler client, but there is no guarantee you'll see ... >> everything if SQL Server doesn't feel like it can keep up with all ... Just make sure the trace file is created on a local ...
    (microsoft.public.sqlserver.server)
  • Re: User and Login auditing
    ... You could use server side traces to trace the same things ... instead of running Profiler. ... You could put triggers on your trace table. ... How to Trace in SQL Server by Using Extended Stored ...
    (microsoft.public.sqlserver.security)
  • Re: Running profiler on busy 8-cpu Sql Server
    ... I also regularly use it on VERY busy servers nearly always without ... I think it's also worth saying that you should introduce Profiler gradually ... SQL Server MVP ... > SQL Server MVP ...
    (microsoft.public.sqlserver.server)