I am often asked what is the optimum value to put in the “Maximum Nodes in Memory Cache” setting in the Site Configuration Application Tab 2 named Cache in your Production environment. The idea is that the node cache is big enough to hold a 'copy' of all the nodes for your entire site, so the suggested size calculated from following script is the one to go for.
However, if you allow authoring on the site, you will need to increase the node cache accordingly.The only real way to determine this value is by some base-lining and further
testing, but trial and error can work too - :).
But be aware that you should not increase beyond 35000. Also be aware that 35000 is a number that works fine on most box with 2 GB memory. Be aware that there is no way to give a real number as the size of a node can vary. If you have many postings with only very small placeholder content you might run fine with 50000. If you have many postings with huge placeholder content (only text content matters - not binaries) then a value of 20000 might be the upper limit.
The following SQL query calculates the number of items in the Content Management Server (CMS) database that must be accounted for when you configure the size of the node cache. To run this query, point your SQL Query Analyzer at your CMS database, and then paste and run the following SQL script in the SQL Query Analyzer window.
NOTE: Microsoft strongly recommends that you set your node cache to at least the number that is returned by this script. If constant authoring and updates occur on your site, increase the node cache size accordingly.
Thanks Stefan for pointing out that this is an old script and I am updating this POST. Here is the link to support article as well ...
Script from Microsoft Support to Calculate Node Size