SQL Server Performance

Execution plan different on two very similar databases

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

  1. WingSzeto Member

    I run a query on a published database and a subscriber database (each database is on its own server). On the published db it used a clustered index scan to return over 8 million records and on the subscriber db it used a nonclustered index seek to return < 100 records. I run update statistics statement every night by issuing "EXEC sp_updatestats" on both databases. Both have identical indexes. I run an update statistics just to that 'supposed to be used' index on the published database and it doesn't help. Do I have some statistics out-of-wreck on that table? Should I use full scan and will it lock the table because the table is a big table and have over 20 million records in it? Please advise.
  2. Luis Martin Moderator

    I can't understand one point.
    With the same query one result have 8 million and in the other server only 100?
    If yes, there is no index problem at all.
  3. moh_hassan20 New Member

    review carefully the indexes and indexed views on both servers for tables,views used in that query.
    If the number of rows are huge different between the two servers , and update statistics recently, sometimes optimizer may change execution plan based on the expected result set, and sometimes may apply table scan.
    for example , if expected record set of a query 900 row , and the table has 1000 row , in that case it may apply table scan, on the same time if the qury result set is 10 , it may use seek index.
    That is based on the density distribution of the index used.

Share This Page