SQL is no exception, in fact it will happily consume all the resources you grant it – at the risk of starving other systems, including the OS.
You can control how much memory SQL can use through the MaxServerMemory property. Setting it too low, means you are throttling SQL – setting it too high and you are throttling the OS. Despair not, help is near: Here is a blog post written by Tara Shankar Jana – with a script, that will give you the optimal setting.
CPU – Priority boost
It may be tempting to give the SQL process a priority boost. But don’t do this! Ever! Doing this will starve any other process (including the OS processes), and it will not make the system perform better – in most cases it will be significantly worse.
Here is a blog post by Arvind Shyamsundar on the topic: Priority boost details and why its not recommended
If you want to disable priority boost, you can do it using this sql script:
sp_configure 'priority boost', 0 reconfigure with override
One more thing…
Since you are reading this, you probably want to get the best performance out of SQL on the hardware available. Check one more thing: The power plan! You likely bought the hardware for your server to use it, so make sure to set the power plan to High performance – also on any VM hosts.
What has this got to do with Dynamics AX?
Nothing and everything. The three guidelines above apply to any use of SQL Server – including when SQL is used with Dynamics AX.
I recently visited an AX customer with performance problems. It turned out that SQL was granted 100% of the memory on the box, it was set to run with priority boost, and the power plan was set to balanced. The first two due to best intentions, the last due to this being the default setting. This starved the OS for resources, making overall performance of the system unpredictable – some simple queries would take seconds to complete, and blocks were observed too. Getting these settings right fundamentally changed the behavior – it was like night and day.
Kudos to Tara for educating me on these topics.