Re: Running profiler on busy 8-cpu Sql Server

From: Greg Linwood (g_linwoodQhotmail.com)
Date: 03/23/04 

Date: Wed, 24 Mar 2004 10:22:44 +1100

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: Problem altering table and adding a default
    ... SQL Server does not allow that - you can only add nullable ... > After you add a new column in Enterprise Manager, uncheck Allow Nulls, ... the ALTER TABLE statement above will pass to the SQL Server ... > 4) Start Profiler and perform a new trace. ...
    (microsoft.public.sqlserver.server)
  • Re: Problem altering table and adding a default
    ... ALTER TABLE dbo.Test_tbl ADD Notnull_col1 charNOT NULL ... SQL Server does not allow that - you can only add nullable columns. ... After you add a new column in Enterprise Manager, uncheck Allow Nulls, bind the uder- ... Start Profiler and perform a new trace. ...
    (microsoft.public.sqlserver.server)
  • Re: stress by profiler ?
    ... Are you running the Profiler on the same machine as SQL Server? ... The response time of DB access is also good. ... -- very low Disk Read ...
    (microsoft.public.sqlserver.server)
  • Re: Should I Index?
    ... Wayne Snyder, MCDBA, SQL Server MVP ... > A good place to start is the index tuning wizard. ... Use profiler to do this. ... some suitable indexes based on the workload. ...
    (microsoft.public.sqlserver.server)
  • Re: Login failed for user sa
    ... The default SQLProfilerStandard has data columns ... to change the Events to add relevant Security Audit events. ... > System event log and SQL server error log is showing too ...
    (microsoft.public.sqlserver.programming)