SQL Server Performance

views and Indexes

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by sehajsingh, Jan 31, 2007.

  1. sehajsingh New Member

    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.




  2. Luis Martin Moderator

    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.



  3. MohammedU New Member

  4. satya Moderator

    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.
  5. jasonlel New Member

    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.





  6. mmarovic Active Member

    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.

Share This Page