One DBA's Ongoing Search for Clarity in the Middle of Nowhere


*or*

Yet Another Andy Writing About SQL Server

Friday, September 30, 2011

Things I Learned from Kimberly & Paul in 140 Characters or Less

As I mentioned previously, we had Paul Randal (B/T) and Kimberly Tripp (B/T) from SQLSkills in town this week at our local user group (http://www.omahamtg.com/ / @OmahaSSUG) and I wanted to share some of the things I gleaned (your vocabulary word of the day) from the 2.0-2.5 hours.

Why 140 characters or less?  I learned a few years ago a great way to compile the bits and pieces that I pick up in technical presentations, webcasts, etc. was to Tweet them - it gave me a record to go back to when I had more time to actually consider and digest the information and it had the side bonus of putting it out there for anyone who happened to be following me or my tags so that they could benefit as well.

The catch to anyone else who wants to do this - you have to make sure you credit the speaker (as seen in my tweets belows) *and* you have to make sure you accurately render what they are saying.  The only thing worse than tweeting/blogging/posting something from a well-respected source without giving them credit and asking permission is putting their name on something different from what they actually said that is *wrong*!

(Note – I had Paul verify that they were OK with my blogging this information since it is 100% their content compiled by me – even though it was presented at a free user group it is still SQLSkills’s content!)

-

Here is a record of the items I tweeted that evening, with a few explanations or comments along the way:

@PaulRandal @KimberlyLTripp & @sqlrus prepping for the @OmahaSSUG special user group - too cool! #sqlpass #sqlskills http://yfrog.com/kg18cpj

(Sorry, this is me being a geeky fanboy - sue me!)

From @KimberlyLTripp at @OmahaSSUG - use RAID 10 for safety, but 0+1 may perform slightly better #sqlpass #sqlskills

(Kimberly explained how even though many vendors pitch 0+1 as RAID 10, they are distinctly different and 0+1 has greater impact since you lose an entire group of drives if just one drive fails rather than losing just half of a single pair under RAID 10)

From @KimberlyLTripp at @OmahaSSUG - use Instant Initialization to help autogrow quicker w/o having to -0- out all space #sqlpass #sqlskills

(This one is pretty self-explanatory other than the side note that this only works for DATA files - LOG files must be -0- initialized as mentioned by Kimberly & Paul later in the evening)

From @PaulRandal at @OmahaSSUG - use log_reuse_wait_desc in sys.databases to see why the tran log isn't clearing #sqlpass #sqlskills

(Paul explained that if your transaction log is still showing a large amount of space "active" (non-clearable) and you don't know why, this column will show what operation or item is blocking the space)

From @PaulRandal at @OmahaSSUG - TRUNCATE TABLE is still fully logged - just runs efficiently via "deferred drop queue" #sqlpass #sqlskills

(Paul explained that there is no such thing as a non-logged operation - some things are more efficiently logged than others and TRUNCATE TABLE is an example of such an operation; everything is logged to some degree because it is necessary for crash recovery if the SQL engine goes down)

From @PaulRandal at @OmahaSSUG - Tran logs are not written in parallel so no gain from multiple log files #sqlpass #sqlskills

(Paul described how the only time you ever really should need an additional log file is if your primary transaction log file fills its disk and you need to add an additional file to prevent the database from freezing up)

From @PaulRandal at @OmahaSSUG - num of tempdb files - start with 1/4 or 1/2 num of cores - too many files can be slow #sqlpass #sqlskills

(Paul discussed that old logic was number of tempdb data files should equal the number of cores in the box, but that was several versions old; it is better to start with 1/4 or 1/2 the number of cores and expand only if needed)

From @KimberlyLTripp at @OmahaSSUG - overindexing can be worse than underindexing & always automate index maintenance #sqlpass #sqlskills

(Kimberly described how often developers and vendors index every single column unnecessarily causing far too much storage to be taken up by the indexes relative to the actual data itself; she also talked about how index maintenance (reorg's and rebuilds) are important for indexes to be effective)

From @KimberlyLTripp at @OmahaSSUG - missing index DMVs tune plan that was executed but DTA considers other query plans #sqlpass #sqlskills

(Kimberly talked how about the Database Engine Tuning Advisor (DTA) can be superior to the missing index DMV's because the DMV's only consider the query plan that was actually used, while the DTA actually considers other potential query plans that might be used if other indexes, etc. were present)

From @KimberlyLTripp at @OmahaSSUG - Great indexes may not even be used by optimizer w/o accurate updated statistics #sqlpass #sqlskills

(Again, pretty much self-explanatory - create statistics and keep them updated!)

From @KimberlyLTripp at @OmahaSSUG - Use "sp_create_stats 'indexonly', 'fullscan'" to create stats on secondary columns #sqlpass #sqlskills

(This one was particularly interesting - the concept of creating statistics on secondary columns on indexes was new to me - blog by Kimberly here on statistics creation/maintenance)

From @PaulRandal at @OmahaSSUG - Page splits can cause 50 times as many TLog entries as a similar operation w/o split #sqlpass #sqlskills

(This is one that makes me chuckle every time I see someone mention it - Paul orchestrated a worst case scenario involving splits upon splits upon splits, all of which need to be handled before the actual insert commits)

From @PaulRandal at @OmahaSSUG - Rebuilding clustered index does *not* rebuild all nonclustereds (2005+) #sqlpass #sqlskills

(This is one that I was actually wrong on - I still bought into the old incorrect belief that rebuilding the clustered *did* rebuild the related non-clustered indexes - it was interesting (and a little scary) to find out otherwise!)

From @PaulRandal at @OmahaSSUG - alert on informational 825's in SQL Error Log - shows potential I/O subsystem problems #sqlpass #sqlskills

(This one I had no clue about - Paul described how 825's are informational "read-retry" messages, showing that a read has only succeeded after failing 1-3 times, and how dangerous this can be since the accompanying failures aren't logged as long as the retry is successful - blog post by Paul here)

From @KimberlyLTripp at @OmahaSSUG - Number 1 DBA issue - test test test and test some more #sqlpass #sqlskills

(Again, self-explanatory - test your backups/disaster recovery/maintenance/indexes/stored procedures/etc/etc/etc)

Big thanks to @PaulRandal & @KimberlyLTripp for an amazing @OmahaSSUG meeting - great stuff (for free!) #sqlpass #sqlskills cc\@sqlrus

Once again - thanks to Paul, Kimberly, and SQLSkills for all you do - each time I see you (now twice at SQLConnections and once at this user group) I feel like my head is going to explode from the knowledge you cram in - and it is great!

Thursday, September 29, 2011

It's the Paul & Kimberly Show!

Paul Randal (B/T) and Kimberly Tripp (B/T) from SQLSkills are in town this week on a training engagement with a local company and one of the employees approached them about speaking to our local user group (http://www.omahamtg.com/ / @OmahaSSUG) and it was great!

Many thanks to Paul & Kimberly for their presentation on Ten Mistakes they Frequently See and to our User Group Co-Leader John Morehouse (B/T) for coordinating the event.