Query running slow in SQL 2005 after migrating from SQL 2000

The other thing you can do is create a plan guide. I am not an expert on
this, and unfortunately I missed Kalen's session on it at PASS, but she
would be best to explain it. You can also look at:

http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx

--
Jonathan Kehayias
SQL Server MVP
http://jmkehayias.blogspot.com
http://www.sqlclr.net


"Plamen Ratchev" wrote in message

> If the query still runs slows after updating statistics, and there is no
> chance you have a bad plan because of parameter sniffing, then you may
> want to look at changing the query.
>
> You can post the query here and someone may have suggestions on how to
> improve.
>
> Also, you can run the the query through the Database Tuning Advisor to see
> the recommendations. In addition, if you get a parallel execution plan you
> can test running on a single processor using the MAXDOP query hint.
>
> Here is an article with some interesting points:
> http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/28/477.aspx
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
 >> Stay informed about: Query running slow in SQL 2005 after migrating from SQL 2000 

Can you please share how you resolved the below issue you posted on Nov.24,2008. I am exactly having same problem but this is from sql 2000 to sql 2008.
-------------------------
Fra posted on Monday, November 24, 2008 3:05 PM

We migrated a large database (more than 50GB) from SQL 2000 to SQL 2005 using
backup/restore method. After restore, we tested a query that takes 20-30
seconds in SQL 2000 whereas in SQL 2005 it takes forever. I have run DBCC
DBREINDEX, DBCC UPDATEUSAGE (0) & sp_updatestats on the database on SQL 2005
but still no improvement. On checking the Execution Plan, it is using
Clustered Index Seek on SQL 2000 whereas it is using Clustered Index Scan on
SQL 2005. It is SQL 2005 64-bit server with 2 quad CPU and 16GB memory. Any
suggestion would be appreciated.




Fra wrote:

Query running slow in SQL 2005 after migrating from SQL 2000
24-Nov-08

We migrated a large database (more than 50GB) from SQL 2000 to SQL 2005 using
backup/restore method. After restore, we tested a query that takes 20-30
seconds in SQL 2000 whereas in SQL 2005 it takes forever. I have run DBCC
DBREINDEX, DBCC UPDATEUSAGE (0) & sp_updatestats on the database on SQL 2005
but still no improvement. On checking the Execution Plan, it is using
Clustered Index Seek on SQL 2000 whereas it is using Clustered Index Scan on
SQL 2005. It is SQL 2005 64-bit server with 2 quad CPU and 16GB memory. Any
suggestion would be appreciated.

Previous Posts In This Thread:

On Monday, November 24, 2008 3:05 PM
Fra wrote:

Query running slow in SQL 2005 after migrating from SQL 2000
We migrated a large database (more than 50GB) from SQL 2000 to SQL 2005 using
backup/restore method. After restore, we tested a query that takes 20-30
seconds in SQL 2000 whereas in SQL 2005 it takes forever. I have run DBCC
DBREINDEX, DBCC UPDATEUSAGE (0) & sp_updatestats on the database on SQL 2005
but still no improvement. On checking the Execution Plan, it is using
Clustered Index Seek on SQL 2000 whereas it is using Clustered Index Scan on
SQL 2005. It is SQL 2005 64-bit server with 2 quad CPU and 16GB memory. Any
suggestion would be appreciated.

On Monday, November 24, 2008 5:47 PM
Plamen Ratchev wrote:

If the query still runs slows after updating statistics, and there is no
If the query still runs slows after updating statistics, and there is no
chance you have a bad plan because of parameter sniffing, then you may
want to look at changing the query.

You can post the query here and someone may have suggestions on how to
improve.

Also, you can run the the query through the Database Tuning Advisor to
see the recommendations. In addition, if you get a parallel execution
plan you can test running on a single processor using the MAXDOP query
hint.

Here is an article with some interesting points:
http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/28/477.aspx

--
Plamen Ratchev
http://www.SQLStudio.com

On Monday, November 24, 2008 6:03 PM
Jonathan Kehayias wrote:

The other thing you can do is create a plan guide.
The other thing you can do is create a plan guide. I am not an expert on
this, and unfortunately I missed Kalen's session on it at PASS, but she
would be best to explain it. You can also look at:

http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx

--
Jonathan Kehayias
SQL Server MVP
http://jmkehayias.blogspot.com
http://www.sqlclr.net


"Plamen Ratchev" wrote in message



Submitted via EggHeadCafe - Software Developer Portal of Choice
Adding WCF Service References
http://www.eggheadcafe.com/tutorials/aspnet/a1647f10-9aa4-4b0c-bbd9-df...a9fab8e
 >> Stay informed about: Query running slow in SQL 2005 after migrating from SQL 2000 

gan
Yep, it could happen, perhaps you need to re-write the query. Can you show
us the actual query along with an execution plan?



<gan sub> wrote in message ...
> Can you please share how you resolved the below issue you posted on
> Nov.24,2008. I am exactly having same problem but this is from sql 2000 to
> sql 2008.
> -------------------------
> Fra posted on Monday, November 24, 2008 3:05 PM
>
> We migrated a large database (more than 50GB) from SQL 2000 to SQL 2005
> using
> backup/restore method. After restore, we tested a query that takes 20-30
> seconds in SQL 2000 whereas in SQL 2005 it takes forever. I have run DBCC
> DBREINDEX, DBCC UPDATEUSAGE (0) & sp_updatestats on the database on SQL
> 2005
> but still no improvement. On checking the Execution Plan, it is using
> Clustered Index Seek on SQL 2000 whereas it is using Clustered Index Scan
> on
> SQL 2005. It is SQL 2005 64-bit server with 2 quad CPU and 16GB memory.
> Any
> suggestion would be appreciated.
>
>
>
>
> Fra wrote:
>
> Query running slow in SQL 2005 after migrating from SQL 2000
> 24-Nov-08
>
> We migrated a large database (more than 50GB) from SQL 2000 to SQL 2005
> using
> backup/restore method. After restore, we tested a query that takes 20-30
> seconds in SQL 2000 whereas in SQL 2005 it takes forever. I have run DBCC
> DBREINDEX, DBCC UPDATEUSAGE (0) & sp_updatestats on the database on SQL
> 2005
> but still no improvement. On checking the Execution Plan, it is using
> Clustered Index Seek on SQL 2000 whereas it is using Clustered Index Scan
> on
> SQL 2005. It is SQL 2005 64-bit server with 2 quad CPU and 16GB memory.
> Any
> suggestion would be appreciated.
>
> Previous Posts In This Thread:
>
> On Monday, November 24, 2008 3:05 PM
> Fra wrote:
>
> Query running slow in SQL 2005 after migrating from SQL 2000
> We migrated a large database (more than 50GB) from SQL 2000 to SQL 2005
> using
> backup/restore method. After restore, we tested a query that takes 20-30
> seconds in SQL 2000 whereas in SQL 2005 it takes forever. I have run DBCC
> DBREINDEX, DBCC UPDATEUSAGE (0) & sp_updatestats on the database on SQL
> 2005
> but still no improvement. On checking the Execution Plan, it is using
> Clustered Index Seek on SQL 2000 whereas it is using Clustered Index Scan
> on
> SQL 2005. It is SQL 2005 64-bit server with 2 quad CPU and 16GB memory.
> Any
> suggestion would be appreciated.
>
> On Monday, November 24, 2008 5:47 PM
> Plamen Ratchev wrote:
>
> If the query still runs slows after updating statistics, and there is no
> If the query still runs slows after updating statistics, and there is no
> chance you have a bad plan because of parameter sniffing, then you may
> want to look at changing the query.
>
> You can post the query here and someone may have suggestions on how to
> improve.
>
> Also, you can run the the query through the Database Tuning Advisor to
> see the recommendations. In addition, if you get a parallel execution
> plan you can test running on a single processor using the MAXDOP query
> hint.
>
> Here is an article with some interesting points:
> http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/28/477.aspx
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
>
> On Monday, November 24, 2008 6:03 PM
> Jonathan Kehayias wrote:
>
> The other thing you can do is create a plan guide.
> The other thing you can do is create a plan guide. I am not an expert on
> this, and unfortunately I missed Kalen's session on it at PASS, but she
> would be best to explain it. You can also look at:
>
> http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx
>
> --
> Jonathan Kehayias
> SQL Server MVP
> http://jmkehayias.blogspot.com
> http://www.sqlclr.net
>
>
> "Plamen Ratchev" wrote in message
>
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> Adding WCF Service References
> http://www.eggheadcafe.com/tutorials/aspnet/a1647f10-9aa4-4b0c-bbd9-df...a9fab8e
 >> Stay informed about: Query running slow in SQL 2005 after migrating from SQL 2000 

I had the same problem and after a long research i didn't find a solution.
I did reindex, update statistics with full scan, but nothing. My issue was related to Ntext fields, and that made me search in the wrong place my answer that i need.

As a last resort i plan to drop all indexes and all statistics.

I start with the statistics, and what do you think? it was sufficient . Why you'll ask?
Well....
While Restoring the DB on SQL2005 i had Statistics ON, and SQL made statistics for every column on almost every table, and while running some queries after restore, it used those bad stats.

SO If you had Statistics On while restoring a DB on SQL2005, after the restore ends, DROP all Statistics that are named _WA_Sys_xxxxxx . That will do.

Best regards,

> On Monday, November 24, 2008 3:05 PM Fra wrote:

> We migrated a large database (more than 50GB) from SQL 2000 to SQL 2005 using
> backup/restore method. After restore, we tested a query that takes 20-30
> seconds in SQL 2000 whereas in SQL 2005 it takes forever. I have run DBCC
> DBREINDEX, DBCC UPDATEUSAGE (0) & sp_updatestats on the database on SQL 2005
> but still no improvement. On checking the Execution Plan, it is using
> Clustered Index Seek on SQL 2000 whereas it is using Clustered Index Scan on
> SQL 2005. It is SQL 2005 64-bit server with 2 quad CPU and 16GB memory. Any
> suggestion would be appreciated.


>> On Monday, November 24, 2008 5:47 PM Plamen Ratchev wrote:

>> If the query still runs slows after updating statistics, and there is no
>> chance you have a bad plan because of parameter sniffing, then you may
>> want to look at changing the query.
>>
>> You can post the query here and someone may have suggestions on how to
>> improve.
>>
>> Also, you can run the the query through the Database Tuning Advisor to
>> see the recommendations. In addition, if you get a parallel execution
>> plan you can test running on a single processor using the MAXDOP query
>> hint.
>>
>> Here is an article with some interesting points:
>> http://rentacoder.com/CS/blogs/real_life_it/archive/2006/04/28/477.aspx
>>
>> --
>> Plamen Ratchev
>> http://www.SQLStudio.com


>>> On Monday, November 24, 2008 6:03 PM Jonathan Kehayias wrote:

>>> The other thing you can do is create a plan guide. I am not an expert on
>>> this, and unfortunately I missed Kalen's session on it at PASS, but she
>>> would be best to explain it. You can also look at:
>>>
>>> http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx
>>>
>>> --
>>> Jonathan Kehayias
>>> SQL Server MVP
>>> http://jmkehayias.blogspot.com
>>> http://www.sqlclr.net
>>>
>>>
>>> "Plamen Ratchev" wrote in message
>>>


>>>> On Thursday, March 18, 2010 3:35 PM gan sub wrote:

>>>> Can you please share how you resolved the below issue you posted on Nov.24,2008. I am exactly having same problem but this is from sql 2000 to sql 2008.
>>>>
>>>> -------------------------
>>>>
>>>> Fra posted on Monday, November 24, 2008 3:05 PM
>>>>
>>>>
>>>>
>>>> We migrated a large database (more than 50GB) from SQL 2000 to SQL 2005 using
>>>>
>>>> backup/restore method. After restore, we tested a query that takes 20-30
>>>>
>>>> seconds in SQL 2000 whereas in SQL 2005 it takes forever. I have run DBCC
>>>>
>>>> DBREINDEX, DBCC UPDATEUSAGE (0) & sp_updatestats on the database on SQL 2005
>>>>
>>>> but still no improvement. On checking the Execution Plan, it is using
>>>>
>>>> Clustered Index Seek on SQL 2000 whereas it is using Clustered Index Scan on
>>>>
>>>> SQL 2005. It is SQL 2005 64-bit server with 2 quad CPU and 16GB memory. Any
>>>>
>>>> suggestion would be appreciated.


>>>>> On Sunday, March 21, 2010 5:35 AM Uri Dimant wrote:

>>>>> gan
>>>>> Yep, it could happen, perhaps you need to re-write the query. Can you show
>>>>> us the actual query along with an execution plan?


>>>>> Submitted via EggHeadCafe
>>>>> Entity Framework Code-First Library CTP 5 Quick Facts
>>>>> http://www.eggheadcafe.com/tutorials/aspnet/1be19af6-7384-4eca-9076-d1...d0638cc
 >> Stay informed about: Query running slow in SQL 2005 after migrating from SQL 2000