mystery background process?

Posted on 2007-09-28
Medium Priority
Last Modified: 2008-01-09
I am running SQL Server 2005 Developer version 9.00.2050 on a test system.    It's a busy little system with 18 databases living under one instance of SQL Server.   frequently, when I am working/testing an app, etc, the system seems to grindd to incredibly slow speeds, even when there is nothing else running except SQL.  A simple query takes 90 - 120 seconds, even in the Management Studio.  I have up to date virus scanner and spyware running and I belive the system is "clean".  I look at the windows task manager and see sqlsrvr running, doing something.  The hard disk is getting pounded.   I can fire up SQL Profiler, and it doesn''t show anything external to my tasks at hand.  Yet, I know those tasks are absolutely not resource intense.  

I suspect the Engine is doing some sort of background cleanup process.  But I can't figure out what, or even how to look to see what irt may be doing.   Any ideas what to look for?  Any tools I can use to  find the culprit?  
Question by:fhcdaver
  • 4
  • 3
LVL 27

Assisted Solution

ptjcb earned 160 total points
ID: 19980449
This link has a lot of information on what to check for performance issues.

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19981838
how much RAM does your machines have?
what is the setting of "max memory setting" for the sql server?
what other applications are running on that box?

how big are the transaction log files of your databases?
what is the recovery model of all your user databases?
do you have some regulary full and/or transaction log backups running on all those databases?

yes, I know, lot's of questions.
but those are the basics to see if the minimum config is right or wrong.

Author Comment

ID: 19982051
This is a test/development machine.  

1 gig of ram.   (it's at my desk)
Server Properties:  Mim memory = 0, max is  2147483647
Other apps runing on this box?  just about anything, but frequently, Access (client to my sql databases, usually), SQL Management Studio, Outlook,

The databses run all around 150 - 200 meg (max) and transaction logs are not especiallylarge.    While there are 18 databases in this SQL server instance, I'm only actually working in one at any gien time, the others are static.  So, I don't regularly back them up unless I'm working in them.

REcovery models:  15 of 18 are simple, 3 are full
Automatic Options:  All have auto create statistics, auto update statistics.  A few have auto close, one has auto shrink.

I probablay can delete some of these, but I think that's still just hiding whatever my real problem is.

thanks for your help.

I will also check out the MS link.

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 340 total points
ID: 19982126
ok, change the max memory setting from the 2GB to 400MB. that should help.

Author Comment

ID: 19991175

In the Server > properties > memory it shows:

"Maximum memory (in MB) 2147483647"

So are you saying to change it from 2147483647 to just 400?

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19991583

Author Comment

ID: 19999807

That appears to have helped, although it's still "sluggish".  That is, I don;t see sql going off into 2 and 3 minute siestas anymore, but knowing what the tasks are that I'm running it's still a bit slower than I'd like.  do I bump up that 400 to something else? or look to other areas to tune?

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20000065
if it's still slow, I would consider getting more RAM -- ie 2GB  -- for your machine... and then bump up the 400MB to 1000MB for example.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

862 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