Poor Execution Plan in SQL Server 2005

Hi,
We have a database in SQL Server 2000. I made a copy of this
database(mdf,ndf,ldf) and attached it to SQL Server 2005 on the same
machine. When I try one particular SP in both of them, SQL Server 2000
performs almost 2.5 times faster than 2005. The execution plan in 2005
contains HASH physical operators while 2000 has much better plan. I'm
wondered that why this can happen while all of indexes are exactly the same
in both databases.
A few days ago, I heard from a colleague that he had experienced bad
performance after upgrading the database of a customer to SQL Server 2005.
They were forced to move the database to 2000 again! I didn't believe it but
now I'm experiencing a real one!
Any help would be greatly appreciated.
Leila
Hi Leila
Are you aware that upgraded databases have all of their index statistics
disabled & that you have to either recompute statistics or rebuild the
indexes before SQL Server 2005 will use any indexes? You didn't mention
this, so perhaps youre problem is this simple?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Leila" <Leilas@.hotpop.com> wrote in message
news:O12cNSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a database in SQL Server 2000. I made a copy of this
> database(mdf,ndf,ldf) and attached it to SQL Server 2005 on the same
> machine. When I try one particular SP in both of them, SQL Server 2000
> performs almost 2.5 times faster than 2005. The execution plan in 2005
> contains HASH physical operators while 2000 has much better plan. I'm
> wondered that why this can happen while all of indexes are exactly the
> same in both databases.
> A few days ago, I heard from a colleague that he had experienced bad
> performance after upgrading the database of a customer to SQL Server 2005.
> They were forced to move the database to 2000 again! I didn't believe it
> but now I'm experiencing a real one!
> Any help would be greatly appreciated.
> Leila
>
|||Thanks Greg,
Do you mean if I perform:
UPDATE STATISTICS MyTable WITH FULLSCAN
on every table, will correct the problem? Or other statements must be used
for indexes?
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:epPHSOM6GHA.3508@.TK2MSFTNGP06.phx.gbl...
> Hi Leila
> Are you aware that upgraded databases have all of their index statistics
> disabled & that you have to either recompute statistics or rebuild the
> indexes before SQL Server 2005 will use any indexes? You didn't mention
> this, so perhaps youre problem is this simple?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O12cNSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
>
|||That should work ok assuming the DB isn't too big & you're not trying to
squeeze the task into a small window.
Another option is to rebuild the indexes, which might achieve some
defragmentation as well, but I'd try the first option you suggested first.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Leila" <Leilas@.hotpop.com> wrote in message
news:eS%23qO0O6GHA.3952@.TK2MSFTNGP04.phx.gbl...
> Thanks Greg,
> Do you mean if I perform:
> UPDATE STATISTICS MyTable WITH FULLSCAN
> on every table, will correct the problem? Or other statements must be used
> for indexes?
>
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:epPHSOM6GHA.3508@.TK2MSFTNGP06.phx.gbl...
>
|||Actually once I created a new database in SQL Server 2005 and imported
tables from 2000. Then generated index scripts from 2000 and applied to
2005. This way I think there must not be need to updating statistics. The
performance improved a little but still the execution plan in 2005 is poor
and about 2 or 1.5 times slower than 2000!
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%2304XchP6GHA.4064@.TK2MSFTNGP03.phx.gbl...
> That should work ok assuming the DB isn't too big & you're not trying to
> squeeze the task into a small window.
> Another option is to rebuild the indexes, which might achieve some
> defragmentation as well, but I'd try the first option you suggested first.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eS%23qO0O6GHA.3952@.TK2MSFTNGP04.phx.gbl...
>
|||Actually once I created a new database in SQL Server 2005 and imported
tables from 2000. Then generated index scripts from 2000 and applied to
2005. This way I think there must not be need to updating statistics. The
performance improved a little but still the execution plan in 2005 is poor
and about 2 or 1.5 times slower than 2000!
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%2304XchP6GHA.4064@.TK2MSFTNGP03.phx.gbl...
> That should work ok assuming the DB isn't too big & you're not trying to
> squeeze the task into a small window.
> Another option is to rebuild the indexes, which might achieve some
> defragmentation as well, but I'd try the first option you suggested first.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eS%23qO0O6GHA.3952@.TK2MSFTNGP04.phx.gbl...
>
|||Leila wrote:
> Actually once I created a new database in SQL Server 2005 and imported
> tables from 2000. Then generated index scripts from 2000 and applied to
> 2005. This way I think there must not be need to updating statistics. The
> performance improved a little but still the execution plan in 2005 is poor
> and about 2 or 1.5 times slower than 2000!
>
So, you're not TRULY comparing the performance of identical databases
then. I would suggest comparing the 2005 execution plan for this query
to that from 2000. What's different? Could you possibly have missed an
index in your migration? It might be worth migrating the database
properly, by doing a backup of the 2000 database, restoring it to 2005,
and then updating the statistics.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks Tracy! I will try this one too...
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45264E4B.9080207@.realsqlguy.com...
> Leila wrote:
> So, you're not TRULY comparing the performance of identical databases
> then. I would suggest comparing the 2005 execution plan for this query to
> that from 2000. What's different? Could you possibly have missed an
> index in your migration? It might be worth migrating the database
> properly, by doing a backup of the 2000 database, restoring it to 2005,
> and then updating the statistics.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Can you please post the SET STATISTICS PROFILE ON output for the query from
SQL Server 2000 and SQL Server 2005? Ensure that you have the same data,
indexes and machine configuration for comparison purpose. And to ensure that
stats are updated, please run UPDATE STATISTICS WITH FULLSCAN on the tables
involved in the query only. See Books Online for more details on UPDATE
STATISTICS syntax. The statistics profile output will help narrow down the
problem and maybe rewrite the query differently in SQL Server 2005 to get
better plan.
As a side-note, after upgrade from previous version we do consider
statistics to be out of date. But we will consider statistics that can be
automatically updated and refresh them upon first use. So you don't really
need to do anything special unless you hit some cases where you need higher
sampling rate for example or you have auto-update disabled for statistics
and so on. There are also other benefits for indexes on string columns
because we now maintain summary information so refreshing statistics will
help in this case. Please see the "Index Statistics" topic in Books Online
for more details.
Umachandar Jayachandran
Microsoft SQL Server Performance Team
SQL Server Engine Team Tips Blog at
http://blogs.msdn.com/sqltips/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Leila wrote on Fri, 6 Oct 2006 12:33:54 +0330:
L> Actually once I created a new database in SQL Server 2005 and
L> imported tables from 2000. Then generated index scripts from 2000
L> and applied to 2005. This way I think there must not be need to
L> updating statistics. The performance improved a little but still the
L> execution plan in 2005 is poor and about 2 or 1.5 times slower than
L> 2000!
L> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
L> news:%2304XchP6GHA.4064@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
|||Thanks indeed!
I can connect to 2000 and 2005 via SSMS and prepare an XML Plan. Do you
think it can help more if be posted here?
"Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com> wrote in
message news:OCdvVvZ6GHA.3760@.TK2MSFTNGP02.phx.gbl...
> Can you please post the SET STATISTICS PROFILE ON output for the query
> from SQL Server 2000 and SQL Server 2005? Ensure that you have the same
> data, indexes and machine configuration for comparison purpose. And to
> ensure that stats are updated, please run UPDATE STATISTICS WITH FULLSCAN
> on the tables involved in the query only. See Books Online for more
> details on UPDATE STATISTICS syntax. The statistics profile output will
> help narrow down the problem and maybe rewrite the query differently in
> SQL Server 2005 to get better plan.
> As a side-note, after upgrade from previous version we do consider
> statistics to be out of date. But we will consider statistics that can be
> automatically updated and refresh them upon first use. So you don't really
> need to do anything special unless you hit some cases where you need
> higher sampling rate for example or you have auto-update disabled for
> statistics and so on. There are also other benefits for indexes on string
> columns because we now maintain summary information so refreshing
> statistics will help in this case. Please see the "Index Statistics" topic
> in Books Online for more details.
> --
> Umachandar Jayachandran
> Microsoft SQL Server Performance Team
> SQL Server Engine Team Tips Blog at
> http://blogs.msdn.com/sqltips/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Leila wrote on Fri, 6 Oct 2006 12:33:54 +0330:
> L> Actually once I created a new database in SQL Server 2005 and
> L> imported tables from 2000. Then generated index scripts from 2000
> L> and applied to 2005. This way I think there must not be need to
> L> updating statistics. The performance improved a little but still the
> L> execution plan in 2005 is poor and about 2 or 1.5 times slower than
> L> 2000!
>
>
> L> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> L> news:%2304XchP6GHA.4064@.TK2MSFTNGP03.phx.gbl...
>
>
>
>
>
>
>
>
>
>