Tech Paper - Best Practices for Performance Tuning

Version 3

    Deploying the ArcFM Solution on SQL Server White Paper

    Following is guidance for performance tuning for a SQL Server implementation.


    Guidance for Improving Query Performance

    The following sections describe the various performance considerations for the standard components and for customer-specific queries.


    Optimize Customer-Specific Queries

    For queries used by customer-specific code, you should go through the following steps in order to identify performance issues, consider optimizations, and verify the benefit of the optimizations.

    • Identify slow-running queries
    • Consider optimizations
    • Use scalar promotion
    • Verify optimizations
    • Use logical optimization


    Recommendations for Monitoring Performance of the Database Tier

    You should perform system monitoring during development of your system and periodically during production. Before "going live," look for bottlenecks and gauge your ability to scale to your expected long-term workload.

    Once in production, you should create a performance baseline and monitor trends in resource consumption against the performance baseline so that you can predict future bottlenecks and determine when resource limitations might begin to impact performance as your customer base grows. You should not worry about seeing short spikes in utilization or high consumption during processes that are operating at an acceptable performance level.

    Note that SQL Server 2008 R2 introduces application and multi-server management, which can help you manage the database environment more efficiently at scale, with visibility into resource utilization for consolidation and improved efficiencies across the application lifecycle. For more information, see SQL Server 2008 R2 - Application and Multi-Server Management.

    Table 1 shows metrics that your monitoring plan should include.




    Database File Sizes (including tempdb)

    Need for expansion of files or storage.

    Log File Sizes

    Need to backup transaction log more often.

    Processor/% Processor Time: All Instances

    Additional processors.

    Average Disk Queue Length

    Storage configuration too slow.

    Average Disc sec/transfer

    May indicate a large amount of disk fragmentation, slow disks, or disk failures (Should be 10 ms or less.)

    Disk Bytes/sec for each LUN

    Need to spread data across more LUNs.

    Paging in Pages/sec

    Need for additional memory.

    Network Interface Bytes Received/sec and Network Interface Bytes Sent/sec

    Need for increased network capacity or segmentation.

    Table 1, Monitoring plan