THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Bit Bucket (Greg Low): IDisposable

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Book: Refactoring SQL Applications - Stephane Faroult

I've had a number of people tell me over the years that they aren't surprised when I say "Recommended" at the end of any book review I write, as I never seem to say "Not Recommended". I suppose that comes under the heading of "if you can't say anything good about something, don't say anything". So, I'm going to temporarily change tack with this book.

For a long time, I've wished there were more books tackling the thorny topic of refactoring databases. Most people would consider Scott Ambler's book to be a seminal work in this area but while I loved the fact that he wrote on the topic, I found the methods he used to be way too generic for SQL Server developers and DBAs. For example, where persisted calculated columns could be used, Scott would use triggers. I'd been eagerly awaiting any other books in this area.

Breaking with another tradition where I normally only purchase books that friends and colleagues have recommended, I followed the Amazon data mining advice when it suggested another up-coming book that I might like. Refactoring SQL Applications by Stephane Faroult and Pascal L'Hermite sounded just like what I'd been looking for and I even pre-ordered it.

I really found reading this book uncomfortable. Stephane has tried to write a book that covers multiple database engines, not just SQL Server but that's again where I suspect the problem began. Even though SQL Server is discussed throughout the book, I'm guessing that Stephane is more comfortable with Oracle than with SQL Server. I found myself cringing many times while reading it. In particular, I found advice such as "indexes are more often superfluous than missing" extraordinary. Reading the logic that supported these arguments felt like watching a train wreck in slow motion. I could see where it was going; it wasn't going the right way and it was too late to stop it. What this typically involved was Stephane creating indexes that weren't good indexes for the query at hand, then deciding that because of that, that indexing itself wasn't that useful.

I'm still looking for a great book for SQL Server developers and DBAs in this area.

Not recommended for SQL Server users! (I'm not able to comment sufficiently authoritatively for Oracle users)

Published Friday, July 03, 2009 4:58 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Saggi Neumann said:

Hi Greg,

I too don't know enough about Oracle, but as an anecdote, I lately discovered that if developers find out about the Database Tuning Advisor, you can quickly find yourself in bizarro world where indexes *are* superfluous (and include most of the columns as well just for the heck of it), and getting rid of them isn't an easy task. It's actually harder than adding indexes to a database full of heaps.

There are roles in msdb to allow running and managing jobs, to manage DTS packages and to send mail, but there's no role to disable DTA, right? So I googled "disable database tuning advisor" and the first result to show was this:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/sql_tune.htm

"Oracle Database automatically runs the SQL Tuning Advisor on selected high-load SQL..."

So - I'm not saying that indexes are superfluous by default in Oracle and I'm not saying that they aren't, but if Microsoft, the leading "we'll do it for you" company doesn't do automatic index tuning, then how come Oracle who only recently found out about GUI does that?! ;-)

Cheers,

S. Neumann

July 3, 2009 1:36 AM
 

Greg Low said:

Yes, I feel your pain. I've been to a number of sites where they run DTA blindly and just accept the recommendations. It's often quite a mess after a while and with statistics as well, not just indexes.

The missing index DMVs can also lead you down that path, particularly given their focus on an individual query rather than on a query load.

But still more commonly, I go to sites where the devs "just haven't yet gotten around to doing any indexing" and that's often not pretty.

I was at a site last Tuesday that had 20G tables without any sort of index or structure to the tables at all. Not surprisingly, queries to find entries from the last thirty days were somewhat slow :-)

Regards,

Greg

July 3, 2009 1:55 AM
 

Mike Walsh said:

I have actually been reading his "Art of SQL" book and finding it a good book to give to developers to get into a better mindset around thinking of performance, thinking of sets, etc. Haven't finished it yet but was going to finish it and think about the next book. Might hold off on checking this one out.

I found him when looking for an analogy on join types and found some of his you tube videos to be a handy reference to give to some developers I work with.

July 6, 2009 8:00 PM
 

Kevin G. Boles said:

+1 on the DTA-is-often-horrible mantra!  Earlier this year I spent almost 150 manhours doing an index cleanup project for a client that blew out tons of DTA-recommended indexes over the course of 12-14 months.  With the increase in data volumes their applications became extremely slow due to excessive blocking and DML index maintenance activity.  I was able to remove over 60% of all indexes with less than 5% reduction in read performance but > 200% improvement in overall DML response time all while reducing database space used by a very significant amount.  DTA sure does love to include columns for 'covering' queries!!  

July 7, 2009 10:48 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement