Exe.Plan & Indexes

[billum]

Hi,

Iam trying to optimize some queries and views.When I run the queries and view the execution plan,
it shows clustered Index scan for some tables.For eg. table1 may have a PK and in the execution plan it shows
it is using that, as table1.pkindexname.The query may have another column (col2)of table1 which is not indexed
used for a join to another table say table2.colname (where table2.col may be a PK and Indexed)
.Is this the reason why it shows clustered Indexed scan? Will it increase the performance if I Index on
table1.col2 also.

I would greatly appreciate some guidelines on this

Thanks
Bill

 

[tlbroadbent]

The answer correct answer depends on several facors. How many rows does each table contain? Which columns are selected rom each table? What is the selection criteria of the query? If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[billum]

First table (table1) contains approx 20,000 rows and the second contains less than 200.Almost 3 colums are selected from both tables and 3 from a 3rd table.It has a left join and 2 innerjoins and criteria is table1.timestamp not null.

That query was only a specific scenario that I presented.I have other queries which give the same info.Any article or
paper to read and understand from an execution plan with regard to indexes is greatly aprreciated

Thanks
Bill

 

[tlbroadbent]

You can't really do much to optimize the query if the selection criteria is on the larger table with criteria of "IS NOT NULL." SQL will perform a table scan or clustered index scan which is the same thing because clustered indxes are on the same pages as the data.

Check here for some links to sites and articles on performance. You'll want to read some of the articles on indexing.

http://tlbroadbent.home.attbi.com/sql/ss_performance.htm

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm