Database Reference
In-Depth Information
Figure 18-10.
A common set of servers in a BI solution
In addition to the three BI servers, you also have to plan the location for the transactional databases used
as the source of your data warehouse. These source databases can be placed on the SSIS server computer with
minimal impact on SSIS performance if you have your ETL operations occuring off-hours. But, if you need to run
the ETL process many times a day or even many times an hour, it may be necessary to separate the ETL server
and its sources.
Performance Measurements
Performance measurements come in two categories: absolute and relative. Absolute measurements are taken in
reference to a particular value established by a central authority. Relative measurements are compared to other
measurements taken at an earlier moment in time or chosen as an arbitrary value. Either way, the values you
measure against are known as
benchmarks
.
Absolute Performance
An example of an absolute measurement is SQL Server data page fragmentation. Microsoft has established that
the SQL Server database data files are subdivided into 8 KB sections (referred to as
data pages
). The 8 KB limit
gives you a point of reference, or benchmark, to work from.
The amount of page fragmentation can be measured for a table and its associated indexes by comparing
how full the 8 KB pages are. When the table or index is spanned across a large number of pages and those pages
contain substantially less than 8 KB of data, the table or index is said to be
fragmented
.
For example, let's say we have two very small tables, both of which contain less than 8 KB of data. Now let's
say that for some reason the data is written across multiple pages. This represents fragmentation at the page level.
To illustrate this point, note that the Product table in Figure
18-11
has been assigned to two data pages and the
Customers table has been assigned to three data pages. In our example, we are saying that each of these tables is
smaller than 8 KB of data and should be able to fit within a single page.
