Database Reference
In-Depth Information
Figure 18-13.
Decreased Fragmentation after Rebuilding an Index
■
You will not see much gain by defragmenting small tables, such as in our example. But in the larger tables,
such as the ones that make up many Bi solutions, you can see a big difference in sQl select performance when the
tables and indexes are defragmented.
Note
Most often, tuning databases is performed after the BI solution is in use and is automated by database
administrators. The BI solutions developer may not be involved with this process directly; however, this
information is useful when communicating with administrators responsible for maintaining the BI solution. In
addition, it can be used by developers to anticipate the needs of those administrators.
As a developer, make sure initial indexes are in place before the solution is completed. After all, by reviewing
the SQL
SELECT
statements you have created for the BI solution, you can ascertain what initial indexes are needed
more easily than an administrator can who has never worked with the solution before. These
SELECT
statements
are found in the ETL code used in SSIS, the processing code used in SSAS, and the reporting queries used in
SSRS. If you find any of the SQL
SELECT
statements using a SQL
JOIN
,
ORDER BY
, or
WHERE
clause, then you need to
look closer to see whether an index is needed.
■
Knowing when and where to use an index takes research and practice. For more information, visit our
Note
