0

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
1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Aug 29, 2017 at 10:27

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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