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