Got a EMC DataDomain?

Are you restores slow?

Recently we had to restore our 5TB prod database to rebuild an AG node. Here’s some things we learned along the way to hopefully help you speed up your restores.

Backup your VLDB to multiple files. We found 12 to be the sweet spot in our setup. Make sure you’re going to 10gig NICs on both ends of the transfer.

In terms of folder directories on that thing we learned to go wide and small. Let me explain. Our setup is this:
\\datadomain\sql\sqlbu\\\Full or Tlog

Under Tlog we break out by day of the week. We’ve found too many files in a folder cause the DD to choke.

We also learned that you can overrun the DD with work. We could kick off all our backups to an Isilon and be fine. The DD can’t handle that, we’d get “network name not found.” The DD is serving up CIFS and it appears to be not too hard to overrun. To avoid this we use CMS to stagger our backups into groups at different times. Tlogs are generally okay running at top/bottom of the hour. Probably because they’re small and quick.

There’s a registry key you can modify to change the timeout (I don’t have the link) for timeout for CIFS in Windows but we weren’t comfortable with that idea.

That’s all I’ve got for now, if we think or find more I’ll update this posting.

EDIT/UPDATE Here’s some EMC KBs that may help:
KB181958_Poor_Restore_Speed
KB182042_DDR_Performance_TroubleshootingGuide_PDF
KB182188_CIFS_and_Networking_Tuning
KB182268_TroubleShooting_CIFS_Performance

Second Edit: Our OS for DataDomain is 5.5.2.2 as of this writing with all the above

Quick SSRS report builder tip

I started building a report in SSRS. I had never done this before. I learned two things over time that I wish I had known from the start and I want to share them below.

1) Use stored procs for everything
2) When you hit preview, if your report doesn’t look right, maybe you added some rows to your get stored proc and you don’t see the new data in VS but you do in SSMS, hit this thing https://imgur.com/a/66ri8 (note the red arrow pointing at the refresh button)

Apparently there’s a cached version of your report in that preview pane. So if you’re like me and you’re wondering why in the world are you not pulling new data. That will probably fix things up for you.

Learning that only took like a day and a half. I wish I was kidding.

Madison, Columbus, and Indy SQL Sat Recap

I’ve been a bit busy this year. I kept submitting to places and for some oddball reason I kept getting picked. I was selected for Atlanta as well but had to remove myself because of work. I had to also remove myself pre selection from Maine thanks to work. Boo work.

So let’s recap shall we?
Madison was awesome. Thanks to Jess and the team there for a fantastic event. I loved that they had 75 minute sessions. That’s definitely my speed for my build a clusters talk as I usually find myself running out of room. It is an 8 hour drive from Cleveland. I got my wife to come with me and she watched me present. It was nice to spend a weekend together even if she had to go back to the room early because she didn’t feel well during the afterparty. I also found a new favorite beer, go find yourself some spotted cow.

http://www.newglarusbrewing.com/index.cfm/general/index

Columbus marked the one year anniversary of my speaking start. So this event will always hold a special place in my heart. It’s a quick drive and Peter Shore and David Maxwell who run it are great guys and do a really great job. My session this year went well, except I had a total brain fart and somehow forgot to go through the installer to show how you span an instance across the nodes of your cluster. I threw the slides up on the SQL Sat site for this talk and added that in at the end. So I hopefully caught myself there. Otherwise it was another lovely day in Cbus.

I just came home today from Indianapolis for SQL Sat Indy. I have family about 10 minutes from the venue so I was able come in Thursday night and have dinner with them. It was nice catching up. I also spent Friday in Adam Machanic’s Query Tuning precon. Well worth your monies. All of them. I mean the dude is awesome to watch and will teach you ways to manhandle the optimizer. He wrote Whoisactive and giving him monies for that precon was thanks enough for that tool alone. As for the day Hope Foley and her team did a fantastic job. The event ran smooth, lunch was delicious, and a few fellow speakers came to my session. That actually made me pretty nervous. I think I did a good job in my talk and hopefully I taught some people some useful clustering facts. A special thanks to Drew, Andy, Rob, and Ben for attending.

Speaking of my Clustering talk, there’s no way I can keep doing it in an hour. It’s just way too much stuff, so I’ve got to come up with some new material for next year. Well, speaking of next year my wife and I are going to have a baby in March. I won’t be traveling as near as much as I did in this last year. I’ve been very grateful for the opportunity to travel and present. I love the entire SQLSAT and SQLFAMILY concepts and what they stand for. I never thought I’d be a publited speaker or enjoy it. Now I can’t get enough. I’m already really sad in thinking about how I can’t get out next year to travel around near as much due baby number two. Don’t get me wrong, I’m excited for our second child but I knew this would happen. I’m just bummed out about it. This will give me a year or so to hopefully come up with a few talks and keep blogging. I’ll be at the Cleveland and Cbus events but that’s probably it.

I also will probably step down from helping run the Cleveland RUG. I’m secretary and speaker coordinator right now. I’m glad to help and book things. I know I won’t have the the time to devote to this sort of stuff when I’m a parent of a newborn and 4 year old. When they get a bit older I’ll be able to get back into it. At least that’s what I’m telling myself. PS ONSSUG is booking for March 2017 and onward so get in contact with me if you want a month. 😉

As for future talks I have two that I want to try and create. One is on Distributed Replay and the other on Storage Spaces direct. I think the Replay one is going to happen for work as I might need to learn it, now ish. So hopefully I can give that in Columbus and my RUG next July.

One last thing. Before my session an attendee said to me they had saw someone tweeting about me and then they had decided to follow me and come see my talk. It was the most surreal speaking moment to me. I have a pretty low self value of myself if I’m honest. I’m just a dude who had to learn clustering and now enjoys going around and teaching and helping other people with it. I never in my life would have thought someone would come watch this session let alone say they saw me on twitter and decided to show up. I’m really blown away by this.

I think that I am a poster child case for imposter syndrome. Infact at the start of my talk when I mention my title, senior sql dba, I say that’s a load of crap. I just break a lot of stuff and have to google how to fix it. I could probably go on this track but that would lead to another 1000 words and to be honest my living room is a mess and I just want to go chill right now and place some xbox before bed.

If you made it this far, thanks for reading and talk to you soon.

PASS Submission feedback ’16

Hi.

I have not really had anything of note to write about in a while. I submitted to the PASS Summit and was not selected. I’m not shocked I didn’t get selected, I’ve only been speaking a year. I’m glad I entered my hat in the ring to go through the process and see how to improve for next year. Below is my info and feedback that I got from the review committee. I’d like to say thanks to those who volunteer their time to do this job. Without you folks there would not be a Summit.

 

General Session (75 minutes)
Enterprise Database Administration & Deployment
High Availability: Availability Groups / Mirroring / Clustering / Replication
Level 100

Title:
Clusters! From the ground up, let us build one together.

Abstract:
You are looking to protect your SQL instance from a machine failure.  Or maybe your storage budget and tight and you don't want to have your databases in two places with one of the other highly available options that SQL provides.  Let us learn what a cluster is and why it might be the right thing for you.  We'll explore the system, network, storage and database administration sides of the equation.  And, when it is all said and done we will even have built a SQL clustered instance!

Prereq:
There are none, we will go through all the steps outlined in the abstract and goals to teach the audience how how to do this in a lab in 75 minutes.

Goal 1
Teach the audience about system administrator life in active directory, domain naming system, and windows failover clustering.

Goal 2
Teach the audience about the storage administrator life with block level storage.

Goal 3
Teach the audience how SQL server can sit on top of a Windows failover cluster to build a failover clustered instance.

Comment 1
There is an error. Change the word … budget AND tight… by …buget IS tight…

Or maybe your storage budget IS tight and you don’t want to have your databases in two places with one of the other highly available options that SQL provides

Comment 2
Abstract: not compelling

topic: level too low

Subjective Rating: not interested

Comment 3
Abstract:  Abstract I awkwardly worded in places and contains a typo.  “Or maybe your storage budget and tight” I presume and = is.

Topic:  I like the idea of an introductory level session on building a cluster.

Subjective: Not sure this session would draw well.  Aims to cover a ton of information….. AD, DNS, SAN, etc., all with no pre-reqs required.  This could pose a serious challeng

 

My thoughts:

Comment 1: Well, I’m an idiot who can’t read. So that’s my fault on the wording.

Comment 2: Pissed me off. Too low level, not compelling and not interested? Seemed a bit like personal interests getting in the way. I like to think (hey it’s my blog) that new people get a taste of lots of different items they need to know about when they step into the clustering world.

Comment 3: Not wrong on covering a ton of stuff. It’s really hard to do this talk, the 75 minute version is more fun to do than the 60 minute moving at the pace of a freight train.

After I present this thing in Indy and Cbus this year I’m probably going to stop submitting it and come up with something else.

 

Session 2  –  lightning talk
Centrally manage your instances
Enterprise Database Administration & Deployment
Management Tools
Level 100

Abstract:

Did you run the query against all your instances? Do you know all your instances? How do you keep track of this?

In this lightning talk we will go through quickly cataloging your environment and getting your instances into a Central Management Server.  I'll even show you how to deploy a job, backups for example, to all your instances at once!

Prereq:
None

 

Goal 1
Show what a Central Management Server is and why it is useful.

Goal 2
Catalog your inventory of SQL instances.

 

Goal 3
Add your instances into the Central Management Server and deploy a test job to them all.

 

Comment 1
Abstract: compelling
topic: relevant
Subjective rating: interesting

Comment 2

Abstract: Well written, clear, and concise abstract.

Topic: Simple and effective. Fits the level.

Subjective: Not a fan of lightening talks. Could wrap them into “Here’s stuff you should Google if interested” lists that are given to attendees.

Comment 3
Abstract:  For a lighting talk, this abstract is okay
Topic:  Interesting topic
Subjective:  I may attend this session

Comment 4
The outline does not seem to clearly describe the contents of the presentation.  The subject matter may be difficult to effectively present in the time allotted.  For a 10 minute session, maybe it should be more specifically focused.

 

My thoughts:

Comment 1
Sweet, thanks!

Comment 2
Can’t disagree with the googling. But thanks for the rest.

Comment 3
I’ll take it.

Comment 4
I’ve done it in 10 minutes and it fit like a glove. It’s pretty specifically focused and I doubt could be a hour long session.

 

And there you have it. I’ll probably throw the CMS lightning talk in the pool again for next year. Now to figure out another fun session to present.

Thanks for reading. 🙂

Edit: I forgot to add the following:
Clusteres: Not Accepted Popular topic, higher rated session selected
CMS : Not Accepted Other sessions selected based on building a balanced program for track coverage, speaker coverage, topic coverage, and session rating

More SQL Sats

Hello.

This is totally a me, me, me post.  March 5th I’m presenting my clustering session at SQL Sat Chicago.  April 5th I’m giving a lightning talk on Central Management Server.  April 9th I’m giving my clustering session at SQL Sat Madison.

The lightning talk is new and I’m excited to try it out.  I’ve submitted both sessions to the PASS Summit in October.  I eagerly await those rejections. 🙂

Other than that not a lot has been going on for me to write about.

We broke it. PASS. A Virtual Chapter?

My goal should be to write in this monthly.  But not to write just to write, but to write on things that have a meaning to write about, so here we go.

First, we have a 4tb db with 60 cores/512 ram.  Checkdb on it (while in a FCI) would cause a failover due to slow IO on local SSD tempdb in SQL 12.

Slow IO on local SSD.

Huh?

140 hours later on a case with Microsoft these fixes came out in SP3 of SQL12 because of us:

KB 3029825

FIX: DBCC CHECKDB/CHECKTABLE command may take longer in SQL Server 2012 or SQL Server 2014

KB 3107400

Improved tempdb spill diagnostics in Showplan XML Schema in SQL Server 2012

KB 3107172

Improved tempdb spill diagnostics using Extended Events in SQL Server 2012

3107173

Improved memory grant diagnostics using Extended Events in SQL Server 2012

 

We broke it real good. The outcome in SQL 14/16 is to throttle IO with resource governor.  It was paging to disk with a high amount of data because the optimizer said it was 2.8 TB of RAM to do Checkdb on an object that was using 96 gig of RAM during the Checkdb process (25% of the max RAM in the system which is the cap.) So it paged like crazy to do math.  Use a startup parm -k with a IO value you want to set the max to curb these Tempdb spills so your FCI doesn’t fail over. (I do not believe the KB for this -k parm is updated as of this posting.)

 

I attended my first PASS summit.  I really, can’t even go into words what a week this was for me.  Best of my professional life.  I got to tour building 16 at Microsoft and meet the SQL dev team.  I got to meet Conor Cunningham and tell him how excited I am for Query Store, and then Mark Souza walked out of a room and I got to say hi to him again.  I mean, I just.. haha it was awesome. I loved every minute of PASS. I must go yearly. I need to take more pictures with people. I need to keep a running notepad of things so I don’t forget. I need to make it to bush gardens next time along with Top Pot (or is it Voodoo?). Just such an amazing time.

I’ll be doing my build a FCI presentation for the PASS VC on HADR on Dec 8th at 1 eastern time.  Totally terrified for this, but each time I give this talk I am so what else is new?

If I don’t write again, I will have turned 31 in December, Christmas/NewYears will have come, and our Cleveland SQL Sat will be on Feb 6th. Please come out for the event if you’re in the area.