Developing Matt

My Technical Journal

Accidental DBA – Paul Randal, Kimberly Tripp

leave a comment »

I took a class titled ‘Accidental DBA’ by Paul Randal and Kimberly Tripp.  It was my introduction to this couple and I discovered why people love to attend their classes.  It takes a stroke of brilliance to bridge the gap between the inner workings of of a database and translate it into a common language.  Someday I hope to have enough understanding to be able to teach a class of my own.  They are my role models for now.  Soon I will be theirs.  : )   When you view the notes the first is not in any kind of outline.  It is interesting to note that some instructors are more logical in their presentation.  That is, they follow a logical topical path (Paul).   Others present a more referential structure.  They will hit a topic and apply information from a lot of places that apply to that topic (Kimberly).  Neither is correct of corse.  The former provides easier outlining structure, the latter provides a more Jackson Pollock paint splattering structure (try it).  Just as pretty; just different.  I digress.  My notes:    accidentaldbanotes

Highlights and snapshot overview

  • In 2005 manage the temp database. It stores more structures and data than ever before (worktables, sort tables, hashing, userdefined object, table variables, # or ##, triggers)  Also see Brandon Leuthke’s post on top 10 dmvs to use to query tempdb.  For all these reasons isolate the tempdb, preallocate tempdb. 
  • How to move databases: see kb 224071  Whitepaper on working with tempdb
  • dm_db_file_space_usage tells how space is being used in tempdb
  • Autoshrink is evil
  • Autoclose should be turned off
  • Autogrow is ok, but don’t let it get out of control (2005 is better than 2000).  And it can create problems with internal log fragmentation.
  • Optimize log file, only one log file, isolate log file, pre-allocate log, defrag drive where log resides
  • Anatomy of data modification (how it updates)
  • Locks are like breadcrumbs and are OK! (most)
  • See kb 271509 to see about queuing and on how to view who is blocking at the head of the chain (sp_blocer_pss80).  In kb there is a bunch of code that can be used including a dbcc input buffer (shows last command of that blocking user)
  • Nice discussion on transactions and locks and how it works (A transaction is an atomic unit.  When the transaction is complete sql server writes logs to transactions, release locks, notifies user). 
  • Purpose of a checkpoint: reduce roll-forward recovery time…batch i/o to disk….help bring what is closer to current…no guarantee on consistency (only way is with logs)
  • Recovery models discussed
  • Page types (data, index, text, sort, boot, file header, allocation maps, diff map, MI map)
  • How data is stored..the record structure
  • How data is organized (heap, clustered index, non-clustered index, lob data, etc)
  • How data is accessed (singleton lookup, range scan, allocation order scan)
  • The 5 phases of execution
  • Fragmentation and corruption (page splits and wasted space are the cause.)
  • Schemas that use page splits: guids, updates to rows with variable len columns, random insertion points, mismanaged fill factor (whatever the heck that is)
  • Corruption types
  • How to catch corruption: (set alert on error types 823,824,825….turn on pagechecksums…checkdb should be run regularly (at least with physical_only))
  • What to do with corruption

 

     

Advertisements

Written by matt

May 12, 2008 at 1:05 am

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

%d bloggers like this: