Separating Clustered Indexes/Heaps from Nonclustered Indexes
For heavily used tables, separating the table from its nonclustered indexes can have a
substantial performance impact. This is true not only for a DSS-type database, which is
heavy with indexes, but also for an online transaction processing (OLTP) database that
requires numerous nonclustered indexes. Normally with an OLTP database, the more
indexes on a table, the poorer the performance, because insert, update, and delete are
the more common operations. Each statement must update not only the table, but also
every index. Nonclustered indexes tend to be fairly narrow, meaning a single index is
built on one to four columns (of course, there are exceptions). If you separate a table??™s
data and its nonclustered indexes into different filegroups, you can significantly reduce
I/O requirements.
Separating Read-Only and Read/Write Tables
A filegroup has only two properties: default and read-only. From an I/O perspective, separating
read-only operations from write operations can improve performance, assuming
that you??™re placing them on separate filegroups placed on separate I/O paths.
Pages:
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187