SQL Server Two Minute Memory Tune-up
August 20, 2000
You cannot do a proper tuning job on SQL server in two minutes, but you can get a good idea of how well a server is set up. This article will show you how to quickly evaluate the efficiency of memory usage on your server.
The script I use works both on SQL 6.5 and 7, though the results you get and the actions you can take differ for each version. Some of the commands are in SQL 7 for backward compatibility only, and MS recommend you use Performance Monitor instead, but I still like to use these commands to get a quick idea of what is happening.
Here is the script:
If these figures do not look so good, the first place to look is the "Sp_configure memory" command to see if SQL Server has enough memory allocated to it. This is particularly important with 6.5, which has a very low default setting, but less likely to be the cause of problems with SQL 7, which manages it's memory automatically. See the "Causes of cache confusion" at the bottom of this article for other possible causes.
How much memory you allocate to SQL Server 6.5 depends on what else your server is doing as well as the total available memory. This Microsoft article contains guidelines and example memory allocation settings.
SQL 7 users be warned-- this article
says not to run the
Take care when reducing this setting--setting procedure cache too low can kill your server. Reduce it slowly and carefully, and never ever set it to zero. Here is a Microsoft article on the subject.
Causes of cache confusion
The most obvious reasons for inefficient cache usage are lack of available RAM and poor memory configuration, but before you rush out and buy some new DIMMS think about some other possible causes.
Here is a good article from Microsoft on the importance of good database and query design.
Finally, remember that caching statistics are meaningless if your server has just started. Cache efficiency increases over time, so let your server "settle down" into it's normal usage pattern before you try to measure efficiency.
Other memory considerations
Cache is one of the most important uses of RAM, but it by no means the only important one, and SQL Server allocates memory to cache only after other requirements are met.
It follows that you can increase or decrease the amount of RAM available for cache by adjusting the memory allocation for other aspects of SQL Server, particularly with version 6.5, which allocates memory in a less dynamic way than later versions.
Some example settings you can change are Sort Pages (6.5 only) Index Create Memory (7 only) Locks, Open Objects, Tempdb In RAM (6.5 only, and usually not recommended) There are others too, and as you read more in Books Online or http://support.microoft.com Be warned though: an incorrect setting for any one these can kill your server, so read up first, and adjust things slowly and carefully.