Microsoft SQL Server 2008 R2 index slows down query

Question

I'm currently working on speeding up the Microsoft SQL Server 2008 R2 performance. When analysing queries the Microsoft Database Engine Tuning Tool comes up to create an index with this query:

CREATE NONCLUSTERED INDEX [samplelocation1] ON [dbo].[sample_location]  
(
    [sample_id] ASC,
    [sample_code] ASC
) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

This takes down the execution time of the query on the testing server (which runs the same version of SQL server, 2008 R2) using a database restored from a backup from 17 to 5 seconds.

On the production server however, execution time goes up from 17 seconds to 1 minute 40 seconds. What's going on?

The query:

select * 
from sample_view  
where version_date >= '<date>' 
  and version_date - 0.9999999 <= '<date>' 
  and (cus_id in (select company_id 
                  from company_emp_relation_view 
                  where user_id = '<userid>') 
       or 
       fac_id in (select company_id 
                  from company_emp_relation_view 
                  where user_id = '<userid>'))  
  and sample_code in (select min(sample_code)  
                      from sample_location 
                      group by sample_id) 
  and (rfq_status<>'I') 
  and location <> 'D'  
order by 
  version_date desc

The query wasn't written by me, and looks overly complex but I'd like to solve this without changing any queries. My biggest surprise is that the effect of the index is not the same across systems.

Answer 1

Well, the amount and distribution of the data on your test vs. your production system might be totally different - and thus, anything you determine on the test system with a few hundred rows might NOT be working the same way on the production system with hundreds of thousands of rows ..

The amount and distribution of data is a key factor in how the query optimizer decides whether or not to use an index (or just do a table scan instead). So any performance tuning must be performed on the actual data (or a copy thereof) - not on a "dummy" dev or test system with only a fraction of the data...