SQL Server Two Minute Memory Tune-up

August 20, 2000

Introduction

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:

dbcc traceon(3604)

go

dbcc sqlperf(lrustats)

go

dbcc bufcount(1)

go

-- dbcc memusage 
-- Comment out above
-- for SQL Server 7

go

dbcc proccache

go

dbcc traceoff(3604)

dbcc traceon simply turns on the output for some of the subsequent commands--without this you will not see all the results you need. The Traceoff command at the end turns this feature off again.

dbcc sqlperf(lrustats) outputs details of the cache performance. The most interesting figures are the "cache hit ratio"--which should be near to 100% as possible, and "cache flushes"--which tells you the number of times that data in cache has been paged out to disk to make room for other data. In an ideal world this would be zero, but if your database sizes add up to more memory than you have physical RAM allocated to SQL Server then this will not usually be the case.

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.

dbcc bufcount(1) SQL 7 users can skip this one as it's configured automatically. SQL 6.5 users should look for a line like "The Average Chain Size is: 2.922601" in the output. This indicates the efficiency of the cache indexing structure--values between 2 and 4 are OK with 3 being the optimum in SQL 6.5. The adjuster for this is "sp_configure hash buckets", and because this is poorly documented in SQL 6.5 Books Online many sites still have the default setting--Read more about this setting at the Microsoft support site.

dbcc memusage details the largest tables currently in your cache. Ideally these should be pretty stable, so make a note of what objects are in cache, and the space they take up, over a few readings. SQL 6.5 will also detail the largest Stored Procedures in your Procedure Cache - more on this below.

SQL 7 users be warned-- this article says not to run the dbcc memusage command on SQL 7. I have never had problems with it myself, but I would advise you not to use it on production servers.

dbcc proccache gives more succinct details about the Procedure cache usage. Again SQL 7 handles this pretty well on it's own, but SQL 6.5 usually needs some help. This is because SQL 6.5 by default allocates 30% of it's total cache memory to handling (mostly) stored procedures, while the rest is give over to data cache. With large memory configurations this can result in a lot of wasted space in Procedure Cache that could be better used more effectively as Data Cache. The adjuster for this is "sp_configure procedure cache".

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.

• Missing Indexes can cause unnecessary table scans, which clear other data out of cache. SQL 7 users can consider using SQL Profiler and the Index Wizard to spot table scans and improve index efficiency. Unfortunately SQL 6.5 users have to do this the hard way.
• Poor database design and poor query design can lead to table scanning, excessive disk IO and inefficient memory usage.
• Mixing databases with OLTP (short transaction) and OLAP (long report) characteristics on the same server can cause inefficient cache use and other problems

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.