Help: why SQL 2005 is slow?

Hi,
During the weekend, I heard that SQL Server 2000 is very slow with tables co
ntaining rows over millions. So I did some
tests with our new database on SQL Server 2005 Standard Edition. The machine
is Windows 2003 Standard Server box
with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID configuration. It
has drives: C: with 11GB free space
and D: with 169GB free space. SQL 2005 was installed on D: drive.
The size of the database is 10GB containing 47 tables. The main table, Items
, contains 96 columns and 30 millions rows.
All columns are in varchar data type, and 2 of them are in varchar(2500). Th
ere is no any index setup in the table neither.
The time consumed for some SQL statements with this table are the followings
:
========================================
==============
No. SQL Statement Tim
e
========================================
= =========
1 SELECT * FROM Items WHERE item_num='10029' 16 minutes
----
-- --
2 SELECT COUNT(*) FROM Items 13 minute
s
----
-- --
3 ALTER TABLE Items
ADD rid INT PRIMARY KEY IDENTITY(1,1) 10 hours 50 minute
s
----
-- --
4 SELECT COUNT(*) FROM Items 20 minute
s
----
-- --
5 SELECT * FROM Items WHERE item_num='10029' 18 minutes
========================================
==============
The first 2 statements were run without primary key in the table. The statem
ents #4 and #5 were run after "rid" was added as primary key.
This is the first time I work with a database in such size. But the performa
nce of SQL Server 2005 surprised me.
Would you please tell me:
1. Is such performance normal with such number of rows?
2. Do I have to do something to improve the performance with such simple sta
tement when the number of rows>1 million or >10 millions?
3. Is SQL Server 2005 the right one to handle a database with such big table
, or should I consider DB2 9 or Oracle 10g?
Thank you
HongboHongbo wrote:
> Hi,
> During the weekend, I heard that SQL Server 2000 is very slow with
> tables containing rows over millions. So I did some
> tests with our new database on SQL Server 2005 Standard Edition. The
> machine is Windows 2003 Standard Server box
> with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID
> configuration. It has drives: C: with 11GB free space
> and D: with 169GB free space. SQL 2005 was installed on D: drive.
> The size of the database is 10GB containing 47 tables. The main table,
> Items, contains 96 columns and 30 millions rows.
> All columns are in varchar data type, and 2 of them are in
> varchar(2500). There is no any index setup in the table neither.
> The time consumed for some SQL statements with this table are the
> followings:
> ========================================
==============
> No. SQL
> Statement Time
> ========================================
= =========
> 1 SELECT * FROM Items WHERE item_num='10029' 16 minutes
> ----
--
> --
> 2 SELECT COUNT(*) FROM Items 13
> minutes
> ----
--
> --
> 3 ALTER TABLE Items
> ADD rid INT PRIMARY KEY IDENTITY(1,1) 10
> hours 50 minutes
> ----
--
> --
> 4 SELECT COUNT(*) FROM Items 20
> minutes
> ----
--
> --
> 5 SELECT * FROM Items WHERE item_num='10029' 18 minutes
> ========================================
==============
> The first 2 statements were run without primary key in the table. The
> statements #4 and #5 were run after "rid" was added as primary key.
> This is the first time I work with a database in such size. But the
> performance of SQL Server 2005 surprised me.
> Would you please tell me:
> 1. Is such performance normal with such number of rows?
> 2. Do I have to do something to improve the performance with such
> simple statement when the number of rows>1 million or >10 millions?
> 3. Is SQL Server 2005 the right one to handle a database with such big
> table, or should I consider DB2 9 or Oracle 10g?
> Thank you
> Hongbo
Do I read this right, you have 30 million rows an no indexes? I would
think about adding a few to help you out. SQL 2005 can handle that with
proper design and hardware.
I believe there is a tremendous amount of paging going on there to
accommodate your full table scans and on a single drive, hence your poor
performance.
Ryan Sanders
http://ryanlsanders.blogspot.com