Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2002
    Location
    Libya
    Posts
    50

    Unanswered: Low performance MS SQLServer2000

    Hi,
    My application is working on my dedicated server PIII 1.2GHz 256MB RAM on MS SQL-Server 2000.

    I have a very low performance where my application is working very slow, some times I get a time script out error message.

    My server is consuming 200 MB of bandwidth per month.

    Is SQL Agent affecting the performance?

    Any tips to increase performance are welcomed.

    Thank you in advance

    Khalid
    Khalid Yousef

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Post more info. All I see 256 MB RAM is low.

  3. #3
    Join Date
    Apr 2002
    Posts
    20

    Re: Low performance MS SQLServer2000

    Have you upgraded from SQL7? I've noticed indexes may need to be reevaluated when going to SQL2K.

    --jfp

  4. #4
    Join Date
    Jan 2002
    Location
    Libya
    Posts
    50
    I did not upgrade from SQL7, I have started on SQL2000.
    Also, I did update statistics of all databases, but still there is slow performance.

    I have 16 database working on MS SQL2000, all in size are 2GB.
    I had tried the Index Tuning Wizard previously and worked with the findings.

    What kind of info is needed?
    Thank you both for your interest in my problem

  5. #5
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    What kind of application: LAN/20 stable users x ASP/10000 random users
    Type of major opperation: insert/update/select/combination
    and so on...

    Use Task Manager, Performance Monitor and SQL Profiler to find the source of your problem on the server.
    If you have LAN clients, update their software with the latest MDAC and configure default SQL TCP/IP interface only.

    SQL tuning is overall network software/hardware tuning.
    Sometimes you must buy a new server or advance your hardware, othertimes it is an algorithm/locking problem.

  6. #6
    Join Date
    Apr 2002
    Posts
    20

    Re: Low performance MS SQLServer2000

    Sometimes the first guess is the best. Why do you think it is SQL Agent? What's running? Not Update Statistics I hope. DB maintenance needs to be done off hours and will cause locking.

    I'm with ispaleny, run SQL Profiler during a period of slowness and watch what is happening. You have very small databases (although a lot of them for some reason) which should have no problems processing quickly.

    What is the running script which had timeouts? What else was running?

    --jfp

  7. #7
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    16 databases x 2GB = 32 GB for your database-server. I would say that running this on 256MB of RAM would be a serious bottleneck but then again, with only 200MB of datatransfer/month I'm a bit puzzled. With RAM beeing so cheap these days I would definetly buy some just to be sure that this wasn't my problem, but you also might want to take a look at the memory-stuff in Windows Task Manager like ispaleny says. Take a look under "Processes" and check how much ram "sqlservr.exe" is using and compare it with your total amount of RAM. If it uses more than 150MB I would definetly buy some more. Offcourse you also need to follow the other recomandations provided here...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  8. #8
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    well RAM is one very important thing. also the number of users connecting to the server and the type of connections also slows up.

    check this site there are some amazing tips there.

    http://www.sql-server-performance.com/

    ciao,
    Nihar

  9. #9
    Join Date
    Jan 2002
    Location
    Libya
    Posts
    50
    Hi again,
    Thank you all for following the issue.

    Ispaleny, my application is ASP working on SQL-Server 2000 with major operations: INSERT/UPDATE/SELECT.
    I will run some counters on the Performance monitor to investigate.

    JFP, well, SQL Agent is running to executed my sheduled jobs which run at 01:00 am where it is not peak hours. The scheduled jobs are DBMaintenance done once per week, every Sunday and the rest are some daily calculations from my log files to descripe my traffic, it all takes 5 minutes running.

    Frettmaestro, I have 16 databases all together are 2GB in size, so this could solve your puzzle!, I agree to add more RAM since "sqlservr.exe" is using 160 MB of memory.

    Nihar, thank you for providing the link, it seems so interested.

    I will run some counters and present my results.

    Thank you for your time.
    Khalid Yousef

  10. #10
    Join Date
    Dec 2002
    Posts
    63
    Another potential bottleneck could be your hard drive. During your peak usage, run a perfmon and check the disk que counters. If you see high ques, you may want to consider some type of raid solution. Not sure what you are running now though.

  11. #11
    Join Date
    Feb 2003
    Posts
    109

    most importantly

    you should take your dynamic UPDATE INSERT statements and change them into Stored Procedures.

    you can get a 10-fold increase in performance (well i dont know about 10-- but A LOT FASTER)
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  12. #12
    Join Date
    Feb 2003
    Posts
    109

    AND YOU NEED MORE MEMORY

    and there isnt a single reason in the world that you dont have 2gb memory.

    especially if it is pc133-- you could buy that much memory for $300-$400.
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  13. #13
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    i had a similar problem check this link:

    http://www.vbforums.com/showthread.php?threadid=231284

    i ended up, upgrading the ram****(very important) as well as running some performance tuning methods.

  14. #14
    Join Date
    Mar 2003
    Location
    Grenoble - France
    Posts
    2

    Re: Low performance MS SQLServer2000

    My reply might be stupid (I'm quite new to SQL), but the only time I got significant performance gain with SQL SERVER (divided CPU usage per about 10 times) was by creating the proper indexes, after analyzing all the WHERE and ORDER BY clauses in the main requests.
    (Actually, I have created an index for each column or couple of columns used by important WHERE an ORDER BY clauses. Order of columns in indexes also dramatically affect performances in my database)


  15. #15
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Hi

    Do yourself a favour, turn on Profiler and set a filter on duration (minimum) to something like 2000 (2 seconds). I myself get calls from users saying this is slow or that is slow. So I figure that end-users are happy with about 2 second response time from the database (network is another issue).

    Capture which queries are over this threshold, and then run them through Query Anaylzer with "Show Excecution Plan". You may find that indexes that you thought would be used are not. I have increased performance by taking a single query and breaking it down into 2 with use of a temporary table. Of course designing queries to use stored procedures gives you this flexiblitly.

    If no queries appear to take longer then the threshold you will need to look else where (network).

    But first if something is slow find out what is slow.
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •