I'm working on a web application hosted on multiple load-balanced web front ends (WFEs). This application has an API method that performs a database query and returns the results. This query only performs reads. There is a single database server.
We are finding that when the API method is called in quick succession and the requests are distributed across the WFEs, the database queries that execute will intermittently deadlock.
There are occasional writes to the database that occur from other parts of the system. I haven't yet verified if they are occurring at the same time.
Load is increasing and this issue is likely to occur more frequently over time. We have optimised the query and query transaction settings as best we can.
Is there a more appropriate architecture for this scenario?
(The technologies involved are MS .NET / Web API / SQL Server 2008 R2 although we're open to any options.)