How many requests can SQL Server handle per second ?

Question

I am using JMeter to test our application 's performance. but I found when I send 20 requests from JMeter, with this the reason result should be add 20 new records into the sql server, but I just find 5 new records, which means that SQL server discard the other requests(because I took a log, and make sure that the insert new records are sent out to sql server.)

Do anyone have ideas ? What's the threshold number of request can SQL server handle per second ? Or do i need to do some configuration ?

Yeah, in my application, I tried, but it seems that only 5 requests are accepted, I don't know how to config , then it can accept more.

Answer 1

I'm not convinced the nr of requests per seconds are directly releated to SQL server throwing away your inserts. Perhaps there's an application logic error that rolls back or fails to commit the inserts. Or the application fails to handle concurrency and inserts data violating the constraints. I'd check the server logs for deadlocks as well.

Answer 2

To get benchmark tests for SQL Server and other RDBMS, visit the Processing Performance Council Web

Answer 3

This is very dependent on what type of queries you are doing. You can have many queries requesting data which is already in a buffer, so that no disk read access is required or you can have reads, which actually require disk access. If you database is small and you have enough memory, you might have all the data in memory at all times - access would be very fast then, you might get 100+ queries/second. If you need to read a disk, you are dependant an you hardware. I have opted for an UltraSCSI-160 controller with UltraSCSI-160 drives, the fastest option you can get on a PC type platform. I process about 75'000 records every night (they get downloaded from another server). For each record I process, the program makes about 4 - 10 queries to put the new record into the correct 'slot'. The entire process takes about 3 minutes. I'm running this on an 850 MHz AMD Athlon machine with 768 MB of RAM. Hope this gives you a little indication about the speed.

Answer 4

Use either SQL Profiler or the LINQ data context for logging to see what has actually been sent to the server and then determine what the problem is.

Enable the data context log like this:

datacontext.Log = Console.Out;

As a side note, I've been processing 10 000 transactions per second in SQL Server, so I don't think that is the problem.

Answer 5

You can also use Sql Server profile to check how your querys are executed