Posted by Mike Read on 01/25/06 16:23

Hi Greg

>>
>> We've pretty much done what we can but some queries will always need a
>> full table scan.
>>
>
> Why? I'd suggest perhaps posting some DDLs here. Some folks here can
> sometimes do some amazing work.
>

The main table is 1 billion rows of about 60 columns, we've indexed on the
most common attributes users might select on but we allow them to mine
the data using arbitary SQL so they might decide to look for an arithmetic
combination of parameters (again some of which we have anticipated and
materialized) or do stats on the columns etc.

>
> Well, generally more RAM is good.
>
> But keep in mind SQL Server 2000 Standard is limited to 2 gig of RAM.
>
> So make sure you're using Enterprise on an OS that will permit use of more
> RAM.
>
> I'd highly suggest at least Windows 2003 for your OS and ideally moving to
> SQL 2005 to boot.

We're running on Windows 2003 with 4 Gb.

>
> For example, SQL 2005 Enterprise on Windows 2003 Enterprise can supply up to
> 64 Gig of RAM. (if you really have money to burn,go to Enterprise for
> Itanium Systems.. 1TB of RAM. Oh and send a few checks my way. :-)
>

A bit out of our price range though we could almost cache the table :)

> Also, you may want to if you haven't already, get more disks and partition
> tables accordingly.
>
> For example, if it's only one large table that gets scanned, move it to its
> own set of disks. This will isolate the disk I/O.


Currently we spread all database/tables across 4 RAID volumes to increase
aggregate IO. Initially most queries were accessing the main large table
but the new project DB is currentlty much smaller so there is scope for
some separation

Thanks
Mike

[Back to original message]