SQL: SELECT 1 for testing connectivity is pointless

I spend quite a bit of time tracing queries sent from applications to SQL Server.

Having now done this for a long time, I can recognize many of the data frameworks that are used by various developers. I think that if any of the developers ever spent much time looking at traces, they’d be amazed at how the requests they have made to the framework are translated to commands that are sent to SQL Server.

I’ve also been posting lately about how the quickest way to scale SQL Server is to just stop talking to it unnecessarily.

One variant of the JDBC drivers that I’ve seen, has a command that, while it’s really a “nothing” for SQL Server to execute, breaks this rule.

What I see is that the framework regularly executes this command:

SELECT 1

Yep, that’s it. And why does it do this? It appears that they are doing it to check if they have connectivity to the server.

I’m sure some framework developer thought that was a good idea. These are the people who don’t get things like race conditions, and timing, and are the sort of people who create applications that nearly always work.

This command is actually completely and utterly pointless, and worse, incurs the latency of a regular round trip to the server while doing so.

Even if the server responds to your SELECT statement, that tells you NOTHING about the state of your connection immediately after the command is completed.

[caption id=“attachment_2743” align=“alignnone” width=“461”] Image by Markus Spiske[/caption]

The fact that the server responded to you, doesn’t in any way at all, guarantee that the server will still be connected for your next command batch.

Please don’t do this.

It wastes your time, your end-user’s time, and SQL Server’s time. Worse, every little extra command that SQL Server has to process takes it away from the real processing that you want it to do.

2018-03-05