Professional SQL Server® 2005 Performance Tuning

2.8. Identifying Bottlenecks

When investigating performance issues, you should seek first to identify a bottleneck and second to resolve the bottleneck! Bottlenecks are typically caused through misconfiguration or by reaching performance or scalability limitations within software or hardware. Identifying a bottleneck is a major aspect of performance troubleshooting. Doing so in a timely and efficient fashion is a skill that requires understanding of system and software architecture and a methodical approach.

2.8.1. Locating Memory Bottlenecks

SQL Server just loves memory — it can't get enough of it! If ever there was an application that can use all the memory in a server, it is SQL Server. However, this is for the benefit of users. Disks are slow and therefore SQL Server will do all it can to pre-empt user requests by reading ahead and loading data pages into memory so they're provided to users as quickly as possible when requested.

When designing a platform to deliver a new service, or definitely when reviewing capacity on existing infrastructure, it's necessary to determine how much free memory is available. This can also be useful in determining whether a memory shortage is contributing to or even causing a performance issue.

Identifying memory pressure can be further categorized as identifying either internal or external memory pressure. If SQL Server is unable to acquire sufficient memory, it is external memory pressure, or if there's contention within memory allocated to ...