To design the fragmentation strategy for all tables in a database, you must understand how the tables are used and the kinds of data that they contain.
The table-access pattern for a database helps determine how individual tables should be fragmented and how the fragments should be placed on the disk system.
The table might look like the following small sample.
Query/Transaction | customers
40M rows 8 GB |
states
50 rows 1024 KB |
orders
80M rows 15 GB |
---|---|---|---|
query 1 | I | J | |
query 2 | J | J | A |
query 3 | S | J | J |
In the table, S stands for Select, D stands for Delete, I stands for Insert, A stands for Add, and J stands for join, based on a WHERE clause in the query.
You might expand this simple table or create two more detailed tables. One detailed table might contain query information, such as a description of the query or the query SQL code, the filter columns, the frequency with which the query is run, and similar information. The second table might list detailed information about each table, including the columns most often used as filters and similar information that helps you determine fragmentation possibilities.
The sample information in Table 1 shows the following access pattern:
If you are planning a database, you might not have exact numbers for the table size and rows. If you know what columns each table will contain and approximately how many rows you will have initially, use the sizing calculators in Appendix A. Estimating Table and Index Size, to estimate table size. If you are migrating a database from a different database server, use the table and row size information from the original database.
The number of different values and duplicate values in keys and join columns in a table also affects your fragmentation decision. If you are planning a database, estimate the number of values and duplicated keys or join column values in tables as well as you can.
If you are migrating a database, you might know or be able to find out the data distribution for table data.
Data-distribution information lets you see how fragments would be balanced if you used an expression fragmentation scheme based on any column in the table.
After you have evaluated the table access pattern and data distribution, you can begin to decide on table fragmentation and fragmentation schemes. Consider the following issues:
Depending on performance goals for your client applications, you can decide the best fragmentation schema for tables. Using a combination of data-access and data-distribution information, refer to information in Choosing Fragmentation Schemes to plan the fragmentation strategy for the entire database.
Some tables are so small, they should not be fragmented. For example, in the matrix example in Table 1, the states table is very small. Fragmenting this table would make no sense.
For efficient OLTP transactions, if small tables are often joined in transactions, you might duplicate the tables on all coservers where fragments of large tables that are also accessed by the transactions are located. If all tables and table fragments that satisfy a transaction are located on the same coserver, serial plans are possible.