Re: Running profiler on busy 8-cpu Sql Server
From: Greg Linwood (g_linwoodQhotmail.com)
Date: 03/24/04
- Next message: Sue Hoegemeier: "Re: Another example of incomplete support of some legal database names"
- Previous message: Andrew J. Kelly: "Re: SQL Server memory problem"
- In reply to: Brian Moran: "Re: Running profiler on busy 8-cpu Sql Server"
- Next in thread: Brian Moran: "Re: Running profiler on busy 8-cpu Sql Server"
- Reply: Brian Moran: "Re: Running profiler on busy 8-cpu Sql Server"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 24 Mar 2004 15:31:09 +1100
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
> >
> >
>
>
- Next message: Sue Hoegemeier: "Re: Another example of incomplete support of some legal database names"
- Previous message: Andrew J. Kelly: "Re: SQL Server memory problem"
- In reply to: Brian Moran: "Re: Running profiler on busy 8-cpu Sql Server"
- Next in thread: Brian Moran: "Re: Running profiler on busy 8-cpu Sql Server"
- Reply: Brian Moran: "Re: Running profiler on busy 8-cpu Sql Server"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
