• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 562
  • Last Modified:

SQL 2005 Express using high memory and cpu

First off let me say that I'm not very experienced with MS SQL.  

We have a server that is running Server 2003 R2 Standard and has MS SQL Server 2005 express.  SQL has been running well for a month or so but now is using a large amount of memory (up to 1.7 GB) and is sometimes using 50-99% of the CPU.  The server has 2 GB memory and is running on a dual core opteron.  If I restart the SQL service for the database, the memory usage will drop but gets up to over 1.5 GB in about a day.  I first noticed it today because the SQL database was not working.  If anyone has some input or suggestions about troubleshooting I would greatly appreciate it.  
2 Solutions
So, regardless of the server it is on, Express will only ever use 1 CPU and 1GB of memory.

I am guessing that you're having some backup/IO issues or you have a rampant set of queries.
hbmonlineAuthor Commented:
I am using backup exec to backup the database.  The backups have been reporting as successful and I have not seen any SQL or backup exec errors in Event Viewer.  Do you have any suggestions on how I can look for the query problem or the backup/IO problem?  Like I said, I'm not very familiar with SQL.  Thanks.
First things, first, get yourself familiar with SQL Server profiler. http://msdn.microsoft.com/en-us/library/ms187929.aspx

Next, take a look at

SELECT * FROM sys.dm_exec_query_stats

this is going to give you some stats from queries that have been cached.  You're looking for a high number of average reads and/or high cpu time.
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.

hbmonlineAuthor Commented:
Is the profiler available for 2005 express?  I downloaded the 2005 express exe again and made sure everything got installed but still cannot locate the profiler.  Is there a separate download location for this utility?  Thanks.
Shreedhar EtteCommented:

Download and install the SQL Server Management Studio : http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

To start SQL Server Profiler from the Tools menu
 In the SQL Server Management Studio Tools menu, click SQL Server Profiler.

I hope this helps,
Chris MConsulting - Technology ServicesCommented:
Unfortunately you cannot connect to SQL express remotely otherwise you would have run Profiler remotely from some other machine.

If you do not have profiler already installed on that server where SQL express is then I suggest that you install management tools for SQL server (either off the developer CD, Standard or Enterprise CD) on your server.

Launch SQL profiler and connect to your SQL express instance. This way you will collect query information which also included memory utilization for critical decision making.

I suspect there are other applications using some of the rest of your RAM (probably IIS or other services) so observe memory usage at process level from task manager.

Lastly, launch Windows performance monitor and see how you're fairing.
With those three tools in combination, pin-pointing the culprit is just moments away.

Good day,
Chris Musasizi
>>Unfortunately you cannot connect to SQL express remotely otherwise you would have run Profiler remotely from some other machine.

Sure you can.

Profiler doesn't come with express, but you can run a server-side trace to accomplish the same thing (its all that profiler does---just creates a trace and reads it).

Also, look at the DMV I pointed out before...its likely going to have all of the info you need to diagnose your problem.
hbmonlineAuthor Commented:
Sorry I did not post back sooner.  

Now that I have educated myself about MS SQL a little more I have found that the server is running ok.  The severe memory spike which made the server become unresponsive seemed to be an isolated incident because it has not happened again.  

I also learned that SQL will take up a large amount of free memory and release it when other applications and services need it.  I was clueless on this fact and thought that the large amount of memory used by the SQL service was abnormal.  The CPU usage fluctuates but does not effect the server's performance.  

A very handy utility I found was AnjLab SQLProfiler.  Since you cannot install the actual Profiler utility from the full SQL disk on an Express installation, this utility gave me virtually the same result and I was able to check out how the server was performing.  I would suggest anyone using SQL Express to give it a try.  

Lastly, I would like to thank everyone for the help and suggestions.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now