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
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.