We help IT Professionals succeed at work.

mystery background process?

fhcdaver
fhcdaver asked
on
774 Views
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?  
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.
fhcdaverpresident

Author

Commented:
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.

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
fhcdaverpresident

Author

Commented:
Hi,

In the Server > properties > memory it shows:

"Maximum memory (in MB) 2147483647"

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

thAnks
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
yes
fhcdaverpresident

Author

Commented:
Hi,

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?

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.