SQL Design: Big table, thread access serialization.

Question

Hi there.

I have one BIG table(90k rows, size cca 60mb) which holds info about free rooms capacities for about 50 hotels. This table has very few updates/inserts per hour. My application sends async requests to this(and joined tables) at max 30 times per sec.

When i start 30 threads(with default AppPool class at .NET 3.5 C#) at one time(with random valid sql query string), only few(cca 4) are processed asynchronously and other threads waits. Why? Is it becouse of SQL SERVER 2008 table locking, or becouse of .NET core? Or something else?

If it is a SQL problem, can help if i split this big table into one table per each hotel model? My goal is to have at least 10 threads servet at a time.

Answer 1

It is more than likely on the .NET side. If it were table locking more threads would be processing, but they would be waiting on their queries to return. If I remember correctly there's a property for thread pools that controls how many actual threads they create at once. If there are more pending threads than that number, then they get in line and wait for running threads to finish. Check that.

Answer 2

This table is tiny. It's doesn't even qualify as a "medium sized" table. It's trivial.

You can be full table scanning it 30 times per second, you can be copying the whole thing in ram, no server is going to be the slightest bit bothered.

If your data fits in ram, databases are fast. If you don't find this, you're doing something REALLY WRONG. Therefore I also think the problems are all on the client side.

Answer 3

Have you tried changing the transaction isolation level?

Even when reading from a table Sql Server will lock the table

try setting the isolation level to read uncommitted and see if that improves the situation,

but be advised that its feasible that you will read 'dirty' data make sure you understand the ramifications if this is the solution

this link explains what it is.

link text

Answer 4

Rather than ask, measure. Each of your SQL queries that is actually submitted by your application will create a request on the server, and the sys.dm_exec_requests DMV shows the state of each request. When the request is blocked the wait_type column shows a non-empty value. You can judge from this whether your requests are blocked are not. If they are blocked you'll also know the reason why they are blocked.