Hardware advice
-
I was wondering if some of you nice folks could give me hardware advice for my very simple, but heavy-load SQL Server 2000 database. I apologize if this is over-broad for the forum--feel free to tell me so.
I have a PowerEdge 750 that that will support up to 2 15K SCSIs in RAID, and up to 4GB RAM. I'd love to run it on that, but if I need different hardware, I'll get what it takes.
The vast majority of the activity is on one very simple table, which is stored case-sensitively, and searched on case-insensitively.
CREATE TABLE PHRASES
(
Phrase NVARCHAR(225) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
Frequency INTEGER NOT NULL,
CreationDate DATETIME NOT NULL
)
Phrase is catalogued for full-text searching, and has a clustered index as well. It is searched on like this:
SELECT TOP 5 Phrase FROM Phrases
WHERE CONTAINS(Phrase, '"keyword1" and "keyword2" and "keyword3"') AND
Phrase LIKE '%keyword1%keyword2%keyword3%' COLLATE database_default --to enforce keyword order
ORDER BY Frequency
Adding records is a simple INSERT of a phrase or UPDATE of a phrase's Frequency depending on whether the phrase is already in the table.
Requirements:
-up to 100 million rows
-20 searches per second
-500 ms max response time on searches (this is a must)
-1000 new records (INSERTs or UPDATEs) per second
All advice is very much appreciated....I'm (obviously) not a SQL Server expert, so thanks for bearing with me.
David
-
First thing, you're going to need more than 2 disks.
Take a look at the HP Solution Sizers for SQL Server. You can enter the requirements info there and it'll suggest a hardware setup. That'll give you a good starting point.
http://h71019.www7.hp.com/ActiveAnswers/cache/71110-0-0-0-121.html
--------------------
Colt 45 - the original point and click interface
-
David,
You may also consider planning your database structure. For example, this table and its non-clustered indexes should be on different drives You don't say you have any, but you have full-text indexes. Also consider transaction logs on the separate drive, page file on the separate drive and maybe someone will recommend to split the table. Post this question also on the SQL Server Administration topic
Yelena
Regards,Yelena Varsha
-
Make sure you are using SQL EE to address all the RAM.
If you can separate OS & SQL install on one RAID 1 set, SQL data RAID10, SQL Logs RAID 1 or 5, make sure tempdb is large enough - maybe on its own disk or RAID 1, more RAID controllers the better. Like putting indexes on a different file group and controller.
Anyway that you can archive the table into two or partition based on some other criteria. Potential for indexed partitioned views?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply