MS SQL Server - Profiler Question

Author

Profiler Question

Buk Usu

2006-10-24, 6:30 pm

Hi,

on sql server 2000 ... apart from going to the execution plan, is there a
way to identify queries that are doing table scans ?

thanks

Hari Prasad

2006-10-24, 6:30 pm

Hi,

You could use the graphical execution plan or use the Set command (set
showplan_text on ) followed the query to get the
details of the statments executed.

eg:-

set showplan_text on
Select * from sysobjects

Thanks
Hari
SQL Server MVP

"Buk Usu" <bukusu@gmail.com> wrote in message
news:evimfFe3GHA.1256@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> on sql server 2000 ... apart from going to the execution plan, is there a
> way to identify queries that are doing table scans ?
>
> thanks
>

Tracy McKibben

2006-10-24, 6:30 pm

Buk Usu wrote:
> Hi,
>
> on sql server 2000 ... apart from going to the execution plan, is there a
> way to identify queries that are doing table scans ?
>
> thanks
>
>

What we typically do is analyze the trace logs for an hour's worth of
activity, focusing on the number of reads. The queries that do the most
reads are then reviewed, looking for missing indexes, inefficient code,
etc...

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

JXStern

2006-10-24, 6:33 pm

On Thu, 21 Sep 2006 20:52:26 -0400, "Buk Usu" <bukusu@gmail.com>
wrote:

>on sql server 2000 ... apart from going to the execution plan, is there a
>way to identify queries that are doing table scans ?

ha! apparently not directly.

http://www.sql-server-performance.c...
tips.asp

Now, the problem with this is that all you get is the IndexID, which
refers to index objects found in the sysindexes table of the database
you are tracing. IndexID in Profiler matches up with the indid column
in the sysindexes table, and the name of this object can be found in
the name column. When you look at the name column, you will see than
many of the table scans are performed on system tables. You can ignore
these. What you want to focus on are user tables. Unfortunately, you
can't directly see the column name, but what you will see is the index
name, or the statistic's name. A column cannot only have an index; it
can also have statistics, even if there is no index.

Once you have identified the name of object that is subject to too
many table scans, the next part of your detective job is to identify
the column referred to by the index or statistic name. Once you do
that, you have identified the problem column and now can look at it in
order to see if it can benefit from better indexing or a
better-written query. [2000] Updated 5-1-2006

--

I see that if you turn on the SCAN event, you get a constant dribble
of scans against id=2 by sa with no other info. Pretty funny.

--

You can still look for scan events on the same spid as execute your
commands and match them up.

J.

Paul Wehland

2006-11-08, 7:12 pm

Can't you use the 'Scans' event class within SQL 2000 Profiler?
Add 'Scan:Started' to your selected event classes.

-pwehland

"Buk Usu" <bukusu@gmail.com> wrote in message
news:evimfFe3GHA.1256@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> on sql server 2000 ... apart from going to the execution plan, is there a
> way to identify queries that are doing table scans ?
>
> thanks
>

Sponsored Links