Databases Reference
In-Depth Information
A resource bottleneck is the highest cost component in terms of time, on the critical path when
performing user-requested activity. In terms of overall perceived application performance, you could
consider the network, user authentication, or a database as a bottleneck. More relevant, within the
database server, you could consider memory, CPU, or disk throughput as a bottleneck if performing
an operation is impaired significantly by any one of these.
Successfully identifying a bottleneck involves using System Monitor to understand individual
performance counters, in the context of overall system performance. Abstracted, there is little
useful performance data as this is all relative to the hardware and use profile of the server. Jumping to
conclusions and making assumptions are quick ways to prolong a poor performance condition. Always
consider the key components together when making your assessment.
Kernel Mode versus Application Mode
When considering server performance, you should be familiar with the difference between Kernel
mode and Application mode operations. Once you understand this concept, this knowledge can be
applied to both CPU and memory resources or bottlenecks. Most performance-related discussions
will seek to identify kernel mode or application mode pressure or contention early in the trouble-
shooting cycle in order to further narrow down likely causes and to determine potential routes to
seek resolution.
Kernel mode refers to core windows internal operations where the kernel has unrestricted access to
system memory, external devices, and so on. For example, processes or threads are managed, and the
I/O manager takes care of device drivers. Also the virtual memory manager controls all virtual memory
and the hard disk.
Application mode (also known as
user mode
) is responsible for everything else, such as servicing all
requests from user applications including IIS and SQL Server. All user mode applications access resources
through the executive, which runs in Kernel mode. An application requesting disk I/O submits the
request through the kernel mode executive, which will carry out the request and return the results to
the requesting user mode process.
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.
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.
Search WWH ::
Custom Search