Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm running onto a weird issue.

We've got two db servers, one is development and the other one is production.

Both have same specs.

VMs (Hyper-V) Windows Server 2008 R2 64 Bit SQL Server 2008 R2 64 Bit

If I run the exact same query on our dev server it takes 10 seconds to give me 10 results. The same query in the production server takes 1:30 minutes sometimes more.

Is there an easy way to find the source of the problem? Is it something related to indexes, workload?

This is just one example, the queries are dynamically generated, this is used by our web application and it generates queries on an advanced search menu

EDIT Added Query

SELECT Client_Id, Project_Id, Person_Id, Membership_No, Family_Name,
       First_Name, Password, Company, Country_Name, EMail, Member_Type,
       Member_Type_Description, Member_Status, Member_Status_Description,
       Category, Member_Category_Description, Balance, Date_Registered 
FROM uvw_Members_Display 
WHERE Client_Id='EHA' 
  AND Project_Id='EHA' 
  AND ( Client_Id='EHA'
    AND Project_Id='EHA' 
    AND (  (( uvw_Members_Display.Member_Type LIKE '01' ))
        OR ( uvw_Members_Display.Member_Type LIKE '02' )
        OR ( uvw_Members_Display.Member_Type LIKE '03' )
        OR ( uvw_Members_Display.Member_Type LIKE '04' )
        )
    AND ( uvw_Members_Display.Member_Status LIKE 'I' )
    AND ( Balance =0 )
    AND ( uvw_Members_Display.Category LIKE '02' ) 
    AND Date_Registered IS NOT NULL 
      ) 
ORDER BY Person_Id DESC
share|improve this question
1  
What are the execution plans for each query? Please post them if you can. Just a guess here, but it could be a result of bad parameter sniffing (simple-talk.com/sql/t-sql-programming/parameter-sniffing). Without seeing the plans and what is cached, it's hard to tell though. –  Thomas Stringer Jun 1 '12 at 14:40
    
there are no stored procedures or parameters involved. is just a select query with a couple of joins. –  Federico Giust Jun 1 '12 at 14:53
    
I just added one of the queries as an example –  Federico Giust Jun 1 '12 at 14:57
    
When I have run into situations such as this the first thing I do is verify that my both the schema (including keys, indexes, etc) and data (volume of data as well as "bad" data can cause increased query time) for the tables underlying the view (i.e. uvw_Members_Display) are identical on both the development and production environments. Then I know that I am comparing "apples" to "apples" as the saying goes. –  SQLToddNelson Jun 4 '12 at 16:31
1  
Start by looking at the execution plan at the output from SET STATISTICS IO ON and see what the outputs are. Odds are the statistics are out of date on the production server and need to be updated, but the execution plan and the output from SET STATISTICS IO ON should tell you what's going on. –  mrdenny Jun 5 '12 at 13:06

1 Answer 1

up vote 1 down vote accepted

Thanks to mrdenny! Your commment pointed me in the right direction!

I've pasted the query into SQL Management Studio

Then used the option Display Estimated Execution Plan

This gave me an output that an index in one of the tables belonging to the view needed to be recreated, so I deleted it and create it from scratch using the suggested output.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[MemberOrders] ([Client_Id],[Project_Id],[Person_Id])

Now the query takes only 2 seconds and not the 1:30min that it used to take!

Thanks a lot guys

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.