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.
