Re: Running profiler on busy 8-cpu Sql Server

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

Date: Tue, 23 Mar 2004 22:09:03 -0500

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: Running profiler on busy 8-cpu Sql Server
    ... I always start with a light trace and add more detail as I go along to ... SQL Server MVP ... > I think it's also worth saying that you should introduce Profiler ... > SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • 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)
  • 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: SQL Profiler producing phantom traffic
    ... Could it be that there was a server side trace running? ... Vyas, MVP (SQL Server) ... > used profiler to help with their coding. ... > between the users machine and the SQL server. ...
    (microsoft.public.sqlserver.server)
  • Re: schedueling proflier
    ... An understanding of the SQL Profiler architecture will help you understand ... SQL Profiler uses a client / server architecture. ... that attaches to a server component called SQL Trace to perform it's work. ...
    (microsoft.public.sqlserver.server)