SQL Server 2005 Books

I've been doing a lot of reading over the past few months trying to get my
SQL 2005 skills in shape and thought I'd share a few things about the books as
well as things I learned from them. I know book reviews aren't the most exciting
reading, so I'm going to combine a few in hopes of holding your interest. The
things listed aren't the only things I learned by far, but are just things I
tagged when I had an aha moment. Maybe if you find this format useful you'll let
me know so I can do another set, or even better, maybe you can write up some of
your own and send in.

This is the essentially the third edition,
following the editions dedicated to SQL 7 & SQL 2000. For SQL 2005 the book has
been split into four parts, covering the storage engine, query optimization,
TSQL programming, and TSQL querying. There's no doubt that SQL 2005 is a
significant release and has a lot of new features, but I kind of miss having one
book (for ease of use and buying one book instead of four). But as far as the
content goes, first class information as always. It contains a lot of low level
information (byte level schemes for pages for example) that will be of most use
to the very advanced DBA, but great reading for someone on the road to advancing
their skills.

Some things I learned:

You can no longer pin a table in SQL 2005. DBCC Pintable is still here
and you can run it without error, but it doesn't do anything. For those not
familiar with it, the idea was that you force SQL to maintain a table in
memory, overriding it's memory management. Take a look at it in Books Online
and I swear you have to read it twice to realize it. I never had found a
reason to pin, but it's interesting - if arcane - to know it doesn't do
anything any longer

A nice bit on page splitting that describes it's possible for SQL to
need to split a page more than once. Imagine a scenario where you have a
full page and need to insert a row on that page, SQL physically splits the
page approximately in half, leaving about 4000 bytes available on each page.
Now we try to insert our new row on either page, but the row is large - say
5000 characters. The row won't fit and the page has to be split again. Not
sure how often it happens or if it matters too much, but it is interesting

That I need to spend more time on the new isolation options. I get the
concepts, but I haven't mastered the details as much as I should have

Set Deadlock_Priority has changed. Before the options were LOW and
NORMAL, now there is a new setting of HIGH, can you call also set it to an
integer in the range -10 to +10, where LOW=-5, NORMAL=0, and HIGH=5.

Paid for this one myself and it will stay on the shelf, but it's not the
first book I'd recommend you buy if you're a beginner to intermediate user.

Disclosure: Brian is my long time business partner, so I'm not an unbiased
reviewer, but I hope you'll still find my comments interesting.

This is a hard book to read cover to cover. Not because it's poorly written,
but rather it has a lot of detail and covers a wide range of subjects, ranging
from converting DTS packages to SSIS all the way through building custom tasks
for SSIS. I find it to be a good reference book and it's where I start when I
try something new in SSIS.

Things I learned:

A great paragraph covers getting temp tables to work. SSIS seems to use
connections freely and then close them (good), but the downside is that if
you create a temp table at one step and then want to clean it up later (or
more likely take some action based on it) the table will be gone because
you're now on two different connections. Setting the retain same connection
property of the connection manager to true makes the problem go away.
Simple, but I wouldn't say intuitive

Not a new lesson, but as I was working through a couple of the
early chapters I remembered how much I hate portions of SSIS. Paths have to
be escaped (C:\\blah\\etc), equal is ==,
and in some places you address variables by ordinal position! End of rant

There's an example in Chapter 8 that imports the SSC rss feed to
demonstrate working with SSIS and one of the comments was that even though
it's importing a parent/child relationship, there is no guarantee that the
children wouldn't get inserted before the parents, causing you grief if you
have defined foreign key constraints. This seems strange to me, but falls
into the category of nice to know up front I guess

The most interesting point was about the move away from staging data in
SSIS because it can keep most of what it needs in memory, increasing
performance and decreasing the work to manage staging. It went on to discuss
that there was still merit to staging - one example being to stage right
after a data retrieval from a slow/remote location, and another was that by
breaking it up into a couple of steps with staging you get better
restartability (checkpoints only work on the control flow). The last bit was
pretty insightful, that most of us use the tools we know - temp tables for
staging - but that the SSIS raw files were better suited for the job. It's
easy to get used to doing things one way and not realize that the tools &
rules have changed

Yep, paid for this one too and it also stays on the shelf. As a
beginner/intermediate user I find it to be a good reference, seems like it
addresses some more advanced scenarios that would be helpful to a real power
user.

This book covers SQL 2000 and was published in 2004, somehow I missed it then
and only got around to looking at a copy late last year. I found the book to be
very approachable and my only complaint was that it didn't pay enough attention
(in my opinion of course) to data partitioning strategies. It's got very good
coverage of query tuning and index design.

Things I learned:

I've been advocated for owner (schema) qualifying objects using the two
part syntax for years, for the slight boost in performance saved due to less
work figuring out which object to access as well as the greater clarity I
think the two part conveys. I didn't realize that not using the two part
syntax can result in separate execution plans being created - maybe because
I've been using the two part syntax! Might be something worth looking at if
you're using NT authentication, it could drive some churn in the procedure
cache

A great example of how transactions can affect performance compared
doing 256,000 inserts in a loop with and without an explicit transaction.
The explicit transaction was almost 10 times faster and the log flush rate
dropped from 1100/sec to 14/sec. This an extreme example, but it does make
it clear that transactions cause log flushes, so it's worth looking at
whether you can reduce them some by using explicit transactions (please
don't start adding transactions everywhere, remember they can affect
concurrency).

I like the book and find it useful, so much so that we're now providing all
the students that attend our performance tuning class with a copy. I hope the
author publishes a new version for SQL 2005.