extremely bad performance running on huge box ?

hi all !!!
I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box running
Windows 2003 Server. no other apps run in this machine.
a table witn 20 million records is truncated and recreated once every week.
records are quite simple: (key,value). Table in indexes on key field.
the problem is thar running a "select count(*) from table" takes more than 2
minutes.
even worst, doing a "select * from table where (condition on indexed key
field)" also takes more than 2 minutes.
during these queries, which I do from the Query Analyzer, the overall CPUs
usage is less than 3%.
what could be wrong ? any help would be greatly appreciated.
thanks in advance,
ant.Please post your table DDL (including constraints and indexes), sample data
and problem queries so that we can better help.

> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
This will require a scan and the duration will be proportional to table
size. If you have only a clustered index and data are not cached, then all
data pages will be read. 2 minutes may or may not be reasonable depending
on your row size and disk configuration.

> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
This doesn't seem right. If your 'indexed key field' is unique, I would
expect sub-second response. With a non-unique index, I'd expect the
response time to be roughly proportional to the number of rows that satisfy
the condition as long as the expression is sargable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Antonio Lopez Arredondo" <adog@.someplace.com> wrote in message
news:uzLV4A%238DHA.3348@.TK2MSFTNGP09.phx.gbl...
> hi all !!!
> I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box
running
> Windows 2003 Server. no other apps run in this machine.
> a table witn 20 million records is truncated and recreated once every
week.
> records are quite simple: (key,value). Table in indexes on key field.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
> during these queries, which I do from the Query Analyzer, the overall CPUs
> usage is less than 3%.
> what could be wrong ? any help would be greatly appreciated.
> thanks in advance,
> ant.
>

extremely bad performance running on huge box ?

hi all !!!
I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box running
Windows 2003 Server. no other apps run in this machine.
a table witn 20 million records is truncated and recreated once every week.
records are quite simple: (key,value). Table in indexes on key field.
the problem is thar running a "select count(*) from table" takes more than 2
minutes.
even worst, doing a "select * from table where (condition on indexed key
field)" also takes more than 2 minutes.
during these queries, which I do from the Query Analyzer, the overall CPUs
usage is less than 3%.
what could be wrong ? any help would be greatly appreciated.
thanks in advance,
ant.are there 4 physcial processors or is this a Xeon box with
Hyper-Threading,
if HT is enabled, then regardless of the number of actual
processors, try OPTION (MAXDOP 1) or 2
suppose your average row size is 100Bytes, then 20M rows
means the table is 2GB in size
a select count(*) requires a full table scan.
if your data is on a single disk, you can probably achieve
a sequential transfer rate of 50MB/sec, meaning your query
should take 40sec,
if you have 2 disks striped, you ought to be closer to
100MB/sec or 20sec.
if the entire table is in memory, the scan rate should be
800MB/sec,
so something is not right.
on the select *, i need to know the index and the full
query,
also keep in mind the time it QA to receive a large number
of rows
>--Original Message--
>hi all !!!
>I have a SQL Server 2000 running on a 4 processor 2 GB
ram DELL box running
>Windows 2003 Server. no other apps run in this machine.
>a table witn 20 million records is truncated and
recreated once every week.
>records are quite simple: (key,value). Table in indexes
on key field.
>the problem is thar running a "select count(*) from
table" takes more than 2
>minutes.
>even worst, doing a "select * from table where (condition
on indexed key
>field)" also takes more than 2 minutes.
>during these queries, which I do from the Query Analyzer,
the overall CPUs
>usage is less than 3%.
>what could be wrong ? any help would be greatly
appreciated.
>thanks in advance,
> ant.
>
>.
>|||Please post your table DDL (including constraints and indexes), sample data
and problem queries so that we can better help.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
This will require a scan and the duration will be proportional to table
size. If you have only a clustered index and data are not cached, then all
data pages will be read. 2 minutes may or may not be reasonable depending
on your row size and disk configuration.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
This doesn't seem right. If your 'indexed key field' is unique, I would
expect sub-second response. With a non-unique index, I'd expect the
response time to be roughly proportional to the number of rows that satisfy
the condition as long as the expression is sargable.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Antonio Lopez Arredondo" <adog@.someplace.com> wrote in message
news:uzLV4A%238DHA.3348@.TK2MSFTNGP09.phx.gbl...
> hi all !!!
> I have a SQL Server 2000 running on a 4 processor 2 GB ram DELL box
running
> Windows 2003 Server. no other apps run in this machine.
> a table witn 20 million records is truncated and recreated once every
week.
> records are quite simple: (key,value). Table in indexes on key field.
> the problem is thar running a "select count(*) from table" takes more than
2
> minutes.
> even worst, doing a "select * from table where (condition on indexed key
> field)" also takes more than 2 minutes.
> during these queries, which I do from the Query Analyzer, the overall CPUs
> usage is less than 3%.
> what could be wrong ? any help would be greatly appreciated.
> thanks in advance,
> ant.
>