This chapter explains how to determine the current sizes of tables and indexes and how to estimate table size for space planning.
Topic |
Page |
|---|---|
Knowing the sizes of your tables and indexes is important to understanding query and system behavior. At several stages of tuning work, you need size data to:
Understand statistics io reports for a specific query plan. Chapter 1, “Using the set statistics Commands,” in Performance and Tuning Series: Improving Performance with Statistical Analysis describes how to use statistics io to examine the I/O performed.
Understand the optimizer’s choice of query plan. The Adaptive Server cost-based optimizer estimates the physical and logical I/O required for each possible access method and chooses the cheapest method. If you think a particular query plan is unusual, use dbcc traceon(302) to determine why the optimizer made the decision. This output includes page number estimates.
Determine object placement, based on the sizes of database objects and the expected I/O patterns on the objects. Iimprove performance by distributing database objects across physical devices so that reads and writes to disk are evenly distributed. Object placement is described in Chapter 1, “Controlling Physical Data Placement.”
Understand changes in performance. If objects grow, their performance characteristics can change. One example is a table that is heavily used and is usually 100% cached. If that table grows too large for its cache, queries that access the table can suddenly suffer poor performance. This is particularly true for joins requiring multiple scans.
Perform capacity planning. Whether you are designing a new system or planning for growth of an existing system, you must know your space requirements to plan for physical disks and memory needs.
Understand output from Adaptive Server Monitor and from sp_sysmon reports on physical I/O.
