locked
SQL Server Performance RRS feed

  • Question

  • Hello friends,

    I have written a Windows Service in C#. This service connects over ADO.NET to an SQL 2005 Database.

    The service iterates over numerous records in a nested for loop and executes some stored procedures.

    The calculation that runs takes almost 1 minute per outer for loop iteration because the stored procedures are using some cursors and doing a lot of data manipulation.

    The service is not multi-threaded. So the for loop is executed serially.

    My problem is that when I monitor the CPU utilization, 75% of the CPU is idle and only 25-26% is being used by SQL Server.

    I have set my Windows Service priority to HIGH. Plus I used SP_Configure to boost the SQL Server Priority to HIGH as well.

    However, none of these changes have actually made a difference with regards to CPU Utlization.

    I am trying to run the calculation as fast as possible, so can any suggest a way in which more CPU time can be committed to the calculation.


    Dwight
    Thursday, July 15, 2010 9:58 PM

Answers

  • The calculation that runs takes almost 1 minute per outer for loop iteration because the stored procedures are using some cursors and doing a lot of data manipulation.


    Dwight,

    If you can change the stored procedures logic to set-based operations, that would speed it up.

    You see 25% CPU utilization because that much work the CPU can do at the moment.  Common reason: CPU is waiting for disk i/o.

    Consider the following steps for high performance:

    1. Optimize/reengineer stored procedures

    2. Perform index optimization - implement regular index maintenance

    3. Optimize/reenginer client-server software architecture - do more on server side if possible

    4. Optimize SQL Server configuration settings - example parallelism option

    5. Add more memory if feasible

    6. Optimize disk configuration - related link:

    http://www.sqlusa.com/bestpractices/configureharddisk/

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Friday, July 16, 2010 9:51 AM