Performance, Optimization, and Tuning

You have to provide some thought to database design, relations, and the index creation process. In the olden days of exposed database engines-APIs that you had to write very low-level code for-index tuning, for example, was basically nothing more than creating and recreating indexes to find solutions that provided the best rate of return on the data you were querying. Today SQL Server comes equipped with the tools to make this job a lot easier.

The Microsoft SQL Server 2005 Database Engine Tuning Advisor and the SQL Server Profiler help you select and create an optimal database, collection of tables, sets of indexes, indexed views, and partitions without requiring an expert understanding of the structure of the database or the internals of Microsoft SQL Server.

The Database Engine Tuning Advisor, for example, analyzes the workload of one or more databases, reporting also on their physical implementation. Using batteries of tests comprising sets of T-SQL statements that execute against a database or databases, the Database Engine Tuning Advisor can recommend adding, removing, or modifying physical design structures in the databases.

For example the Database Engine Tuning Advisor can

• Propose a collection of indexes to implement. It does this using the query optimizer to analyze queries in a workload.

• Recommend aligned or nonaligned partitions for a database.

• Recommend indexed views for a database.

• Report on the effects of the proposed changes in indexes and queries.

• Consider alternatives in which you supply possible design choices in the form of hypothetical configurations for Database Engine Tuning Advisor to evaluate.

Chapter 18 is devoted to operations management of SQL Server 2005 implementation, and we cover the database and index in more detail in that chapter. However, let’s first discuss here what good indexing practice comprises:

• Keep the number of update queries that either change or delete rows to a minimum. In other words try to change or delete as many rows as you can in as few queries as possible. This will ensure that index re-creation and maintenance overhead is kept to a minimum.

• Use nonclustered indexes on columns often queried. This practice lets you cover more columns with indexes without the cost of a clustered index. By the same token, you need to use the Tuning Adviser to see if an index on simple data is not more costly than a sequential table scan without an index, which is entirely feasible.

• Use clustered indexes on key columns. Also keep them for unique, identity, and nonull columns, which typically benefit from clustered indexes.

• Evaluate your disk subsystems to help speed up data access and index creation. While SQL Server may have highly optimized index creating algorithms, the index creation and maintenance speeds depend on the hard disk technology employed. For starters, stick to hardware RAID technology, which is more efficient for database solutions than software RAID. Also use faster and more reliable hard disks in the RAID configurations.

• Get to know your data and investigate what costs and plans the query optimizer is consistently coming up with for your query and the indexes that have to live with it.

• Learn how to use the performance tools (see Chapter 18).

Besides these tips, you should take the time to fully understand the difference between clustered and nonclustered indexes. Also, if you have not read the architecture chapters in Part I, now is the time to do so because they provide you with insight into the underlying architecture of the databases and tables and how the data pages that comprise tables are structured and maintained. It helps to navigate the road when you know how it is paved.