[ root | ruminations | remote ]

How to assert that your SQL does not do full table scans

In database applications it is often important to ensure that your SQL uses indexes to avoid performance problems. Consider an SQL select statement like this

SELECT text FROM response WHERE questionId = 27 AND participantId = 38

If there is no index on response(questionId, participantId), the database needs to do a full table scan, and if the response table have a lot of rows, this will degrade performance. Such a performance problem might only be observed during testing on large amounts of data, which is not typically something a developer will do before every commit.

It would be useful to be able to detect this kind of problem much earlier, preferably during unit testing. Here I will describe one way to do this using Microsoft SQL Server and .NET:

SQL Server makes a lot of information accessible through performance counters, and one of those performance counters is incremented every time SQL Server does a full table scan. Utilizing that information, we can write a unit test that verifies that indexes are properly in place by asserting that the number of full table scans performed by a piece of code is equal to zero:

using(new AssertSqlFullScanCount(0))
   var responses = Response.GetResponses(q, p);
   Assert.That(responses, ...);
This code uses the following helper class:
public class AssertSqlFullScanCount : IDisposable
   private int expected;
   private long actual;
   private PerformanceCounter fullScans;
   public AssertSqlFullScanCount(int expected)
      this.expected = expected;
      this.fullScans = new PerformanceCounter(
         "MSSQL$SQLEXPRESS:Access Methods",
         "Full Scans/sec");
      this.actual = fullScans.RawValue;

   public void Dispose()
      actual = fullScans.RawValue - actual;
      Assert.That(actual, Is.EqualTo(expected));
Mirrored from dead link: https://bestbrains.dk/Blog/2010/03/25/HowToAssertThatYourSQLDoesNotDoFullTableScans.aspx

If you enjoyed reading this, catch me on Twitter