April 27, 2009 at 12:15 pm
I have requirement where will get the rows coming in in huge no. per minute in a table
what kind of approach i should follow like Indexs etc.
what kind of table design should be
April 28, 2009 at 6:04 am
Have you looked into setting the database in bulk-logged mode.
Inserts are certainly quicker without indexes so if you can avoid indexes until after the data is inserted - that will depend on your use.
Also table locks will improve performance. Certainly on DTS packages you can set "fast load" in the properties of a transform and remove the "check constraints" flag and set "tab lock" to help performance.
I think you need to define your needs in a bit more detail along with the environment in which this will operate and that might help you identify what your best options are.
April 28, 2009 at 6:17 am
Hi ritesh.saluja,
also if you're managing tons of inserts per minute, consider creating one or more filegroups on different disks system and put those tables on them. That will reduce IO bottlenecks.
Alejandro Pelc
April 28, 2009 at 6:45 am
Good point - more spindles is good so make sure your datafiles, logs, tempdb are placed separately if possible and then start thinking about your operating system paging file, available RAM etc
Always a fun can of worms. Alternative is to start loading up data and if it doesn't fit in the available window of time or unduly impacts something else's performance then try to fix something.
April 28, 2009 at 6:53 am
Yeap, follow Chris advice and separate everything. Also consider having 1 datafile in tempdb per processor (this is for avoiding bottlenecks on the tempdb).
If you're server is Win 2003 or prior, I reccomend you read Jimmy May's article about disk partition alignment (http://blogs.msdn.com/jimmymay/archive/2008/10/14/disk-partition-alignment-for-sql-server-slide-deck.aspx)
Alejandro Pelc
April 28, 2009 at 6:57 am
It is worth stressing that the tempdb files should be the same size as each other if you have multiple files.
April 29, 2009 at 9:40 am
You didn't give us nearly enough information to guide you here. If you want the highest inserts per second, simply have a heap table with NO indexes whatsoever. That obviously won't be very useful, but you didn't say what else you might be doing with the table. Also for fastest work, try to do some bulk mechanism as others suggested and keep the database in SIMPLE recovery mode (again, this may conflict with other unprovided requirements).
On a separate topic, comments from others about tempdb are meaningless for your objective.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 29, 2009 at 9:45 am
That's interesting - is tempdb not used at all in data loads - I've had problems here recently with large dataloads and tempdb so I'm surprised to hear that.
April 29, 2009 at 10:24 am
I agree with TheSQLGuru about the lack of information to correctly guide ritesh, but also think that tempdb comments are important because of the same reason: didn't provide us with details. I don't think the entire SQL instance will be used only for inserting huge amounts of data but also something will be done with the information. In that case, every little thing will help boosting the performance.
Cheers,
Alejandro
Alejandro Pelc
April 29, 2009 at 10:53 am
Alejandro Pelc (4/29/2009)
I agree with TheSQLGuru about the lack of information to correctly guide ritesh, but also think that tempdb comments are important because of the same reason: didn't provide us with details. I don't think the entire SQL instance will be used only for inserting huge amounts of data but also something will be done with the information. In that case, every little thing will help boosting the performance.Cheers,
Alejandro
Your logic makes no sense. If it did (i.e. giving generic advice on performance "just because") then we would be flooding the forum with hundreds of comments on how to tune a sql server for every post.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply