1

I'm analyzing the HTTP logs to one of our web services and I've noticed a lot of request to an endpoint such as /entities/0 which will end up effectively doing something like SELECT x, y, z FROM entities WHERE Id = 0, and I know for a fact there's no entity with Id = 0.

There are approximately 600,000 requests to this endpoint in a 24hr period. Each request opens it's own connection to the SQL Server and executes the query which returns an empty result.

I'm wondering how bad this actually is. What overhead is associated on the SQL Server with having an open connection, and what overhead with repeatedly returning the empty result for the same query (WHERE Id = 0), does SQL Server cache and optimize for this?

We're using SQL Server 2012.

  • Requests may not be bad by themselves, but opening a new connection for each request certainly is. You should be using a connection pool in the application. – mustaccio Jul 27 '16 at 14:27
  • I am assuming this behavior is to check for connectivity/availability? – Jason B. Jul 27 '16 at 14:35
  • If you can't stop this at the application level you could potentially add a check constraint to the table that checks "Id <> 0" then likely the plan won't even access the table at all (if it is using the literal 0 rather than parameterized and doesn't get auto parameterized anyway) – Martin Smith Jul 27 '16 at 14:45
  • @JasonB. No, it's actually a bug but I just want to understand more of what effect this might be having in the production environment. – Greg B Jul 27 '16 at 14:54
2

That is about 7 requests per second if the rate is flat over the 24 hour period, which shouldn't have a noticeable impact on a decently specced SQL Server machine. If they are far more bunched then they may be more of a problem.

I would be inclined to investigate where the requests are coming from. If they don't do anything useful then they may indicate a bug in the application.

SELECT <something> FROM <table> WHERE <column>=<value_that_doesn't_exist> is not going to tax the query planner or engine really, the work top bring up and tear down a connection will be more arduous to the SQL infrastructure than the work to lookup a cached query plan and read a couple of pages to see the row does not exist.

Assuming that the Id column is the table's clustering key there will be at most a couple of page reads and they will be from RAM as they are not ever going to leave the buffer cache due to how frequently they are accessed. If that column isn't the clustering key then add another page or two to those read (again, almost certainly from RAM not disk). If the column isn't indexed at all (I'd be surprised if that is the case given it is called "Id") then things are potentially more problematical as there will be a table scan involved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.