When given a choice between using GUI tools and using Transact-SQL, I choose

the latter whenever possible or practical.  This isn’t from a sense

of technical superiority, but rather a need to counteract my lazy nature. 

This article will briefly describe a few queries that I use to troubleshoot

memory bottleneck issues that are normally identified using System Monitor

(Performance Monitor).  System Monitor is useful for tracking trends

over time (using counter logs), however sometimes I like to see snapshots

of the current state of a SQL Server Instance.  Using Query Analyzer,

you can add or integrate these queries I detail into your own Transact-SQL

script library or procedures as you see fit.

SQL Server 2000 memory address space is made up of the memory pool and the

executable code pool. The executable code pool contains memory objects such

as loaded OLE DB Provider DLLs for distributed queries, extended stored procedure

DLLs, and executable files for the SQL Server engine and net-libraries.

The memory pool contains the various system table data structures; buffer

cache (where data pages are read), procedure cache (containing execution

plans for Transact-SQL statements), log cache (each transaction log for each

database has its own cache of buffer pages), and connection context information. 

The memory pool is often the highest consumer of memory for busy SQL Server

instances.

Generally speaking, I've identified most "true" memory bottleneck issues

via errors that manifest in the SQL Log. For example, a user may submit a

prepared statement with an enormous IN clause.  In such a scenario,

we may see an error such as "Failed to reserve contiguous memory of Size=XXXX".

When I see this error, I like to run a few different queries in Query Analyzer

to pinpoint any abnormally high or low numbers.

In all of these queries, I use the sysperfinfo system table.  This table

is used to store internal SQL Server performance counters – the very same

counters that are retrieved by using System Monitor.

When investigating a potential memory bottleneck scenario, I begin by checking

the total memory used by the SQL Server executable.  For a default instance

of SQL Server I execute:

SELECT  cntr_value/1024 as 'MBs used'
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Memory Manager' and
   counter_name = 'Total Server Memory (KB)'

For a Named instance, I use the following code instead, where InstanceName

is the second part of your Named Instance name, for example SERVERNAME\INSTANCENAME:

SELECT  cntr_value/1024 as 'MBs used'
from master.dbo.sysperfinfo
where object_name = 'MSSQL$InstanceName:Memory Manager' and
   counter_name = 'Total Server Memory (KB)'

This query returns the total MBs used by SQL Server.  Of course, this

number can fluctuate from second to second. Using the System Monitor may

become necessary in order to track trends in memory utilization, in which

case you could create a counter log (not covered in this article).

When viewing the total server memory, lets start with the obvious questions…

Is the total MB used by SQL Server less than the maximum available? 

Maximum memory usage should cause you to dig further.  Less than maximum

should also cause concern if your SQL Server instance is on a machine with

other applications (not recommended). SQL Server may not be reaching its

potential if it has to compete for resources.     

This next query is used for returning the size of the buffer cache, procedure

cache, and free pages in MBs for a Default instance. For querying Named Instances,

remember to replace 'SQLServer:Buffer' with 'MSSQL$InstanceName:Buffer Manager'.

SELECT  'Procedure
Cache Allocated',
     CONVERT(int,((CONVERT(numeric(10,2),cntr_value)
* 8192)/1024)/1024)
as 'MBs'
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Buffer Manager' and
   counter_name = 'Procedure cache pages'
UNION
SELECT  'Buffer Cache database pages',
     CONVERT(int,((CONVERT(numeric(10,2),cntr_value)
* 8192)/1024)/1024)
as 'MBs'
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Buffer Manager' and
   counter_name = 'Database pages'
UNION
SELECT  'Free pages',
     CONVERT(int,((CONVERT(numeric(10,2), cntr_value)
* 8192)/1024)/1024)
as 'MBs'
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Buffer Manager' and
   counter_name = 'Free pages'  

Regarding these results returned from this query, keep watch for very high

or low numbers.  For example, with “contiguous memory” errors look out

for a large buffer cache coupled with a small procedure cache (small being

relative to your query activity, of course).  Sometimes prepared statements

or other user queries may suffer when the procedure cache is unable to expand

due to fully utilized buffer caches.

This is by no means a full account of SQL Server memory bottleneck investigation

methodology, but rather a helpful technique that you can use in your troubleshooting

toolkit.