And the DSS doesn't necessarily have to be read-only: You can create views on the DSS that are designed to facilitate report-writing and which, because of their complexity, make data seen through a view act as though it's read-only. When you implement a DSS, you can take the query load off your transactional database by analyzing the most commonly executed queries, creating covering indexes for the tables, and creating effective indexed views for the data.
Don't Over-Index Transactional Tables with Heavy I/O Activity
A transactional table is a table in an online transaction processing (OLTP) database. You can use transactional tables for reports or as input to programs that export data (e.g., in a SELECT ... FOR XML query). However, the OLTP database's primary purpose is capturing data, and if you over-index a transactional table, you might inhibit performance of INSERT, UPDATE, and DELETE operations. Each index is a database structure—a table that contains a column of values arranged in ascending order, with bookmarks that point to associated rows in the data pages. Therefore, when you add a record to a transactional table, an INSERT operation occurs not only on the data page, but also in all associated indexes. And when you delete a row from the data page, SQL Server removes the corresponding row from all associated indexes. Certainly you need to create some indexes on heavily used OLTP tables, but don't overdo it—an UPDATE operation involves not only the data page but also the number of index pages that matches the number of indexed columns that you need to modify. For more information about how indexing affects UPDATE operations, see Kalen Delaney, Inside SQL Server, "Indexing Updates," July 2002, InstantDoc ID 25198.
Don't Index Wide Columns
If a table's columns are wide (i.e., the columns have a data type of char(25) or larger), indexes created on those columns might result in a large number of pages, thereby negating the benefit of using an index in the first place. Nonclustered indexes in SQL Server 2000 and 7.0 are structured differently from those in earlier releases. If you're not careful, you can create indexes that take more space on disk than the original data pages. (For more information about changes in index structure and the possible detrimental effect on your database, see "Move Up to SQL Server 2000," by Michelle A. Poolet and Michael D. Reilly, February 2002, InstantDoc ID 23512.)
A Double-Edged Sword
Proper indexing can make a sluggish database perform better. Conversely, improper indexing can make a high-performing database run poorly. The difference depends on how you structure the indexes—and on your follow-up. You need to test and observe the effect of a newly created index on your production environment, and you need to remove indexes that compromise your database's performance. Your indexing strategy is an important part of your responsibilities as a physical data modeler and DBA.
End of Article
Prev. page
1
[2]
next page -->
