SQL Server 2000 Slow response time

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » SQL Server 2000 Slow response time

SQL Server 2000 Slow response time

Rate Topic

Display Mode

Topic Options

Author

Message

Ramesh Lende

Posted Monday, October 08, 2007 6:48 AM

Valued Member

Group: General Forum Members
Last Login: Friday, June 03, 2011 9:11 AM
Points: 64, Visits: 104

Hello,
I am a VB programmer and all of a sudden all the DBA's in my company have resigned.
I have been promoted to take care of DBA activities. I know something about SQL but am not hard core DBA.
And to make situation worst, all of sudden all the users have started reporting that their applications are becoming slow.

Being a front-end programmer, i verified and there doesn't seem to be any issue from front-end. There is no major release
done. We are using a customized healthcare software from a third party company and there is nothing changed to it. So, all
the fingers are now pointing to either databse or network. Network folks are doing their things but i have been asked to
look at databases and report anything weired i could find.

Now, that's the task. How to find out if database is working fine or not. Any ideas/tips from anyone would be greatly appreciated.
Being a lay-man in this area, anything you can suggest would be very helpful. We are using SQL Server 2000.

Thanks,
Ramesh.

Post #407957

colin.Leversuch-Roberts

Posted Monday, October 08, 2007 7:06 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:10 PM
Points: 2,668, Visits: 688

if this company is in the UK I might be available for consultancy
I feel for you and really the best I can suggest is you get hold of Inside SQL Server 2000 or the sql 2000 admin guide form microsoft - this will take you through the basic stuff.
slow down is often down to stats and index rebuilds - but if your dba's were any good they should have put maint plans in place and documented the processes etc. etc.
Never assume the application is ok - experience tells me that most problems come from applications, not the server - I think the accepted figure is 20/80 hardware/software for tuning.
Or get on a sql 2000 admin course - could be tricky now but you'll probably get the best kick start. I'd suggest you get a contract DBA to assit you - to instruct a non dba will be tricky.
check out these in BOL, sp_updatestats, dbcc updateuage, dbcc dbreindex, dbcc showcontig, sp_autostats.
make sure your database is making log backups ( and full backups ) and make sure auto create and auto update stats are enabled.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Post #407970

Steve Jones - SSC Editor

Posted Monday, October 08, 2007 9:57 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 3:26 PM
Points: 31,425, Visits: 13,738

If you're in the US, let me know where. I can recommend a few people as well. Or you can work remotely with Colin.

However you should hire someone. There's too many possibilities. You'd be better off hiring someone for a day or two, working with them to understand what they're looking at and learning. (and documenting in case you leave :) )

Any good consultant should teach you what they're doing. If they don't, hire someone else. Ask the question ahead of time as well.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #408073

Ramesh Lende

Posted Monday, October 08, 2007 10:15 AM

Valued Member

Group: General Forum Members
Last Login: Friday, June 03, 2011 9:11 AM
Points: 64, Visits: 104

Steve and Colin,
Thanks for your reply.
I am leaving in remote area of Pennsylvania where it is difficult to find out one DBA. People are coming and they don’t like the place so they are leaving very soon. My company is trying their best to hire someone at onsite. Due to HIPAA regulations, they would not be interested in remote DBA’s. So I have to try and make sure that at least basics are working fine until they find a real professional DBA.

I am looking at update statistics and re-indexing. Any other suggestions?

Thanks,
Ramesh.

Post #408084

Jeff Moden

Posted Monday, October 08, 2007 8:44 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 32,906, Visits: 26,792

Backups... if for nothing else, to clear log files that are getting big and growing.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #408258

Jeff Moden

Posted Monday, October 08, 2007 8:47 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 32,906, Visits: 26,792

Ramesh Lende (10/8/2007)

I am leaving in remote area of Pennsylvania where it is difficult to find out one DBA. People are coming and they don’t like the place so they are leaving very soon. My company is trying their best to hire someone at onsite. Due to HIPAA regulations, they would not be interested in remote DBA’s. So I have to try and make sure that at least basics are working fine until they find a real professional DBA.

Which city?

--Jeff Moden

Post #408260

~paul hewitt

Posted Tuesday, October 09, 2007 2:24 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, January 17, 2012 2:51 AM
Points: 935, Visits: 251

A couple of slightly off-the-wall causes I have seen before:

- The network guys had made a change to firewall configuration / internal tcp/ip routing which was noticably slowing down the traffic. (They wouldn't tell me precisely what they did, I think they were embarassed cos they initially blamed the applications for causing the problem and I spent a week proving otherwise)

- On one occasion the hard drive of a server was 99% full (mainly due to the guys keeping several months worth of old backup files). Made everything crawl on the server. It was so obvious a problem that no one had thought to check.

Post #408326

Joe B-478020

Posted Tuesday, October 09, 2007 9:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:39 AM
Points: 183, Visits: 225

1. Check available hard drive space on the server drives.

2. Also, if you are running databases in full recovery mode, a database backup does not mark the log file for reuse. Assuming they are set to automatically grow, they will consume all available hard drive space.

At my client, the application slowed when the log file exceeded 45 GB and eventually stopped working because to add 10% (5 GB) to the log file took longer than the application's SQL connection timeout setting.

Usually, log files go to the same folder but it is possible to override the location on a log file by log file basis. Track them down and see how big the log files are.

3. Is it one database that is slow or all of them? If it's a single database, it probably needs its transaction log dealt with, dbcc shrinkdatabase, dbcc reindex, etc. Database Maintenance plans are a handy way to do those tasks. If it's the entire server, it could be free drive space, log file sizes, size of tempdb, etc.

4. Have you added more users, applications, additional server load, etc.? i.e. Do you have enough CPU and RAM for the task at hand?

5. Reboot the server.

Post #408530

MOHD ZULKIFLEE BIN MOHD NOOR

Posted Sunday, October 25, 2009 10:23 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 9:02 PM
Points: 2, Visits: 65

My advise is to get yourself a good performance monitoring tools e.g from Quest, Red Gate, Idera etc that will definitely save your a$$ as a non DBA. You'll notice the problem of your problem from the tools.

I think your previous DBAs had done some query to reindex or defrag the indexes on interval. That's why you found slow reporting services in your company after they left. try looking for that script jobs. Or maybe, you need to refresh the server by restarting it. Look for the DBA's log books.

Post #808483

« Prev Topic | Next Topic »

Permissions