SQLCMD vs. Registered Servers

I always like it when my regular testing produces a usable example of something fun. For example, for a long time I have held that even on a local VM, connecting using SQLCMD is slower than Registered Servers.

I love SQLCMD, don't get me wrong, and I've written some very useful system maintenance scripts that would be very, very difficult to have gotten right any other way. That said, in my opinion, SQLCMD is just another tool for us to use when solving various problems.

For what I wanted to do with this test, though, was run a dead-simple query, SELECT GETDATE(); against all seven SQL 2016 instances on my VM.

Using SQLCMD, it took 29 seconds to run, and using Registered Servers, the same query against all seven instances took less than one second. Amazing performance.

I then added a bit of timing metrics to the output of the SQLCMD version, and figured out that SSMS, using Registered Servers, is so much faster because it's a multi-threaded, multi-connecting application. SQLCMD is, but it's very nature, single threaded, due to running all commands in one SPID.

Like I said earlier, I've had great experiences using SQLCMD and use it regularly for a whole host of things, but it's selling point isn't speed.