Post reply

General performance tuning

  • October 18, 2006 at 8:47 am

    #114710

    Hi,

    This is a general question on performance tuning.

    My company has developed an client-server application in VB.NET and the server environment are Windows 2000 SP4 and SQL Server 2000 SP3. Our application runs on the server together with SQL Server. When running the application and SQL Server during production the server load is too high, so that the application is not working properly.

    Question 1: Would the installation of SP 4 and all hotfixes improve the SQL Server performance?

    Question 2: Are there any first step common practices I should check in order to increase the performance?

    I understand there are a lot to do regarding performace and I am trying to grasp the basics here. So any tips are useful to get me on my way.

    Grateful for answers

    /M

  • October 19, 2006 at 12:34 am

    #666707

    Dear All,

    There is a demo for performance tunning on

    http://www.sqlserverfaq.com/ and lots of tips which will give you help.

     

    Have a Nice Day...

     

    Regards,

    Chirag

     

  • October 19, 2006 at 2:57 am

    #666728

    Thank you for your answer... Good to have some demos to look at!

    I have two more questions (if some other would like to answer the first, go ahead):

    Question 1: What percentage of CPU usage would be preferred on a SQL Server for best performance (30%, 10%, 50%)?

    Question 2: The application database has approximately 50 tables but no relations and reference keys at all! This is bad, but how does it affect the performance on the server?

    Best

    /M

     

  • October 19, 2006 at 3:37 am

    #666735

    Your first question has different answers depending on what role you are in - developers and dba would love SQL Server to be running with low CPU, budget holders would want it to be running high - otherwise they've bought a machine too powerful for the job and spent too much money!  CPU really isn't the only factor though; memory usage and disk usage are if anything more important.  There are no hard and fast rules; it depends on the load you throw at it.

    Question 2 - relationships.  I'm assuming you've got primary keys set up, just no foreign keys.  That in itself won't reduce performance at all, I believe.  In fact, since there are less constraints to check, it may even have a slight beneficial impact on performance.  You don't need foreign keys to do joins, and no indexes are created by defining a foreign key relationship.  The problem is you're then relying on your application code to enforce referential integrity, which introduces a lot of extra code, all of which has to be debugged.  Application code is also seldom complete or bug-free.

  • October 19, 2006 at 12:01 pm

    #666896

    Budgets aside.

    SQL Server 101 - A SQL Server is a server dedicated to SQL Server alone.

    • SQL Server is SMP aware (can access multiple CPUs) and utilize resources more efficiently if it does not have to share them.
    • VB on the other hand, unless coded in a very specific manner is not SMP aware.
    • Also, not mixing a DBMS and an application on the same server also simplifies the isolation, diagnisis and fixing of performance related issues.

    Now we'll add budgets back into the picture. Ask those with the purse strings if it's a better ROI to make an additional server expenditure now and expend a little time tuning for performance initially or not to make the server purchase and to have to spend a great deal more time diagnosing and remedying performance related issues - whether they be application or database. Also when you ask that question be sure to factor in wasted user time, wasted client/customer time and potential business loss in addition to just your time needed.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 5 posts - 1 through 4 (of 4 total)

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