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.