SQL Server - database size and insertion rate
-
Hi,
We need to create a database for a data intensive project that must be able to hanlde a steady amount of about 40 million entries (rows) in a table. Entries into the table will also be made at a high insertion rate.
Will SQL server be able to handle a database with this size and speed requirement?
-
Who knows?
What is a "high insertion rate"? for you? How will this happen? The simple number of rows is quite handable.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi,
Yes very much and the speed depends on your server configuration and the maintenance that you do. We have a database which contains more than one million rows(growing).
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
High insertion rate for us is about 300 entries per second. Entries can be batched. So, in you opinion, this can be done with sqlserver?
-
SQL server can handle it given appropriate...
hardware configuration
maintainence plan
query and achitect performance optimization
-
A few things you can do ...
create a new database file and point the database to the new file on creation. Make sure the file is sized to accommedate 6-12 months data when you create the database (don't want to run autoextend while inserting records ... overhead on performance).
DON'T USE RAID5!!! (very slow on inserts)
If you are going to use indexes, create them on a seperate database file on seperate disks. If you are not going to update record data after the insert, make the fill factor of the indexes (and if you are going to use a cluster table index) to 90% + (otherwise you are going to waist space with no valid reason). Just make sure that the values will be inserted in sequence ... example will be a date column to capture the create_date_time ... etc.
Christo Pretorius
-
Christo has summed it up.
You can comfortably handle that number of records and the high rate.
But to make things even more comfy, ovoid clustered indexes except for a timestamp key, and partition data over multiple datafiles, it will allow you to create a partitioned view for enhanced query performance
HABIB.
-
There is still insufficient info to anser this. Franks question: "How will this happen?" is important.
Is this 1 connection to the DB performing the inserts, or is it several, 10, 100 or more clients ? Executing dynamic SQL, or stored procs ?
-
I agree with Enthusiast, there are a lot of unanswered questions here.
I can safely say that a 40 million row table is no big deal assuming you're running on the right hardware but it would be stretch for anybody to specify/guarantee a workable solution without a whole lot more information:
(1) 40 million rows of what? What does the table structure look like, at a minimum would want to know the #/type of fields.
(2) Do you have 40 million rows that need to be loaded already and then need to continue to append additional data at a "high rate"? You already stated that "batch" was acceptable, planning on using DTS, BCP or another third party tool? How frequently does the data need to be updated?
(3) Aside from putting additional data into the table what else do you need to do with it? Are you just collecting a bunch of data for grins? What type of queries do you need to support, how frequently, # of concurrent users, etc.
-
To piggy-back on this RAID 5 thing, read closely through this: http://www.baarf.com/
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply