Is SQL 2000 better than 7?

We are using SQL 7 and Forte application and Win NT. I
have migrated DB to SQL 2000 and new Windows 2000 server.
Our DB is big, 150 G. Testing on new 2000 server, I see
that many of our procedures are running slower. One
procedure is like not running. Taking all resources and
hanging. I can ont even see the exec plan. I have tried
set plan still not working. This particular procedure is
using multiple unions and one of them using self join.
I have other procedures they are not that compecated but
the are slow too. I am having hard time convincing my boss
that SQL 2000 is better than 7. We really don't see any.
On top of that some procedure are not working at all.
Please send me your expert opinion.
Hello Aziz,
If my statement will work that here you go "SQL Server 2000 is far better
than SQL Server 7.0" :-)
I hope you have rebuilt all the indexes and updated statistics for all the
databases.
HTH
Saleem@.sqlnt.com
"Aziz Karim" wrote:

> We are using SQL 7 and Forte application and Win NT. I
> have migrated DB to SQL 2000 and new Windows 2000 server.
> Our DB is big, 150 G. Testing on new 2000 server, I see
> that many of our procedures are running slower. One
> procedure is like not running. Taking all resources and
> hanging. I can ont even see the exec plan. I have tried
> set plan still not working. This particular procedure is
> using multiple unions and one of them using self join.
> I have other procedures they are not that compecated but
> the are slow too. I am having hard time convincing my boss
> that SQL 2000 is better than 7. We really don't see any.
> On top of that some procedure are not working at all.
> Please send me your expert opinion.
>
|||Update statistics with FULLSCAN on all indexes. Also, using the Profiler,
check for lock escalation events. If you see a lot, then you may have to
tune some queries.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aziz Karim" <anonymous@.discussions.microsoft.com> wrote in message
news:145d01c499b4$34e51d50$a301280a@.phx.gbl...
We are using SQL 7 and Forte application and Win NT. I
have migrated DB to SQL 2000 and new Windows 2000 server.
Our DB is big, 150 G. Testing on new 2000 server, I see
that many of our procedures are running slower. One
procedure is like not running. Taking all resources and
hanging. I can ont even see the exec plan. I have tried
set plan still not working. This particular procedure is
using multiple unions and one of them using self join.
I have other procedures they are not that compecated but
the are slow too. I am having hard time convincing my boss
that SQL 2000 is better than 7. We really don't see any.
On top of that some procedure are not working at all.
Please send me your expert opinion.
|||Thank you for your reply.
Tom, I tried again now with FULLSCAN. Same thing. I don't
think I will be able to use Profiler. I can not see the
exec paln since this query never finish. It is using a
table with 186 mil rows. With SQL server 7 no problem.

>--Original Message--
>Update statistics with FULLSCAN on all indexes. Also,
using the Profiler,
>check for lock escalation events. If you see a lot, then
you may have to
>tune some queries.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Aziz Karim" <anonymous@.discussions.microsoft.com> wrote
in message
>news:145d01c499b4$34e51d50$a301280a@.phx.gbl...
>We are using SQL 7 and Forte application and Win NT. I
>have migrated DB to SQL 2000 and new Windows 2000 server.
>Our DB is big, 150 G. Testing on new 2000 server, I see
>that many of our procedures are running slower. One
>procedure is like not running. Taking all resources and
>hanging. I can ont even see the exec plan. I have tried
>set plan still not working. This particular procedure is
>using multiple unions and one of them using self join.
>I have other procedures they are not that compecated but
>the are slow too. I am having hard time convincing my boss
>that SQL 2000 is better than 7. We really don't see any.
>On top of that some procedure are not working at all.
>Please send me your expert opinion.
>.
>
|||You can trace the lock escalation event, even if the statement does not
complete. Also, you can trace the SQL:BatchStarting event. That way, you
can see which statement is slow.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aziz" <anonymous@.discussions.microsoft.com> wrote in message
news:18a701c499d3$e91e9020$a401280a@.phx.gbl...
Thank you for your reply.
Tom, I tried again now with FULLSCAN. Same thing. I don't
think I will be able to use Profiler. I can not see the
exec paln since this query never finish. It is using a
table with 186 mil rows. With SQL server 7 no problem.

>--Original Message--
>Update statistics with FULLSCAN on all indexes. Also,
using the Profiler,
>check for lock escalation events. If you see a lot, then
you may have to
>tune some queries.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Aziz Karim" <anonymous@.discussions.microsoft.com> wrote
in message
>news:145d01c499b4$34e51d50$a301280a@.phx.gbl...
>We are using SQL 7 and Forte application and Win NT. I
>have migrated DB to SQL 2000 and new Windows 2000 server.
>Our DB is big, 150 G. Testing on new 2000 server, I see
>that many of our procedures are running slower. One
>procedure is like not running. Taking all resources and
>hanging. I can ont even see the exec plan. I have tried
>set plan still not working. This particular procedure is
>using multiple unions and one of them using self join.
>I have other procedures they are not that compecated but
>the are slow too. I am having hard time convincing my boss
>that SQL 2000 is better than 7. We really don't see any.
>On top of that some procedure are not working at all.
>Please send me your expert opinion.
>.
>
|||Tom Moreau wrote:
> You can trace the lock escalation event, even if the statement does
> not complete. Also, you can trace the SQL:BatchStarting event. That
> way, you can see which statement is slow.
>
You can also add SQL:StmtStarting and SP:StmtStarting to nail it down
even further. You can also run an estimated execution plan from Query
Analyzer and see if anything stands out without executing the query.
David G.
|||I know which statement is slow. I think SQL 2000 using
different path than 7 for this query. Do I have to fine
tune all of these slow query?
I am trying to find a general solution to solve all
problems.
Aziz

>--Original Message--
>You can trace the lock escalation event, even if the
statement does not
>complete. Also, you can trace the SQL:BatchStarting
event. That way, you
>can see which statement is slow.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Aziz" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:18a701c499d3$e91e9020$a401280a@.phx.gbl...
>Thank you for your reply.
>Tom, I tried again now with FULLSCAN. Same thing. I don't
>think I will be able to use Profiler. I can not see the
>exec paln since this query never finish. It is using a
>table with 186 mil rows. With SQL server 7 no problem.
>using the Profiler,
>you may have to
-[vbcol=seagreen]
>--
>in message
boss
>.
>
|||As with anything in software development, there is no magic bullet. It is
possible that the specific query is degraded when parallelism is used. If
so, try OPTION (MAXDOP 1).
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
<anonymous@.discussions.microsoft.com> wrote in message
news:187501c499ed$e5100df0$a301280a@.phx.gbl...
I know which statement is slow. I think SQL 2000 using
different path than 7 for this query. Do I have to fine
tune all of these slow query?
I am trying to find a general solution to solve all
problems.
Aziz

>--Original Message--
>You can trace the lock escalation event, even if the
statement does not
>complete. Also, you can trace the SQL:BatchStarting
event. That way, you
>can see which statement is slow.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Aziz" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:18a701c499d3$e91e9020$a401280a@.phx.gbl...
>Thank you for your reply.
>Tom, I tried again now with FULLSCAN. Same thing. I don't
>think I will be able to use Profiler. I can not see the
>exec paln since this query never finish. It is using a
>table with 186 mil rows. With SQL server 7 no problem.
>using the Profiler,
>you may have to
-[vbcol=seagreen]
>--
>in message
boss
>.
>
|||I appreciate your help Tom.
I used OPTION (MAXDOP1) but it is runnig for 30 min. In
SQl 7 takes only 8 min.
I see that my CPU is not that busy for this OPTION.
Also I found in MS website that sometimes this option may
not work. There is a bug.

>--Original Message--
>As with anything in software development, there is no
magic bullet. It is
>possible that the specific query is degraded when
parallelism is used. If[vbcol=seagreen]
>so, try OPTION (MAXDOP 1).
>--
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>..
><anonymous@.discussions.microsoft.com> wrote in message
>news:187501c499ed$e5100df0$a301280a@.phx.gbl...
>I know which statement is slow. I think SQL 2000 using
>different path than 7 for this query. Do I have to fine
>tune all of these slow query?
>I am trying to find a general solution to solve all
>problems.
>Aziz
>statement does not
>event. That way, you
-[vbcol=seagreen]
>--
>message
then[vbcol=seagreen]
-[vbcol=seagreen]
>-
server.
>boss
>.
>
|||I have also tried SET FORCEPLAN ON
Not helping either.

>--Original Message--
>As with anything in software development, there is no
magic bullet. It is
>possible that the specific query is degraded when
parallelism is used. If[vbcol=seagreen]
>so, try OPTION (MAXDOP 1).
>--
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com
>..
><anonymous@.discussions.microsoft.com> wrote in message
>news:187501c499ed$e5100df0$a301280a@.phx.gbl...
>I know which statement is slow. I think SQL 2000 using
>different path than 7 for this query. Do I have to fine
>tune all of these slow query?
>I am trying to find a general solution to solve all
>problems.
>Aziz
>statement does not
>event. That way, you
-[vbcol=seagreen]
>--
>message
then[vbcol=seagreen]
-[vbcol=seagreen]
>-
server.
>boss
>.
>