Simultaneous writes limit in SQL 2005

Question

Simultaneous writes limit in SQL 2005

jeffrey_bryant

Valued Member

Points: 60
More actions
December 11, 2008 at 11:24 am

#204026

This is more of a general knowledge question about SQL 2005.
I am part of team testing an application that does time tracking. We are seeing an issue where under high load the SQL server will stop responding when the application tries to do more than 230 - 280 simultaneous writes to the time sheet table.
I was told that this is a limitation of SQL (More than 250'ish simultaneous writes to a single table).
Does anyone know if this is documented anyplace and if this is still an issue in SQL 2008?
Granted this is not very likely but as the usage ramps up past 30k users it's not toally out of the question.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

What kind of writes? You can certainly do more that 250 Inserts/sec to a table in SQL Server.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Lowell SSC Guru Points: 323547 More actions · Answer 2

I'm pretty sure what you are seeing is an actual hardware limitation, i think; you start getting disk contention for the IO at a certain point, and yours is hitting 250; constraints and indexes can slow down the actual performance as well of course.

there's an article i read where some good hardware was being used to test INSERTS and identity, and it started having trouble at about 400 inserts per second;

hope that helps.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 3

And even as you hit the limitations on a single table inside a single database on a single server, you can start to look at partitioning and parallel processing using multiple servers.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

jeffrey_bryant Valued Member Points: 60 More actions · Answer 4

Great thanks for the info. This really confirms what I had thought. Just wanted to make sure there wasn't a major gotcha someplace on this issue.

Unfortunately the Dev team for this application seems to not be very willing to look at some of the obvious options, but hey I'm just the tester on this one...what do I know. 😀

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 5

jeffrey_bryant (12/11/2008)
Great thanks for the info. This really confirms what I had thought. Just wanted to make sure there wasn't a major gotcha someplace on this issue.
Unfortunately the Dev team for this application seems to not be very willing to look at some of the obvious options, but hey I'm just the tester on this one...what do I know. 😀

If you're anything like a friend of mine, more than the developers.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning