Finding queries/stored procedures that cause table scans

[mkal]

If this isn't the correct forum just let me know where to go.

How can I find out which queries/stored procedures are causing table/index scans?

I know I can use profiler and select scans:started and scans:Stopped but that doesn't tell me which queries/stored procedures are the cause.

Thanks
mk

 

[calico]

Hi,

Big job !

HINT 1 :

With sql 2005 : with Profiler and Database Tuning Advisor.
Check carefully new indexes suggested by the Database Tuning Advisor. IF an index is suggested for a table, may be it is because a table scan exist on this table. Try to identify SP or view ou had-hoc query that use this table. For each SP, view , had-hoc query you identified, in MSSM, check the execution plan to confirm that a table scan is going to append with this objects.

HINT 2:

With sql 2005 : With Profiler only, if you select "scans:started", I think (not sure) that you can select the column that contain the "object ID" of the table that is scanned. Try to identify SP or view ou had-hoc query that use this table. For each SP, view , had-hoc query you identified, in MSSM, check the execution plan to confirm that a table scan is going to append with this objects.

I hope I understanded you question correctly and this can help you

Calico

 

[mkal]

Hi Calico,

You've touched on exactly my issue when you said:"...Try to identify SP or view ou had-hoc query that use this table."

So just how does one go about finding all the SP/views/ad hoc queries that use 'this table'? Is there a easy way to scan all of these objects to look for a given table name i.e. one that shows a lot of scans being done on it?

Thanks!

 

[calico]

Hi,

You can use the "Scripting Wizard" for SP, VIEW and UDF
to script all those objects into a file and after edit (scan) this file to look for the given table name .
(on MSSM, righ click on the DB / tasks / Generate script)

You must use PROFILE to dentify had-hoc query.
Identify the corresponding object-id of this table / activate a filter on Profiler to see only statements where object-id of this file appears on the appropriate column/field. On TEXT column/field you can see SP, View, UDF (?) and had-hoc query that use this table. Copy/Paste this had-hoc query and verfy the execution plan of this had-hoc query to see if a table scan for the given table name occurs with this had-hoc query

May be you can buy or find free tools on internet that can help you.

Calico