fbpx

DallasDBAs.com

Explaining SQL Server in plain english

TSQL2sday

TSQL Tuesday: Folks Who Have Made a Difference

by 3 Comments

TSQL Tuesday Brent Ozar PASS Summit Ewald Cress

 

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Ewald Cress (b|t), asking us to post about those who have made a difference our life in the world of data.

I’d like to call out 3 (of the many) people who have been instrumental in my SQL Server career (and two of them will only see this if I send it to them):

The early years:   Max Lutz

Max was my manager at Jobs.com back in 2000.  He taught me quite a bit about T-SQL development that I had no clue on.  Max had been with Microsoft for some time before jobs.com and came in with a wealth of experience.  He also covered me when I took down the company by pointing out that I had implemented a “disaster recovery” plan for situations just like that.  I have not seen or heard from him since 2001.

The middle – current:  Rand Boyd

Rand is one of my best friends on the planet.  I met him in 2004 when I was on the first of two contracts I did at Microsoft in the SQL Support center in TX.  He was my go-to for calls where I had no clue what to do.   After my second contract was up I recruited him into the job I was moving to and we worked side by side every day either in person or WFH for the next 8 1/2 years.   He’s still there, I’ve moved on but we stay in touch as well as 2 introverted DBAs can.   His friendship is more valuable to me than all the SQL I know.

The current:  Brent Ozar

Cliché?  Probably.  I met Brent at PASS Summit 2008, when I was wandering around the Daily Grill at the Sheraton the first morning.   Brent was waving people over to his table to join him for breakfast.  Funny guy, super approachable and great stories.   I recall bacon.  Tim Ford was there as well and someone else I’ve forgotten the name of.   Since then I’ve been reading his blogs, buying his training and went to a pre-con of his at SQL Saturday Houston this year.  A lot of people know the same things as Brent, but his style of teaching them works as well for me as it probably doesn’t work for others.  I’ve started speaking/presenting this year and in addition to my own personality I’ve brought in some Brent and some Pinal Dave to my style.

Special shout-out to Andy Yun (b|t) for giving me the kick in the pants I needed to start the public speaking journey I began this year. 3 SQL Saturdays, one pre-con and two DFW area independent classes…all in 6 months.   I had the chance to thank him in person last week at Summit.

There are a great number of people that could have been mentioned here due to their amazing work in and for the community, but these are the ones that have impacted me directly and in person.

Thanks for reading!

Kevin3NF (<<stalk me!)

TSQL Tuesday: Interviewing Patterns and Anti-Patterns

by 4 Comments

T-SQL Tuesday is a monthly blog party hosted by a different member of the SQL Server Community each month.  It is the brainchild of Adam Machanic (b|t).  This month’s host is Kendra Little (b|t) who gives us the topic of “Interviewing Patterns and Anti-Patterns“.

Of the suggested choices, I picked:

  • You can write about your own highs and lows as a candidate or as an interviewer

I’ve been in the workforce for 26 years as of this writing…and in IT since 1998.  I have bounced around between Independent Contractor/Consultant, to Full-time employee, to W-2 contractor working for someone else’s client.   I’ve been on probably around a hundred face-to-face interviews, several hundred if you add in phone screens and recruiter calls.  On the other side of the desk I’ve interviewed dozens of database professionals, either to come work for/with me, or on the behalf of recruiters that didn’t have a SQL Server person that could do the tech screen.

I’ve seen a few things good, bad and ugly along the way.   I’ve been asked a few brilliant questions, and some seriously stupid ones (“What kind of tree would you be?”  Really? Dude….)

My number one rule for a candidate BEFORE you go into an interview:

  • Make sure you know something about the business and it’s industry before you walk in the door.  Everyone knows how to back up a database, or use Profiler, or what an Availability Group is.   Those are givens and of course you need to be able to discuss them.   But, when it is your turn to ask questions…you need to have some ready to go.   Know what industry the firm is in.  Know where they rank.  Are they gaining or losing ground?  Why?  What are they doing to get better as a firm?   This shows that you care about more than just your paycheck.
  • Be ready to admit what you don’t know!  When I was interviewing for a contract support gig at Microsoft in 2004, I told them I had never seen a cluster and had only touched replication once, in a lab.   They put me on the team that supported clustering and replication (and security and DTS and….).  Because I followed with “but I’m happy to learn them”.  I still use that line today…only now its Azure and PowerShell!
  • If the interview is over a meal, DO NOT order something you eat with your hands.   Specifically a Club Sandwich at a bank in Tulsa, OK.  You’re going to drop some in your lap and look like a slob.  If you are a slob, there’s no need to advertise! 😀

Yes, that’s more than one number one rule.  Its ok, because its my blog and that works here.  Its like magic or something.

My rules for when you are interviewing a SQL Server candidate:

  • Respect the candidate’s experience.  If she has 15 years as a DBA, don’t waste your breath on the types of backups. Its insulting.
  • Do ask what features they know well, know a little, and don’t know at all.  See above advice to candidates.
  • Do NOT make the job description look like a shopping list and put every SQL feature you’ve ever heard of in the requirements section.  If you are contemplating a Data Warehouse project that isn’t even funded yet, that goes into the “nice to have” part.   Focus on the top 5 skills that the person will spend the majority of their time on.
  • The candidates are human too…treat the first one you see as an equal, as well as the 10th, or 100th.   They are giving you their time, just as you are using theirs…but you are getting paid to be there.  The relationship and interaction with them in the first interview may very well set the tone for many years of working with this person.

Some wacky stories from my past:

  • See food suggestion above.  That was me, obviously.  Probably had food in the beard as well.  Maybe even a bat in the cave just to round it out.  Never heard back from them, after they flew me in for the lunch conversation. Be professional.
  • I’m a pretty confident interviewer…but I got too confident for one a few years ago and didn’t really prep.  Did it from my truck outside a Taco Bell (hardly the first time).  Nobody told me there would be 4 other people on a bad speakerphone in addition to the person I was supposed to talk to. Nor did they tell me that Negative Nancy was going to hit me with trick question after trick question.  I said early on I was not a SQL developer…so she spent 20 minutes asking me T-SQL syntax questions.  I could hear the giggle in her voice.  I finally said I was done and left the call.  Told the recruiter there was no way I would ever work for that team.  Recruiter told me I did well based on what she heard back.   NOPE.
  • Best question I ever asked:  Interview with a home builder (there are dozens in the DFW area) post-2008 recession.   Knowing that the housing industry was absolutely pummeled, I asked how they weathered the storm, and what actions were taken by the company to protect themselves.  The previously quiet manager of the IT dept. piped up and said “We laid off 60% of the sales force, and built this amazing data center since materials were extremely cheap.”  I showed him I was thinking outside of the (SQL) box, and responded by joining the team lead and me in the conversation.  I got the gig, but turned down the FT offer a few months later.

That’s all I have.  Well…I have tons more, but there are other posts in this months TSQL Tuesday for you to read, so go do that!

Thanks for reading,

Kevin3NF

TSQL Tuesday – Changing Times

by Leave a Comment

T-SQL Tuesday is a blog party started by Adam Machanic (b/t) over five years ago. The first Tuesday of each month a blogger hosts the party and suggests a topic. Anyone who is interested blogs on that topic on the second Tuesday. It can be a lot of fun and quite a challenge to blog on a topic you didn’t pick.

This month’s topic is presented by Koen Verbeeck (b/t), who has challenged us to write about “The times they are a-changing”, a response to Will the Cloud Eat my DBA Job? by Kendra Little (b|t).

I previously blogged my Predictions for the future of the DBA role, so I offer these posts as my T-SQL Tuesday submission:

Prediction – The SQL Server DBA Role

Supporting post on why I am embracing PowerShell

Yes Koen, the times are indeed changing, as they have been for years and always will.  Remember, your mobile phone has more power than the best computers 50 years ago.  And when was the last time you actually saw a buggy-whip? 🙂

Anticipate the changes, test the betas, learn the terminology….or be left behind maintaining that old SQL Server 7.0 install in the corner that everyone is scared to touch.

Thanks for reading!

Kevin3NF

Feel free to stalk me on twitter…

 

TSQL Tuesday: The daily WTW?

by Leave a Comment

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Kennie N Ponotoppidan (b|t), and covers the topic “The Daily Database WTW” (yes, I know,  I changed it to “What the What”.  Personal preference…)

Kennie is asking us to post about the most insane thing we’ve seen in SQL Server.   Mine was so bad I’ve already blogged about it here, so I’m just going to copy and paste…because I’m lazy 🙂

I walked into a new client in 2015 and found this, all one one server:

o   SQL 2008 R2, RTM
o   Incorrect Memory configuration
o   Full recovery model on data that only changes once a week at most
o   ZERO documentation of server settings
o   New data is imported to a new table and a UNION ALL query is modified to add that table
o   ZERO documentation of code/databases
o   Stored Procedures have no comments, poor formatting and developer names in the name of the proc
o   Autogrow is 1MB, data imports are hundreds of MB each
o   Everyone is sysadmin, probably including you…
o   Change control process is intentionally shortcut and ignored on this internal production system
o   Ownership changed to me in December, then was yanked back 3 weeks later with developers overwriting my fixes in prod.

 

I still feel the stench of that assignment…but its slowly wearing off 🙂
Thanks for reading!

T-SQL Tuesday: Shiny New Toys

by 1 Comment

T-SQL Tuesday is a blog party started by Adam Machanic (b/t) over five years ago. The first Tuesday of each month a blogger hosts the party and suggests a topic. Anyone who is interested blogs on that topic on the second Tuesday. It can be a lot of fun and quite a challenge to blog on a topic you didn’t pick.

This month’s topic is presented by Matt Gordon (b/t), who has challenged us to write about “Fixing Old Problems with Shiny New Toys”, specifically new items introduced in SQL 2014 and up.

True to my typical post style which focuses on small shops, accidental DBAs, and junior DBAs I went looking for something that could very easily benefit people that are using the basic SQL Server features.  In this case they may not even realize how limited they were.

I chose to write about Index Maintenance in the built-in Maintenance Plan portion of SQL Server.

A brief summary of the built-in Maintenance Plans is that they allow you to drag-and-drop your way to basic SQL Server maintenance items such as Backups, Index maintenance, CheckDB, Statistics updating, etc.  This a tool that has been around since at least version 7 that I know of.  It wasn’t always very good, and it gets a bad rap from a lot of DBAs.  It has been dramatically improved over the years in flexibility and reliability.

In SQL 2014 and before, you had a choice to rebuild or reorganize indexes, and it was pretty much an all or none proposition.  There were a few options, but still limited.  If you wanted to get granular, you needed to roll your own code, buy a 3rd party utility, or use something like Ola Hallengren’s free scripts (known everywhere as the “Ola scripts“).

2014 Rebuild task…a few choices, but not a lot.

2014 Reorganize Index dialog…even fewer choices:

Don’t misunderstand me…its great that these options are available, especially for the SQL Server customer that doesn’t have a DBA or the budget for a 3rd party product.   The biggest downside is that they will rebuild EVERY index on the selected database(s)…whether it needs it or not.  This can cause significant blocking, resource usage and transaction log files that can grow way beyond what is expected.

In SQL 2016, Microsoft introduced the ability to Rebuild or Reorganize based on Fragmentation %, Size of the Index (page count) and usage:

These options allow you to rebuild at whatever levels make sense to you. Taking a hint from the Ola Scripts, I set mine to rebuild everything over 30% fragmented, and over 500 pages in size.  “Used in the last xx days” is a nice feature as well.  The stats sampling option is something I didn’t know was there until I started writing this!

Similarly, Index Reorganize is also now allowing you to get more granular:

These options give the small SQL shop the ability to “set it and forget it” in ways that are more intelligent than previously available.

Pro tip….do the rebuild first, so you don’t accidentally Reorganize everything over 5%, including the ones over 30%…

There are dozens of articles and blogs on Rebuild vs. Reorganize, so I won’t re-hash that here…but I will Google it for you.

Many DBAs will scoff at this and turn up their nose at SQL Maintenance Plans…but they definitely have their place.  Not every shop can use a “free” product such as the Ola Scripts, or have budget for MinionWare.   An additional great aspect of this is that since Microsoft wrote it, they support it!

Large shops with multiple instances, and a DBA staff…go get ’em!  If you are a small shop looking to upgrade from 2008, R2, 2012…this is only available in 2016…which just runs faster, and has lots of Shiny New Toys!

Thanks for reading,

Kevin3NF

T-SQL Tuesday: Connect item – Last Log Backup Time

by 1 Comment

T-SQL Tuesday is a blog party started by Adam Machanic (b/t) over five years ago. The first Tuesday of each month a blogger hosts the party and suggests a topic. Then anyone who is interested blogs on that topic on the second Tuesday. It can be a lot of fun and quite a challenge to blog on a topic you didn’t pick.

This month’s topic is presented by Brent Ozar (b/t), who has challenged us to find our favorite Bug report or feature request on MS Connect and write about it.

I don’t have a lot of experience with Connect, other than landing there from Google a few times while researching stuff.   I’m pretty sure I’ve never filed a report or request.  So, I just went looking through the latest requests and found one concerning “Check the time for the latest log backup” from none other than Ola Hallengren (w).

Ola’s feature request is:

I would like to ask for a way to check when the last log backup was performed.

His reasoning for the request:

This enables you to have a backup strategy with more frequent transaction log backups when there is a lot of activity in the database.

And his proposed solution:

Add a new column to sys.dm_db_log_space_usage or sys.database_recovery_status called LastLogBackupTime.

I LOVE this idea…back up the T-log more frequently during busy times, less often during off hours.  At my current client, there is almost nothing happening outside of a 12 hour workday window, so this would be perfect here.

Now, I am possibly misunderstanding Ola’s request or the intent…and that’s ok.  This query from the msdb..backupset table already contains this info via a relatively short amount of code:

Use MSDB
go

Select
    [Database_Name] as [Database Name],
    DB_id([database_name]) as [DB ID],
    Max(backup_Start_date) as [Last log backup]
From
    msdb..backupset bs
Where 1=1
    and db_id([database_name]) is not null
    and type = 'L'
Group by 
    [database_name]
Order By 
    Max(backup_start_date)

This returns a result set of:

So…the info requested can already be gathered.  There may very well be other ways to do this, or querying this table may be deprecated.  It seems that Ola is looking for a cleaner solution by adding to an existing System View in MSDB in order to more easily tie it to log size.

I do know and respect Ola’s maintenance scripts, so I have posted a comment on the Connect item asking for some clarification. And, I have voted it up.  I encourage YOU to do the same….as this simple request is probably going to wind up in his scripts 🙂

Thanks for reading!

Kevin3NF