Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


SQL Server 2K memory usage

Posted on 2008-06-23
Medium Priority
Last Modified: 2012-08-13
Typically when our system is running normal we notice SQL server uses around 200-300 megs of memory on our server. However, whenever our website experiences SQL Server time outs, the memory usage on our server goes up to 1.7 gigs.

How can we find out what is causing the memory usage to spike to those level?
Question by:rlstewart
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 21848050
Try using the "sp_lock" command, followed by "sp_who2".
LVL 13

Accepted Solution

MikeWalsh earned 2000 total points
ID: 21848085
Well first you will want to see if SQL is using that memory. Go to task manager and sort by memory usage. Is it SQL? How large is your database? How well written are your queries? (A lot of table scans, a lot of data churn). How is your procedure cache reused? Check out this link to look at some performance counters to look at in performance manager:

Look at the memory counters and cache/buffer counters (What is the buffer cache hit ratio around the time you experience this?)

What kind of queries are being run? Are you bringing a lot of data in? 1.7GB is not a lot of memory and my guess here is that SQL Server is using as much as it will use when this happens. You probably have 4GB of RAM and are not using the /3GB switch I presume?

You may need to look at more RAM depending on your findings, you may need to tune your queries or improve disk I/O performance (Split log files to different drives, increase number of spindles, etc.) There are variables at play that will determine if you can use more memory (Edition of SQL Server 2000, Edition of OS, etc.)

Author Comment

ID: 21850202
I found part the answer i was looking in the activity monitor. Here it listed the CPU and memory usage of individual users connected to sql server. But this only seems to list part of the memory that is being used  by sql server.

Anyone know what else I can used to determine how memory is allocated within SQL server?
LVL 13

Expert Comment

ID: 21851338
rlstewart -

I was explaining to you how you could see where the usage of memory on the server was. I also pointed you to some performance counter information that will help you.

Memory within SQL Server is not allocated at the per query level per se. There is connection memory to manage user connections (small portion), lock memory (generally low unless you have abnormal locking behavior or very high transaction count), and various caches. The two major users of memory are the Buffer (data) Cache - This is what holds memory that is used by SQL Server to keep data in cache after it has been read from disk. This means that subsequent requests for the same data to read (or even write) can happen in Memory rather than to disk directly (obviously writes are eventually pushed to disk but reads can stay in cache as long as they are being accessed). This is probably the major consumer of memory here. You are probably storing a lot of data for your database (though I don't know, I asked a few questions and the answers to those will determine next steps).

The other major memory consuming cache is the procedure cache. This is where your execution plans for queries and procs is stored. Rather than recompile a plan and do the cost benefit analysis of various options, the cache will store a good plan found fast and subsequent executes of the same stored proc and query pattern will use that cached plan. This saves time and cpu cycles during execution.

These are not bad things by themselves. So rather than focus on trying to find out who is using the memory I would focus on why you are hanging. I am guessing that the memory increase is just a symptom of either high activity (which would also mean more memory pressure), some serious blocking (again could cause more memory to be used) or some other symptom.

So I would look for blocked processes as indicated, run some SQL Server performance counters (as linked) and do some methodical analysis of the problem not the memory.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question