>Hi Guys,
>I have one database with about 60 tables. One table has over 20 Million records and groing at 600,000 per month. I have another 5 tables with about 5 million records in each. The Sql Server is currently running on a dell Server 1 GIG Ram and 256BG HDD. No Raid. Using VFP connecting with ODBC. The System is struggling at the moment when we run queries or if there is a lot of writing to the database some of the machines running VFP freezes.
>
>Is it possible to make it faster by
>1. increasing the number of CPU's or RAM
>2. Split the Database into two and have them sitting on different servers. How would I do my queries with databases sitting on 2 different servers.
>
>How can I make it work faster ?
>The client are losing their patience.Can anyone help me.
>

So many variables can affect SQL performance that it is difficult to tell what is going on. However, here's a few suggestions:

1) Are there any other things running on the machine or is it a dedicated SQL box? Anything else should be moved to a different server.
2) What is the maximum RAM the server will hold? Max it out. SQL is a resource pig.
3) Are all of the database objects in the same physical file? RAID can help here. You can move indexes to a different file on another disk so that updates will happen faster.
4) Are you using a lot of VARCHAR fields? This can cause problems with updates; if the record size grows beyond what it is currently allocated then the whole record has to be moved.
5) If you have a lot of indexes on your tables, make sure they are all absolutely necessary. Excess indexes will slow updates down.
6) Are you bringing only the minimum amount of data down to the VFP client? Not only the minimum amount of rows but columns as well. "SELECT * " is a performance killer.

Dunno if anyone else recommended it, but here is my favorite SQL performance web site, it may give you some other ideas:
http://www.sql-server-performance.com/