SQL Server MVP Deep Dives 2, Chapter 3
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.
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,
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);
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.