Wonderful article, I just download the MSDN webcast from Kinberly and I must say: I am impressive with your capacity to translate very complex features in terms everybody can understand and follow. Thanks a lot, and please, continue writing to SQL Magazine, I saw that your last article was 2 years ago.

Take Care!

MarcosGalvani 6/19/2009 1:38:55 PM

I enjoyed this article a lot...however, I have 2 comments:
1. It is my understanding that several narrower non-clustered indexes are actually a better approach in many cases than composite indexes since SQL Server 2000 and higher takes advantage of index intersection.

2. I agree with updating stats but I think a better idea than leaving auto-update stats on is to turn it off and then update stats on a nightly or even weekly schedule instead. This way, you don't have to worry about a slow-down due to SQL Server deciding to update stats in the middle of a busy day.

Thoughts?

barrys 2/12/2007 8:12:07 AM

Even understandable to those of us without decades of experience (yet). Thank you.

Chris.Smith 2/9/2007 10:30:02 AM

Great to see an article like K. Tripps' Essential Aspects of DB Design. 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.


You mentioned fragmentation. Just today I was looking at some very high fragmentation numbers. The thing is, 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 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 a 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 devices interfaces, which takes 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 2 TB of disk and 64 G memory, a cache hit rate of 99.8%, and disk queues under .2/sec despite 59 databases used by over 100 apps with nearly 3000 users I'm not sure compacting it has much point.

Obviously, I'd enjoy a few less articles on how to get by with RAID 5 and some more real life stuff with higher end devices.

---------------
all the best
Roger Reid

DianaMay 1/26/2007 12:16:28 PM