Slow SQL Server 2000 Performance

Question

Slow SQL Server 2000 Performance

davidschuster

Old Hand

Points: 399
More actions
March 3, 2004 at 2:42 pm

#109070

I have a server running SQL Server 2000. There are about 10 databases on this server. The response time of the application using these databases is very slow. How can I troubleshoot within SQL Server to determine which database is causing this problem? How can I tell what stored procedures are being used and how long is each one taking?
Any help would be appreciated.
Thanks
David

Viewing 7 posts - 1 through 6 (of 6 total)

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

ThomasH SSCertifiable Points: 7437 More actions · Answer 1

SQL Server "Profiler" is the generic answer to your generic question. I'd much rather see you target very specific areas of performance concerns... particular query, particular procedure, application connection time, application load time, particular application form load time, etc. Mainly just to determine if the issue is application side or SQL server side. Also, was the performance good at one time, and has gotten worse over time, or is it the initial performance is not good?

Once you understand the BITs, all the pieces come together

Alamir SSCertifiable Points: 6085 More actions · Answer 2

what is your server amount of RAM ? bye the way MS SQL Server is a monster in dealing with RAM .. and if you give him more RAM .. SQL Server will give you Higher performance ..

Alamir Mohamed
Alamir_mohamed@yahoo.com

tverweij SSC-Addicted Points: 429 More actions · Answer 3

Try the following:

1. Reindex the database:

--START SCRIPT

declare @table sysname

create table #tmp(TABLE_QUALIFIER sysname ,

TABLE_OWNER sysname ,

TABLE_NAME sysname ,

TABLE_TYPE varchar(32),

REMARKS varchar(254))

insert into #tmp(TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, REMARKS)

exec sp_tables null,null,null, "'TABLE'"

declare x cursor for

select

TABLE_NAME

from

#tmp

open x

fetch next from x into @table

while @@fetch_status = 0

begin

dbcc dbreindex(@table)

fetch next from x into @table

end

close x

deallocate x

-- END SCRIPT

2. update the page usage: DBCC UPDATEUSAGE(0)

3. create the statistics:sp_createstats

4. update the statistics:sp_updatestats.

davidschuster Old Hand Points: 399 More actions · Answer 4

Tom,

The performance was good at one time and has gotten worse in the past few weeks.

How do I target specific areas like queries, procedures, application load and connection?

My network engineer is in the process of applying SP3a.

me-129624 Old Hand Points: 329 More actions · Answer 5

At the server level, if you do not know which db to trace, try

Using perf mon is also a good idea if you are on W2K server and you can watch for full table scans - if it is more than 1 ot 2, u know that queries need to be optimized - they should make use of available indexes.

cache hit ratio (also thro perf mon) will tell you if you need to add more memory (ideally it should be a high number)

Alamir SSCertifiable Points: 6085 More actions · Answer 6

of course apply SP3a .. it solve a lot of several pugs in SQL Server 2000

did you make a scheduled maintainance plan to rearange your indeses and upade statistics .. if not .. do it now and make it work daily.

I hope this help u

Alamir Mohamed
Alamir_mohamed@yahoo.com