Database Reference
In-Depth Information
CHAPTER 10
When you hear the phrase “database performance,” what do you think it
means? Most of us think of how fast a database returns a query. We want
to be able to send a query, and, no matter how much data it is returning,
we want that query to run instantaneously. Unfortunately, as most of us
know, that just doesn't happen. However, performance can be tuned to re-
trieve data sets in the most efficient way possible. Whether that means get-
ting one row back in less than a second or trying to retrieve 50,000 filtered
rows in less than an hour, it can almost always be accomplished. One of the
primary ways of speeding access to the data is by using an index.
In this chapter, we discuss what indexes are, how to determine which
indexes you need, and how to implement them. We also talk about how in-
formation you gathered as far back as the logical modeling step will help
shape the indexes you implement in your physical database. Note that all
this information is specific to Microsoft SQL Server; however, most of the
concepts have parallels in all the other major RDBMSs.
Before we can discuss indexes, it is important to have a cursory under-
standing of how SQL Server 2008 stores its data on disk. We won't go very
deep, but we cover enough that you should be able to explain at a high
level how data is stored and retrieved. If you are interested in a deeper
level of knowledge on this subject, there are numerous topics, articles,
white papers, and classes on SQL Server internals and data access. For our
purposes, we'll take the 15-minute approach.
Because SQL Server runs on Microsoft Windows, all its data is stored
in files in the file system of Windows. Every database has at least two files:
a file that contains the actual data (known as the
data file
), and a file that
contains information about the transactions that have occurred involving
that data (known as the
transaction log file
). A database can have more
221
Search WWH ::
Custom Search