views and Indexes

I am working as DBA and doing some optimization jobs.
I found the long running queries. These queries call views(joins more than two tables) and executes the result.
When I execute queries, the execution plan shows Index scan, Clustered index scan in participating tables. I also execute the SQL statements of the views, this also show, same nature of execution plans(index scan, clustered index scan). Is it necessary to remove scans from views first or can remove scans from query directly ?
why clustered scan/index scan happens evenif there are indexes in join columns, where clause, orderby clause ?

Please send me your kind answers soon.

Database Engine Tuning Adviser: How to tune your new SQL Server 2005
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1161285_tax301334,00.html?adg=301324&bucket=ETA

http://blogs.msdn.com/queryoptteam/
http://www.sql-server-performance.com/transact_sql.asp

Read above mentioned articles to know more about query optimization...


MohammedU.
Moderator
SQL-Server-Performance.com

Run the execution plan and see what index it is using. Sometimes SQL will use the wrong index.

quote:Originally posted by sehajsingh

I am working as DBA and doing some optimization jobs.
I found the long running queries. These queries call views(joins more than two tables) and executes the result.
When I execute queries, the execution plan shows Index scan, Clustered index scan in participating tables. I also execute the SQL statements of the views, this also show, same nature of execution plans(index scan, clustered index scan). Is it necessary to remove scans from views first or can remove scans from query directly ?
why clustered scan/index scan happens evenif there are indexes in join columns, where clause, orderby clause ?

Please send me your kind answers soon.

quote:Originally posted by sehajsingh

I am working as DBA and doing some optimization jobs.
I found the long running queries. These queries call views(joins more than two tables) and executes the result.
When I execute queries, the execution plan shows Index scan, Clustered index scan in participating tables. I also execute the SQL statements of the views, this also show, same nature of execution plans(index scan, clustered index scan). Is it necessary to remove scans from views first or can remove scans from query directly ?

Before query optimizer starts preparing an execution plan of query that refers to a view (views), view name is replaced by the query that defines a view.

For example, if view definition is:


create view example as select col1, count(*) as occurance from tableT group by col1

The query:

select e.col1, e.occurance, a.col2
from tableA a
join example e on e.col1 = a.col1
where e.occurance = 1

is first translated to:

select e.col1, e.occurance, a.col2
from tableA as a
join (select col1, count(*) as occurance from tableT group by col1) as e on e.col1 = a.col1
where e.occurance = 1

and only after that query optimizer starts its magic.