Performance effect if SQL2000 and SQL2005 on same box

Hi everyone,
I am going to upgrade SQL2000 to SQL2005 because 3rd party application
requires SQL2005. My plan is upgrade my current database on SQL2000 to
SQL2005, but keeping SQL2000 for fall back plan.
Is there any impact on performance if I have SQL2005 and SQL2000 running on
the same machine?
Thanks alot for any help in advance.
Han.
The issues are not different from running two SQL2000 instances or two
SQL2005 instances.
Linchi
"Han" wrote:

> Hi everyone,
> I am going to upgrade SQL2000 to SQL2005 because 3rd party application
> requires SQL2005. My plan is upgrade my current database on SQL2000 to
> SQL2005, but keeping SQL2000 for fall back plan.
> Is there any impact on performance if I have SQL2005 and SQL2000 running on
> the same machine?
> Thanks alot for any help in advance.
> Han.
sql

Performance effect if SQL2000 and SQL2005 on same box

Hi everyone,
I am going to upgrade SQL2000 to SQL2005 because 3rd party application
requires SQL2005. My plan is upgrade my current database on SQL2000 to
SQL2005, but keeping SQL2000 for fall back plan.
Is there any impact on performance if I have SQL2005 and SQL2000 running on
the same machine?
Thanks alot for any help in advance.
Han.You will obviously have resource usage of you have both service started at the same time. But having
2000 installed but not started will not degrade performance of a 2005 install on that machine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Han" <Han@.discussions.microsoft.com> wrote in message
news:FE01070A-7736-47CF-AECA-D9DB9CA5F75E@.microsoft.com...
> Hi everyone,
> I am going to upgrade SQL2000 to SQL2005 because 3rd party application
> requires SQL2005. My plan is upgrade my current database on SQL2000 to
> SQL2005, but keeping SQL2000 for fall back plan.
> Is there any impact on performance if I have SQL2005 and SQL2000 running on
> the same machine?
> Thanks alot for any help in advance.
> Han.|||The issues are not different from running two SQL2000 instances or two
SQL2005 instances.
Linchi
"Han" wrote:
> Hi everyone,
> I am going to upgrade SQL2000 to SQL2005 because 3rd party application
> requires SQL2005. My plan is upgrade my current database on SQL2000 to
> SQL2005, but keeping SQL2000 for fall back plan.
> Is there any impact on performance if I have SQL2005 and SQL2000 running on
> the same machine?
> Thanks alot for any help in advance.
> Han.

Performance counter quest

Hi,
SQL2000 Standard sp3.
Windows 2000 server, 2 physical processors, Intel hyper threading tech.
I am running Performance monitor from another machine which is windows 2000 terminal , to monitor the above mentioned sqlserver. The output of the csv file showing total processor time > 300. I dont understand why it is showing instead of values < 100. I
n that machine i can see .NET CLR counters also. Is this behaviour because of .NET?
(PDH-CSV 4.0) (Pacific Daylight Time)(420)\\AB01\Process(_Total)\% Privileged Time\\AB01\Process(_Total)\% Processor Time
4/13/04 5:00 AM349.7912317399.6868476
4/13/04 5:00 AM350399.8958333
4/13/04 5:00 AM399.7918835399.8959417
4/13/04 5:01 AM343.8541667399.6875
How can i take the processor time to calculate cpu usage? Any help appreciated
thanks
kris
Look at the Processor(_Total)\% Processor Time for values that you expect.
Some of the counters are explained at :
http://www.extremeexperts.com/sql/ar...LCounters.aspx
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"kris" <anonymous@.discussions.microsoft.com> wrote in message
news:BF219D19-16FC-40DD-8728-31A2E8E2580A@.microsoft.com...
> Hi,
> SQL2000 Standard sp3.
> Windows 2000 server, 2 physical processors, Intel hyper threading tech.
> I am running Performance monitor from another machine which is windows
2000 terminal , to monitor the above mentioned sqlserver. The output of the
csv file showing total processor time > 300. I dont understand why it is
showing instead of values < 100. In that machine i can see .NET CLR
counters also. Is this behaviour because of .NET?
> (PDH-CSV 4.0) (Pacific Daylight Time)(420) \\AB01\Process(_Total)\%
Privileged Time \\AB01\Process(_Total)\% Processor Time
> 4/13/04 5:00 AM 349.7912317 399.6868476
> 4/13/04 5:00 AM 350 399.8958333
> 4/13/04 5:00 AM 399.7918835 399.8959417
> 4/13/04 5:01 AM 343.8541667 399.6875
> How can i take the processor time to calculate cpu usage? Any help
appreciated
> thanks
> kris
>
>

Performance counter quest

Hi
SQL2000 Standard sp3.
Windows 2000 server, 2 physical processors, Intel hyper threading tech
I am running Performance monitor from another machine which is windows 2000 terminal , to monitor the above mentioned sqlserver. The output of the csv file showing total processor time > 300. I dont understand why it is showing instead of values < 100. In that machine i can see .NET CLR counters also. Is this behaviour because of .NET
(PDH-CSV 4.0) (Pacific Daylight Time)(420) \\AB01\Process(_Total)\% Privileged Time \\AB01\Process(_Total)\% Processor Tim
4/13/04 5:00 AM 349.7912317 399.686847
4/13/04 5:00 AM 350 399.895833
4/13/04 5:00 AM 399.7918835 399.895941
4/13/04 5:01 AM 343.8541667 399.687
How can i take the processor time to calculate cpu usage? Any help appreciate
thank
kriLook at the Processor(_Total)\% Processor Time for values that you expect.
Some of the counters are explained at :
http://www.extremeexperts.com/sql/articles/SQLCounters.aspx
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"kris" <anonymous@.discussions.microsoft.com> wrote in message
news:BF219D19-16FC-40DD-8728-31A2E8E2580A@.microsoft.com...
> Hi,
> SQL2000 Standard sp3.
> Windows 2000 server, 2 physical processors, Intel hyper threading tech.
> I am running Performance monitor from another machine which is windows
2000 terminal , to monitor the above mentioned sqlserver. The output of the
csv file showing total processor time > 300. I dont understand why it is
showing instead of values < 100. In that machine i can see .NET CLR
counters also. Is this behaviour because of .NET?
> (PDH-CSV 4.0) (Pacific Daylight Time)(420) \\AB01\Process(_Total)\%
Privileged Time \\AB01\Process(_Total)\% Processor Time
> 4/13/04 5:00 AM 349.7912317 399.6868476
> 4/13/04 5:00 AM 350 399.8958333
> 4/13/04 5:00 AM 399.7918835 399.8959417
> 4/13/04 5:01 AM 343.8541667 399.6875
> How can i take the processor time to calculate cpu usage? Any help
appreciated
> thanks
> kris
>
>

Performance counter quest

Hi,
SQL2000 Standard sp3.
Windows 2000 server, 2 physical processors, Intel hyper threading tech.
I am running Performance monitor from another machine which is Windows 2000
terminal , to monitor the above mentioned sqlserver. The output of the csv f
ile showing total processor time > 300. I dont understand why it is showing
instead of values < 100. I
n that machine i can see .NET CLR counters also. Is this behaviour because o
f .NET?
(PDH-CSV 4.0) (Pacific Daylight Time)(420) \\AB01\Process(_Total)\% Privileg
ed Time \\AB01\Process(_Total)\% Processor Time
4/13/04 5:00 AM 349.7912317 399.6868476
4/13/04 5:00 AM 350 399.8958333
4/13/04 5:00 AM 399.7918835 399.8959417
4/13/04 5:01 AM 343.8541667 399.6875
How can i take the processor time to calculate cpu usage? Any help appreciat
ed
thanks
krisLook at the Processor(_Total)\% Processor Time for values that you expect.
Some of the counters are explained at :
http://www.extremeexperts.com/sql/a...QLCounters.aspx
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"kris" <anonymous@.discussions.microsoft.com> wrote in message
news:BF219D19-16FC-40DD-8728-31A2E8E2580A@.microsoft.com...
> Hi,
> SQL2000 Standard sp3.
> Windows 2000 server, 2 physical processors, Intel hyper threading tech.
> I am running Performance monitor from another machine which is windows
2000 terminal , to monitor the above mentioned sqlserver. The output of the
csv file showing total processor time > 300. I dont understand why it is
showing instead of values < 100. In that machine i can see .NET CLR
counters also. Is this behaviour because of .NET?
> (PDH-CSV 4.0) (Pacific Daylight Time)(420) \\AB01\Process(_Total)\%
Privileged Time \\AB01\Process(_Total)\% Processor Time
> 4/13/04 5:00 AM 349.7912317 399.6868476
> 4/13/04 5:00 AM 350 399.8958333
> 4/13/04 5:00 AM 399.7918835 399.8959417
> 4/13/04 5:01 AM 343.8541667 399.6875
> How can i take the processor time to calculate cpu usage? Any help
appreciated
> thanks
> kris
>
>

Performance Condition Alert Type

I am trying to setup a Performance Condition Alert and on this one SQL
2000 Server the 'Type:' drop down does not give me the option to choose
"SQL Server performance condition alert". "SQL Server event alert" is
the only item listed in the drop down. Is there some setting on the
Server that I am missing that needs to be activated? I am connected
with the SA account; Windows 2000 sp4; SQL 2000 sp2; SQL Litespeed
(this is the only server with LiteSpeed).

Thank you."Stephanie" <stuttle@.centene.com> wrote in message
news:1122068829.573542.227430@.g43g2000cwa.googlegr oups.com...
>I am trying to setup a Performance Condition Alert and on this one SQL
> 2000 Server the 'Type:' drop down does not give me the option to choose
> "SQL Server performance condition alert". "SQL Server event alert" is
> the only item listed in the drop down. Is there some setting on the
> Server that I am missing that needs to be activated? I am connected
> with the SA account; Windows 2000 sp4; SQL 2000 sp2; SQL Litespeed
> (this is the only server with LiteSpeed).
> Thank you.

In some cases, it seems that the SQL Server performance counters are not
shown in the Windows Performance Monitor, so it may be that you're seeing
something similar:

http://groups.google.ch/group/micro...ec3d36b0b?hl=en
http://www.extremeexperts.com/SQL/F...erCounters.aspx

It would also be worth installing SP4 - whether or not it helps to resolve
this issue, you need at least SP3 for protection from the Slammer worm.

Simon

performance

Are there any out of the BOX performance gains going from sql7 to sql2000 ?
ThanksYes, performance enhancements are numerous going from SQL7 to SQL2000. Bette
r
performance with differential backups, better parallelism with DBCC CHECKDB,
better peroformance with BLOB via such features as text in row, better
parallelism with CREATE INDEX, better large memory support, and so on. It's
night and day between SQL7 and SQL2000.
Funactionality improvement is huge as well.
Linchi
"Hoosbruin" wrote:

> Are there any out of the BOX performance gains going from sql7 to sql2000
?
>
> Thanks
>
>

performance

Are there any out of the BOX performance gains going from sql7 to sql2000 ?
ThanksYes, performance enhancements are numerous going from SQL7 to SQL2000. Better
performance with differential backups, better parallelism with DBCC CHECKDB,
better peroformance with BLOB via such features as text in row, better
parallelism with CREATE INDEX, better large memory support, and so on. It's
night and day between SQL7 and SQL2000.
Funactionality improvement is huge as well.
Linchi
"Hoosbruin" wrote:
> Are there any out of the BOX performance gains going from sql7 to sql2000 ?
>
> Thanks
>
>

perform aggregate function & group by

Hello, anyone can help?
below SQL works in sybase but fail in sql2000. sql2000 show error on the sum
of netweight (cannot perform aggregate function) and group by commodity_code
(invalid column name !!)
SELECT
(select HTS
from bur_inv_item_list
where item = material
and plantcode = plant) as commodity_code,
(select DESCRIPTION
from bur_inv_item_list
where item = material
and plantcode = plant) as description,
uom as uom,
(select Orig
from bur_inv_item_list
where item = material
and plantcode = plant) as coo,
netweight =
sum(case when plant = '0014' or plant = '0024' then
round(qty * (select weight
from bur_inv_item_list
where item = material
and plantcode = plant), 2)
else
round(qty * (select weight
from bur_inv_item_list
where item = material
and plantcode = plant) /2.20462, 2)
end),
sum(ext_cost) as cost
from bur_inv_cntr_list
group by
commodity_code,
description,
uom,
coo
order by
commodity_code,
description,
uom,
coo
;The logical evaluation order of a SELECT statement is (top to bottom):
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
TOP
As you see, the SELECT hasn't happened yet when the GROUP BY is performed. T
his mean that you cannot
refer to any column alias name in the GROUP BY clause. Some product diverts
from the ANSI SQL
standard behavior, SQL Server does not (in this regard). So push the express
ions in a derived table
and work against that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:emgGyQOaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Hello, anyone can help?
>
> below SQL works in sybase but fail in sql2000. sql2000 show error on the s
um of netweight (cannot
> perform aggregate function) and group by commodity_code (invalid column na
me !!)
>
> SELECT
> (select HTS
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as commodity_code,
> (select DESCRIPTION
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as description,
> uom as uom,
> (select Orig
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as coo,
> netweight =
> sum(case when plant = '0014' or plant = '0024' then
> round(qty * (select weight
> from bur_inv_item_list
> where item = material
> and plantcode = plant), 2)
> else
> round(qty * (select weight
> from bur_inv_item_list
> where item = material
> and plantcode = plant) /2.20462, 2)
> end),
> sum(ext_cost) as cost
> from bur_inv_cntr_list
> group by
> commodity_code,
> description,
> uom,
> coo
> order by
> commodity_code,
> description,
> uom,
> coo
> ;
>|||hi tibor,
thanks for your info. do you have any sample/case. I want to see the sample
to modify below script.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u3R1jsPaGHA.504@.TK2MSFTNGP03.phx.gbl...
> The logical evaluation order of a SELECT statement is (top to bottom):
> FROM
> WHERE
> GROUP BY
> HAVING
> SELECT
> ORDER BY
> TOP
> As you see, the SELECT hasn't happened yet when the GROUP BY is performed.
> This mean that you cannot refer to any column alias name in the GROUP BY
> clause. Some product diverts from the ANSI SQL standard behavior, SQL
> Server does not (in this regard). So push the expressions in a derived
> table and work against that.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:emgGyQOaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>|||Here's a very simple example where a derived table is used so you don't have
to repeat the DATEPART
expression:
USE pubs
SELECT DATEPART(mm, pubdate) AS pub_month, COUNT(*) AS no_of_titles
FROM titles
GROUP BY DATEPART(mm, pubdate)
SELECT pub_month, COUNT(*) AS titles
FROM
(
SELECT DATEPART(mm, pubdate) AS pub_month
FROM titles
) AS i
GROUP BY pub_month
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:OqUKBdRaGHA.2368@.TK2MSFTNGP03.phx.gbl...
> hi tibor,
> thanks for your info. do you have any sample/case. I want to see the sampl
e to modify below
> script.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:u3R1jsPaGHA.504@.TK2MSFTNGP03.phx.gbl...
>|||Hi Tibor,
Many Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ebF1yHSaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Here's a very simple example where a derived table is used so you don't
> have to repeat the DATEPART expression:
> USE pubs
> SELECT DATEPART(mm, pubdate) AS pub_month, COUNT(*) AS no_of_titles
> FROM titles
> GROUP BY DATEPART(mm, pubdate)
> SELECT pub_month, COUNT(*) AS titles
> FROM
> (
> SELECT DATEPART(mm, pubdate) AS pub_month
> FROM titles
> ) AS i
> GROUP BY pub_month
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:OqUKBdRaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>