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
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
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