Is there a real world benefit (or penalty) from running dedicated SQLA Windows servers with the -gb option set to high? As Breck observed here the documentation is sparse. I haven't been able to discern any particularly good (or bad) effects. We have tended to include it but probably on no more solid basis than it sounds like a good thing. However I'm also aware of the generally very sound SQLA principle of "don't mess with the default settings unless you have a good reason to". It may be that it only comes into play when the CPUs are stressed. As was observed in the old newsgroups, the never ending quest for the undocumented "make everything work better" switch continues :) |
I can't tell - but as you ask for "dedicated servers" and "-gb high" would priorize the dbsrvXX process over processes with "normal" priority - couldn't you have a look at those other processes that would then possibly run "worse" (say, via Sysinternals Process Explorer) ... - and decide if that could be problematic?
On a rather empty dedicated Windows 2012 R2 Server, there are only a few processes with "high" base priority (= 13) and almost none with "above normal" (= 9).
If "high" seems too risky but "above normal" would do (which seems not available as option for -gb), you might also start the dbrsvXX process via the CreateProcess() WinAPI and set the process priority there...
Sometimes, when the process has a priority higher than "normal", the system (or your particular process) might freeze, because all resources were occupied by a random heavy task.
A good guy from SO (http://nickcraver.com/blog/2016/02/17/stack-overflow-the-architecture-2016-edition/#databases-sql-server) said that they try to keep DB's CPU consumption as low as possible. So if you achieve the same with your application, you might not need to play with priorities at all.
That's interesting isn't it. The old "more is less" problem. Mmmm... I'm beginning to think it best left on the default.
A fascinating article, thank you Vlad!
This sentence caught my eye: "The main reason we’re on Elasticsearch instead of something like SQL full-text search is scalability and better allocation of money" ...search sucks on DCX and this forum, and this might be the explanation (even though SO uses SQL Server).
I am glad that you found the article helpful. I usually know the people's opinion that if the database should do as much work as possible (e.g. the business logic should be executed on the DB side).
Regarding the full-text search engines. There are many opensource products that worth looking at them. E.g. Apache Lucene (or Solr). Big web shops use them, and this is much more efficient/flexible than SQL.