Re: How to detect the sql statement that causes full table scans

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Bernd Maierhofer \(dato\) (bernd.maierhofer_at_dato.at)
Date: 08/25/04 

Date: Wed, 25 Aug 2004 07:48:55 +0200

Hi Howard,

thank you for your input, however, some questions still remain:
If Profiler does not output an index id, does this mean there is a table
scan?
Any way to filter the Profilerīs output to show only scans with NO index id?

TIA Bernd

"Howard Long" <howard@howardlongxxx.com> schrieb im Newsbeitrag
news:cgf18j$apb$1@sparta.btinternet.com...
> "Bernd Maierhofer (dato)" <bernd.maierhofer@dato.at> wrote in message
> news:elLTVVaiEHA.3016@tk2msftngp13.phx.gbl...
> > well, this is of course, what I tried to do, but profiler does not tell
me
> > what sql-statement is the reason. Scans generally are profiled, how do I
> > identify table scans? And how do I identify the sql-statements?
>
> Fairly simple - you stick on SP:StmtCompleted and SQL:StmtCompleted (often
> with SP:Completed, SQL:BatchCompleted and RPC:Completed) with a filter of
> Duration over, say, 200. That will catch all your queries taking over
200ms.
>
> I generally have on Lock:Escalation and SP:Recompile too.
>
> Then you might want to add on a selection of Execution Plan, Show Plan
All,
> Show Plan Statistics and/or Show Plan Text, perhaps with some filtering
for
> the particular statement(s) causing the issue. Alternatively, often it's
> possible to extract the results of the xxxCompleted and run them in Query
> Analyser to determine the offending statement, especially as it's not too
> parameterised.
>
> If your system's not too busy you can switch a lot more on and open up the
> filtering too. Debugging this kind of thing is usually interactive, and
you
> need to tweak Profiler on the fly to provide you with the answers you want
> for your particular predicament. Unfortunately Profiler needs sa-type
> privileges to operate, and this interactive method of problem
determination
> is sometimes not compatible with corporate security policies, especially
on
> production systems.
>
> Kind Regards, Howard
>
>

Relevant Pages

  • Re: How to detect the sql statement that causes full table scans
    ... > well, this is of course, what I tried to do, but profiler does not tell me ... > what sql-statement is the reason. ... Then you might want to add on a selection of Execution Plan, Show Plan All, ... filtering too. ...
    (microsoft.public.sqlserver.server)
  • Re: delete statement causing procedure to hang
    ... It then performs updates on around 2K rows, ... Profiler shows it stop at the delete ... Running my maintenance plan each night apparently isn't working as ... > starting to hang. ...
    (microsoft.public.sqlserver.programming)
  • Getting Show Plan All Event in the Profiler for all statements longer than 500msec.
    ... Is it possible to let the Profiler return the Show Plan All event for ... It does not filter on the ... We now return all the queries which take longer than ...
    (microsoft.public.sqlserver.server)
  • performance in a citrix environment
    ... server portions of the project but I recently got involved with the UI in ... I plan to use the .NET Memory Profiler (evaluation ... verson for now), the CLR profiler, and possible Red-Gate's .NET profiler. ... addition I'm going to be taking a look at the .NET perfmon counters although ...
    (microsoft.public.dotnet.framework.performance)
  • Re: Stored Procedures Data view webparts
    ... I just tried filtering on one of my DVWP's that uses a stored proc ... and diagnose with SQL profiler. ... have these problems with stored procedures and filtering the data. ... When i do attempt to i break the stored proc and just get the generic ...
    (microsoft.public.sharepoint.portalserver)