Databases Reference
In-Depth Information
provides further detail on the classification of each problem area, with examples of the types of problems
you can expect to see within each problem area.
Configuration-BasedPerformance Problems
SQL Server doesn't require any specialist knowledge to install and get started with databases, but
this can be its downfall at times. There are often cases where server engineers are asked to install and
configure SQL Server and make decisions based on assumptions or unfamiliarity with the product.
Common examples include not separating data and log files onto separate disks (where performance
gains can be made through Input/Output (I/O) optimization by the disk controllers reducing disk
head movement. A similar example is an
sp_configure
option within SQL Server that calls Priority
Boost, which sounds attractive, almost too good to be true — a hidden switch that makes SQL Server go
faster! In fact Priority Boost can reduce SQL Server performance by prioritizing activity over that of the
operating system (which is why it's disabled by default). There are some niche cases where there is a
performance gain, but generally this would require detailed testing in a lab environment to qualify
enabling this switch.
From this discussion, it becomes apparent that configuration-based performance problems generally fall
into two further sub-areas:
Hardware/operating system configuration
❑
SQL Server configuration
❑
Microsoft invests a great deal of time and energy in determining the default values for each configuration
setting within Windows and SQL Server itself. In most situations, the scenarios under which customers
operate SQL Server fall within the use-case scenarios that map to the default configuration values.
However, there may be times when this one-size-fits-all approach might not meet your requirements and
there are many good reasons to change a number of settings. It's important that you have undertaken
an initial baseline and then performance test to measure the impact of any settings you change.
Schema-BasedPerformance Problems
SQL Server as a database platform allows customers to host an increasingly diverse array of applications,
storing new data types and with new connectivity options. On this basis, performance of the SQL Server
database engine is to a large extent dependent on good database design and implementation. Regardless
of how fantastic the SQL Server query optimizer operates, there are relatively few design issues that the
database engine can overcome entirely. While fast hardware and the latest version of SQL Server may
compensate for some design flaws, this approach is fairly limited in terms of scalability and can restrict
return on investment for the business.
In many cases the best opportunity to optimize the database schema is while designing the database.
Understanding the type of the data stored and the method and frequency of retrieval will influence
the design and overall approach. Changing the database schema when an application has already been
written may require application changes, which can be time-consuming and therefore often expensive
and risk-prone.
Achieving a fully normalized database can be an interesting challenge. Adhering to the structured
procedure for normalization typically results in an academically correct database schema. It is important
Search WWH ::
Custom Search