Step 3: Memory consumption

Learn a good way to measure macro-level memory usage in SQL Server 2000 in this step.

The actual amount of memory that SQL Server uses may be deceiving because, by default, SQL Server is configured

to capture a high percentage of the total memory on the server. That feature is combined with SQL Server's ability to ensure Windows and other applications installed on the server have sufficient memory. If you are familiar with your applications, you can limit the amount of memory that SQL Server dynamically consumes based on the Memory tab of the Server Properties interface in Enterprise Manager.

A good measure of the macro-level memory usage can be achieved by reviewing the following Performance Monitor counters:

  • Memory: Available Bytes
  • Memory: Pages/sec
  • Process: Working Set
  • SQL Server: Buffer Manager: Buffer Cache Hit Ratio
  • SQL Server: Buffer Manager: Total Pages
  • SQL Server: Memory Manager: Total Server Memory (KB)
  • SQLServer:Cache Manager - Cache Hit Ratio - _Total
  • SQLServer:Cache Manager - Cache Pages - _Total
  • Memory: Page Reads/sec
  • Memory: Page Writes/sec
  • Memory: Page Input/sec
  • Memory: Page Output/sec
  • Paging File: % Usage


SQL Server Properties: Memory


Hunt down SQL Server performance problems

 Home: Introduction
 Step 1: CPU usage
 Step 2: Disk IO queuing
 Step 3: Memory consumption
 Step 4: Network bandwidth
 Step 5: Transaction-level performance

ABOUT THE AUTHOR:   
Jeremy Kadlec
Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.
Copyright 2005 TechTarget
This was first published in December 2005

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close