How to identify these in sql server

Question

Post reply

How to identify these in sql server

v03066

SSC Enthusiast

Points: 116
More actions
February 6, 2006 at 2:41 pm

#168068

Hi,
How do I identify these in sql server 2k?
1) what tables are going thru a full table scan.
2) What are the unused indexes over a period of say 3 months so that these can be dropped to improve performance ?
TIA
Mo

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

An easy tool to use is sql profiler.

Don't necessarily recommend for Production, but certainly can be used. You can select different events.

The scans event class will show you table, or index scans.

I do not know of a 3rd party application that does this.

I'm not sure you can get more specific using serverside tracing

http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm

v03066 SSC Enthusiast Points: 116 More actions · Answer 2

I am still not clear on what options do I choose on sql profiler to see what tables or indexes are going for a full scan.

What do I need to choose on the events,data columns or the filters tab in SQL Profiler ?

TIA

Ray M SSC-Insane Points: 21093 More actions · Answer 3

In Events remove everything.

Then add the Scan:Stopped event.

In Data columns Add the IndexID, and ObjectID columns. (Remove any you don't want.)

Then you can use the indexid to run this query

Select name

From sysindexes

where id = yourid

select *

From sysobjects

Where id = yourObjectid

v03066 SSC Enthusiast Points: 116 More actions · Answer 4

Hi,

Thx for the reply. I tried it and here's a sample of that.

Scan:Stopped 2 190623722 Microsoft Office 2000 x63880 0 500 304 2006-

02-07 13:12:08.170

1) Why isn't the TextData appearing ?

2) This output still does not answer my question of what sqls are causing a full table scan ?

TIA

Ray M SSC-Insane Points: 21093 More actions · Answer 5

Unfortunatelly its not that straitforward. I do not know of third party tools to help with this.

but as far as sql. its a little painful.

I don't know your architecture, But you can also add the TSQL-->SQL:StatementStarted Event. (or stored procedures Starting, etc)

So yo will see a Statement then the scan.

Scan:Stopped 149575571 1 SQL Query Analyzer

SQL:StmtCompleted select * from Sales SQL Query Analyzer

You can start a profiler trace, and save results to a table. Then query the saved table for events and see related queries.

I'm sorry, but I do not know of an easy answer for you.

Shawn Wilson Ten Centuries Points: 1271 More actions · Answer 6

In profiler add just the following event:

Performance:ExecutionPlan

you will see something like the following in textdata

Execution Tree

--------------

Compute Scalar(DEFINE[LabLink_01212006].[TextData]=[LabLink_01212006].[TextData]))

|--Filter(WHERElike([LabLink_01212006].[TextData], '%Fetch%', NULL)))

|--Table Scan(OBJECT[Northwind].[dbo].[LabLink_01212006]))

it indicates a tablescan and shows the tablename

If you want to see only tablescans.... filter the textdata column so like %Table Scan%

that's it.