Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Troubleshooting SQL Server with the Sysperfinfo Table

By Joseph Sack, (first published: 2003/05/14)

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.
Total article views: 32214 | Views in the last 30 days: 13
 
Related Articles
FORUM

Max Sql Server Memory Vs Buffer Pool

Max Sql Server Memory Vs Buffer Pool

FORUM

counters

counters help

BLOG

Querying Performance Counters in SQL Server

In a previous post, there was a comparison between sysperfinfo and sys.dm_os_performance_counters wh...

FORUM

Limit buffer pool memory amount by database

Limit buffer pool memory amount by database

FORUM

Clearing adhoc plans from the memory CACHESTORE will be useful for the data buffer memory

Clearing adhoc plans from the memory CACHESTORE will be useful for the data buffer memory

Tags
performance tuning    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones