Fluctuate in DB Performance Statistics

Dear All,
I have noticed that the DB performance statistics is not
stable as it will rise at certain time dramatically.
I have checked that there isn't any job at that time.
E.g. Server\memory/pages , Current disk queue length,
processor time
Can any one suggest on any cause this?
Thanks.most database applications today consists of a mix of
simple transactions and complex queries that process a lot
of data. this includes reports or other sophisticated
features.
if you have for example 100 concurrent users running the
simple transactions, you might see a stable load on the
server,
however, anytime someone hits one of the complex queries,
you might see cpu spike.
this is why many people recommend separating OLTP and DSS
applications. there is no such thing as a machine powerful
enough to handle both functions on one server.
the OLTP server depends on fast response times, meaning it
should operate at low cpu loading.
the DSS app is supposed to run with cpu pegged, if not,
then you should be running more DSS queries to it, you
bought the hardware, you may as well use it.
>--Original Message--
>Dear All,
>I have noticed that the DB performance statistics is not
>stable as it will rise at certain time dramatically.
>I have checked that there isn't any job at that time.
>E.g. Server\memory/pages , Current disk queue length,
>processor time
>Can any one suggest on any cause this?
>Thanks.
>.
>|||Run profiler at that time to see if it is any TSQL commands causing this. Also, it can be the
checkpoint process.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jackty" <anonymous@.discussions.microsoft.com> wrote in message
news:022d01c3ad86$140683a0$a601280a@.phx.gbl...
> Dear All,
> I have noticed that the DB performance statistics is not
> stable as it will rise at certain time dramatically.
> I have checked that there isn't any job at that time.
> E.g. Server\memory/pages , Current disk queue length,
> processor time
> Can any one suggest on any cause this?
> Thanks.

Flat file Import performance question

Hi there,

I have a question regarding 2 approaches importing data from a flat text file. I'm taking the approach suggested by Phil Brammer to use a checksum to see whether a row has exists, and if it does check whether it has changed.

http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed

I would like to hear some opinions on 2 approaches:

1) Importing the data int a temp table first then do the data processing from the temp table.
Or
2) Importing the data by doing the data processing direcly from the text file without placing it in a temp table first.

A hunch told me that it will be faster doing approach 1 but I would like to hear some experienced opinions first

Best regards
Mike

I would think #2 would be *slightly* faster just because there are less components at work.

With that said, I prefer #1 always because it quickly loads the data to a table which I can then use SQL to get the data out. This might be advantageous for sorting the data, filtering the data, joining the data to another table, etc... Basically I like to push as much work off to the database engine that I can. Sure, SSIS can merge (join) data sets together, but the database engine will do a much better job. Also, having the data staged will help me to restart the process should it fail, work in batches (perhaps) by loading more than one file to the table if needed, and guarantee that the data will be there when I need it in an environment that might not guarantee that the file will always be around for use.

|||Thanks Phil,

I really appreciate your valuable input.

Best regards
Mike
|||

If you can afford, I would suggest to test both approaches, in your own environment, and then base your decisions on that way collected (real) data.

Thanks,

Bob

sql

Flat file Import performance question

Hi there,

I have a question regarding 2 approaches importing data from a flat text file. I'm taking the approach suggested by Phil Brammer to use a checksum to see whether a row has exists, and if it does check whether it has changed.

http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed

I would like to hear some opinions on 2 approaches:

1) Importing the data int a temp table first then do the data processing from the temp table.
Or
2) Importing the data by doing the data processing direcly from the text file without placing it in a temp table first.

A hunch told me that it will be faster doing approach 1 but I would like to hear some experienced opinions first

Best regards
Mike

I would think #2 would be *slightly* faster just because there are less components at work.

With that said, I prefer #1 always because it quickly loads the data to a table which I can then use SQL to get the data out. This might be advantageous for sorting the data, filtering the data, joining the data to another table, etc... Basically I like to push as much work off to the database engine that I can. Sure, SSIS can merge (join) data sets together, but the database engine will do a much better job. Also, having the data staged will help me to restart the process should it fail, work in batches (perhaps) by loading more than one file to the table if needed, and guarantee that the data will be there when I need it in an environment that might not guarantee that the file will always be around for use.

|||Thanks Phil,

I really appreciate your valuable input.

Best regards
Mike
|||

If you can afford, I would suggest to test both approaches, in your own environment, and then base your decisions on that way collected (real) data.

Thanks,

Bob

Fixed Memory = 3Gb sqlserver.exe uses 1.7Gb

Hello
We are trying to enhance the performance of our server. We recently noticed
that despite having 4Gb of RAM in the server, sqlserver.exe was only using
1.7Gb. There are 4 databases on the server, one of which is at 9Gb and in
constant use (by around 50 people).
We had thought that perhaps there was a problem with the dynamic memory
allocation so switched to fixed at 3Gb. Having restarted, the sqlserver.exe
process is still only using 1.7Gb of RAM (and still running at 100% CPU).
Surely when allocating memory in this way it should show sqlserver.exe with
the total amount of RAM. (Looking in task monitor to get the usage figure.)
Any ideas?
Thanks
DavidDavid Morgan wrote:
> Hello
> We are trying to enhance the performance of our server. We recently notic
ed
> that despite having 4Gb of RAM in the server, sqlserver.exe was only using
> 1.7Gb. There are 4 databases on the server, one of which is at 9Gb and in
> constant use (by around 50 people).
> We had thought that perhaps there was a problem with the dynamic memory
> allocation so switched to fixed at 3Gb. Having restarted, the sqlserver.e
xe
> process is still only using 1.7Gb of RAM (and still running at 100% CPU).
> Surely when allocating memory in this way it should show sqlserver.exe wit
h
> the total amount of RAM. (Looking in task monitor to get the usage figure
.)
> Any ideas?
> Thanks
> David
>
Which version of SQL? 2000 Standard will only use 2GB of RAM...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||1.7 GB indicates SQL Server uses 2GB (considering the memtoleave area - Goog
le for more info). What
version and edition of SQL Server and the OS?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Morgan" <microsoft_newsgroups.nospam@.davidmorgan.me.uk> wrote in mess
age
news:eacc4dLvGHA.1224@.TK2MSFTNGP03.phx.gbl...
> Hello
> We are trying to enhance the performance of our server. We recently notic
ed that despite having
> 4Gb of RAM in the server, sqlserver.exe was only using 1.7Gb. There are 4
databases on the
> server, one of which is at 9Gb and in constant use (by around 50 people).
> We had thought that perhaps there was a problem with the dynamic memory al
location so switched to
> fixed at 3Gb. Having restarted, the sqlserver.exe process is still only u
sing 1.7Gb of RAM (and
> still running at 100% CPU). Surely when allocating memory in this way it
should show
> sqlserver.exe with the total amount of RAM. (Looking in task monitor to g
et the usage figure.)
> Any ideas?
> Thanks
> David
>|||Thank you to both above. You are quite correct.
SQL Server 8.00.760 - SP3 (Standard Edition)
Bummer. Guess the 2005 upgrade is coming sooner than I thought.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23XKgriLvGHA.2232@.TK2MSFTNGP05.phx.gbl...
> 1.7 GB indicates SQL Server uses 2GB (considering the memtoleave area -
> Google for more info). What version and edition of SQL Server and the OS?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David Morgan" <microsoft_newsgroups.nospam@.davidmorgan.me.uk> wrote in
> message news:eacc4dLvGHA.1224@.TK2MSFTNGP03.phx.gbl...
>

Fixed Memory = 3Gb sqlserver.exe uses 1.7Gb

Hello
We are trying to enhance the performance of our server. We recently noticed
that despite having 4Gb of RAM in the server, sqlserver.exe was only using
1.7Gb. There are 4 databases on the server, one of which is at 9Gb and in
constant use (by around 50 people).
We had thought that perhaps there was a problem with the dynamic memory
allocation so switched to fixed at 3Gb. Having restarted, the sqlserver.exe
process is still only using 1.7Gb of RAM (and still running at 100% CPU).
Surely when allocating memory in this way it should show sqlserver.exe with
the total amount of RAM. (Looking in task monitor to get the usage figure.)
Any ideas?
Thanks
DavidDavid Morgan wrote:
> Hello
> We are trying to enhance the performance of our server. We recently noticed
> that despite having 4Gb of RAM in the server, sqlserver.exe was only using
> 1.7Gb. There are 4 databases on the server, one of which is at 9Gb and in
> constant use (by around 50 people).
> We had thought that perhaps there was a problem with the dynamic memory
> allocation so switched to fixed at 3Gb. Having restarted, the sqlserver.exe
> process is still only using 1.7Gb of RAM (and still running at 100% CPU).
> Surely when allocating memory in this way it should show sqlserver.exe with
> the total amount of RAM. (Looking in task monitor to get the usage figure.)
> Any ideas?
> Thanks
> David
>
Which version of SQL? 2000 Standard will only use 2GB of RAM...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||1.7 GB indicates SQL Server uses 2GB (considering the memtoleave area - Google for more info). What
version and edition of SQL Server and the OS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Morgan" <microsoft_newsgroups.nospam@.davidmorgan.me.uk> wrote in message
news:eacc4dLvGHA.1224@.TK2MSFTNGP03.phx.gbl...
> Hello
> We are trying to enhance the performance of our server. We recently noticed that despite having
> 4Gb of RAM in the server, sqlserver.exe was only using 1.7Gb. There are 4 databases on the
> server, one of which is at 9Gb and in constant use (by around 50 people).
> We had thought that perhaps there was a problem with the dynamic memory allocation so switched to
> fixed at 3Gb. Having restarted, the sqlserver.exe process is still only using 1.7Gb of RAM (and
> still running at 100% CPU). Surely when allocating memory in this way it should show
> sqlserver.exe with the total amount of RAM. (Looking in task monitor to get the usage figure.)
> Any ideas?
> Thanks
> David
>|||Thank you to both above. You are quite correct.
SQL Server 8.00.760 - SP3 (Standard Edition)
Bummer. Guess the 2005 upgrade is coming sooner than I thought.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23XKgriLvGHA.2232@.TK2MSFTNGP05.phx.gbl...
> 1.7 GB indicates SQL Server uses 2GB (considering the memtoleave area -
> Google for more info). What version and edition of SQL Server and the OS?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David Morgan" <microsoft_newsgroups.nospam@.davidmorgan.me.uk> wrote in
> message news:eacc4dLvGHA.1224@.TK2MSFTNGP03.phx.gbl...
>> Hello
>> We are trying to enhance the performance of our server. We recently
>> noticed that despite having 4Gb of RAM in the server, sqlserver.exe was
>> only using 1.7Gb. There are 4 databases on the server, one of which is
>> at 9Gb and in constant use (by around 50 people).
>> We had thought that perhaps there was a problem with the dynamic memory
>> allocation so switched to fixed at 3Gb. Having restarted, the
>> sqlserver.exe process is still only using 1.7Gb of RAM (and still running
>> at 100% CPU). Surely when allocating memory in this way it should show
>> sqlserver.exe with the total amount of RAM. (Looking in task monitor to
>> get the usage figure.)
>> Any ideas?
>> Thanks
>> David
>

Fix legacy data - Missing primary key + duplication record + large table

We have a large table which is very old and not much ppl take care about, recently there is a performance problem from the report need to query to this table. Eventally we find that this table have primary key missing and there is duplicate data which make "alter table add primary key" don't work

Besides the data size of this table require unacceptable time to execute something like "insert into new_table_with_pk from select distinct * from old table"

Do you have any recommendation of fixing this? As the application run on oracle , sybase and sql server, is that cross database approace will work?How big is ur table?.have u tried to create non clustered index on the field(s) of the tables which ur report using in filter condition.U have to do this modification when ur database have less load.|||How big is ur table?.have u tried to create non clustered index on the field(s) of the tables which ur report using in filter condition.U have to do this modification when ur database have less load.

It is about 2 millions rows. We haven't create non clustered index as we like to remove duplication and create primary key. So you mean first create index then do [insert into new_table from select distinct * from old_table]?|||first of all 2m records is not that big.distinct keyword is the killer here,it will affect the perfomance of ur query
Some options are,

change ur database option to 'true' for 'select into/bulkcopy' before u doing select * into.

exec sp_dboption 'urdatabase','select into/bulkcopy',true

this will improve the perfomance of the query 'select into' .

u should try checksum function in sql to remove duplicate.
before that add new column in exist table with identity,primary key

alter table urtable add [id] int identity(1,1)
CONSTRAINT id_pk PRIMARY KEY

-- select checksumvalues
eg:count duplicate in city,state,zip,contract in authors table.And delete duplicates

use pubs
go
delete authors from (
select max(au_id) as au_id,checksumvalue from
(
select au_id,au_lname,checksum(city,state,zip,contract) as checksumvalue from authors
) as t group by checksumvalue
having count(*)>1
) as t1 where authors.au_id=t1.au_id and authors.au_lname=t1.au_lname
--Note u should compare atleast one column from duplicate columns in where --clause(because checksum functiont is not 100% acuurate) as u can see , I --used 'au_lname' field in the above query

Same way u should compare with ur new [id] column which u created.



finally u have to build index on that table for better perfomance.

come back to me,if have doubts on how to remove duplicates using checksum function. try to put tables DDL also.


NB: dont forgot change back database option to,

exec sp_dboption 'urdatabase','select into/bulkcopy',false

after finish this job.

five connection limit

Hi!
Everybody know that MSDE (based on SQL Server 2000) has five-connection
limit after which its performance degrades. Do SQL server 2005 Express
edition also has five-connection limit? Or this limit was dropped?Hi
No connection limit in Express though a performance throttle, but it is
limited to use only 1 CPU, 1GB RAM and a database can have a maximum data
size of 4GB.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Igor Solodovnikov" <IgorSolodovnikov@.discussions.microsoft.com> wrote in
message news:op.s2ltu9n6n8ihmu@.iw2k.helpmicro.local...
> Hi!
> Everybody know that MSDE (based on SQL Server 2000) has five-connection
> limit after which its performance degrades. Do SQL server 2005 Express
> edition also has five-connection limit? Or this limit was dropped?|||The limit was removed in SQL 2005 Express. See
http://msdn2.microsoft.com/en-us/library/ms165672.aspx.
Happy Holidays
Dan Guzman
SQL Server MVP
"Igor Solodovnikov" <IgorSolodovnikov@.discussions.microsoft.com> wrote in
message news:op.s2ltu9n6n8ihmu@.iw2k.helpmicro.local...
> Hi!
> Everybody know that MSDE (based on SQL Server 2000) has five-connection
> limit after which its performance degrades. Do SQL server 2005 Express
> edition also has five-connection limit? Or this limit was dropped?

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
>>
>

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...
>|||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...
>