This chapter introduces the methods that Adaptive Server uses to access rows in tables. It examines various types of queries on single tables, and describes the access methods that can be used, and the associated costs.
Chapter 2, “Optimizer Overview,” explains how the optimizer uses search arguments and join clauses to estimate the number of rows that a query will return. This chapter looks at how the optimizer uses row estimates and other statistics to estimate the number of pages that must be read for the query, and how many logical and physical I/Os are required.
This chapter looks at queries that affect a single table.
For queries that involve more than one table, see Chapter 6, “Accessing Methods and Costing for Joins and Subqueries.”
For parallel queries, see Chapter 8, “Parallel Query Optimization.”
This chapter contains information about query processing that you can use in several ways as it:
Provides a general overview of the access methods that Adaptive Server uses to process a variety of queries, including illustrations and sample queries. This information will help you understand how particular types of queries are executed and how you can improve query performance by adding indexes or statistics for columns used in the queries.
Provides a description of how the optimizer arrives at the logical and physical I/O estimates for the queries. These descriptions can help you understand whether the I/O use and response time are reasonable for a given query. These descriptions can be used with the following tuning tools:
optdiag can be used to display the statistics about your tables, indexes, and column values.
See Chapter 6, “Statistics Tables and Displaying Statistics with optdiag,” in the Monitoring and Analyzing for Performance book.
showplan displays the access method (table scan, index scan, type of OR strategy, and so forth) for a query.
See Chapter 5, “Using set showplan,” in the book Performance and Tuning: Monitoring and Analyzing for Performance.
statistics io displays the logical and physical I/O for each table in a query.
Provides detailed formulas, very close to the actual formulas used by Adaptive Server. Use these formulas are meant to be used in conjunction with the tuning tools, from the book Performance and Tuning: Monitoring and Analyzing for Performance:
optdiag can be used to display the statistics that you need to apply the formulas. See Chapter 6, “Statistics Tables and Displaying Statistics with optdiag.”
dbcc traceon(302) displays the sizes, densities, selectivities and cluster ratios used to produce logical I/O estimates, and dbcc traceon(310) displays the final query costing for each table, including the estimated physical I/O. See Chapter 7, “Tuning with dbcc traceon.”
In many cases, you will need to use these formulas only when you are debugging problem queries. You may need to discover why an or query performs a table scan, or why an index that you thought was useful is not being used by a query.
This chapter can also help you determine when to stop working to improve the performance of a particular query. If you know that it needs to read a certain number of index pages and data pages, and the number of I/Os cannot be reduced further by adding a covering index, you know that you have reached the optimum performance possible for query analysis and index selection. You might need to look at other issues, such as cache configuration, parallel query options, or object placement.
