Database Reference
In-Depth Information
containing data from the columns referenced in the index.
In the select statement, we only reference columns A and B. The index was created
using columns A and B. Therefore, everything the query has requested is contained
within the index itself, so the query never has to go back to the source table for any data.
Once this select statement is modified to include column C or D, the query can no longer
resolve the request just using the index. Remember how we said the index is a mini
copy of the table. In the mini copy of the table, those columns do not exist. Therefore,
we must go back to the source table for the contents of C or D. This means that
retrieving what is stored in the other columns of the table requires looking within the
contents of MYTABLE itself. The following three select statements use the index to
help speed the query along, but also have to look at the source table ultimately to
retrieve all the data requested:
Select A,B,C from dbo.mytable where A='Mary'
Select A,B,D from dbo.mytable where A='Mary'
Select A,B,C,D from dbo.mytable where A='Mary'
What is clear is that whatever you can do to minimize physical I/O, the faster your
database will perform. Storage array vendors do this by putting intelligence into the
physical hardware (storage array) and how it utilizes memory to minimize physical I/O.
Database vendors such as Microsoft do this by putting intelligence into the software
(database engine) itself, the operating system, and how it leverages memory. Server
vendors do it by putting memory associated with the CPU sockets. At every level—from
the physical hardware (such as storage arrays) to the software (such as the SQL Server
database)—vendors are finding ways to use memory to speed up performance.
As DBAs, we are constantly in a balancing act of how much of the IT food group (disk,
CPU, memory, and network) we feed our database. It is clear that memory is one of the
most powerful levers we have in our toolbox to optimize database performance. The
choices we make will have a huge impact on overall database performance.
Host Memory and VM Memory
We as people have two types of memory: short-term and long-term memory. As we
experience life, our short-term memory is recorded. Over time, it then feeds our long-
term memory. When people lose their short-term memory, they would then lose the
ability to obtain new long-term memory. As you can see, short-term memory and long-
term memory are interconnected; this is true for your virtualized database as well, and
it's important to understand the distinction between the two.
In your virtualized environment, memory exists at three levels that are also
