Friday, March 23, 2012

Problems with Profiler on SQL 2k5

Has anyone run into any problems with Profiler for Server 2k5 killing their
applications?
We have a clustered Production environment (active/passive). The database
is connected to via an MS Access ADP file which in turn the end users open
via load balanced Citrix Servers (there are 3 or 4 in the farm). Things got
a little slow in the past week so I went to run Profiler and PerfMon on the
SQL Server.
PerfMon caught some spikes in the Disk Queue Length & Pages per Seconds, but
it looked like normal intermintant activity, nothing persistant that could be
causing huge problems. Profiler (I filtered it down to a specific database
and looked for duration of 30 seconds or longer) wasn't finding any queries
which met my criteria.
However, every time I started Profiler --whether or not PerfMon was running,
whether I was logging to a trace table, trace file or not logging at all --
All the people logged on through the ADP had problems. The application froze
up, screens when blank, queries and reports timed out.
We verified with our system admins that it wasn't a network issue,
everything seemed to still work fine through the backend (though we weren't
running complex queries), but nothing through the front end worked until
Profiler was stopped. The Events I was using were the Scans Started, Scans
Stopped, SQL Batch Completed, Lock Deadlock, Lock Deadlock Chain, RPC
Completed and SQL Transactions. That's it.
Does anyone know of any issues that might have caused this scenario?
Thanks in advance,
Catadmin
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
Hello Catadmin,
The fact that profiler slowed the system suggests you were capturing lots
of events. The selection you have shouldn't generate lots of events. One
scenario where it would would be where the app (access) is using server side
cursors. This results in all the rows from a query beiung retrieved in batches
sometimes 1 row per batch. If you have 10 users querying a 100 row table,
you could end up with 1000 statements rather than 10.
What where the most common events in the profiler trace
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Has anyone run into any problems with Profiler for Server 2k5 killing
> their applications?
> We have a clustered Production environment (active/passive). The
> database is connected to via an MS Access ADP file which in turn the
> end users open via load balanced Citrix Servers (there are 3 or 4 in
> the farm). Things got a little slow in the past week so I went to run
> Profiler and PerfMon on the SQL Server.
> PerfMon caught some spikes in the Disk Queue Length & Pages per
> Seconds, but it looked like normal intermintant activity, nothing
> persistant that could be causing huge problems. Profiler (I filtered
> it down to a specific database and looked for duration of 30 seconds
> or longer) wasn't finding any queries which met my criteria.
> However, every time I started Profiler --whether or not PerfMon was
> running, whether I was logging to a trace table, trace file or not
> logging at all -- All the people logged on through the ADP had
> problems. The application froze up, screens when blank, queries and
> reports timed out.
> We verified with our system admins that it wasn't a network issue,
> everything seemed to still work fine through the backend (though we
> weren't running complex queries), but nothing through the front end
> worked until Profiler was stopped. The Events I was using were the
> Scans Started, Scans Stopped, SQL Batch Completed, Lock Deadlock, Lock
> Deadlock Chain, RPC Completed and SQL Transactions. That's it.
> Does anyone know of any issues that might have caused this scenario?
> Thanks in advance,
> Catadmin
|||"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:B408EA49-35B4-4919-94D9-99F5151B58E1@.microsoft.com...
> Has anyone run into any problems with Profiler for Server 2k5 killing
> their
> applications?
> We have a clustered Production environment (active/passive). The database
> is connected to via an MS Access ADP file which in turn the end users open
> via load balanced Citrix Servers (there are 3 or 4 in the farm). Things
> got
> a little slow in the past week so I went to run Profiler and PerfMon on
> the
> SQL Server.
> PerfMon caught some spikes in the Disk Queue Length & Pages per Seconds,
> but
> it looked like normal intermintant activity, nothing persistant that could
> be
> causing huge problems. Profiler (I filtered it down to a specific
> database
> and looked for duration of 30 seconds or longer) wasn't finding any
> queries
> which met my criteria.
> However, every time I started Profiler --whether or not PerfMon was
> running,
> whether I was logging to a trace table, trace file or not logging at
> all --
> All the people logged on through the ADP had problems. The application
> froze
> up, screens when blank, queries and reports timed out.
> We verified with our system admins that it wasn't a network issue,
> everything seemed to still work fine through the backend (though we
> weren't
> running complex queries), but nothing through the front end worked until
> Profiler was stopped. The Events I was using were the Scans Started,
> Scans
> Stopped, SQL Batch Completed, Lock Deadlock, Lock Deadlock Chain, RPC
> Completed and SQL Transactions. That's it.
> Does anyone know of any issues that might have caused this scenario?
>
First, it sounds like you don't have the right methodology for performance
analysis and improvement in SQL Server 2005. In 2000 you always had to use
trace or profiler to analyze performance, but in 2005 this is not always
necessary.
Have you examined the server reports in SQL Server 2005? The reports and
the Dynamic Management Views can give you a ton of information about what's
running, using resources and waiting on your box.
First run the the "Performance = Top Queries by Total IO". This is the best
single snapshot of your workload. More detail is available from the
"Dynamic Management Views" that these reports are based on.
For performance analysis and improvement methodology see:
SQL Server 2005 Waits and Queues
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx
Second, you should generally not use Profiler to run a trace on a production
machine. Instead use profiler to define the events you are interested in
and generate a script to create a server-side trace. Then run the trace on
the server, writing the trace output to a mostly-idle local drive (the
system drive works well for this if you have space).
See, eg
SQL Profiler and Trace
http://www.sql-server-performance.com/qdpma/inst_4_profiler.asp
How to troubleshoot SQL Server performance issues
http://support.microsoft.com/kb/298475/
David
|||"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:uIr1On7IHHA.4376@.TK2MSFTNGP03.phx.gbl...
> Hi David
> These reports are limited to only reporting on what's in the cache at the
> time you run them. This isn't exactly comprehensive & can be quite
> mis-leading if the proc cache is high transient.
Yes, but since you can just walk up to any server, any time and look at the
reports, they are a great place to start.

> I disagree with your statement that SQL Profiler generally shouldn't be
> used on a production system. If the production system has such little
> bandwidth that it can't tolerate the miniscule overhead of tracing, it
> will probably have much bigger problems.
>
But SQL Profiler has more than a "miniscule" amount of overhead when tracing
thousands of events per second. On many systems it won't have a noticeable
impact, but on a production system that has a high number of batches/sec and
is struggling to keep up and imposing user waits, the extra overhead of
Profiler can be devastating. For instance the OP reported
"However, every time I started Profiler --whether or not PerfMon was
running,
whether I was logging to a trace table, trace file or not logging at
all -- All the people logged on through the ADP had problems. The
application
froze up, screens when blank, queries and reports timed out."

> I do agree that using a server-side trace is better, but it's also
> extremely inconvenient for ad-hoc use.
That's why the reports, are so helpful, despite their limitations.
.. . ..
David
|||"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OyIcJp8IHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi David
> The OP appears to be mis-using the Profiler & is having a bad experience
> as a result. This is no reason to state that Profiler shouldn't generally
> be used on a production system.
> . . ..
The possible loss of availibility caused by "mis-using the Profiler" is not
a good reason? I guess "production" means different things to different
people.
David
|||Hi
Along with Simon's suggestion you should also note that filtering does not
stop the event being captured, it is just thrown away and not logged if it is
not satisfied, therefore it could potentially cause more work on the server.
The Profiler GUI can use space on the TEMP directory, so this should be a
disc that is not the system disc or used by SQL Server. If you are also
creating result sets in TEMP then you would see potential problems. Your
system admin should have changed the default TEMP location for a disc other
than the system disc (as you don't want the disc to fill up!)
John
"Catadmin" wrote:

> Has anyone run into any problems with Profiler for Server 2k5 killing their
> applications?
> We have a clustered Production environment (active/passive). The database
> is connected to via an MS Access ADP file which in turn the end users open
> via load balanced Citrix Servers (there are 3 or 4 in the farm). Things got
> a little slow in the past week so I went to run Profiler and PerfMon on the
> SQL Server.
> PerfMon caught some spikes in the Disk Queue Length & Pages per Seconds, but
> it looked like normal intermintant activity, nothing persistant that could be
> causing huge problems. Profiler (I filtered it down to a specific database
> and looked for duration of 30 seconds or longer) wasn't finding any queries
> which met my criteria.
> However, every time I started Profiler --whether or not PerfMon was running,
> whether I was logging to a trace table, trace file or not logging at all --
> All the people logged on through the ADP had problems. The application froze
> up, screens when blank, queries and reports timed out.
> We verified with our system admins that it wasn't a network issue,
> everything seemed to still work fine through the backend (though we weren't
> running complex queries), but nothing through the front end worked until
> Profiler was stopped. The Events I was using were the Scans Started, Scans
> Stopped, SQL Batch Completed, Lock Deadlock, Lock Deadlock Chain, RPC
> Completed and SQL Transactions. That's it.
> Does anyone know of any issues that might have caused this scenario?
> Thanks in advance,
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)
|||I will admit that the trace template I was using was defined and created by
my predecessor for use with SQL Server 2000. However, I used it several
times on SQL Server 2000 (production with the same amount of users) without
any problems whatsoever. So, why would it be creating problems now with SQL
Server 2005? It's not an application change issue.
I did not know the Duration column had been changed from milliseconds to
microseconds. How many microseconds are in a second?
I also did not know about scripting the Profiler session into a trace that
could be run Server-side. I'll look into that and into removing the Scans
Started and Scans Stopped.
As far as the comments about mis-using SQL Server Profiler (This isn't a
flame, it's merely my perspective on the matter, so please don't take this
personally):
It's rather hard to use it properly when there is no good documentation on
what all the different counters mean and what values they should have and
should not have. Books Online for SQL 2k5 is NOT very helpful with the new
version of Profiler. Yes, Deadlock and Deadlock Chain and some of the common
counters are explained in various books, but I have yet to find one
comprehensive resource that explains every last one and says what values are
good and what values are bad. If you know of one, please let me know so I
can spread the word. I know for a fact that I'm not the only DBA having this
problem.
Also, when I studied for the MCDBA and now when I'm going over material for
the MCITP, one of the things I keep reading about Profiler is that it's used
for "identifying and tuning slow running queries". In fact, the course for
the Implementation and Maintenance of SQL Server 2k5 (70-431) says, "The SQL
Server Profiler is also useful for tracking errors or creating a trace file
to find the cause of a problem" which was exactly the use I was attempting to
put it to. Our system had slowed down dramatically for no reason we could
determine, so I was attempting to see if we had any long running queries that
might be causing the problem. Are the course and books wrong? Is this not
what Profiler is for?
And if you could please explain the following to me, I would greatly
appreciate it. If the queries are only regularly called against the
Production box, how does it help me if I can't use Profiler on a production
box? A query runs faster on a box that only developers use than it does on a
box the end users use merely because of the difference in the number of
connections and threads. Also, errors are more likely to show up on
Production than they are on QC because one or two people can't possible go
through every last scenario. Or am I the only DBA who has had this
experience?
Lastly: Greg, though I appreciate your suggestion of using a third party
tool, the simple fact of the matter is that we don't have a budget to use
external tools for things that SQL Server 2005 should be able to manage
itself. My boss would toss me into the lake for even suggesting that we
spend money on something Profiler is supposed to be able to do. His reply
would be "If Profiler (and the rest of SQL) suddenly can't do what it used to
do, then our business will simply have to re-evaluate the reasons we use
Microsoft SQL Server to begin with and consider another database alternative".
Thanks again for your assistance,
Catadmin
|||Hi
You may want to look at some of the MSDN/Technet webcasts such as "Profiling
for Better Performance" by Kimberly Tripp, MSDN webcast at
[url]http://msevents.microsoft.com/cui/webcasteventdetails.aspx?eventid=1032278616&eventc ategory=5&culture=en-us&countrycode=us[/url]
and also the webcast "Using SQL Server 2005 Profiler" by Brian Knight at
http://www.jumpstarttv.com/Media.aspx?vid=59
To create a trace from the a profile you already have use the
file/export/script trace definition/for SQL Server 2005 menu options.
The duration is described in Books online topic "Describing Events by Using
Data Columns" this states "In SQL Server 2005, the server reports the
duration of an event in microseconds (one millionth, or 10-6, of a second)
and the amount of CPU time used by the event in milliseconds (one thousandth,
or 10-3, of a second). In SQL Server 2000, the server reported both duration
and CPU time in milliseconds. In SQL Server 2005, the SQL Server Profiler
graphical user interface displays the Duration column in milliseconds by
default, but when a trace is saved to either a file or a database table, the
Duration column value is written in microseconds."
You may also want to look at what maintenance tasks you have running to keep
the indexes up-to-date. Check out the index fragmentation using the
sys.dm_db_index_phyical_stats view and the ALTER INDEX commands. There is
more on this in Books Online and Kalen Delaney has a good chapter on index
internals that includes information about using this in "Inside Microsoft SQL
Server 2005: The Storage Engine" ISBN 0735621055
HTH
John
"Catadmin" wrote:

> I will admit that the trace template I was using was defined and created by
> my predecessor for use with SQL Server 2000. However, I used it several
> times on SQL Server 2000 (production with the same amount of users) without
> any problems whatsoever. So, why would it be creating problems now with SQL
> Server 2005? It's not an application change issue.
> I did not know the Duration column had been changed from milliseconds to
> microseconds. How many microseconds are in a second?
> I also did not know about scripting the Profiler session into a trace that
> could be run Server-side. I'll look into that and into removing the Scans
> Started and Scans Stopped.
> As far as the comments about mis-using SQL Server Profiler (This isn't a
> flame, it's merely my perspective on the matter, so please don't take this
> personally):
> It's rather hard to use it properly when there is no good documentation on
> what all the different counters mean and what values they should have and
> should not have. Books Online for SQL 2k5 is NOT very helpful with the new
> version of Profiler. Yes, Deadlock and Deadlock Chain and some of the common
> counters are explained in various books, but I have yet to find one
> comprehensive resource that explains every last one and says what values are
> good and what values are bad. If you know of one, please let me know so I
> can spread the word. I know for a fact that I'm not the only DBA having this
> problem.
> Also, when I studied for the MCDBA and now when I'm going over material for
> the MCITP, one of the things I keep reading about Profiler is that it's used
> for "identifying and tuning slow running queries". In fact, the course for
> the Implementation and Maintenance of SQL Server 2k5 (70-431) says, "The SQL
> Server Profiler is also useful for tracking errors or creating a trace file
> to find the cause of a problem" which was exactly the use I was attempting to
> put it to. Our system had slowed down dramatically for no reason we could
> determine, so I was attempting to see if we had any long running queries that
> might be causing the problem. Are the course and books wrong? Is this not
> what Profiler is for?
> And if you could please explain the following to me, I would greatly
> appreciate it. If the queries are only regularly called against the
> Production box, how does it help me if I can't use Profiler on a production
> box? A query runs faster on a box that only developers use than it does on a
> box the end users use merely because of the difference in the number of
> connections and threads. Also, errors are more likely to show up on
> Production than they are on QC because one or two people can't possible go
> through every last scenario. Or am I the only DBA who has had this
> experience?
> Lastly: Greg, though I appreciate your suggestion of using a third party
> tool, the simple fact of the matter is that we don't have a budget to use
> external tools for things that SQL Server 2005 should be able to manage
> itself. My boss would toss me into the lake for even suggesting that we
> spend money on something Profiler is supposed to be able to do. His reply
> would be "If Profiler (and the rest of SQL) suddenly can't do what it used to
> do, then our business will simply have to re-evaluate the reasons we use
> Microsoft SQL Server to begin with and consider another database alternative".
> Thanks again for your assistance,
> Catadmin
|||"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u7bsF5AJHHA.4760@.TK2MSFTNGP03.phx.gbl...
> It's possible to mis-use the SQL Server Management Studio as well - should
> it generally not be used on production servers too?
> You say that "production" means different things to different people -
> here's what it means to me. I operate a business that specialises in
> providing remote performance SLAs to a wide range of customers (and have
> been doing so for a number of years now). Under our SLAs, it can cost us
> if monitored servers aren't performing against the SLA (there are usually
> also rewards for exceeding KPIs). As a policy, we monitor the servers with
> server-side traces on a 24x7 basis and we have never seen these traces
> harm performance, even on the busiest of servers.
> Without doing this tracing, we'd really be flyng "blind" & would have no
> way of answering calls fom our customers such as "something was running
> slow 30 minutes ago, can you look into it?".
> Doing this tracing also allows us to be pro-active in our tuning - we can
> see problems before they manifest into larger problems. Without doing the
> tracing, you're pretty much commited to reactive style tuning which is way
> down the food chain from our perspective.
I absolutely agree that you should run server-side traces 24x7 on servers.
The benefits you mention are well worth the small overhead of tracing.

> I think use of the SQL Profiler / SQL Trace should be encouraged, along
> with quality advice about best usage practises rather than blanket
> statements warning people against using it on their production systems,
> simply because some *might* mis-use it.
>
I think we basically agree that unless you know what you're doing, you
shouldn't use SQL Profiler on a production system.
David
|||Thanks for your support.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Greg Linwood" <g_linwood@.hotmail.com>
news:ulhnPDIJHHA.3916@.TK2MSFTNGP02.phx.gbl...
>I agree that, unless you know what you're doing with any tool, you
>shouldn't be using it on a production system. However, this is a fairly
>obvious & generic argument..
> Note that in this case, the user was simply continuing to use a script
> which had been working fine on their SQL 2000 server. The problem appears
> to be that MS has stupidly changed the Duration measurement without
> adequate warning, consequently breaking the customer's system. I wonder
> whether MS mentioned that the Duration measurement has been changed in
> their upgrade preparations documentation?
> Seems to me that it's MS who don't know what they're doing with their own
> product in this case (c:
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23gvHdoEJHHA.1240@.TK2MSFTNGP03.phx.gbl...
>
sql

No comments:

Post a Comment