SQL query with nested subqueries
Let's just focus on this part, because that's where your performance goes:

SELECT
    COUNT(1)
FROM Results LEFT JOIN Users ON Results.UserId = Users.Id
WHERE
    Results.TestId = Tests.Id AND
    Results.MarkedBy IS NULL AND
    Results.QuestionNumber &gt;= 1 AND
    EXISTS (
      (SELECT ClassName FROM UserClasses WHERE UserClasses.UserId = Users.Id)
      INTERSECT
      (SELECT ClassName FROM TestClasses WHERE TestClasses.TestId = Tests.Id)
      INTERSECT
      (SELECT ClassName FROM UserClasses WHERE UserId = @teacherId)
    )


That pattern of EXISTS (... INTERSECT ...) is better written as a chain of INNER JOIN.

The query optimizer of your database already did that internally as well, but it chose the wrong order for the join, resulting in overly large temporary result sets. Especially when joining UserClasses straight on Results without applying the more selective filter by @teacherId first.

SELECT
    COUNT(1)
FROM Results
INNER JOIN TestClasses ON
    TestClasses.TestId = Tests.Id AND
    TestClasses.TestId = Results.TestId
INNER JOIN UserClasses AS TeacherClass ON
    TeacherClass.UserId = @teacherId AND
    TeacherClass.ClassName = TestClasses.ClassName
INNER JOIN UserClasses AS UserClass ON
    UserClass.UserId = Results.UserId AND
    UserClass.ClassName = TestClasses.ClassName
WHERE
    Results.MarkedBy IS NULL AND
    Results.QuestionNumber &gt;= 1


I reordered the JOIN clauses to ensure that the product remains as small as possible after each single step. Further, I eliminated the unnecessary join on the User schema.

However, you don't actually need full INNER JOIN either. If your database system supports that, you can safely replace the 2nd and 3rd of the INNER JOIN with LEFT SEMI JOIN operators instead.



So much for fixing the inner select. But as a matter of fact, now we don't even need to do it as a subquery any more, but can just handle if as a LEFT JOIN with COUNT and GROUP BY on the outmost query.

Whether this actually gains any performance needs to be tested.



There are also a couple of flaws in your database scheme:

Take the UserClasses table schema. You are abusing it to describe both the roles of teacher and student for any given class, without distinguishing between these two. I suspect you coded the user role into the Users schema instead, but it would have been better to store different roles in different schemes.

You are apparently storing class names as string literals in multiple schemes, this is an indirect violation of the 2NF, but even worse, it requires string comparisons to match the corresponding columns against each other. This should be refactored ASAP.

There also appears to be a possible design flaw in Results. If the same test is reused by two different classes, and a pupil is enrolled into both, his test results are now shared between both classes. Test results should probably linked to a specific enrollment to a class, rather than just to the generic test. This also allows to simplify this query further, as the most expensive part of joining on UserClass for querying pupil enrollment is then obsolete.