SQL Server Performance

Tunning question

Discussion in 'Performance Tuning for DBAs' started by marina_sirota, Aug 9, 2003.

  1. marina_sirota New Member

    Hi, Friends!
    My problem is: Ms SQL 2000, Service Pack 2 (we should install Service Pack3- may it resolve that problem?), 1G RAM, database not so big- about 600M.
    We have an application (very complicated) of our client running on it.After 15-20 minutes RAM reach the limit.
    So what I can do in DB configuration to fix the problem without fixing SQL or stored procedures in application.
    I'm new in Ms SQL, so every advance will be appreciated!!!!
  2. joechang New Member

    sql server is designed to use as much memory as it needs (default setting) without paging. that is, if your system has 1GB, and other apps are using 200MB, sql server will use upto 796MB leaving ~4M free,
    now if you want to leave 300M for other apps, then set the max memory for sql at 700M. if no other apps are on this server, leave it alone

    just because sql is using all the memory is not a problem, you did pay for the memory, why not use it?
  3. marina_sirota New Member

    Sorry for misunderstanding..... The problem is: very bad performance, very slow responce time.
    The problem started when we got a new verion of application.
    I think I should tune it.
  4. bambola New Member

    Open a trace and try to see what is this application doing. Check for duration and locks and
    see if the problem is there.

  5. gaurav_bindlish New Member

    What are the typical operations done on the database? Are you using DTS?

    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  6. rushmada New Member

    Is there any anti virus software scheduled at
    Server and also at client level.
    If so schedule these jobs at late nights.

  7. satya Moderator

    As you've got most of the responses valid, also capture counters using PERFMON or SYSMON for H/w and SQL process. Also make sure no issues with the network and be sure have regular maint.plan & DBCC checks against the database for optimum performance.

    Upgrade it to SP3a for fixes that may help you.
    BTW< what is the OS and service pack level?

    Satya SKJ
  8. marina_sirota New Member

    Hello to all and thank you very much for your responces!!!
    My client is running application on SQL server.
    The application include a lot of stored procedure that just execute SQL statements (no code).
    According to your advices I installed SP3a and I have checked I have service pack 3 for W2K.
    Anti Virus soft will be running only in nights or may be we will install it at all.
    After I'm doing trace I see some transactions running very slowly and taking a lot of CPU.
    I don't have a right to fix an application, but as I can see a lot of additional indexes needed.
    I have a procedure rebuild indexes running every week.
    The question: when I open and application and checking Performace Manager on the server
    I see that CPU utilisation growth dramaticatily, the critical moment is when the application work with a one of queries.
  9. satya Moderator

    THen best bet is to run PROFILER and submit trace to INDEX TUNING WIZARD for index recommendation, which will give you bit of gap to find out other deficiency on the system.

    Also consider using VIEWS whereever necessary to avoid direct table contacts.

    Go with this PROFILER tips link http://www.sql-server-performance.com/sql_server_profiler_tips.asp for information.

    BTW, what is the size of the database (ok got it.. its 600M) and users connected?
    Anytime have you setup "priority boost" configuration option?


    Satya SKJ
  10. marina_sirota New Member

    about 600 MB and about 10 users for now.
  11. satya Moderator

    Then follow as suggested by using PROFILER for those slow running queries with the help of ITW.
    This way you can reduce overload on CPU.

    Satya SKJ
  12. marina_sirota New Member

    Thank you very much!!!!!
    I was really suprised by the amount of quick and helpfull answers!!!!
  13. marina_sirota New Member

    another question about installation of SP3a service pack, just for knowledge:
    how may it help for performance?
  14. satya Moderator

  15. satya Moderator

    May be SP3a specifically doesn't address your issue in terms of PERFORMANCE, but thats for sure few issues with SP3 have been fixed in SP3a.

    Refer thru README.HTML for more information on SP3a fixes.

    Satya SKJ
  16. marina_sirota New Member

    Another question<img src='/community/emoticons/emotion-1.gif' alt=':)' />) sorry for being so curious:<br />Anti Virus Software check the data in the database and reduce performance?
  17. satya Moderator

    Don't be sorry, this forum is intended to help.

    Its a fact running AV on the SQL Server will degrade the performance. If its compulsory and system is exposed on internet, then install anti-virus software, but exclude your database folders from regular scans. Keep your anti-virus signature files up to date.

    Also consider implementing FIREWALL on the network to avoid disaster from virus.

    Satya SKJ
  18. marina_sirota New Member

    Dear Satya!
    You are absolutely genious!!! hope some day I will know about sql server as much as you!
    The last (I hope) question: before I run any query, when I only start application (clicking on *.exe) I see that Sql start to use double CPU amount.
    How do you explain it?
  19. gaurav_bindlish New Member

    True, as Satya said, if the server is behind a firewall, there is no need for antivirus. Antivirus may not directly impact the SQL Server Process but since the ant-virus process itself consumes lot of memory so it should not be installed if avoidable.

    I would also recommend looking athttp://www.sql-server-performance.com/sql_server_performance_audit.asp for checking the server.

    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  20. bambola New Member

    quote:I see that CPU utilisation growth dramaticatily, the critical moment is when the application work with a one of queries.
    What is this query doing? Is it called within a stored procedure? If so you can also rewrite it (if needed) without breaking the application code. or you might be able to improve performance with the right choice of indexes.
    As Satya suggested you can use index tunning wizard. You can also take a look at the execution plan when running the same code from QA.


  21. satya Moderator

  22. marina_sirota New Member

    Thank you for all of you!!!!!!

Share This Page