Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2000 Slow response time Expand / Collapse
Author
Message
Posted Monday, October 8, 2007 6:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 3, 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
Posted Monday, October 8, 2007 7:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
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
Posted Monday, October 8, 2007 9:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:42 AM
Points: 31,284, Visits: 15,747
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
Posted Monday, October 8, 2007 10:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 3, 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
Posted Monday, October 8, 2007 8:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 35,592, Visits: 32,181
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #408258
Posted Monday, October 8, 2007 8:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 35,592, Visits: 32,181
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #408260
Posted Tuesday, October 9, 2007 2:24 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!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
Posted Tuesday, October 9, 2007 9:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 3:29 PM
Points: 194, Visits: 250
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
Posted Sunday, October 25, 2009 10:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 25, 2014 10:25 PM
Points: 2, 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.
Post #808483
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse