SQL Server 2000 Slow response time

Question

SQL Server 2000 Slow response time

Ramesh Lende

Ten Centuries

Points: 1034
More actions
October 8, 2007 at 6:48 am

#178633

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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

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.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

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.

Ramesh Lende Ten Centuries Points: 1034 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

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

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

~paul hewitt SSCommitted Points: 1933 More actions · Answer 6

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.

Joe B-478020 SSC Eights! Points: 966 More actions · Answer 7

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.

MOHD ZULKIFLEE BIN MOHD NOOR SSC Journeyman Points: 76 More actions · Answer 8

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.