April 26, 2003 at 6:40 am
We are running SQL 2000 on clusters with failover configuration(Compaq Clusters) with good capacity. We need to come up with a backup and maintenance strategy for a database, whose tables (2 specifically) grow at a rapid rate, 250,000 records per day. We need to come up with statistics on that table (number of records per day, week and month categorywise). We need to retain data for around 6 months. Any suggestions? What is the max records a table can hold before losing performance.
April 29, 2003 at 8:00 am
This was removed by the editor as SPAM
April 29, 2003 at 9:18 am
srinigk
There are so many variables it is hard to give a definitive answer. We have one database with over 110 million rows which performs consistently as it continues to grow. Others that are much smaller have performance issues.
It depends a lot on having the indexes defined well. If there is a clustered index, it should be on an increasing value and not random, etc. For safety, it is nice to have a staging table where new information is accepted. This allows the submission process to remain efficient. The data can be moved to the permanent tables in bulk moves and less critical times.
One other point for performance...foreign keys and constraints have a significant impact on performance. Use them wisely.
Sorry for a general answer. Hope this helps.
Guarddata-
April 29, 2003 at 10:55 am
I run an active/passive cluster (Win2K Advanced Server/SQL Server 2000). One database is currently 160+ GB, one table is 528,000,000+ rows. The database gets BULK INSERTs and SELECTs only. BULK INSERTs happen every 5 minutes. I do sometime of backup every hour. I have not seen any performance issues since we started SQL Server up. Of course, we have 4 processors, 4 GB of RAM.
To answer your questions:
1. Performance. Depends on hardware, see above. My one table is 160+GB; if my system only had 2 processors I would be seeing a performance difference. But I'm not seeing one because the system can handle it.
2. I do:
-Full Backup once a day.
-Differential every four hours, except for when the full backup is done.
-Transaction Log backups every hour except for when the Full Backup or Differential Backups are being done.
I do my backups to hard drive using the SQL Server backup commands. Then I copy the .bak files to tape.
3. For statistics, I use sp_spaceused. I copied the stored procedure and made some changes to it. I eliminated some of the data it provides which I didn't need. If you do this, remember to rename the procedure as you should never make changes to system stored procedures.
-SQLBill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply