SQL Server Performance

Index seek on one execution plan but index scan on another between two servers

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by WingSzeto, Jun 4, 2009.

  1. WingSzeto Member

    I ran an execution plan on a query in two different servers (say A and B) which both are SQL 2008 std. The query execution plan is same in terms of choosing the same index. But the plan in Server A uses index seek and in server B it uses index scan with the same index. . I have tried rebuild that index and rebuild statistics on that table with full scan but the execution plan still use index scan instead of index seek in server B. I am so puzzled by it. What did I miss?
    w
    WingSzeto, Jun 4, 2009
    #2

  2. MohammedU New Member

    Both servers has the same data? same number of rows?
    MohammedU, Jun 4, 2009
    #3

  3. WingSzeto Member

    Both servers have the same data on Tuesday. Server B is our production server and server A has a copy of database from Server B. For the tables that were used in the query, their size has grown about 1~3 %. The biggest table now has 36,000 more records in Server B (13093284) compared to server A (13057112). Keep in mind that the query do have the where clause which will further reduce the row difference between the two servers. I ran the same query on both server yesterday so the row records between the two servers are different but not big.
    W
    WingSzeto, Jun 5, 2009
    #4

  4. satya Moderator

    Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.
    Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.
    A simple formula is a TABLE SCAN occurs when no useful indexes exist which reads all data, row by row, to find the match. However, a SCAN might be faster than a SEEK if the table is relatively small and cached in memory.
    http://msdn2.microsoft.com/en-us/library/aa964133.aspx and the data that needs to fetched is the key always.
    satya, Jun 5, 2009
    #5

  5. WingSzeto Member

    Satya,
    Thank for the reply. My puzzle is that the identical query ran on the two different server which almost has the same data and same number of rows in all the tables involved (see my other reply to another MVP). Furthermore, the execution plan between the two servers is the same in terms of index choosen, in other words, the same index is used, but one uses index scan and the other uses index seek. The joins are specific with the where clause to further narrow the data choice. I expect both would use index seek on the same index, but is not.
    W
    WingSzeto, Jun 5, 2009
    #6

Share This Page