Solved

SQL Server 2K memory usage

Posted on 2008-06-23
6
490 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
[X]
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
6 Comments
 
LVL 8

Expert Comment

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

Accepted Solution

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

Author Comment

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

Expert Comment

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

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

627 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