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 :)

asked 24 Aug '16, 08:04

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249
accept rate: 20%

1

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...

(24 Aug '16, 09:19) Volker Barth
2

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.

(02 Sep '16, 07:41) Vlad
Replies hidden

That's interesting isn't it. The old "more is less" problem. Mmmm... I'm beginning to think it best left on the default.

(02 Sep '16, 10:09) Justin Willey

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).

(02 Sep '16, 10:32) Breck Carter

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.

(05 Sep '16, 09:54) Vlad
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×275
×260
×14

question asked: 24 Aug '16, 08:04

question was seen: 1,720 times

last updated: 05 Sep '16, 09:54