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.

Professional SQL Server 2005 Integration Services by Brian Knight & many others, $35 at Amazon

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.

SQL Server Query Performance Tuning Distilled by Sajal Dam, $35 at Amazon

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.