mfp: SQL–More memory and CPU is not always a win

[Blog bot]

Источник: http://blogs.msdn.com/b/mfp/archive/...ays-a-win.aspx
==============
All computer programs will run better when adding more memory and CPU cycles – right?  Not necessarily true.

Assuming everything else is equal, then more memory and CPU will be a win; however, all computer systems have finite resources, also memory and CPU. Granting more to one program (or service), will take it away somewhere else.

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.

MaxServerMemory

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.

Cindy Gross has written a blog on the topic.

 

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.




==============
Источник: http://blogs.msdn.com/b/mfp/archive/...ays-a-win.aspx

[gl00mie]

Из серии Windows Server Administration for Dummies.

PS. Я по заголовку сначала подумал, что будут рассуждения о том, как SQL Server не может нормально использовать терабайт оперативки...

[Vadik]

И все равно - много ресурсов и кривой сетап лучше чем просто кривой сетап