views and Indexes | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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.

If there are index scan (cluster or not) SQL is using the best plan he can find.
The problem are table scan, etc. Do the following: SET STATISTICS IO ON your query SET STATISTICS IO OFF and take a look if there are a lot of read ahead.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.
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
http://www.sql-server-performance.com/mr_indexing.asp too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
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.

]]>