Tuesday, October 11, 2016

T-SQL Tuesday #83. We're still dealing with the same problems.



This months T-SQL Tuesday is hosted by Andy Mallon at T-SQL Tuesday #83 , the subject is "We're still dealing with the same problems."

In the 20+ years I have been working with MS SQL Server, some issues continue to pop up, miss configured NIC settings, badly configured servers in general, bad disk configurations, horrible use of tempdb, table variables, SQL Variant... just to name a few.

However, in most of the jobs I have had, I come in as the DBA for a system already in use, sometimes for many years.  And many times without there ever having been a person in charge of the database(s).  I end up dealing with pre-existing issues in the architecture of the databases.

The one thing I see over and over, is a complete lack of foresight in design.  Or as I like to call it "Spec Fixation,"  which is where things are developed exactly to specifications, without any thoughts to how the system will be used, or what could be needed in the future.

Two examples are in order I believe.  One worked out, the other, well, still a work in progress.

Example 1:
Working to maintain listener information, such as age groups, for a large nation wide radio company, the developer I was helping was developing it with the 3 buckets hard coded for age groups.  I rejected the design and coached him how to make the buckets table driven.  He kept insisting that is not what the design called for, to which I replied I really did not care what the spec said as long as we met the minimum.  Since I refused to move anything to production that was not table driven, he finally accepted my solution and implemented it.  On the day of the deployment, everything worked the way the business wanted it.  However, by 4 PM, the business manager came to him and basically said "uhm, you know, we really need more fidelity to this data, can you make it 5 buckets instead of 3?"  In 15 minutes of work we had it with the 5 buckets.  Estimated delivery time if we had gone with his original design would have added 2 columns to the table, changes to the front end etc.  Win!

Example 2:
ETL.  Spec said only Address Line 1 is needed to be loaded, so the developers only bring that line in (plus name, city etc.).  Fast forward 8 years, I get a request on my desk: "Please add Address Line 2 to import, and all tables.  Oh, and we need historical data for previously loaded files.  And for all address types".

Groan.
No normalization in this database (which is just one of about 40 databases with SIMILAR structure, but not identical).

2 weeks of research, here is what I found that needed changed:
60 tables
10 views
260+ procedures that reference the above tables and views.
1 DTS(!!!!!!!!!!!!!!!) package
1 SSIS package.

And that was just to get data INTO SQL.  Also needed to rewrite to get the data back out to the client after we did our job with it.  Oh, and no way to get the historical data loaded.

This could ALL have been prevented if the original developers had just loaded all address information available in the file from day 1 (all lines have been in the file since the very first test file we got, which I managed to dig up).  Instead it was a major undertaking.


So, please, take a moment to think how the system will be used, and how it could evolve.  It can save major headaches in the future.

First time presenting at a SQL Saturday (#560 Charlotte)

Back in February I ended up doing a presentation called IO Tuning 101 for the local PASS chapter, so when the local BI PASS chapter announced they where looking for speakers for a SQL Saturday in September I cleared my schedule and decided to send in my submission.

I had tried to get the speech in for Summit, but was turned down.  Part of it was lack of experience, both in submitting and speaking. Admittedly it was an extreme long shot for Summit, but you never know if you do not try.

I was however happy to have been selected to speak at SQL Saturday #560 in Charlotte on September 17, 2016.  This approximately 1 hour introduction to query tuning using SET STATISTICS is an extreme entry level speech, no slides, just demos and me explaining what we are seeing on the screen.  It shows how some very simple tuning of disk I/O can drastically improve query performance, and is something just about anyone can try out.  This is a reactionary tactic, not something that necessarily works in the design phase, but in 20+ years of doing this, one of the most common requests I get goes along the lines of "this ran fine yesterday, why is it slow today?"

Was I nervous?  Yupp.  Did it go well?  I thought so, the feedback was mostly positive, some thought it was too basic, but that is the entire reason for this presentation.  Having seen so many good speakers coming to our user group to speak, most of it is too high end for a lot of people and it scared them off.  I'm going to be working on a series of introductory topics, and intermediary topics, too try to help more people get up to speed.  Now, too find the time....

And yes, I do plan on applying to speak at more SQL Saturdays.  It helps me learn, and hopefully it helps other people in our community.