April 25, 2005 at 2:57 pm
SQL Server 2000 / Windows 2003 Server
I have a large table that stores logs from network equipment box (about 600M rows = 20GB on disk). Primary operations are inserts (95% of the time) - around 2-3M rows a day.
I want to collect data from 3 network boxes. The performance question is, what is better:
(A) to create ONE database and THREE tables inside in different filegroups
OR
(B) to create THREE databases and ONE table inside each
With best regards,
Andrey
April 26, 2005 at 12:49 am
Well u should make one database and add the tree tables into it.
April 26, 2005 at 5:27 am
can you please explain why?
Regards,
Andrey
April 26, 2005 at 7:23 am
Actually, either way will work. With separating the tables, you will need to put each table into a separate file group. What you want to concentrate on is physically separating the tables/dbs so that you get the best read/write performance. If everything is separate but in the same physical disk, you have defeated the purpose and will get the same or worse performance as before.
April 26, 2005 at 8:26 am
Thank you Rookie,
The choice I have is --> RAID5 storage for data. There is no way I can separate tables (or database) to by disk (controllers). Everything is going to reside on the same RAID5 partition.
I also understand that both ways will work. I want to find which way is performance-wise better.
Andrey
April 27, 2005 at 10:03 am
There is one advantage to using 3 separate databases (one table in each) vs. 3 tables in one database that has been overlooked. This is the distribution of transaction log activity. Depending on the transactional inserts per second the 3 dtabase option may perform better. It will also allow you to 'tune' things somewhat based on the input.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 27, 2005 at 11:27 am
true.
Andrey
April 27, 2005 at 12:10 pm
Rudy, that may not be true, particularly if the different logs can't be separated onto different disks/arrays. If the transaction percentages are correct it might be better to have a single transaction log that is written to sequentially rather than to have three. This will minimize the disk heads needing to skip from one to the other...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
April 27, 2005 at 12:38 pm
dc, you are correct since they are using local disk. In my environment this is not an issue since all databases (data/log/backup) are on separate SAN Meta-LUNs. So the worries separation by disk controller and disk spindles do not exist. But in this example, inserts of 2-3 million rows per day (how long is their day ? 8 hours or 24 ?) is a very small volume. With a good 'smart' caching RAID controller there should not be any impacts on performance with one or three databases.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply