SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2000 Slow response time


SQL Server 2000 Slow response time

Author
Message
Ramesh Lende
Ramesh Lende
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 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.
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12125 Visits: 715
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/
Steve Jones
Steve Jones
SSC Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: Administrators
Points: 145911 Visits: 19425
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 Smile )

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
My Blog: www.voiceofthedba.com
Ramesh Lende
Ramesh Lende
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214202 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214202 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
~paul hewitt
~paul hewitt
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1111 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.
Joe B-478020
Joe B-478020
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 262
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
MOHD ZULKIFLEE BIN MOHD NOOR
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 66
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search