Post reply

Slow SQL Server 2000 Performance

  • 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

  • March 3, 2004 at 4:03 pm

    #497118

    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

  • March 4, 2004 at 1:49 am

    #497169

    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

  • March 4, 2004 at 3:03 am

    #497196

    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.

     

  • March 4, 2004 at 7:36 am

    #497281

    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.

  • March 4, 2004 at 11:22 am

    #497365

    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)

     

  • March 7, 2004 at 12:31 am

    #497716

    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

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

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