SQL Server Two Minute Memory Tune-up

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.

Neil Boyle
Neil Boyle
Neil Boyle left school at the age of sixteen thinking that computers were things that only existed in Star Trek. After failed careers as a Diesel Mechanic, Industrial Cleaner, Barman and Bulldozer Driver he went back to college to complete his education. Since graduating from North Staffs Poly he has worked up through the ranks from Trainee COBOL Programmer to SQL Server Consultant, a role in which he has specialised for the past seven years.

Latest Articles