I've been tasked with finding a solution to some performance issues we've been having with our PHP web application (basically, we're reaching a "failure" point when combining high-volume users with peak traffic/load hours). What I've found so far is that the bottleneck is occurring when trying to access the MS SQL Server database. Our sysadmin suggested that it's probably due to SQL Server having to do too much context switching, because of the amount that the code queries the database.
Upon looking more into context switching and how to reduce it, though, I've only been able to find vague mentions of how to actually do so at the application code level, mostly along the lines of "refactor your code so it doesn't make so many calls," or .Net-specific tips.
We're dealing with a large, complex codebase, so we can't do things like completely rewrite the system, and we also have pretty well optimized (as best we could) the individual queries that we can, so what other refactoring opportunities can we look for to help make our code not bring our database server to its knees?
I don't currently know the full stats on our database server, but it's beefy enough to run MS SQL Server 2008 and was doing well until recently.
ETA: I'm simply a developer and don't have any authoritative power, so I can't do things like hire consultants. While I'm willing to make the suggestion to my superiors, I'm primarily looking for things that we can do in-house to further work toward solving the underlying issue.
As I explained in a comment, I understand that the context switching is more a symptom of something else, which is then causing the issues we're actually seeing (slow responses from the database; just like in an OS, doing a lot of swapping results in slow response from an application, but is itself a symptom of other things taking up too much RAM). What is causing the context switching? A lot of database access from the application code. The problem is, the individual queries are already as good as we can get them right now, as indicated by our monitoring software, so what else can we do to help this problem?
My admin's use of context switching has since been clarified. It seems that the issue, given his clarification, is that there are a lot of relatively small calls being made, which would require the database server to enqueue them as it handles each one in turn, driving up the response time as the scripts wait for their requested data. Are there any strategies, then, for combining these database calls, or otherwise adjusting how an MVC-structured PHP application makes calls to the database so that the scripts aren't constantly waiting on the database?
