Solved

SQL Server 2K memory usage

Posted on 2008-06-23
6
470 Views
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?
0
Comment
Question by:rlstewart
  • 2
6 Comments
 
LVL 8

Expert Comment

by:pzozulka
Comment Utility
Try using the "sp_lock" command, followed by "sp_who2".
0
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 500 total points
Comment Utility
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.)
0
 

Author Comment

by:rlstewart
Comment Utility
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?
0
 
LVL 13

Expert Comment

by:MikeWalsh
Comment Utility
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.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now