Databases Reference
In-Depth Information
determine if the processor is the bottleneck, look at the processor free and busy
time. The higher the percentage of busy time, the greater the load in the proces-
sor. When this averages over 90 percent of total time, then processor perfor-
mance is likely a bottleneck.
The only ways to correct a processor bottleneck are to reduce the processor
load, install a faster processor, or install one or more additional processors. You
need to be aware that there are a couple of possible problems with using a mul-
tiple processor computer. Different DBMSs have different support options, some
limiting the number of processors supported. A few lower-end database products
cannot recognize or use multiple processors, which means that an additional
processor would be a waste of money.
Many DBMSs, including SQL Server, let you configure
processor affinity.
This controls how, or even if, the database server makes use of multiple proces-
sors. The problem might not be with the processors, but that the database has
been configured to ignore any additional processors.
8.1.3 Understanding Database Performance
Database performance issues relate to database object design and use. One of the
goals of data normalization is to improve write performance by reducing the
amount of data that must be written during updates. However, normalization
can impact read performance because data retrieval often means having to join
multiple tables to get all of the data you need. In the real world, you typically
work toward a compromise between “perfect” normalization and database read
performance.
Database objects, especially indexes, also impact performance. Indexes are
used to sort and organize data. Well-designed indexes help to optimize data read
performance. The problem is that each time you update the data in a table, the
database server must also update the data in all of the table indexes. The more
indexes you have, the more overhead required to keep them updated.
The problem can be that you don't have the right indexes to support your
application's queries. The query processor may not recognize or use poorly
designed indexes, making them a waste of resources. SQL Server 2005 provides
utilities and reporting tools that let you see how, or if, indexes are used by the
query processor and that can even suggest design improvements.
8.1.4 Performance Monitoring
Performance monitoring tools are specific to the operating system and DBMS.
Some DBMSs provide a wide array of tools to help you locate and correct per-
formance problems while others leave you effectively on your own. In every case,
the DBMS relies heavily on the operating system and system resources, so oper-
ating system tools can be vital to detecting and correcting problems.
