DallasDBAs.com

Explaining SQL Server in plain english

Header Right

Blog

Getting Started with Index and Execution Plan Basics

by Leave a Comment

If you are a developer or new SQL Server DBA and have never really worked with query tuning, this post is for you.  If you are an experienced DBA/Tuner and want to nit-pick me saying field vs. column, feel free to leave a comment that I will delete or never publish, lol.  This is basics, yo…

I concentrate on how to measure the performance of a query, as well as the basics of “seeing” how SQL Server is executing the query to return the results.

I could type all of this up, but watching the video is going to be a WHOLE lot better teacher, so here you go:

As always, feel free to reach out via the comments here or on the video.  Or, follow me on Twitter and ask away!

Thanks for reading!

Kevin3NF

PASS Summit 2018 Game Night

by Leave a Comment

Game Night 3.0 is happening at PASS Summit 2018!!

 

Game night is an annual gathering at PASS Summit aimed at those attendees that are not necessarily the bar hopping or karaoke sort (not that there is anything wrong with that…).  Or for those that just want to spend some non-technical time with like minded people in a somewhat quieter setting and make some new friends.

Steve Jones and Andy Warren hosted the first ever Summit Game Night in 2016  and it was a great success.  I was able to teach a few newbies how to play Spades, along with some other sharks 😉

They handed the reigns over to PASS in 2017 and let them do the promotion, coordination with the convention center, etc.  PASS also bought quite a few games, partially influenced by a poll I ran last year.  They also graciously let me be the loud-mouthed spammer of the event on Twitter, Slack and wherever else SQL folks hang out.

IT’S BACK!  2 nights this year!!!

If you want more info about Game Night, or want to give feedback on previous events, PLEASE put something in a comment below and I will make sure you are heard.

This is not a free event though…there will be a choice of alcoholic or non-alcoholic beverages.  $12 gets you in the door with drink tickets.  And of course the PASS Anti-Harassment Policy applies, so that everyone can have a good, safe time.  This includes the games.  You can bring your own, and are encouraged to bring a favorite…but it must be AHP appropriate.  Assume there will be random 8 year-olds watching and you should be fine.

Updated list of the games that PASS owns and will provide:

  • 7 Wonders
  • Apples to Apples *
  • Brain Games
  • Carcassone *
  • Catan
  • Chess *
  • Clue *
  • Codenames
  • Dominoes
  • Dominion
  • Exploding Kittens *
  • Fluxx *
  • Generic card decks *
  • Jenga *
  • Monopoly *
  • Munchkin *
  • Pandemic
  • Scrabble
  • Sequence
  • Stratego *
  • Ticket to Ride *
  • Uno *

* I’ve played this 🙂

Registration links are up:

Wednesday night, 8pm – 10:30pm

Thursday night, 7pm – 10:30pm

Thanks for reading!

Kevin3NF

T-SQL Tuesday: Brick walls

by 1 Comment

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t). This month’s edition is hosted by Wayne Sheffield (b|t) who has asked us to tell a story about a time we ran into a Brick Wall.

So many walls, so few Bulldozers…

The largest brick walls I’ve ever run up against were not technical issues as you might expect…but people and processes.

Processes:

I was working for a company that bought a company that bought the company I had hired on with.  The newest owner was growing their managed hosting business by buying others out.   Not a bad approach when you have deep pockets.  BUT, they never integrated any of them.  Everything was still in its original silo.  So, anytime I got a call or a ticket, I had to search through 4-5 knowledge base/Sharepoints, 2-3 password repositories and 4 ticketing systems.  I could easily spend 2 hours to research and close a ticket that took 3 minutes to resolve on the technical side.

This place broke my flipper.  As in, I no longer gave a flip after it took a full year to get all the access I needed to every environment.  The Bulldozer here is that I got laid off.  They gave me a big bag of money to leave and go do cool things with modern SQL versions for other places.  YES!!

People:

I was asked to bill a crazy high rate for a full-time contract, to watch over a very small environment of 25 servers.  I was asked to put in best practices, be the unofficial team lead, make things go faster, secure them, etc.

BUT…JimBob the Manager (clearly not his real name) gave me the brick wall at every turn. It took 6 weeks to get rid of NetBackup in favor of SQL Server Maintenance Plans. (I know…lets not go there.).  Compressed backups?  Made me prove it.   Index maintenance?  OK, but he wouldn’t let me schedule it.  New indexes that actually made sense?  No chance, as that was a code change to the application our Very Big Vendor had written for us.  Within 3 months, I was down to 15 minutes of real work a day, and the rest spent blogging, Tweeting and answering questions on DBA.StackExchange.

The Bulldozer on this one was me walking out the door into cooler things.  33% pay cut was worth it, to save my sanity and go independent again!

Technology:

I don’t run into technical Brick Walls, because I know how to Tweet using #sqlhelp, as well as read blogs from people I trust in the #sqlCommunity, and Vendor docs.

Thanks for reading!

Kevin3NF


Your DR Plan isn’t a Plan

by 7 Comments

 

My apologies, but someone had to tell you.

You don’t have a Disaster Recovery Plan™. You have a Disaster Recovery Hope. (paraphrased from a source I’ve forgotten…)

If I’m wrong (and I hope I am), its because you are in the 10% (optimistically) of companies that actually test their DR plans and document the results.

In the 18 years I’ve been asking the following question, I’ve gotten one excellent and correct response, and one that was good, but I still poked holes in it.

“If your most important database server melted right now, how long until you are back online?”

I get a lot of responses on this question:

  • 2 days
  • 30 minutes
  • I don’t know
  • Blank Stares (its a response…just a scary one)

If you are not testing your DR plan, you may be betting your company’s existence on a flawed premise: “Our people know what to do. We have backups.”

If your DR plan is a digital “living document” that lives on your Sharepoint server, you are already in trouble.  Everyone involved needs a current, non-digital copy on their desk and at their home.

Disaster recovery is about far more than restoring databases.  But, for my mostly-SQL Server audience, let me ask you this:

Can you do an emergency test restore of your most mission critical database onto identical hardware, to a point 27.5 minutes ago and have everything work as expected?

If you don’t do test restores, you are HOPING your backups are valid. If you don’t have the ability to replace critical hardware or spin up an identical VM, you are HOPING that your server or SAN never fails.

If you are running through your DR Plan regularly, with everyone ready to go and knowing their place in the process…you are HOPING the next disaster happens when everyone is available, online and up to speed.   What if that disaster happens when your Sr. DBA is on vacation?  Who is her backup? A Jr. DBA?

If you don’t know the answer to the “How long…” question above, you really should go find out who in your organization does.  If nobody does, contact me.

If you think you have it 100% nailed…I challenge you to let me into your server room unattended.

Thanks for reading!

Kevin3NF

SQL 101: Extended Events – set up a basic session

by Leave a Comment

Setting up your first Extended Event session is much simpler than I thought it would be!

Extended Events (XE) in SQL Server showed up first in SQL 2008, but they got a GUI in SQL 2012, making them much easier to work with on the fly.  I’m one of those old DBAs that was reluctant to move away from SQL Trace via Profiler for simple needs.  Of course for more complex issues and long-term tracking I used server-side tracing for many years.

With the advent of XE, Microsoft stopped enhancing SQL Trace.  Many of the new features of SQL are not in SQL Trace at all (180 in Trace compared to 1000+ in XE now).  In addition to the video in this post, I strongly recommend you go through the 4 step Stairway to Extended Events by Erin Stellato (b|t).  I’m just now starting my journey with XE so I will be leaving out everything but the most basics steps I used…this is a 101 post after all 🙂

Please forgive the watermark…was testing Camtasia.  I’ll be buying a copy soon!

I also want to thank Dave Mason (b|t) for his post Hide and Seek with Extended Events, which helped me understand why I was not seeing my test data in “Watch Live Data” mode, and come up with the workaround you see in the video.  Dave has his own workaround that I did not test.  Lastly, thanks to my Apprentice DBA (t) for teaching me XE after I had him go learn from the Stairway and some testing on his own.   I used what he taught me the next day to capture a sample command on a production system so I could tune some code in the customer’s test environment.

Thanks for reading!

Kevin3NF


Your SQL Servers are misconfigured

by Leave a Comment

So are some of mine.

So are 90% of the servers I run across, especially in mid-size businesses ( <1000 employees, or 10-20 servers typically).

Its probably not your fault.

There are a bunch of instance level settings in SQL Server that need to be set that are specific to your environment and the workload you are going to run against it.  Most of the defaults that are in the SQL Server installer are set up to make sure the performance is acceptable on a very modest server.  Until version 2016, the installer had not changed much.

The problem is that in a large number of organizations, the person doing the installation of SQL Server is a Developer, or a SysAdmin running a script.   Both are great at their core jobs, but does either of them know what Cost Threshold for Parallelism is?  It is not in the installer…it has to be configured post install or left at a very old default number that most likely makes no sense for you.

Likewise, if you are running SQL Server installed from a script that came with a 3rd party software product, you very likely have the “safest” possible settings in place.

Some of the most common things I see everywhere:

  • Instant File Initialization not enabled
  • Cost Threshold for Parallelism not set correctly
  • MAXDOP set to 1 or ALL CPUs
  • MAX Memory left at default
  • MAX Memory left at default on a multi-instance server
  • Default file locations for system databases, or all
  • No maintenance processes

That is the short list.

Sales Pitch:  I will give you the first hour (one server) free to check on your configuration.  This involves me running a set of scripts, analyzing the results and sending back some recommendations.  Yes, I want to implement them for you as well.

Alternatively, a lighter weight version of this is the Constant Care offering from Brent Ozar Unlimited.  I can take his results and explain what they mean so that we can make intelligent decisions together.  We are not affiliated with Brent Ozar Unlimited, other than as part of the SQL Community.

Let’s talk.  Click HERE to set a meeting on my Calendar.

Thanks for reading!

Kevin3NF