SQL Saturday Jacksonville #552 Pre-Con: A Day of DBA Fundamentals- Install- Maintenance plans-and Security

Hello Dear Reader!  I'm here in beautiful Jacksonville FL for the SQL Saturday 552 events!  Today my friend Dan Taylor (@DbaBulldog | Blog) and I are presenting our Pre-con A Day of DBA Fundamentals, Install, Maintenance Plans, and Security.

We are having a lot of fun so far, and I wanted to share.  If you are in the class or curios you can download the Deck Here.

More updates as we go!

Final Update 4:43 pm

I just wanted to say a big Thank You to all the people who attended our pre-con today and the SQL Saturday Jacksonville team!

We covered the Azure Portal, SQL Azure DB, Elastic Pools, Azure VM's, and Premium Storage.

I'm exhausted.  The class is exhausted.  It's been a great start to the event.  See you all tomorrow!

Here's the Class waving goodbye!

Update 5

We just finished up group activities working on backups and restores.  Getting the group interaction was key.  A very adapt pupil found a great way to use azure for a highly transactional system.

Not gonna lie I gave them some difficult ones to figure out.  In the process looks like Dan caught me blogging!

Alright up next Maintenance Plans, Azure, and one last group exercise!

Update 4 2:30 pm

At the beginning of the day we split the class into 5 groups.  I told them not to get comfortable and we just showed why.

In this job communication is key.  We just make all the teams regroup and reintroduce themselves.  Next we started work on discussing the importance of backups and restores.  Dan led the way preaching the importance of the right backup strategy for the right business objective.

 During lunch we had a good discussion about how to communicate with the business.  I recomended two books that have become a good starting foundation for me, and a mandatory read for everyone on my old team (Much love to the #TeamBall).

If your interested you can find them here.

Crucial Conversations Tools for Talking When Stakes Are High, Second Edition

Crucial Accountability: Tools for Resolving Violated Expectations, Broken Commitments, and Bad Behavior, Second Edition

Update 1:30 pm

Great lunch by Panera here at the SQL Saturday Jacksonville Pre-Con!  We are having a nice group exercise.  I like watching the teams interact and discuss security and what permissions should be given per scenario.

Will we use AD or SQL Authentication?

Granular permissions?

Dan never could resist a great debate on security!

Update 2 11:49 am

We've taken a break for lunch!  The class exercises so far have been a lot of fun.  We've got a smart crew here today.  So far we've covered the different versions of SQL Server, how features and licensing go together, and using SSMS to create basic options and monitor.

Great participation so far.  We had a nice side discussion on what services should sit side by side.  Lot of caveats with this and this will be a nice blog for a later day.

Oh and Devin Knight stopped by!

OK Lunch Time!



Update 1

Scott Gleason (@SQLScottGleason) Is helping us kick this off!

Great crowd so far!  I wonder what Scott thinks of my performance?

Tough crowd!

Goodbye Pragmatic Works

Normally when I write a blog, I start out will a Hello to my Dear Reader.   Not today.  This was almost a blog I didn't write because I didn't know where to start.  So let me start with the people and see if it moves along from there.


To the Consultants

You are an amazing group of women and men.  The amount I have learned from you and with you over the past four years has been staggering.  I say this to those who have left and those who remain.  It was my privileged to work with you every day.

We have too many fun memories for me to count.  I went through my pictures and found hundreds.  I think that speaks well of the times we had together, because it was important enough to take those pictures.

I've said my fair share of mushy goodbyes, listened to Baz Luhrmann's Sunscreen way to many times, and rambled on about coffee filters.  There are some words that you say in life, there are those whose meaning you know intimately because emotion surrounds them.  When I say brother, sister, call someone buddy or big guy, those words have far more meaning than their surface value.

 Each of them is the embodiment of love and relationships that have been built over time.   When I use them my heart thinks of those people in my life and those times when those words were made noble to me.

Team Ball will forever be one of those words now.

 To the Management Team

Thank you for the opportunity to be a leader.  Not just in the community but to a staff that I count as close friends.  There were challenges, struggles, loses, and wins.  The ability to shape the direction of a company is a strange and powerful gift.  So my parting advice to you is so much more simple.  Don't mess it up.

I don't say that to be glib or short, you did great before I was there, and I'm confident you will do great after I am there.  That is my hope for you.  So let me explain.

You've built something special and great.  You have an environment where people come to work with one another as part of their passion.  In my time with you I've learned just how critical proper and constant communication is to everyday success.

There is no book that tells us what to do, or how to lead or grow this company.  There will be trial and error.  Remember to listen to employee concerns, don't change course too quickly or too many times, most importantly remember Why you do what you do.  If you start with Why good things will always follow you.

You have a fantastic team.  I'm very proud to have counted myself as one of them.

 WHERE ARE YOU GOING

For everyone else reading, the next question is where am I going.   Here's a hint.

 Thank you Dear Friends.  I'm sure I will talk to you soon.

Thanks,

Brad

T-SQL Tuesday #73: IOPS on Azure VM's Naughty to Nicer

Hello Dear Reader!  Last week I posted the invitation to T-SQL Tuesday #73.  The premise? 

“As you work with SQL Server look around you.  Is your environment Naughty or Nice?  If it is Naughty what’s wrong with it?  What would you do to fix it?  Do you have a scrooge that is giving you the Christmas chills?  Perhaps you have servers of past, present, and future haunting you.  Maybe you are looking at SQL Server 2016 like some bright shining star in the east.”

I don’t have an environment of my own.  Not one that I get to interact with every day.  Recently I’ve had several experiences where some friends with various companies were having issues and the root cause was all hardware.  Memory, SAN, or CPU one had become a bottleneck.

Several had to do with I/O.  One had issues with their SAN, another had issues with their code (causing unnecessary I/O), and another on their Azure VM.

“So Balls,” you say, “What does this have to do with Naughty & Nice?”

As always Dear Reader, thanks for keeping me on track.  For this week I’m going to tackle how IOp’s can affect your SQL Server.   I’ll be combining this with the pseudo code to mimic the issue my client had.  Then a little on Azure VM’s, and how Premium Storage made a big difference. 

THE SET UP

I’ve got a really bad query.  This is from years past.  Lots of unnecessary row by row logic.  Duplicate logic.  It’s bad. 

Sufficient to say, I’m rewriting it with pseudo code because I don’t want to risk insulting the guilty.  I will post the stats though.  We were on an Azure D13 Series VM, and we moved to a DS 13 Series VM.  This fixes nothing by itself.  Click here to see the machine specs.  See below for a quick table.  We will be using the DS3 VM.  We will move from standard storage to premium storage on the same machine to highlight our fix.

SIZE – AZURE CLASSIC PORTAL\CMDLETS & APIS	CPU CORES	MEM	NICS (MAX)	MAX. DISK SIZES – VIRTUAL MACHINE	MAX. DATA DISKS (1023 GB EACH)	CACHE SIZE (GB)	MAX. DISK IOPS & BANDWIDTH
Standard_DS3\same	4	14	4	OS = 1023 GB	8	172	12,800	OUR DEMO MACHINE
				Local SSD disk = 28 GB			128 MB per second
Standard_DS13\same	8	56	8	OS = 1023 GB	16	288	25,600
				Local SSD disk = 112 GB			256 MB per second

A DS Series VM gives us the ability to use Premium Storage.  We will use that turn bad code into, faster bad code.  Fixing the code comes later.  The first thing I will do is run CrystalDiskmark against my disk. 

Our G drive is standard storage. 

M drive is our premium storage.

The M drive is a 1 TB Premium Storage Drive.  I have done nothing fancy here.  Just attached and formatted.  As you can see we received a little bit better than 128 MB per second sequential in my baseline.

Our really bad query?  It runs in 19:48 seconds on our regular storage.  When I found it in the wild it actually ran for 4 hours.  I could only replicate so much.  Sufficed to say, for this example it will do.

Without tuning the query, but moving the database to premium storage, dropping all data from memory, and rerunning the query we reduced the time to 5:49 seconds.  A roughly 72% improvement.

Premium storage can go faster.  It is a matter of configuring the VM & the Storage to hit the IOP's limits you need.  For example, this is a screen shot from the DS 13 VM I was talking about earlier.  

With a DS 14 we would have been able to go above 512 MB per second.  Premium storage on DS Series VM's is some serious good stuff.

THE WHOLE TRUTH

The query is bad.  Really bad.  I can fix the whole thing by converting it from row by row logic to set based logic.  I can get it down to 2 seconds using the merge statement.

We did.  It was a big fix.  Originally we were hoping to get the process from 4 hours down to 2 hours.  After I was done we had it down to 5 minutes.

I say this often when teaching.  I can throw super-fast hardware at bad code and make it run faster.  But there is still room for improvement.  Fixing the query is for another day.   Today we keep our IO on the ball.   Why is it so fast?

CPU	Memory	SSD	Spinning Disk
Nanosecond	Nanosecond	Microsecond	Millisecond

You can only get as fast as your slowest component.  Premium storage uses SSD's.  We are hastening our retrieval of data from our disks by a factor of x10.  In this case it made our query 72% faster.  There are other things to fix.  The IO issue is one of them.

“So Balls,” you say, “Keep our IO on the ball.  Really?!”

Sorry Dear Reader, I couldn’t resist.  As always, Thank You for stopping by.

Thanks,

Brad