Skip to content

Don’t Plan to Fail

2012/02/21

SQL Server MVP Deep Dives 2, Chapter 3

Architectural Growth Pains – by Chris Shaw (B|T)

One of the things that struck me after I finished this chapter was how readable each topic has been. I realize this is only my third review, but I’ve read ahead a bit and it holds true for at least six of the MVP authors and I am guessing it will be true for all sixty. Chris Shaw pulls you into the possibly dry topic of database design with an anecdote of his days training to become a United States Marine. I grew up a military dependent (aka a Brat) so I related to Mr. Shaw’s story quite well. Even if he is a Jarhead. (Sorry Mr. Shaw, but my dad was a pilot in the US Air Force. Marines will always be Jarheads in my worldview.)

After the introduction Chris outlines some main points on what should be looked at when designing a database. He stresses that a good design in the beginning can save you many headaches down the road. Once again I came away from this chapter learning a few new things. One of them is so simple that I can’t believe I haven’t stumbled on it before. While describing how to see all of the indexes on a table he uses the sp_help stored procedure. It’s probably a testament to how much I live in SQL Server Management Studio and use the GUI interface. I’ve used the system views to access similar information before, but I like the report-like format that sp_help returns.

The first design point Chris discusses is to Manage Your Data Types. He uses a great example about a key field being defined as Int (max value = 2,147,483,647) instead of the larger BigInt (max value = 9,223,372,036,854,775,807). The column was being incremented by one for each row (an Identity column) and when it reached the max value the database crashed. The system was down for quite some time until the change could be made and then populated through any applications that used that column of data. I first read this chapter on my morning bus ride and imagine my surprise when I came in to this error message in my inbox:

…table failed to load fully (partial data did load) due to an arithmetic overflow error on the identity column.
Arithmetic overflow is just a fancy way of saying the number was too big for the data type defined. This identity column had of course been defined as Int several years ago with no expectation of going over 2 billion rows. Luckily the solution was much easier than the one Mr. Shaw described (and even luckier I was not the one on the hook to fix it when the 2,147,483,648th record was trying to insert at 3am), but it did inconvenience several of the data team’s internal customers.
Several pages are spent discussing Indexes and the need to monitor for both Overindexing and Underindexing. Underindexing can lead to poor performance when pulling data out of the database and will only get worse as the amount of data increases. This makes logical sense because it is trying to look up something in a text book that you can’t find in the index. No index means it will take much longer. Shaw explains how to view a query’s execution plan to see that indexes are not being used. I probably don’t spend as much time as I should reviewing query plans, but after the examples in this chapter and in the first two I already find myself looking at the plans more often. In addition to the query plan Shaw shows how to query the DMVs (Dynamic Management Views) to look for missing indexes. He points the reader toward a script written by MVP Glenn Berry (B|T). I hope they don’t mind if I share it here again.
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], 
migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],mid.equality_columns, 
mid.inequality_columns, mid.included_columns, migs.unique_compiles, migs.user_seeks, 
migs.avg_total_user_cost, migs.avg_user_impact 
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK) 
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) 
ON migs.group_handle = mig.index_group_handle 
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) 
ON mig.index_handle = mid.index_handle 
WHERE mid.database_id = DB_ID() -- Remove this to see for entire instance 
ORDER BY index_advantage DESC OPTION (RECOMPILE);
Shaw also shows the query for finding indexes that are not used. This is included in Glenn Berry’s full script of Diagnostic queries which you can find at Glenn’s blog,  SQL Server Performance. To complete his advice on indexing Shaw leaves us with some reminders about maintenance and fill factor. I am familiar with both of these topics now, but I wish I had read his explanation much earlier in my DBA career. This echos the point in the chapter’s summary that it isn’t what we do know that can get us in trouble, but what we don’t know. Good Advice.

Chapter Three SQL Server MVP:

Chris Shaw (B|T) started his database career while in the Marine Corps working with Lotus Ami Pro databases in 1993. From there he went on to companies such as Wells Fargo, Pulte Mortgage, and Yellow Pages Inc., and later consulted with insurance companies, including Anthem Blue Cross and Blue Shield. Chris has enjoyed writing and speaking about SQL Server over the last 10 years at events such as SQL Connections, PASS (Professional Association for SQL Server), and SQL Server Worldwide User Group (SSWUG) ultimate conferences (where Chris was the Conference Chair). He’s the founding member of the Colorado Springs SQL Server User Group.

If you are interested  in growing your SQL knowledge by reading SQL Server MVP Deep Dives Volume 2 you can acquire a copy at Manning Books. I don’t get anything out of it if you buy the book, but you might.

Advertisements

From → SQL Server

2 Comments
  1. Thank you for the kind review of the chapter. There is a lot of work into that chapter because I wanted it to be the best that I could do. I feel very honored to be amoung the authors in the book, and to have a chance to work with the editors was awesome.

    • SQLMD permalink

      You are more than welcome. I am learnng a lot by not just reading ,but also writing the reviews. I probably read your chapter four or five times to make sure I didn’t forget anything.

      It sounds like a great project to be a part of. I don’t know of anything else like it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s