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.
LeilaHi 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...
>> 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
>|||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...
>> 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
>>
>|||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...
>> 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...
>> 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
>>
>>
>|||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...
>> 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...
>> 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
>>
>>
>|||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:
>> 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|||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...
>> 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...
>> 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 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...
> >> 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...
> >> 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
>
>
>
>
>|||You can only save graphical execution plans from SSMS when connected to SQL
Server 2005. So this will not help for SQL Server 2000. It is best to run
both queries using SET STATISTICS PROFILE ON, capture the output and post it
here.
--
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 Sat, 7 Oct 2006 11:11:33 +0330:
L> Thanks indeed!
L> I can connect to 2000 and 2005 via SSMS and prepare an XML Plan. Do
L> you think it can help more if be posted here?
L> "Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com>
L> 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
L>> the execution plan in 2005 is poor and about 2 or 1.5 times slower
L>> than 2000!
L>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
L>> 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...
>> 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...
>>> 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|||oops! I didn't know that! I'll post the result of STATISTICS PROFILE soon!
"Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com> wrote in
message news:eA6IW016GHA.1560@.TK2MSFTNGP04.phx.gbl...
> You can only save graphical execution plans from SSMS when connected to
> SQL Server 2005. So this will not help for SQL Server 2000. It is best to
> run both queries using SET STATISTICS PROFILE ON, capture the output and
> post it here.
> --
> 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 Sat, 7 Oct 2006 11:11:33 +0330:
> L> Thanks indeed!
> L> I can connect to 2000 and 2005 via SSMS and prepare an XML Plan. Do
> L> you think it can help more if be posted here?
>
> L> "Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com>
> L> 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
> L>> the execution plan in 2005 is poor and about 2 or 1.5 times slower
> L>> than 2000!
>
>
> L>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> L>> 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...
> >> 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...
> >>> 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
>
>
>
>
>
>|||Your plans are likely different due to cross-table statistical correlation
in your data columns. These are unfortunately very hard to model well, and
they can be tricky in terms of plan selection.
While that's not great news, I can probably help you improve the overall
performance of your application if you are able to add some indexes. You
need to check with any administrators you have, but SQL Server 2005 has a
feature for indexes called "included columns". This lets you add non-key
columns into an index to make it cover the set of columns your query needs.
Your statisics profile contains a couple of these kinds of patterns:
|--Merge Join(Concatenation)
9 13 12 Merge Join
Concatenation
1 1 | | | | | | |
|--Index Seek(OBJECT:([Kermanshah].[dbo].[TDepUser].[IX_TDepUser_1]),
SEEK:([TDepUser].
0 1 | | | | | | |
|--Index
Scan(OBJECT:([Kermanshah].[dbo].[TDabirDomain].[IX_TDabirDomain_DeptUser]),
What's happening is that you are seeking for a particular value in an index.
However, that index does not "cover" the complete set of columns you need (*
in this case, and I'm sure you can get advice from others here about whether
you need this or not ;).
So, this query seeks on a nice btree, gets a value, and then tries to do a
complete scan of another index to find the other columns you need. (the
scan also pushes a predicate to be evaluated on each row before returning
into the QP, which is why the count is so low). The CPU overhead of this is
still pretty high.
One simple way to improve the performance of this query is to just make sure
you have an index that covers the set of columns you need. You can do this
in SQL 2000 by adding extra "unnecessary" columns into the index key. For
SQL 2005, you can just add them into the INCLUDE (column list) clause in
CREATE INDEX.
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,...n ] )
[ INCLUDE ( column_name [ ,...n ] ) ]
[ WITH ( <relational_index_option> [ ,...n ] ) ]
[ ON { partition_scheme_name ( column_name )
| filegroup_name
| default
}
]
[ ; ]
Once you have done this, you should be able to re-compile the query and see
a plan that has only a seek on a single index.
Your particular query may have multiple such cases. Each of them you fix
should generally improve performance for this query.
It's worth noting that this will make updates slightly more expensive on
this table. However, given that you have this class of issue in both
profiles, I'll go out on a limb and say that fixing this will give you a net
positive performance improvement.
I hope that helps. I have a basic section on this in my blog. I'll try to
get some time to talk more about included columns.
http://blogs.msdn.com/queryoptteam/archive/2006/04/06/570176.aspx
Thanks,
Conor Cunningham
SQL Server Query Processor Development Team
"Leila" <Leilas@.hotpop.com> wrote in message
news:O83cLyR7GHA.1248@.TK2MSFTNGP03.phx.gbl...
> Ok! I attached a zip file to this message. It contains:
> 1) Result of STATISTICS PROFILE for query execution in SQL Server 2000
> (Plain Text Format)
> 2) Result of STATISTICS PROFILE for query execution in SQL Server 2000
> (CSV Format)
> 3) Result of STATISTICS PROFILE for query execution in SQL Server 2005
> (Plain Text)
> 4) Result of STATISTICS PROFILE for query execution in SQL Server 2005
> (CSV Format)
> 5) XML Plan for query execution in SQL Server 2005
> 6) The main query which I execute (It uses a view that is not included in
> this file)
> One important thing in comparison between these two plans appears in first
> 10 lines of CSV files: SQL Server 2000 performs some joins using Merge
> algorithm while SQL Server 2005 uses Hash join. I have written a script
> that declares a cursor from indexes of the database and I do ALTER
> INDEX...REBUILD. Even I wrote a similar script to update all statistics
> with FULLSCAN. This query takes about 11 seconds in SQL Server 2000 (with
> cache) and 20 seconds in 2005 (with cache).
> I really appreciate any help! This problem has prevented us to migrate to
> SQL Server 2005.
> Thanks in advance!
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23xczWX76GHA.1252@.TK2MSFTNGP04.phx.gbl...
>> oops! I didn't know that! I'll post the result of STATISTICS PROFILE
>> soon!
>>
>> "Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com> wrote
>> in
>> message news:eA6IW016GHA.1560@.TK2MSFTNGP04.phx.gbl...
>> You can only save graphical execution plans from SSMS when connected to
>> SQL Server 2005. So this will not help for SQL Server 2000. It is best
>> to
>> run both queries using SET STATISTICS PROFILE ON, capture the output and
>> post it here.
>> --
>> 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 Sat, 7 Oct 2006 11:11:33 +0330:
>> L> Thanks indeed!
>> L> I can connect to 2000 and 2005 via SSMS and prepare an XML Plan. Do
>> L> you think it can help more if be posted here?
>>
>> L> "Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com>
>> L> 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
>> L>> the execution plan in 2005 is poor and about 2 or 1.5 times slower
>> L>> than 2000!
>>
>>
>> L>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> L>> 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...
>> >> 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...
>> >>> 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 Conor,
I like this weblog and I had read your post. Previously I created some new
indexes in SQL Server 2005 for this query and its time became 18 seconds (it
was 20). Then I run Tuning Advisor that created one indexed view with few
more indexes. But it could not be faster than 15-16 seconds while SQL Server
2000 runs in 11 seconds without all these new indexes! It's really strange
that new version of this wonderful software is not performing as well as
previous version. I admit that SQL Server 2005 has great advantages but our
customers have large databases and the query performance is the highest
priority for us. We cannot recommend them to upgrade while we are not
certain about its result. I spent hours to optimize performance of this
query by indexes, although it didn't become as good as 2000, it's odd to
spend a lot of time for each query when upgrading because our database has
many queries like that.
As a developer I like to migrate to SQL Server 2005 but I understand what my
boss feels and why he is worried!
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
message news:ukevToW7GHA.3396@.TK2MSFTNGP04.phx.gbl...
> Your plans are likely different due to cross-table statistical correlation
> in your data columns. These are unfortunately very hard to model well,
> and they can be tricky in terms of plan selection.
> While that's not great news, I can probably help you improve the overall
> performance of your application if you are able to add some indexes. You
> need to check with any administrators you have, but SQL Server 2005 has a
> feature for indexes called "included columns". This lets you add non-key
> columns into an index to make it cover the set of columns your query
> needs.
> Your statisics profile contains a couple of these kinds of patterns:
> |--Merge Join(Concatenation) 9 13 12 Merge
> Join Concatenation
> 1 1 | | | | | | |
> |--Index Seek(OBJECT:([Kermanshah].[dbo].[TDepUser].[IX_TDepUser_1]),
> SEEK:([TDepUser].
> 0 1 | | | | | | |
> |--Index
> Scan(OBJECT:([Kermanshah].[dbo].[TDabirDomain].[IX_TDabirDomain_DeptUser]),
> What's happening is that you are seeking for a particular value in an
> index. However, that index does not "cover" the complete set of columns
> you need (* in this case, and I'm sure you can get advice from others here
> about whether you need this or not ;).
> So, this query seeks on a nice btree, gets a value, and then tries to do a
> complete scan of another index to find the other columns you need. (the
> scan also pushes a predicate to be evaluated on each row before returning
> into the QP, which is why the count is so low). The CPU overhead of this
> is still pretty high.
> One simple way to improve the performance of this query is to just make
> sure you have an index that covers the set of columns you need. You can
> do this in SQL 2000 by adding extra "unnecessary" columns into the index
> key. For SQL 2005, you can just add them into the INCLUDE (column list)
> clause in CREATE INDEX.
> CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
> ON <object> ( column [ ASC | DESC ] [ ,...n ] )
> [ INCLUDE ( column_name [ ,...n ] ) ]
> [ WITH ( <relational_index_option> [ ,...n ] ) ]
> [ ON { partition_scheme_name ( column_name )
> | filegroup_name
> | default
> }
> ]
> [ ; ]
>
> Once you have done this, you should be able to re-compile the query and
> see a plan that has only a seek on a single index.
> Your particular query may have multiple such cases. Each of them you fix
> should generally improve performance for this query.
> It's worth noting that this will make updates slightly more expensive on
> this table. However, given that you have this class of issue in both
> profiles, I'll go out on a limb and say that fixing this will give you a
> net positive performance improvement.
> I hope that helps. I have a basic section on this in my blog. I'll try
> to get some time to talk more about included columns.
> http://blogs.msdn.com/queryoptteam/archive/2006/04/06/570176.aspx
> Thanks,
> Conor Cunningham
> SQL Server Query Processor Development Team
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O83cLyR7GHA.1248@.TK2MSFTNGP03.phx.gbl...
>> Ok! I attached a zip file to this message. It contains:
>> 1) Result of STATISTICS PROFILE for query execution in SQL Server 2000
>> (Plain Text Format)
>> 2) Result of STATISTICS PROFILE for query execution in SQL Server 2000
>> (CSV Format)
>> 3) Result of STATISTICS PROFILE for query execution in SQL Server 2005
>> (Plain Text)
>> 4) Result of STATISTICS PROFILE for query execution in SQL Server 2005
>> (CSV Format)
>> 5) XML Plan for query execution in SQL Server 2005
>> 6) The main query which I execute (It uses a view that is not included in
>> this file)
>> One important thing in comparison between these two plans appears in
>> first 10 lines of CSV files: SQL Server 2000 performs some joins using
>> Merge algorithm while SQL Server 2005 uses Hash join. I have written a
>> script that declares a cursor from indexes of the database and I do ALTER
>> INDEX...REBUILD. Even I wrote a similar script to update all statistics
>> with FULLSCAN. This query takes about 11 seconds in SQL Server 2000 (with
>> cache) and 20 seconds in 2005 (with cache).
>> I really appreciate any help! This problem has prevented us to migrate to
>> SQL Server 2005.
>> Thanks in advance!
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%23xczWX76GHA.1252@.TK2MSFTNGP04.phx.gbl...
>> oops! I didn't know that! I'll post the result of STATISTICS PROFILE
>> soon!
>>
>> "Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com>
>> wrote in
>> message news:eA6IW016GHA.1560@.TK2MSFTNGP04.phx.gbl...
>> You can only save graphical execution plans from SSMS when connected to
>> SQL Server 2005. So this will not help for SQL Server 2000. It is best
>> to
>> run both queries using SET STATISTICS PROFILE ON, capture the output
>> and
>> post it here.
>> --
>> 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 Sat, 7 Oct 2006 11:11:33 +0330:
>> L> Thanks indeed!
>> L> I can connect to 2000 and 2005 via SSMS and prepare an XML Plan. Do
>> L> you think it can help more if be posted here?
>>
>> L> "Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com>
>> L> 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
>> L>> the execution plan in 2005 is poor and about 2 or 1.5 times
>> slower
>> L>> than 2000!
>>
>>
>> L>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> L>> 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...
>> >> 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...
>> >>> 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
>>
>>
>>
>>
>>
>>
>>
>>
>|||My only other "simple" advice would be to try to construct the sql 2000 plan
using hints in sql 2005.
So, take the plan from sql 2000's stats profile output you have, then try to
figure out how to build something that has index and join algorithm/order
hints.
select ... from table1 with (index=whateversql2000 picked) inner hash join
...
or whatever is appropriate for each part of the plan. This is obviously
unpleasant since you have views, but there are occasionally cases where
upgrades can be difficult - usually these are these complex statistical
cases I mentioned in my previous post.
If the customer has paid for SQL 2005 and you can't make progress after
trying what I've suggested, then please call Microsoft CSS and get a case
opened. They may be able to find something to help you or it will be
escalated.
Indexed views would be another possible "solution", but I'd call CSS if you
can't get the plan choice to be acceptable after doing these tricks.
It's also possible that I've just missed something (remember, I'm debugging
blind here ;).
I hope things work out.
Sincerely,
Conor
"Leila" <Leilas@.hotpop.com> wrote in message
news:eueLemi7GHA.140@.TK2MSFTNGP05.phx.gbl...
> Thanks Conor,
> I like this weblog and I had read your post. Previously I created some new
> indexes in SQL Server 2005 for this query and its time became 18 seconds
> (it was 20). Then I run Tuning Advisor that created one indexed view with
> few more indexes. But it could not be faster than 15-16 seconds while SQL
> Server 2000 runs in 11 seconds without all these new indexes! It's really
> strange that new version of this wonderful software is not performing as
> well as previous version. I admit that SQL Server 2005 has great
> advantages but our customers have large databases and the query
> performance is the highest priority for us. We cannot recommend them to
> upgrade while we are not certain about its result. I spent hours to
> optimize performance of this query by indexes, although it didn't become
> as good as 2000, it's odd to spend a lot of time for each query when
> upgrading because our database has many queries like that.
> As a developer I like to migrate to SQL Server 2005 but I understand what
> my boss feels and why he is worried!
>
>
>
>
> "Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
> message news:ukevToW7GHA.3396@.TK2MSFTNGP04.phx.gbl...
>> Your plans are likely different due to cross-table statistical
>> correlation in your data columns. These are unfortunately very hard to
>> model well, and they can be tricky in terms of plan selection.
>> While that's not great news, I can probably help you improve the overall
>> performance of your application if you are able to add some indexes. You
>> need to check with any administrators you have, but SQL Server 2005 has a
>> feature for indexes called "included columns". This lets you add non-key
>> columns into an index to make it cover the set of columns your query
>> needs.
>> Your statisics profile contains a couple of these kinds of patterns:
>> |--Merge Join(Concatenation) 9 13 12 Merge
>> Join Concatenation
>> 1 1 | | | | | | |
>> |--Index Seek(OBJECT:([Kermanshah].[dbo].[TDepUser].[IX_TDepUser_1]),
>> SEEK:([TDepUser].
>> 0 1 | | | | | | |
>> |--Index
>> Scan(OBJECT:([Kermanshah].[dbo].[TDabirDomain].[IX_TDabirDomain_DeptUser]),
>> What's happening is that you are seeking for a particular value in an
>> index. However, that index does not "cover" the complete set of columns
>> you need (* in this case, and I'm sure you can get advice from others
>> here about whether you need this or not ;).
>> So, this query seeks on a nice btree, gets a value, and then tries to do
>> a complete scan of another index to find the other columns you need.
>> (the scan also pushes a predicate to be evaluated on each row before
>> returning into the QP, which is why the count is so low). The CPU
>> overhead of this is still pretty high.
>> One simple way to improve the performance of this query is to just make
>> sure you have an index that covers the set of columns you need. You can
>> do this in SQL 2000 by adding extra "unnecessary" columns into the index
>> key. For SQL 2005, you can just add them into the INCLUDE (column list)
>> clause in CREATE INDEX.
>> CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
>> ON <object> ( column [ ASC | DESC ] [ ,...n ] )
>> [ INCLUDE ( column_name [ ,...n ] ) ]
>> [ WITH ( <relational_index_option> [ ,...n ] ) ]
>> [ ON { partition_scheme_name ( column_name )
>> | filegroup_name
>> | default
>> }
>> ]
>> [ ; ]
>>
>> Once you have done this, you should be able to re-compile the query and
>> see a plan that has only a seek on a single index.
>> Your particular query may have multiple such cases. Each of them you fix
>> should generally improve performance for this query.
>> It's worth noting that this will make updates slightly more expensive on
>> this table. However, given that you have this class of issue in both
>> profiles, I'll go out on a limb and say that fixing this will give you a
>> net positive performance improvement.
>> I hope that helps. I have a basic section on this in my blog. I'll try
>> to get some time to talk more about included columns.
>> http://blogs.msdn.com/queryoptteam/archive/2006/04/06/570176.aspx
>> Thanks,
>> Conor Cunningham
>> SQL Server Query Processor Development Team
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:O83cLyR7GHA.1248@.TK2MSFTNGP03.phx.gbl...
>> Ok! I attached a zip file to this message. It contains:
>> 1) Result of STATISTICS PROFILE for query execution in SQL Server 2000
>> (Plain Text Format)
>> 2) Result of STATISTICS PROFILE for query execution in SQL Server 2000
>> (CSV Format)
>> 3) Result of STATISTICS PROFILE for query execution in SQL Server 2005
>> (Plain Text)
>> 4) Result of STATISTICS PROFILE for query execution in SQL Server 2005
>> (CSV Format)
>> 5) XML Plan for query execution in SQL Server 2005
>> 6) The main query which I execute (It uses a view that is not included
>> in this file)
>> One important thing in comparison between these two plans appears in
>> first 10 lines of CSV files: SQL Server 2000 performs some joins using
>> Merge algorithm while SQL Server 2005 uses Hash join. I have written a
>> script that declares a cursor from indexes of the database and I do
>> ALTER INDEX...REBUILD. Even I wrote a similar script to update all
>> statistics with FULLSCAN. This query takes about 11 seconds in SQL
>> Server 2000 (with cache) and 20 seconds in 2005 (with cache).
>> I really appreciate any help! This problem has prevented us to migrate
>> to SQL Server 2005.
>> Thanks in advance!
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%23xczWX76GHA.1252@.TK2MSFTNGP04.phx.gbl...
>> oops! I didn't know that! I'll post the result of STATISTICS PROFILE
>> soon!
>>
>> "Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com>
>> wrote in
>> message news:eA6IW016GHA.1560@.TK2MSFTNGP04.phx.gbl...
>> You can only save graphical execution plans from SSMS when connected
>> to
>> SQL Server 2005. So this will not help for SQL Server 2000. It is best
>> to
>> run both queries using SET STATISTICS PROFILE ON, capture the output
>> and
>> post it here.
>> --
>> 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 Sat, 7 Oct 2006 11:11:33 +0330:
>> L> Thanks indeed!
>> L> I can connect to 2000 and 2005 via SSMS and prepare an XML Plan. Do
>> L> you think it can help more if be posted here?
>>
>> L> "Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com>
>> L> 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
>> L>> the execution plan in 2005 is poor and about 2 or 1.5 times
>> slower
>> L>> than 2000!
>>
>>
>> L>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> L>> 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...
>> >> 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...
>> >>> 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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>|||P@. (P@.@.discussions.microsoft.com) writes:
> We're having the same issue with some of our queries. In fact, 1 of our
> Datawarehouse loading process that usually takes around 3 hours under
> SQL 2000 took 5days under 2005! yes, 5days! (ok, we were on holiday and
> decided to let the process run till it ends) We've found that many of
> the queries containing sub-queries are 20x slower than before :(
> First thing we did after upgrading was to DBCC CHECKDB, recreated indexes,
> updates stats with fullscan, update usage...
> I would really like to avoid re-optimizing every queries for SQL 2005.
Unfortunately, the only advice I can give is to first use Profiler to
find the slow queries. Then analyse the query plans, and see what
improvements you can make. If you are lucky, there is a common theme
to all of them.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Erland,
We exactly know the slow query! We have done everything (from new indexes to
indexed views) but 2005 is much slower than 2000 :(
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98B42562ACBBYazorman@.127.0.0.1...
> P@. (P@.@.discussions.microsoft.com) writes:
>> We're having the same issue with some of our queries. In fact, 1 of our
>> Datawarehouse loading process that usually takes around 3 hours under
>> SQL 2000 took 5days under 2005! yes, 5days! (ok, we were on holiday and
>> decided to let the process run till it ends) We've found that many of
>> the queries containing sub-queries are 20x slower than before :(
>> First thing we did after upgrading was to DBCC CHECKDB, recreated
>> indexes,
>> updates stats with fullscan, update usage...
>> I would really like to avoid re-optimizing every queries for SQL 2005.
> Unfortunately, the only advice I can give is to first use Profiler to
> find the slow queries. Then analyse the query plans, and see what
> improvements you can make. If you are lucky, there is a common theme
> to all of them.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Not yet :(
"P@." <P@.@.discussions.microsoft.com> wrote in message
news:536109F1-5A16-486E-A34F-5EB74EB78F77@.microsoft.com...
> Hello Leila,
> Did you find any "friendly" solution to your performance issue?
> We're having the same issue with some of our queries. In fact, 1 of our
> Datawarehouse loading process that usually takes around 3 hours under SQL
> 2000 took 5days under 2005! yes, 5days! (ok, we were on holiday and
> decided
> to let the process run till it ends) We've found that many of the queries
> containing sub-queries are 20x slower than before :(
> First thing we did after upgrading was to DBCC CHECKDB, recreated indexes,
> updates stats with fullscan, update usage...
> I would really like to avoid re-optimizing every queries for SQL 2005.
> Any hint is appreciated!
>
> Thank|||does your database is a new one or a copy from the SQL2000 and attached to
your SQL2005 server?
does your query force the order or use any query hint? (like forcing hash
joins, maxdop etc...)
does your hardware is the same?
do you have the x64 version?
I suffer a memory issue which cause the server to "loose" its memory which
cause the server to become near unresponsive.
So a standard process can takes hours instead of minutes.
Track the memory usage and if you see a the free memory to go from 0% to
100% you have the same issue.
ALL the applications see their memory usage to fall. (not only SQL Server)
"P@." <P@.@.discussions.microsoft.com> wrote in message
news:536109F1-5A16-486E-A34F-5EB74EB78F77@.microsoft.com...
> Hello Leila,
> Did you find any "friendly" solution to your performance issue?
> We're having the same issue with some of our queries. In fact, 1 of our
> Datawarehouse loading process that usually takes around 3 hours under SQL
> 2000 took 5days under 2005! yes, 5days! (ok, we were on holiday and
> decided
> to let the process run till it ends) We've found that many of the queries
> containing sub-queries are 20x slower than before :(
> First thing we did after upgrading was to DBCC CHECKDB, recreated indexes,
> updates stats with fullscan, update usage...
> I would really like to avoid re-optimizing every queries for SQL 2005.
> Any hint is appreciated!
>
> Thank|||I have restored db in 2005 and rebuilt all indexes. Both SQL Server
instances are on the same machine. I stop one of them while testing the
other instance.
No hint is used in the query. I'm using 32bit edition on 32bit Windows!
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:u4G42pYNHHA.1276@.TK2MSFTNGP04.phx.gbl...
> does your database is a new one or a copy from the SQL2000 and attached to
> your SQL2005 server?
> does your query force the order or use any query hint? (like forcing hash
> joins, maxdop etc...)
> does your hardware is the same?
> do you have the x64 version?
> I suffer a memory issue which cause the server to "loose" its memory which
> cause the server to become near unresponsive.
> So a standard process can takes hours instead of minutes.
> Track the memory usage and if you see a the free memory to go from 0% to
> 100% you have the same issue.
> ALL the applications see their memory usage to fall. (not only SQL Server)
> "P@." <P@.@.discussions.microsoft.com> wrote in message
> news:536109F1-5A16-486E-A34F-5EB74EB78F77@.microsoft.com...
>> Hello Leila,
>> Did you find any "friendly" solution to your performance issue?
>> We're having the same issue with some of our queries. In fact, 1 of our
>> Datawarehouse loading process that usually takes around 3 hours under SQL
>> 2000 took 5days under 2005! yes, 5days! (ok, we were on holiday and
>> decided
>> to let the process run till it ends) We've found that many of the queries
>> containing sub-queries are 20x slower than before :(
>> First thing we did after upgrading was to DBCC CHECKDB, recreated
>> indexes,
>> updates stats with fullscan, update usage...
>> I would really like to avoid re-optimizing every queries for SQL 2005.
>> Any hint is appreciated!
>>
>> Thank
>|||Leila (Leilas@.hotpop.com) writes:
> We exactly know the slow query! We have done everything (from new
> indexes to indexed views) but 2005 is much slower than 2000 :(
Have you compared the query plans between SQL 2000 and SQL 2005?
I am afraid that I cannot say much more about a query that I know
nothing about.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Can you check if you get the same plan for both 2000 and 2005. Since you
already know the query.
If you find that the plans is different you can use hints or plan forcing to
try and reuse SQL 2000's plan.
Hardik
"Leila" <Leilas@.hotpop.com> wrote in message
news:e9tlD9INHHA.3312@.TK2MSFTNGP03.phx.gbl...
> Hi Erland,
> We exactly know the slow query! We have done everything (from new indexes
> to indexed views) but 2005 is much slower than 2000 :(
>
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns98B42562ACBBYazorman@.127.0.0.1...
>> P@. (P@.@.discussions.microsoft.com) writes:
>> We're having the same issue with some of our queries. In fact, 1 of our
>> Datawarehouse loading process that usually takes around 3 hours under
>> SQL 2000 took 5days under 2005! yes, 5days! (ok, we were on holiday and
>> decided to let the process run till it ends) We've found that many of
>> the queries containing sub-queries are 20x slower than before :(
>> First thing we did after upgrading was to DBCC CHECKDB, recreated
>> indexes,
>> updates stats with fullscan, update usage...
>> I would really like to avoid re-optimizing every queries for SQL 2005.
>> Unfortunately, the only advice I can give is to first use Profiler to
>> find the slow queries. Then analyse the query plans, and see what
>> improvements you can make. If you are lucky, there is a common theme
>> to all of them.
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>|||The plans are almost very different! I tried JOIN HINTs but there are much
more items that must be forced I suppose.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98B6726A2A65Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> We exactly know the slow query! We have done everything (from new
>> indexes to indexed views) but 2005 is much slower than 2000 :(
> Have you compared the query plans between SQL 2000 and SQL 2005?
> I am afraid that I cannot say much more about a query that I know
> nothing about.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Could you attach/add the query and the showplan_text for both 2000 and 2005
so that we have a better idea what is happening.
Hardik
"Leila" <Leilas@.hotpop.com> wrote in message
news:Ozbg36lNHHA.724@.TK2MSFTNGP04.phx.gbl...
> The plans are almost very different! I tried JOIN HINTs but there are much
> more items that must be forced I suppose.
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns98B6726A2A65Yazorman@.127.0.0.1...
>> Leila (Leilas@.hotpop.com) writes:
>> We exactly know the slow query! We have done everything (from new
>> indexes to indexed views) but 2005 is much slower than 2000 :(
>> Have you compared the query plans between SQL 2000 and SQL 2005?
>> I am afraid that I cannot say much more about a query that I know
>> nothing about.
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>|||Leila (Leilas@.hotpop.com) writes:
> One important thing in comparison between these two plans appears in
> first 10 lines of CSV files: SQL Server 2000 performs some joins using
> Merge algorithm while SQL Server 2005 uses Hash join. I have written a
> script that declares a cursor from indexes of the database and I do
> ALTER INDEX...REBUILD. Even I wrote a similar script to update all
> statistics with FULLSCAN. This query takes about 11 seconds in SQL
> Server 2000 (with cache) and 20 seconds in 2005 (with cache). I really
> appreciate any help! This problem has prevented us to migrate to SQL
> Server 2005.
You might have tried this already, but what happens if you rewrite the
query as:
SELECT *
FROM TLetter T
WHERE EXISTS (SELECT *
FROM VLetterPermissionNew V
WHERE V.lettRecordID = T.lettRecordID
AND V. /* ? */ dpusUserID= '00001000003Y')
AND TLetter.lettAccess <= @.UserAccess
I'm not saying that this is going to resolve the overall problem, but at
least you are eliminating the cost of the DISTINCT.
Also, would it be possible for you to change the UNION in the view to
UNION ALL?
Judging from the query plan, it appears that the permissions view is
very complex. This is not that surprising, we have some quite complex
permissions rule in our system as well. We have taken a different tack
on the problem though, and consequently we have the performance problem
elsewhere.
What we have done is to materialize the permissions in a table, so for
each account each user is permitted to see, there is a row in that
table. (With the exception for users that are permitted to see all
accounts. There is a special solution for these.) This makes it very
easy to apply permissions in queries. Just join against the permissions
table. I don't know in which context your query appears, but ours
typically appear in interactive forms, where 11 seconds in response
time would not be acceptable.
As you may guess, we have (had?) some problems with performance of
maintaining this table. There is an attempt to update the data directly
if there is a change that affects permissions, but we also have a nightly
job that makes a complete refresh. Particularly the nightly job has
sometimes been running a bit too long for our customers' taste.
I don't know if you have considered materialising your permissions. This
is not an entirely trivial task, as it reqiure a lot of changes to the
processes that upates the data. And if the updates are frequent, the
cost for maintaing the permissions table may preclude this solution. But
if you take this step, it is very likely that you will be able to reduce
execution times for queries like this to less than one second.
(I have here more or less ruled out indexed view out of hand, since
indexed views comes with so many restrictions. Onf of them is that
the view definition must not include UNION, which yours appear to do.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Thanks for great explanations Erland!
Actually this is the scenario of sending mails inside a big organization.
When a new mail is generated and sent by a user, for example all of the
recipients and their bosses in higher level of organization's hierarchy are
allowed to access the mail. I think materialization is impossible because
there are a lot of users! And the hierarchy is the big trouble! Imagine that
when a new mail is sent, hundreds of rows must be generated to indicate that
which users can access this mail. I believe that this mail system is not
using differed processing logic and everything must be immediate. Therefore
we cannot use nightly jobs.
We are satisfy with the performance of SQL Server 2000, we are not trying to
have this query in 1 second! At least we must gain 2000's performance in
2005.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98B883D53E81CYazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> One important thing in comparison between these two plans appears in
>> first 10 lines of CSV files: SQL Server 2000 performs some joins using
>> Merge algorithm while SQL Server 2005 uses Hash join. I have written a
>> script that declares a cursor from indexes of the database and I do
>> ALTER INDEX...REBUILD. Even I wrote a similar script to update all
>> statistics with FULLSCAN. This query takes about 11 seconds in SQL
>> Server 2000 (with cache) and 20 seconds in 2005 (with cache). I really
>> appreciate any help! This problem has prevented us to migrate to SQL
>> Server 2005.
> You might have tried this already, but what happens if you rewrite the
> query as:
> SELECT *
> FROM TLetter T
> WHERE EXISTS (SELECT *
> FROM VLetterPermissionNew V
> WHERE V.lettRecordID = T.lettRecordID
> AND V. /* ? */ dpusUserID= '00001000003Y')
> AND TLetter.lettAccess <= @.UserAccess
> I'm not saying that this is going to resolve the overall problem, but at
> least you are eliminating the cost of the DISTINCT.
> Also, would it be possible for you to change the UNION in the view to
> UNION ALL?
> Judging from the query plan, it appears that the permissions view is
> very complex. This is not that surprising, we have some quite complex
> permissions rule in our system as well. We have taken a different tack
> on the problem though, and consequently we have the performance problem
> elsewhere.
> What we have done is to materialize the permissions in a table, so for
> each account each user is permitted to see, there is a row in that
> table. (With the exception for users that are permitted to see all
> accounts. There is a special solution for these.) This makes it very
> easy to apply permissions in queries. Just join against the permissions
> table. I don't know in which context your query appears, but ours
> typically appear in interactive forms, where 11 seconds in response
> time would not be acceptable.
> As you may guess, we have (had?) some problems with performance of
> maintaining this table. There is an attempt to update the data directly
> if there is a change that affects permissions, but we also have a nightly
> job that makes a complete refresh. Particularly the nightly job has
> sometimes been running a bit too long for our customers' taste.
> I don't know if you have considered materialising your permissions. This
> is not an entirely trivial task, as it reqiure a lot of changes to the
> processes that upates the data. And if the updates are frequent, the
> cost for maintaing the permissions table may preclude this solution. But
> if you take this step, it is very likely that you will be able to reduce
> execution times for queries like this to less than one second.
> (I have here more or less ruled out indexed view out of hand, since
> indexed views comes with so many restrictions. Onf of them is that
> the view definition must not include UNION, which yours appear to do.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Thanks for great explanation Erland!
Actually this is the scenario of sending mails inside a big organization.
When a new mail is generated and sent by a user, for example all of the
recipients and their bosses in higher level of organization's hierarchy are
allowed to access the mail. I think materialization is impossible because
there are a lot of users! And the hierarchy is the big trouble! Imagine that
when a new mail is sent, hundreds of rows must be generated to indicate that
which users can access this mail. I believe that this mail system is not
using differed processing logic and everything must be immediate. Therefore
we cannot use nightly jobs.
We are satisfy with the performance of SQL Server 2000, we are not trying to
have this query in 1 second! At least we must gain 2000's performance in
2005.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98B883D53E81CYazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> One important thing in comparison between these two plans appears in
>> first 10 lines of CSV files: SQL Server 2000 performs some joins using
>> Merge algorithm while SQL Server 2005 uses Hash join. I have written a
>> script that declares a cursor from indexes of the database and I do
>> ALTER INDEX...REBUILD. Even I wrote a similar script to update all
>> statistics with FULLSCAN. This query takes about 11 seconds in SQL
>> Server 2000 (with cache) and 20 seconds in 2005 (with cache). I really
>> appreciate any help! This problem has prevented us to migrate to SQL
>> Server 2005.
> You might have tried this already, but what happens if you rewrite the
> query as:
> SELECT *
> FROM TLetter T
> WHERE EXISTS (SELECT *
> FROM VLetterPermissionNew V
> WHERE V.lettRecordID = T.lettRecordID
> AND V. /* ? */ dpusUserID= '00001000003Y')
> AND TLetter.lettAccess <= @.UserAccess
> I'm not saying that this is going to resolve the overall problem, but at
> least you are eliminating the cost of the DISTINCT.
> Also, would it be possible for you to change the UNION in the view to
> UNION ALL?
> Judging from the query plan, it appears that the permissions view is
> very complex. This is not that surprising, we have some quite complex
> permissions rule in our system as well. We have taken a different tack
> on the problem though, and consequently we have the performance problem
> elsewhere.
> What we have done is to materialize the permissions in a table, so for
> each account each user is permitted to see, there is a row in that
> table. (With the exception for users that are permitted to see all
> accounts. There is a special solution for these.) This makes it very
> easy to apply permissions in queries. Just join against the permissions
> table. I don't know in which context your query appears, but ours
> typically appear in interactive forms, where 11 seconds in response
> time would not be acceptable.
> As you may guess, we have (had?) some problems with performance of
> maintaining this table. There is an attempt to update the data directly
> if there is a change that affects permissions, but we also have a nightly
> job that makes a complete refresh. Particularly the nightly job has
> sometimes been running a bit too long for our customers' taste.
> I don't know if you have considered materialising your permissions. This
> is not an entirely trivial task, as it reqiure a lot of changes to the
> processes that upates the data. And if the updates are frequent, the
> cost for maintaing the permissions table may preclude this solution. But
> if you take this step, it is very likely that you will be able to reduce
> execution times for queries like this to less than one second.
> (I have here more or less ruled out indexed view out of hand, since
> indexed views comes with so many restrictions. Onf of them is that
> the view definition must not include UNION, which yours appear to do.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> Actually this is the scenario of sending mails inside a big
> organization. When a new mail is generated and sent by a user, for
> example all of the recipients and their bosses in higher level of
> organization's hierarchy are allowed to access the mail. I think
> materialization is impossible because there are a lot of users! And the
> hierarchy is the big trouble! Imagine that when a new mail is sent,
> hundreds of rows must be generated to indicate that which users can
> access this mail.
You are right, that does not sound like a bright idea. A materialized
table would have to build around more static entities. Whether that is
possible in your case, I have no idea.
Did you try the rewrite with EXISTS or consider whether UNION all was an
option?
By the way, if you have a hierachy to weed out, beware that SQL 2005
offers recursive CTE to handle these more easily.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Actually the DISTINCT and UNION are required, but I have not tried EXISTS
yet.
I was thinking of materialization using SSB! For example when a new mail is
sent, a message is sent to queue as well, therefore the user doesn't have to
wait until the materialization to complete. I have convinced my manager that
a latency about 2 or 3 minutes is acceptable so that other users who have
permission can view the new mail. What's your idea?
Another question, why EXISTS has advantage here that you suggest it?
Thanks!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98B9729462BBYazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> Actually this is the scenario of sending mails inside a big
>> organization. When a new mail is generated and sent by a user, for
>> example all of the recipients and their bosses in higher level of
>> organization's hierarchy are allowed to access the mail. I think
>> materialization is impossible because there are a lot of users! And the
>> hierarchy is the big trouble! Imagine that when a new mail is sent,
>> hundreds of rows must be generated to indicate that which users can
>> access this mail.
> You are right, that does not sound like a bright idea. A materialized
> table would have to build around more static entities. Whether that is
> possible in your case, I have no idea.
> Did you try the rewrite with EXISTS or consider whether UNION all was an
> option?
> By the way, if you have a hierachy to weed out, beware that SQL 2005
> offers recursive CTE to handle these more easily.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> Actually the DISTINCT and UNION are required, but I have not tried EXISTS
> yet.
> I was thinking of materialization using SSB! For example when a new mail
> is sent, a message is sent to queue as well, therefore the user doesn't
> have to wait until the materialization to complete. I have convinced my
> manager that a latency about 2 or 3 minutes is acceptable so that other
> users who have permission can view the new mail. What's your idea?
Hah! That was an interesting idea! Service Broker really opens new
possibilities. I would never have though about that one, but in a case
like this, it can make sense, as it solves the problem for the user: having
to wait for the prompt.
> Another question, why EXISTS has advantage here that you suggest it?
When I see a query like:
SELECT DISCINT a.*
FROM a
JOIN b ON a.col = b.col
I get the idea that someone has added DISTINCT since the join to b can
have multiple matches. But since no column from b is included in the result
set, I assume that the join really means "show all rows from a that match a
condition in b", and this is better expressed with EXISTS for two reasons:
1) it speaks in clear terms what we are doing. 2) The DISTINCT is no longer
needed.
It is possible that you need DISTINCT nevertheless, but as I recall your
query had a SELECT *, and if you need DISTINCT in that case, this means
that your letters table does not have a primary key!
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> I get the idea that someone has added DISTINCT since the join to b can
> have multiple matches. But since no column from b is included in the
> result
> set, I assume that the join really means "show all rows from a that match
> a
> condition in b", and this is better expressed with EXISTS for two reasons:
> 1) it speaks in clear terms what we are doing. 2) The DISTINCT is no
> longer
> needed.
Cool! I'll try it!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98B9A2A713BCCYazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> Actually the DISTINCT and UNION are required, but I have not tried EXISTS
>> yet.
>> I was thinking of materialization using SSB! For example when a new mail
>> is sent, a message is sent to queue as well, therefore the user doesn't
>> have to wait until the materialization to complete. I have convinced my
>> manager that a latency about 2 or 3 minutes is acceptable so that other
>> users who have permission can view the new mail. What's your idea?
> Hah! That was an interesting idea! Service Broker really opens new
> possibilities. I would never have though about that one, but in a case
> like this, it can make sense, as it solves the problem for the user:
> having
> to wait for the prompt.
>> Another question, why EXISTS has advantage here that you suggest it?
> When I see a query like:
> SELECT DISCINT a.*
> FROM a
> JOIN b ON a.col = b.col
> I get the idea that someone has added DISTINCT since the join to b can
> have multiple matches. But since no column from b is included in the
> result
> set, I assume that the join really means "show all rows from a that match
> a
> condition in b", and this is better expressed with EXISTS for two reasons:
> 1) it speaks in clear terms what we are doing. 2) The DISTINCT is no
> longer
> needed.
> It is possible that you need DISTINCT nevertheless, but as I recall your
> query had a SELECT *, and if you need DISTINCT in that case, this means
> that your letters table does not have a primary key!
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Thanks Hardik, I'll try it and post the result!
"Hardik Bati [MS]" <hardikb_removethis_@.microsoft.com> wrote in message
news:uSs%23waaOHHA.400@.TK2MSFTNGP04.phx.gbl...
> Can you try and see if this query works for you. I can't assure you but it
> might. Let me know what you find.
> Thanks,
> Hardik
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OfiaaoyNHHA.4848@.TK2MSFTNGP04.phx.gbl...
>> Ok! I attached a zip file to this message. It contains:
>> 1) Result of STATISTICS PROFILE for query execution in SQL Server 2000
>> (Plain Text Format)
>> 2) Result of STATISTICS PROFILE for query execution in SQL Server 2000
>> (CSV
>> Format)
>> 3) Result of STATISTICS PROFILE for query execution in SQL Server 2005
>> (Plain Text)
>> 4) Result of STATISTICS PROFILE for query execution in SQL Server 2005
>> (CSV
>> Format)
>> 5) XML Plan for query execution in SQL Server 2005
>> 6) The main query which I execute (It uses a view that is not included in
>> this file)
>> One important thing in comparison between these two plans appears in
>> first
>> 10 lines of CSV files: SQL Server 2000 performs some joins using Merge
>> algorithm while SQL Server 2005 uses Hash join. I have written a script
>> that
>> declares a cursor from indexes of the database and I do ALTER
>> INDEX...REBUILD. Even I wrote a similar script to update all statistics
>> with
>> FULLSCAN. This query takes about 11 seconds in SQL Server 2000 (with
>> cache)
>> and 20 seconds in 2005 (with cache).
>> I really appreciate any help! This problem has prevented us to migrate to
>> SQL Server 2005.
>>
>> "Hardik Bati [MS]" <hardikb_removethis_@.microsoft.com> wrote in message
>> news:uHw7sjoNHHA.2140@.TK2MSFTNGP03.phx.gbl...
>> Could you attach/add the query and the showplan_text for both 2000 and
>> 2005
>> so that we have a better idea what is happening.
>> Hardik
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:Ozbg36lNHHA.724@.TK2MSFTNGP04.phx.gbl...
>> The plans are almost very different! I tried JOIN HINTs but there are
>> much
>> more items that must be forced I suppose.
>>
>> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>> news:Xns98B6726A2A65Yazorman@.127.0.0.1...
>> Leila (Leilas@.hotpop.com) writes:
>> We exactly know the slow query! We have done everything (from new
>> indexes to indexed views) but 2005 is much slower than 2000 :(
>> Have you compared the query plans between SQL 2000 and SQL 2005?
>> I am afraid that I cannot say much more about a query that I know
>> nothing about.
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>>
>>
>
>sql