SQL Server 2K memory usage

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?
Who is Participating?

Improve company productivity with a Business Account.Sign Up

MikeWalshConnect With a Mentor Commented:
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.)
Try using the "sp_lock" command, followed by "sp_who2".
rlstewartAuthor Commented:
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?
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.