Apress - SQL Server 2008 Query Performance Tuning Distilled (2009)02

Apress - SQL Server 2008 Query Performance Tuning Distilled (2009)02
Nội dung trích xuất từ tài liệu:
Apress - SQL Server 2008 Query Performance Tuning Distilled (2009)02 CHAPTER 2 S Y S T E M P E R F O R M A N C E A N A LY S I S 19Dynamic Management Views immediate snapshot of a large amount of data that was formerly available only ofmost important for monitoring performance and for establishing a baseline. view displaysthis simple query will return the current value for :appropriate server name in the comparison.about the server. Rather than cover them all, I’ll introduce one more that you will find yourselfaccessing on a regular basis,occurring within your system is one of the easiest mechanisms to begin identifying the sourcethe waits that have the longest current count using this simple query:Figure 2-2. Output from You can see not only the cumulative time that particular waits have occurred but also20 CHAPTER 2 SYS TEM P ER FOR MA NC E A NA L YS IS , , , , or in your top ten Hardware Resource Bottlenecks performance is affected by stress on the following hardware resources: limit on overall system performance. Identifying Bottlenecks Note The most common performance problem is usually I/O, either from memory or from the disk. some other resource to complete its operation. You can use Performance Monitor counters request served by a resource includes the time the request had to wait in the resource queue, - tional to the amount of queuing in a system. CHAPTER 2 S Y S T E M P E R F O R M A N C E A N A LY S I S 21with the demand.has no such counter, but a large number of hard page faults represents the overcommittal of counter You will see which counters to use inBottleneck Resolution - -by partitioning a tableMemory Bottleneck AnalysisMemory can be a lazy writer22 CHAPTER 2 SYS TEM P ER FOR MA NC E A NA L YS IS SQL Server Memory Management memory for databases, including memory requirements for data and memory pool consists of a collection of 8KB buffers to manage data pages and plan cache pages, free pages, You can - Figure 2-3. SQL Server memory configuration and . CHAPTER 2 S Y S T E M P E R F O R M A N C E A N A LY S I S 23 con- figuration value but commits memory dynamically, as needed. , serves as a ceiling value to limit the - diately and do not require a restart.where will be 0 andmemory of the system, assuming a single instance on the machine. You should not run other -mend you first get estimates on how much memory is needed by other applications and then value set to prevent the other applicationspool memory (which used to be called - Note SQL Server does consume more memory than simply that specified by thesetting. You can also manage the configuration values for and by using the stored procedure as follows:Figure 2-4. SQL Server memory configuration properties24 CHAPTER 2 SYS TEM P ER FOR MA NC E A NA L YS IS setting is 0MB, and for the cannot be set to less You can also modify these configuration values using the stored procedure. to 200MB and the following set of statements ( and configurations are classified as advanced options. By default, the stored procedure does not affect/display the advanced options. Setting to as shown previously enables the stored procedure to affect/display the advanced options. statement updates the memory configuration values set by . Since ad hoc updates to the system catalog containing the memory configuration values are not recommended, the flag is used with the statement to force the memory configuration. If you do the memory configuration through Management Studio, ...