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.
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
command on SQL 7. I have never had
problems with it myself, but I would advise you not to use it on production
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.