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.  
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.