DISTINCT issue in SQL Server 2008 RRS feed

  • Question

  • Hi

    Recently I have migrated a stored procedure from SQL Server 2005 to SQL Server 2008. When I executed the stored procedure it is running very very slow in SQL Server 2008. I have analyzed the cause of the performance degradation in SQL Server 2005. Here is my observation.

    I am using a query inside stored procedure which has a DISTINCT and INNER JOIN in it. When I removed that distinct my query is running very fast. If I put the dictinct back in place, it is taking more time to run. I check the Execution plan both in SQL Server 2005 database and SQL Server 2008 database. I saw the plan is using "Hash Match" join when I am not using DISTINCT and using "Nested Loops" join when I am using DISTINCT.  But in SQL Server 2005 both the case the execution plan is using "Hash Match" join. How to make this query to run faster as like SQL Server 2005?

    Sorry unfortunatly i cannot place the exact query that i am running.

    Please help.

    Wednesday, June 22, 2011 8:31 PM


All replies