Is Multi-Server Query Parallelized? Yes and no…

I was asked about the post I did on the multi-server query feature we put in SQL Server Management Studio 2008. Because it seems to render on the screen in a sequential fashion, the user thought the queries were issued one at a time from the Registered Server group. The answer is "No", sort of.

So here's what really happens: There is a pool of about 50 or so threads per CPU that we have to work with for those connections. We asked for all the servers at once, and that request is assigned to one of those threads, if it's available. That means that if you had two processors and weren't doing much else, you would see 100 queries issued almost simultaneously - which works out to be pretty parallel. The effect of painting the screen ends up being sequential because we hold the results until we get them all back.

And there you have it.

(Bonus tip: This feature works faster on systems with mulitple procs, as you can see.)