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

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


Show quoteHide quote

Buk Usu wrote:
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...

ha!  apparently not directly.

http://www.sql-server-performance.com/sql_server_profiler_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

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

-pwehland



Show quoteHide quote

Bug in Enterprise Manager (SQL 2000)
copy database error 12291 and 208
SQL Server 2005 Activity Monitor doesn't show connections from Windows 98 clients
differnt execution plans for same query
database mail...can't connect to mailserver