T-SQL Tuesday #23 - Respect your JOIN's

It's that time again....         

Time for the SQL Server blogosphere's...recurring, revolving blog party - T-SQL Tuesday!  This month is hosted by Stuart Ainsworth (B/T) and his chosen topic is JOIN's.

When I think about JOIN's the first thing that comes to mind is always our internal classic example of how our vendor's T-SQL all-to-often arrives:

SELECT * from * JOIN * WHERE * = *

(At least it sure seems that way...)

Of course the statement isn't anything approaching valid code but it demonstrates the underlying point - without appropriate field lists, table lists, and filters (WHERE/ON clauses) your database can not be held responsible for what it returns to you...

What you ASK the database for is what you will get - even if it *isn't* what you really WANT.

For example:

SELECT Sales.CustID, Customers.CustomerName, SUM(Sales.Amount) as Total_Sales

FROM Customers

JOIN Sales

ON Customers.CustomerNumber = Sales.CustID

WHERE Sales.SalesDate BETWEEN '01/01/2011' and '01/02/2011'

GROUP BY Sales.CustID, Customers.CustomerName

Seems innocuous enough right?

What if CustomerNumber in the Customers table is a natural key (001-34F) and the CustID column in Sales is an identity integer?  Even if T-SQL is insidious enough to do the implicit conversion for you (which depending on the version it may or may not do), you aren't comparing apples to apples and if you do get any rows back they will almost certainly be faulty.  For recent versions of SQL you will usually get an error like:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '001-34F' to data type int.

Then there's the more extreme and dangerous example:

SELECT Sales.CustID, Customers.CustomerName, SUM(Sales.Amount) as Total_Sales

FROM Customers

JOIN Sales

ON Customers.CustID = Sales.SalesID

WHERE Sales.SalesDate BETWEEN '01/01/2011' and '01/02/2011'

GROUP BY Sales.CustID, Customers.CustomerName

Now what do you get?

CustID                     CustomerName            Total_Sales
1                               MoreStuff                     10.66
1                               StuffCo                         23.90
2                               StuffIt                           23.00

Cut to the CIO reviewing his morning reports: "Sales are a little down yesterday...hmm, I wonder why MoreStuff and StuffCo have the same Customer ID...wait a minute..."

Just because a query returns results does not mean they are valid, and JOIN's (and their minions WHERE and ON) have more potential to screw up your results than anything else if you don't treat them with respect and know your data - always use the appropriate JOIN (inner/left/right/etc.) for the data you are looking for and always make sure to filter with the appropriate join criteria - compare apples to apples.

I know this really seems like T-SQL 101 stuff, but it never ceases to amaze me how many stored procedures/views/SSRS reports I open up and find the underlying problem to be related to a bad join or faulty join criteria - so apparently it is worth mentioning!  {-:

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!

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.

I am officially a SQLPeople!

My interview went up on the page a couple of days ago - pretty surreal:

http://sqlpeople.net/admin/2011/08/22/andy-galbraith/

Error 1053 While Installing SQL 2005 on Windows 2003

We are moving an old SQL 2005/Windows 2003 server from one blade to another (retiring old blade chasses due to switching hardware vendors) and unfortunately can not move it to Windows 2008 or SQL 2008 due to lack of vendor support (sigh).

As such, I was installing SQL 2005 on the new Windows 2003 SP2 box and during the install I received the following error for the Database service itself:

Unable to start service (1053)
Error Code: 0x8007041d (1053)
Windows Error Text: The service did not respond to the start or control request in a timely fashion.

I turned to my trusty troubleshooting tool of choice and Google'd the hex error code, which brought me to the Microsoft Support Page On a computer that has a multicore processor, you may be unable to install SQL Server 2005.

Sure enough, I was installing on a blade with two multi-core CPU's (Dual Socket AMD with 6 cores each) so I continued to read down the KB article.  There are two workaround methods listed for Windows 2003, and the first method (expanding the SP file and using the HOTFIXPATCH switch to slipstream the SP into the install) didn't work.  I still received the message that the SQL Service would not start.

Sigh.

I almost went looking for a different answer but decided first to at least try the second option - manually changing the number of logical processors visible to 1 using msconfig.  I followed the KB steps, checked Add/Remove Programs to see that it didn't show a SQL instance installed, and tried running the SQL install again, only to have the installer tell me that there is already a default instance of SQL 2005 present on my machine!

ARGH!

I opened Add/Remove Programs and removed everything SQL-related that was listed, and ran the install again - SUCCESS!  I then applied the SP, rebooted the machine, used msconfig to revert to "Normal Startup" and all was well.

Now I just need to remember this next time!

Branding for Dummies

Jen McCown (half of Midnight DBA) has declared this the first Un-SQL Friday, stating that it will be "a completely intermittant blog party about something SQL-adjacent." (and definitely not to be confused with Adam Machanic's T-SQL Tuesday).



I briefly considered the idea and decided that I have a different take than most of the people whose blogs I read, since I don't really consider that I have a brand, other than being yet *another* Andy - that is, I am not (in no particular order):

* Andrew Kelly (Blog/Twitter) - Performance Tuner Extraordinaire
* Andy Leonard (Blog/Twitter) - Mr. SSIS
* Andy Warren  (Blog/Twitter) - Hey, he's SQLAndy!

I apologize to any other SQL Server-related Andy's/Andrew's out there that I have omitted. {-:

As I have discussed in a previous blog, I didn't join Twitter until recently when I needed it for an online event (a SSWUG VConference) and when I did it was rather rushed - I didn't *consider* a brand.  I was just looking for a name that was relatively identifiable and easy to spell (and after several attempts I was surprised that @DBA_ANDY was available!)

My blogging career is even shorter, and when I created this blog I put a little more thought into something that seemed like it could be a brand - something that wasn't already taken and would make me stand out, and I arrived at NebraSQL - a play on words that could eventually become well-known (although definitely not as cool as being the SQL Rockstar or the Midnight DBA's)

I think there are two key points here, and they are both equally important:

(1) Your brand has to identify you - it has to relate to what you do/how you do it/where you do it/etc.  A great brand to illustrate this is SQLSkills (and if you don't know who Paul & Kimberly & co. are, I am surprised you're reading my little blog!)  SQLSkills is *the* training/consulting company for providing SQL Server knowledge, often training Microsoft employees on their own products.

(2) Equally importantly - your brand has to be unique (at least relatively so) - when you create a brand,you can't step on someone else's!  Especially if you believe you will be advancing your brand into any kind of sales/products/etc., you can not start print NebraSQL letterhead only to find out that Joe Nebra in Portland, OR, already owns NebraSQL.com for his consulting business.

For me and myself, I don't ever foresee myself becoming the next SQLSkills - but you never know!

Considering Upgrading my Certs

I am an MCITP - DBA for SQL 2005, and haven't really considered my certs for some time.  After all the recent talk about how Microsoft is changing their MCM program (BrentO here and Microsoft here) I am planning to take the upgrade exam (70-453) for my MCITP - DBA in the very near future...but the piece that makes me ponder is the MCM requirement to also be an MCITP - DBD (developer).

I have always considered myself a DBA, and while I do ETL work in SSIS and routinely troubleshoot vendor code (and created TSQL code like stored procs in my past life) I am not a .NET developer.

Reading the specs for the MCITP - DBD there is an awful lot of CLR and .NET tucked inside there...at first glance it appears like it's not for me.

Sigh.  I guess I will be satisifed with being an MCITP.