Review: MS SQL Server 2008 Internals – Core knowledge for all DBA’s

  • Article

Following on from my review of my library. This week, Microsoft SQL Server 2008 Internals.

Summary:

This is not as the title might suggest, “special” information for elite DBA’s. It contains the base knowledge that all DBA’s need in order to understand what is happening under the covers. It may stop you from making stupid mistakes, ie: The “computer equivalent of driving from Sydney to Brisbane with the handbrake on” or the classic “I didn’t even consider the IO load of TempDB, is that important?”. It is knowledge you are unlikely to get by just downloading white papers. At more than 700 pages it is only for those who are serious about developing their skills in SQL.
Note: It is only applicable to the Relational Engine. It has no coverage of SSAS, SSRS or SSIS, for those areas, see my comments on Resource Recommendations - Integration Services (SSIS & DTS), Resource Recommendations - Data Mining. (I just realised I haven’t posted the info on the SSAS Bible ( Microsoft SQL Server 2005 Analysis Services)  & nor the SSRS resources. I will do soon).

  image

Recommended Audience: SQL Leaders - Everyone who considers themselves “a database person”.  It is stuff you should know, its not everything but forms a good base to build your expertise on.

Version: Predominantly SQL 2008, & SQL 2008 R2 when it ships in 2010. A lot of this book also applies to SQL 2005. But 10 – 20% of the book is only relevant to the new 2008 features & low level changes that do not apply to 2005. Similarly not all the great content in the SQL2005 Internals books made it into this book & they are vey useful in 2008 installations. So I’d consider buying both, or just getting the SQL2005 Internals books if you have yet to convince your management of the significant cost / benefits of upgrading to SQL 2008. Then get this one when they are ready (perhaps it will be called SQL11 or SQL 15 Internals by then) ;-(

Detail:

This book is written by a team of authors. All are very talented. If you’ve been deep in SQL for a while you will notice a difference between the chapters. In some chapters you may think, “I figured that out myself, anyone with a strong knowledge of the once undocumented features like (DBCC PAGE, DBCC LOG) has probably explored to see what that does”. But a few of these chapters will have you thinking “How did they figure that out?”.  At which point it will become clear that these guys are / were Product Managers on the SQL Engineering team. They didn’t just read the source code, they designed it that way. So some of the chapters gives you the why it works the way it does & not just how it works.

Chapter 1: SQL Server 2008 Architecture & Configuration

As the title suggests: Architecture; Storage Engine, Query Processor, Buffer Pool, Checkpoints, Lazy Writer, Resource governor, SQLOS, Scheduler, & the configuration options you need to set to control it all. Especially relevant to those who deal with larger servers with more than 8 CPU’s.

Chapter 2: Change Tracking, Tracing & Extended Events

Unfortunately it would easily be possible to write an entire book on either Extended Events &/or Tracing for load generation, stress testing & performance tuning. This chapter only got 50 pages. It does a good job on highlighting these important & often overlooked features. It details how to set up & use these key features. As I doubt anyone will write a such a niche book, use this as a start & read up on Blogs & white papers for the rest. Extended Events are really powerful & few people have realised what they have.

Chapter 3: Databases & Database Files

Nice coverage of how to control your IO; Understanding the different load characteristics of Log, Data & TempDB. Creating / Shrinking filegroups, Database Options, File formats; GAM, SGAM, IAM. PFS etc. Understanding Database Snapshots.

Chapter 4: Logging & Recovery

This is not When & How to back up. But my favourites for performance tuning; Recovery Mode impacts IO, Understanding Read / Write loads & how they vary with Recovery Mode & checkpoint. Also the core of what is happening in the log files.

Chapter 5: Tables

This provides a lot of detail on the On-Disk layout of the table & explores the impact of different data types / NULLs. It doesn’t provide a lot of recommendations but will be a good reference for these important basics. 

Chapter 6: Indexes: Internals & Management

After the Optimizer, Indexes are the most commonly stuffed up thing in a database. eg: “We don’t have indexes on our foreign keys as maintaining indexes will slow up the updates.”  “So … you don’t think that doing a Table Scan to find each row before you update it could be slowing things a bit? Oh & in a cursor too … priceless.” WELL THERE”S YA PROBLEM!!
This is a big chapter: It shows their on-disk layout, DMV’s to analyse them, Key Index creation clauses & their affect. Commands that let you explore your indexes. Good coverage on Special Structures like Computed Columns & Indexed Views. But unfortunately almost nothing on XML Indexes, Spatial Indexes & integrated Full Text Indexes. A bummer given the huge perf benefit gained over the common “WHERE x LIKE ‘ABC%’ ” queries.  I guess it is hard to sell a 2,000 page book & they do point you to other resources. To compensate they did put a ton of time in addressing the issues people face on Index Maintenance, Fragmentation & what really happens when DML statements (Insert, Update & Deletes) change data.  

Chapter 7: Special Storage

I’ve not seen this info elsewhere, covers new features of  LOB & Row Overflow, Filestream, Sparse Columns, Data Compression, Backup Compression & a small bit on Partitioning Storage. Nothing you couldn’t figure out with DBCC PAGE & a lot of time, but isn’t it nice you can quickly read it instead.

Chapter 8: The Query Optimizer

A few months ago someone asked me “How can you just look at my query & say “it’s gunna suck”? Do you just walk around with the optimiser in your head?”. I thought “well, yer, how else would you tune queries”.  This chapter is the highlight of the book. A lot of details, a little boring, but details you need to know. Once you know how the optimiser views the world & the way it uses statistics, then you are much better able to interpret the show plan output (also covered in this chapter). You are also able create effective test data & realistic load tests. This is a good compliment to the “How to read ShowPlan” documentation in Books Online, Whitepapers & the section in SQL 2005 Internals books & Itzik Ben-Gan’s TSQL Query Tuning 2005 Books

Chapter 9: Plan Caching & Recompilation

Walks thru how your queries can impact plan cache, why it is important & the Dynamic Management Views (DMV’s) you need to understand where to start to tune things.

Chapter 10: Transactions & Concurrency 

This chapter is a good start but doesn’t go far enough. It tells you “What it is & How it works” both very useful but doesn’t give you methodology on how to troubleshoot locking issues. Hopefully it will give you sufficient insight to figure that out on your own. Still, If you’ve even wondered what a SpinLock or a Latch or Intent lock is, this chapter is for you. It shows you how to discover what locks your query is taking. How Lock escalation works, helps you discover how much memory is used by locking & gives insight on the newer features like Row Versioning & Snapshot Isolation.

Chapter 11: DBCC Internals

This is not an exhaustive coverage of all DBCC Commands, many of which are flagged as depreciated. It is mostly about one, the DBCC CHECKDB command & the related consistency commands like TABLE & ALLOC. Given SQL’s reliability you may never need to know about any of this. But if a disk error turns your Database to custard then you will really appreciate the level of insight this chapter gives you in really understanding what the diagnostic messages are telling you. It may make the difference between you “coming up smelling like roses” or merely smelling like Rose fertilizer. (for those not horticulturally inclined I’m told this is best obtained from the back end of horses & cows.)

 If you are too busy to walk to a book store & too lazy to type a search into Bing or Google, the following are links to Online Retailers.

Australians:

Rest of World:

Please tell me if you find these reviews useful (or not)

Dave.

Thought for the post:

If there is someone naked or a couple are intimate on the TV we get embarrassed & often stop our kids from watching. We get horrified if our teenagers can use the internet to look at nude people.
Yet we think nothing of them watching action movies where people are shot, hit, stabbed or otherwise killed, injured or maimed without consequence. We buy our kids computer games where they can role play hurting & killing others.

Why does naked or amorous = Unacceptable.
Yet someone walking around with a loaded weapon which they can kill you on a whim = Acceptable.

The “right to bear arms” vs the “right to bare arse”.  Who sold us on that plan?

I too, find myself playing shoot-ups with my kids & stop them looking at porn. The more I think about it, the more I wonder … why was I born so stupid?

(sorry this thought for the post started life as a joke but I just had an epiphany)

 

Technorati Tags: TSQL,Book Recommendations,Book Reviews,SQL 2008,SQL Server 2008