CODEWITHSUNDEEP
c#.netsqlsql-server-2008
Feryt
Feryt

Reputation: 2302

SQL Design: Big table, thread access serialization

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.

Upvotes: 0

Views: 405

Answers (4)

Remus Rusanu
Remus Rusanu

Reputation: 294387

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.

Upvotes: 0

Kev Hunter
Kev Hunter

Reputation: 2625

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

Upvotes: 0

MarkR
MarkR

Reputation: 63576

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.

Upvotes: 3

Donnie
Donnie

Reputation: 46933

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.

Upvotes: 0

Related Questions