High-End Devices and the Real World
It's great to see articles like Kimberly Tripp's "Essential Aspects of Database
Design" (February 2007, InstantDoc ID 94585). I've added it to the material
I share with our developers who end up designing databases without having much
background. In the SQL Server world, it's all too common that databases are
being designed by folks whose main expertise lies elsewhere. SQL Server Magazine
can do a lot to mitigate this problem.
Regarding fragmentation: Just today I was looking at some very high fragmentation
numbers. The situation: The data and index files are stored on a dedicated Equalogics
iSCSI disk array. These aren't just iSCSI RAID 10 arrays. One reason for the
high price is that the device self-balances I/O across the spindles as it runs.
If it notices a trend that spindle 6 is overused and spindle 3 is underused,
it will start migrating blocks from 6 to 3 to balance the load.
So, does fragmentation have any meaning on this kind of device? The analysis
tools will think of that volume as one ordinary disk with cylinders and tracks
and so forth—but it's a fakeout. SQL Server is writing to the device's
interfaces, which take full responsibility for maintaining the write order and
the atomicity of the write of blocks that are part of a single transaction—even
if the power goes out. The real blocks may be all over the place. Clearly, the
one thing it won't do is compact down multiple pages that can fit into one.
With 2TB of disk space and 64GB of memory, a cache hit rate of 99.8 percent,
and disk queues under .2/sec despite 59 databases used by more than 100 applications
with nearly 3,000 users, I'm not sure that compacting it has much point. Obviously,
I'd enjoy fewer articles about how to get by with RAID 5 and some more real-life
stuff with higher-end devices.
—Roger Reid
See Associated Figure
Understanding Subtypes
I have a question about Michelle A. Poolet's Solutions by Design: "Implementing
Supertypes and Subtypes" (November 2006, InstantDoc ID 93241). I enjoyed the
article and found it to be very helpful. I'm very new to data modeling, but
I have what appears to be an overlapping supertype-subtype structure. I have
a supertype of Person and subtypes of Producer, Agent, and Loss_Adjuster. A
person can be any of the three subtypes or any two of the subtypes together:
that is, a person could be a Producer and an Agent or just a Producer. My question
is, can a subtype table have a relationship with a table outside of the supertype-subtype
structure?
—Andy Russell
See Associated Figure
SQL Server Costs in a Virtualized Environment
I read you regularly and have a question about SQL Server in a virtualized environment.
I pay for SQL Server by the processor, but how does this work when I deploy
SQL Server in a multi-guest virtual machine (VM) system in which only some of
the guests have SQL Server installed? For example, I run Windows 2003 Server
and two VMware guests on a server with dual processors. Only one of the guests
runs SQL Server. Am I charged twice—once for each processor?
—Marco
Guarnacci
Microsoft treats VMs as a device. Therefore, you would be charged per the
number of processors in the VM guest—not the host. If the guest is configured
with a single processor, you would be charged for one processor.
—Michael Otey
Absolutely! That's one of the criteria that I use to
determine whether I need to specialize: Does one
subtype have a relationship with other entities in
the table schema, and does that relationship refer
only to that subtype? In your case, the subtype
Producer might have a relationship with an
entity called Movie, but that relationship wouldn't
be relevant to the other subtypes. Another
subtype, Agent, might have a relationship
with an entity called Studio, and maybe
even to another subtype of Person not listed,
called Actor. Subtypes can have relationships
between themselves, as in Agent to Actor, and
Actor to Agent.
I use subtyping a lot when I'm modeling conceptually. It really helps me
to understand the business conditions and situations. Whether or not I carry
the supertype and subtype entities forward, through logical modeling into physical
modeling, is a decision that I'll make on a case-by-case basis.
—Michelle A. Poolet
