First SQL2K5 server install performance issues

This is comparing performance between two machines...the first is our
current SQL2000 server
Dual 3Ghz Xeons on dual channel scsi drives.
New machine
Dual 3.6Ghz 64-bit Xeons dual channel scsi
SCSI controller in each box is identical as is the drive configuration and
file placement.
I know this may not be a very clear view of the performance but I certainly
expected more from the new server.
We have a table tblMLOS which contains 392468 rows. This following script
after repeated executions runs consistently at 9 seconds on the old server
and 15-17 seconds on the new 64-bit machine. Are we missing something here?
This just doesn't seem right.
The table structure is very simple:
create table tblMLOS
(
lngID int,
lngSponsor int,
lngSponsor2 int,
lngSponsor3 int,
strSponsorType varchar(20)
)
---
declare @.lngCount int
declare @.lngID int
declare curID cursor for
select lngID from tblmlos
set @.lngCount=0
open curID
fetch next from curID into @.lngID
while @.@.fetch_status=0 begin
set @.lngCount=@.lngCount+1
fetch next from curID into @.lngID
end
close curID
deallocate curID
select @.lngCount64 bit will not always be quicker, your code will be purely CPU bound , and
probably only use 1 cpu, the difference in CPU is not much.
Not sure why you are not just doing
select count(1) from lngID from tblmlos
This probably will be quicker.
--
Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:eFa%23wF%23YGHA.3400@.TK2MSFTNGP02.phx.gbl...
> This is comparing performance between two machines...the first is our
> current SQL2000 server
> Dual 3Ghz Xeons on dual channel scsi drives.
> New machine
> Dual 3.6Ghz 64-bit Xeons dual channel scsi
> SCSI controller in each box is identical as is the drive configuration and
> file placement.
> I know this may not be a very clear view of the performance but I
> certainly expected more from the new server.
> We have a table tblMLOS which contains 392468 rows. This following script
> after repeated executions runs consistently at 9 seconds on the old server
> and 15-17 seconds on the new 64-bit machine. Are we missing something
> here? This just doesn't seem right.
> The table structure is very simple:
> create table tblMLOS
> (
> lngID int,
> lngSponsor int,
> lngSponsor2 int,
> lngSponsor3 int,
> strSponsorType varchar(20)
> )
> ---
> declare @.lngCount int
> declare @.lngID int
> declare curID cursor for
> select lngID from tblmlos
> set @.lngCount=0
> open curID
> fetch next from curID into @.lngID
> while @.@.fetch_status=0 begin
> set @.lngCount=@.lngCount+1
> fetch next from curID into @.lngID
> end
> close curID
> deallocate curID
> select @.lngCount
>
>|||This is not a production query. Just one of the many things we are trying
to see if there is any performance increase at all. So far every thing
we've tried is slower on the SQL 2005 box. I just picked this one cause it
is easy and straightforward.
"Simon Sabin" <SimonSabin@.noemal.noemail> wrote in message
news:Or6gZQ$YGHA.5004@.TK2MSFTNGP02.phx.gbl...
> 64 bit will not always be quicker, your code will be purely CPU bound ,
> and probably only use 1 cpu, the difference in CPU is not much.
> Not sure why you are not just doing
> select count(1) from lngID from tblmlos
> This probably will be quicker.
> --
> Simon Sabin
> SQL Server MVP
> http://sqljunkies.com/weblog/simons
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:eFa%23wF%23YGHA.3400@.TK2MSFTNGP02.phx.gbl...
>> This is comparing performance between two machines...the first is our
>> current SQL2000 server
>> Dual 3Ghz Xeons on dual channel scsi drives.
>> New machine
>> Dual 3.6Ghz 64-bit Xeons dual channel scsi
>> SCSI controller in each box is identical as is the drive configuration
>> and file placement.
>> I know this may not be a very clear view of the performance but I
>> certainly expected more from the new server.
>> We have a table tblMLOS which contains 392468 rows. This following
>> script after repeated executions runs consistently at 9 seconds on the
>> old server and 15-17 seconds on the new 64-bit machine. Are we missing
>> something here? This just doesn't seem right.
>> The table structure is very simple:
>> create table tblMLOS
>> (
>> lngID int,
>> lngSponsor int,
>> lngSponsor2 int,
>> lngSponsor3 int,
>> strSponsorType varchar(20)
>> )
>> ---
>> declare @.lngCount int
>> declare @.lngID int
>> declare curID cursor for
>> select lngID from tblmlos
>> set @.lngCount=0
>> open curID
>> fetch next from curID into @.lngID
>> while @.@.fetch_status=0 begin
>> set @.lngCount=@.lngCount+1
>> fetch next from curID into @.lngID
>> end
>> close curID
>> deallocate curID
>> select @.lngCount
>>
>|||I do see in the execution plan between the 2k and 2k5 that the 2k5 is doing
a Clustered Index Insert at every fetch and then a Clustered Index Scan
where as the 2k box is just doing the Scan. The lngID column is a clustered
index and the indexes on the 2k5 box have all been rebuilt with fullscan.
"Simon Sabin" <SimonSabin@.noemal.noemail> wrote in message
news:Or6gZQ$YGHA.5004@.TK2MSFTNGP02.phx.gbl...
> 64 bit will not always be quicker, your code will be purely CPU bound ,
> and probably only use 1 cpu, the difference in CPU is not much.
> Not sure why you are not just doing
> select count(1) from lngID from tblmlos
> This probably will be quicker.
> --
> Simon Sabin
> SQL Server MVP
> http://sqljunkies.com/weblog/simons
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:eFa%23wF%23YGHA.3400@.TK2MSFTNGP02.phx.gbl...
>> This is comparing performance between two machines...the first is our
>> current SQL2000 server
>> Dual 3Ghz Xeons on dual channel scsi drives.
>> New machine
>> Dual 3.6Ghz 64-bit Xeons dual channel scsi
>> SCSI controller in each box is identical as is the drive configuration
>> and file placement.
>> I know this may not be a very clear view of the performance but I
>> certainly expected more from the new server.
>> We have a table tblMLOS which contains 392468 rows. This following
>> script after repeated executions runs consistently at 9 seconds on the
>> old server and 15-17 seconds on the new 64-bit machine. Are we missing
>> something here? This just doesn't seem right.
>> The table structure is very simple:
>> create table tblMLOS
>> (
>> lngID int,
>> lngSponsor int,
>> lngSponsor2 int,
>> lngSponsor3 int,
>> strSponsorType varchar(20)
>> )
>> ---
>> declare @.lngCount int
>> declare @.lngID int
>> declare curID cursor for
>> select lngID from tblmlos
>> set @.lngCount=0
>> open curID
>> fetch next from curID into @.lngID
>> while @.@.fetch_status=0 begin
>> set @.lngCount=@.lngCount+1
>> fetch next from curID into @.lngID
>> end
>> close curID
>> deallocate curID
>> select @.lngCount
>>
>