Go Premium for a chance to win a PS4. Enter to Win


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
  • 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: http://www.sql-server-performance.com/tips/sql_server_performance_monitor_coutners_p1.aspx

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

972 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