CODEWITHSUNDEEP
sql-serverindexing
user1858724
user1858724

Reputation:

Microsoft SQL Server 2008 R2 index slows down query

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.

Upvotes: 3

Views: 1382

Answers (2)

Franklin
Franklin

Reputation: 905

u may use profiler and DTA , it will give recomendations to include indexes on it.

coming to your problem first check the columns version_date , user_id 's having indexes are not. if alredy indexes are there then u have to identify the fragmentaion level of indexes. if fragmentation is in between 5 to 30% then u have to reorganize the index , if fragmentaion is more than 30% then u have to rebuild the indexes.

checking fragmentaion go to required table and goto indexes --> rc on ur index name--> properties--> fragmentaion

if ur table or view doen't contain any indexes try to create one clustered index and then nonclustered index.

Upvotes: 0

marc_s
marc_s

Reputation: 754488

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...

Upvotes: 2

Related Questions