Steve Jones asked data professionals to cover four days in our lives, so I blogged about a normal non-client-facing day, and then an unproductive day.

Today is different: I’m client-facing. We specialize in a 3-day SQL Critical Care:

• Day 1 – we meet with the client to dig through their SQL Server together while we ask them questions about their database, indexes, queries, designs, RPO/RTO goals, hardware, and more.
• Day 2 – we split up. The client goes back to doing their thing, and we write the findings for them. (You can see examples of the findings on that page above.)
• Day 3 – we meet again to deliver the findings, which are a mix of consulting and training. We tell you the fastest way to relief for the pains your SQL Server is facing, and make sure you’re confident in moving forward. (Sometimes clients hire us to fix the problems directly, too, but ideally we’d rather show you how to fix the pain permanently rather than get hooked on expensive pain relief narcotics consulting.)

Today is day 1. Let’s make the magic happen.

6:00AM-6:30 – Emails. Small catch-up stuff.

6:30-7:30 – Reading and learning. Not a lot of RSS stuff from overnight (no surprise, since it’s Monday morning) but Hacker News has a really interesting discussion: The Secret Life of an Autistic Stripper. Forget the article and the inflammatory title – the comments are just interesting to read because there’s such a wide spectrum of personalities in the IT community. Answered a DBA.se question about reading the MySQL transaction log. Happy to see the photos coming in from the Chicago-Mac race, thinking back to when I did it. Saddened to read of a sailor who fell overboard and went missing, and his life vest didn’t inflate. That race is no joke.

7:30-8:00 – Breakfast. Coffee and yogurt. While eating, drool over a cocaine-tastic 1986 911.

8:00-8:15 – Email Richie. He was out last week, so I catch him up to speed on the Github issues I filed over the last week. We have 117 open issues at the moment with a variety of strategic and tactical stuff, pull requests, etc., and I’m sure he’s got a ton of notification emails, so I wanted to help prioritize stuff.

8:15-8:45 – Review client DMV data. Clients run an app that sends us an Excel spreadsheet with data from sp_Blitz, sp_BlitzCache, sp_BlitzIndex, etc. sliced and diced a few different ways – like their plan cache sorted by different metrics – plus a lot of their query plans. I spent some time with it last week, but I want to refresh my memory.

9:00-10:00 – Call starts, talk strategy. We talk about their pain points – the most important issues they want solved during the engagement – and I talk about a few big-picture challenges. For example, this client has a ~10TB database, and in the event that someone has an “oops” query like dropping a table, they want to be able to recover within 1 minute of data loss in under 1 hour of downtime.

10:15-12:00 – Analyzing query memory grants and query plans. We were called in for unpredictable, random performance slowdowns. Thanks to my research into the client’s data ahead of time, I know someone’s running queries that get a really big memory grant, but then don’t use it, and finish quickly. We run sp_BlitzCache @SortOrder = ‘memory grant’ and catch a few – including this gem where SQL Server’s cardinality estimation has gone haywire:

To be clear, it’s not really a bad query: SQL Server’s just making some really questionable guesses about how many rows will come back. In actuality, only a couple hundred thousand rows come back. We talk about the source of those queries, how to tune them, and whether we can offload them to a different server. The server’s other bottleneck is queries that go parallel to burn CPU power, so we start with sp_BlitzCache @SortOrder = ‘cpu’ and walk through some of the top offenders.

12:00-1:00 – Lunch break. Erika made chicken chili.

1:00-2:00 – Analyze VMware configuration & performance. There are multiple ways you can fix a CPU bottleneck, and the goal of the SQL Critical Care® is to find the right one for a given client. This VM’s wide NUMA configuration made things tough for VMware’s CPU scheduling, and I wanted to find out why it was built that way, and if the team was open to changing it. To learn more about this topic, check out Frank Denneman’s excellent NUMA Deep Dive series.

2:15-3:00 – Test trace flag 2335 behavior. The current production server was running this trace flag, and nobody knew why. The resulting query plans showed some really odd memory grants. We took one of the queries, then took it to a QA server where we could flip this trace flag on & off. The devil’s always in the details on that kind of thing. I’ve got the data I need to build their findings, so I bid them adieu.

After client calls finish, I need to walk away from the data for a while before I start assembling findings. I find that if I just start writing up their deliverables right away, I can’t see the forest for the trees. I do one final set of notes in the client’s file, then close it all down for the day.

3:00-3:30 – Emails & reading. I start by getting back to inbox zero, then check Feedly for updated blog posts.

3:30-4:00 – Testing ConstantCare.exe. Richie being the development machine that he is, he’s already implemented a few of my notes from this morning, and needs me to test an updated build in preparation for an early access build for clients.

And I’m out! Very productive day. Tomorrow I’ll work on their findings, and then deliver ’em on Wednesday. Thursday & Friday, I’m proctoring Drew Furgiuele’s PowerShell class while updating my slides for my upcoming Mastering Server Tuning class. Next week, I’ll be out of the office as we move to San Diego.

I do have a few other styles of days, and I’ll blog a couple of those in August:

• Mentoring days – when I analyze SQL ConstantCare® client data in Power BI and send them advice emails
• Onsite days – when I fly to a client’s office, assess their servers, and then teach training classes relevant to the problems they’re facing
• Teaching days – when I host a class (but that one doesn’t lend itself well to the blogging)

Yesterday, I blogged what I did in a random work day per Steve Jones’ suggestion. And yesterday, I said I’d blog today as well because it was going to be a different schedule.

You shouldn’t read this. It’s useless.

It’s useless because as I look back on it, I didn’t accomplish much of anything on Wednesday, July 18th. That’s not rare – there are plenty of days where I feel like I don’t make enough progress. Thing is, Steve wanted us to share the tools we’re using and what problems we’re solving, and this post is pretty well devoid of that information because I didn’t solve problems yesterday.

I didn’t know that when I started the day, obviously.

I was going to delete this, but screw it – it’s already written, so presented for your amusement: a day when I don’t solve any big valuable problems, just tread productivity water:

---

4:15AM-4:30 – Emails. A quick round of emails before showering and going out for coffee.

5:15-6:00 – Reading blogs. Not a lot of new stuff posted yesterday. I answer a DBA.StackExchange.com question about setting up a readable replica and one about cross-database queries in Azure SQL DB. I refresh the tracking page for my new MacBook Pro – it’s made it from Shanghai to Chicago. C’mon, little buddy. (Sitting)

6:00-7:30 – Learning about Lambda event sources and limits. Looking at my calendar, today is an appointment-free focus day that I’ve blocked out for design work on SQL ConstantCare®’s index and query recommendations. With that in mind, I wanna think about how we do that processing. So far, most of the data is hosted in AWS Aurora PostgreSQL, and the application code runs in AWS Lambda using the Serverless framework. However, as we start to analyze query plans, I don’t want those big XML plans stored in a relational database, nor do I want the advice stored there – the relational database is just too much of a bottleneck if we wanna process a lot of plans fast. To scale the new process, could we:

• Store each incoming execution plan as a file in S3
• Have the new file trigger an AWS Lambda function to analyze that plan and make recommendations (and will the function finish in time, given the 300-second limit per function)
• Store the recommendations somewhere other than a database (say, in DynamoDB or in another S3 file)

To pull that off, I need to think about whether the plan analysis functions will need any data from the database (like data about the client’s server, or rule configuration data). I don’t wanna do the architecture design, mind you – Richie’s the architect here – but I just wanna learn enough to have a good discussion with him about how we do it. I finish up my learning by searching for related terms, plus phrases like “problem” or “bug” to see issues people are running into, like this one.

7:30-8:00 – Emails. A sales prospect from yesterday signed their contract, so did some logistics with that. A training class student sent in DMV data about their SQL Server, so analyzed their plan cache and explained the issue they’re facing. FedEx emails to say my new laptop is on the truck for delivery by 10:30AM today. Woohoo!

8:00-8:30 – Break. Make a pot of coffee, down a yogurt, start the laundry.

8:30-9:00 – Emails. More sales inquiries, and an attendee question from my user group presentation last night.

9:00-10:00- Postgres query writing. In our development environment, Richie’s already got ConstantCare.exe sending in index and query plan data. I open up Postico – it’s a Postgres client for the Mac, like the equivalent of SSMS – and start spelunking around in the tables.

This takes a second to wrap your head around, but:

• ConstantCare.exe runs on the clients’ machines
• It queries the clients’ SQL Servers, getting data from DMVs
• ConstantCare.exe exports that data to JSON format, and uploads it to the cloud
• In the cloud, AWS Lambda functions import those JSON files, and insert them into a database (AWS Aurora Postgres)

So I’m querying SQL Server DMVs, but in Postgres, and with ever-so-slightly different names (note the underscores in tables rather than sys.dm_db_index_usage_stats. At first glance, you might think, “Ah, so he’s just editing sp_BlitzIndex so it works off these new table names!” But no, we’re starting again from scratch up in the cloud because up here, we have the benefit of historical data.

For example, the first easy query I start with is warning folks when they have a heap with forwarded fetches, something sp_BlitzIndex already alerts you about. However, in the cloud, I wanna compare today’s forwarded fetches to yesterday’s. You might have forwarded fetches reported on a table that isn’t in active use anymore, but SQL Server is still reporting forwarded fetches since startup. No sense in pestering them about something that isn’t a problem.

10:00-10:30 – Breakfast, and the new laptop arrives. Erika’s up, makes eggs. It takes a special relationship to be able to live together, work together from home, and even work for the same company. We’re pretty much around each other 24/7, and it works. If she passes away before I do, I’m gonna be single for the rest of my life.

Unpack the new laptop, put on the new protective case, boot it up, and point it to my Time Machine backups on the network. Apple makes it delightfully easy to buy a new machine. (We’ll see how the Core i9 throttling issue goes, although I rarely run my laptop at 100% CPU for 20-30 minutes as shown in the video.)

Woohoo! New laptop arrived, going straight into a protective case. pic.twitter.com/7hD2UgAJic

— Brent Ozar (@BrentO) July 18, 2018

10:30-11:00 – Communication. I tweet a photo of the new laptop, which leads to some discussions on Twitter and LinkedIn, and then did some emails.

11:00-11:45 – Office Hours podcast. Every Wednesday, we get together in GoToWebinar, take live questions, and fumble our way through answers. This podcast would take a lot more out of my calendar were it not for the wizardry of DigitalFreedomProductions.com. They record it, upload it to our YouTube channel, make a podcast out of it, transcribe the audio, and create a blog post with the transcription.

11:45-12:15 – Emails. Customer emails, contract negotiation with a prospect.

12:15-1:30 – Lunch. Erika and I head to Wishbone where we both get the same thing every single time: blackened chicken with sides of spinach and red beans (no rice.) We started doing Weight Watchers quite a while back, and I’ve just about hit my target weight (185), but that food is good enough (and low points enough) that I’d eat it even if I wasn’t watching my delicate figure.

1:30-2:00 – Erik’s new book arrives. All productivity stops. So much for this being a focus day.

2:00-3:30 – Back to Postgres query writing. Building my first proof-of-concept for the rebuild-your-heaps script. This is the first index analysis script we’ve done in Postgres, and the first one is always the hardest, putting together the right joins between the right tables, comparing to yesterday’s forwarded fetches, and looking for edge cases that would break it. I’m by no means done – I’ll need to keep going on this tomorrow, but once I’ve got the first index analysis script done, the next one will be a lot easier.

3:30-4:00 – Emails. Signing an NDA for a prospective client, answering a few mentoring questions.

4:00PM – Done. Calling it a day. Not unusual for me – I tend to get a lot done in the mornings, and then coast to a stop in the afternoons. I didn’t make as much progress as I’d like today, but that’s life. Thankfully, I have the next 2 days on my calendar blocked out for the same task, and I’ll likely do better tomorrow. I won’t blog my day again tomorrow though – I’ll hold off for a few days until next week when I’m working with a client. That’ll show a different kind of work day.

5:40-5:45 – Update SQLServerUpdates.com. I get notified that Microsoft published 2017 CU9, so hop into the home office to update the site.

---

So there you have it. I have no idea what you’ll think about that day, but if you enjoyed this (or if you think *I* work a lot), read Jen Stirrup’s Tuesday.

Steve Jones asked data professionals to cover four days in our lives, so here goes the first post: what I did on Tuesday, July 17th, 2018. Nothing special about this day, just the day that Steve prodded me to take part, heh. My days are radically different, so I’m glad he said to do this four times – otherwise I’d feel guilty about just posting this one without more explanation. (I’ll do this again for July 18th because it’ll look totally different, but then hold off for a few days before posting another.)

4:07AM – I wake up without an alarm and do a round of emails before I hop in the shower. (I looked at the first email-sent time to figure out when this happened, hahaha.) I answer a few client and personal emails – things that are easy to tackle from the phone. After a shower, I walk to my nearest coffee shop, grab a big cup, and come back to my home office to work.

4:45-6:45 – Learning. Read the blogs from overnight – in addition to the 362 SQL Server blogs I subscribe to (OPML – Feedly), I also read HackerNews and a lot of non-technical stuff. I tend to bookmark the very best (or most interesting) stuff on my Pinboard feed, and some of those end up in our Monday newsletters. Microsoft published a couple of cumulative updates, so I post them on SQLServerUpdates.com.

(As I mentioned in my interview with Kenneth Fisher, I try to reserve the pre-7AM time for learning. Today it was reading, but sometimes it’s opening up SSMS and trying to understand something that has been stumping me for a while, or learning how PostgreSQL execution plans work, or whatever.)

6:45-7:00 – Breakfast. A fresh pot of coffee and Stonyfield yogurt. Michael Weston, look out.

7:00-9:00 – T-SQL coding. I need to do design work on SQL ConstantCare®’s new index functionality, so to get in the frame of mind, I design & code an sp_BlitzIndex change I’ve been wanting to make for a while. In building the script for Erik to test the changes, I also update my Mastering Index Tuning class demos about the problems with heaps.

9:00-10:00 – Communication time. I answer a few mentoring emails from SQL ConstantCare® clients. I break some bad news to an upcoming client: they hired us for performance tuning, but while analyzing their data for that engagement, Erik found out they have database corruption across multiple AG replicas. In more enjoyable news, I answer a DBA.se question that happens to pop up in our company Slack room. (We get notifications for new SQL Server questions, so if they look fun, we jump in. That one involved SQL Server on a forklift, so yes, I was intrigued.)

10:00-10:30 – Break. Walk around, make coffee, read blogs. I see my first Minerva Blue Porsche 911 and  then wipe the drool off my keyboard and monitor. The audio of it driving around is wonderful.

10:30-12:00 – SQL ConstantCare® design. We’ve got a new version of ConstantCare.exe that collects index and query metrics, then tells you what index and query changes to make in order to get better performance. Started designing the business logic for modifying SQL Server’s missing index recommendations and building better ones. While designing it, I review the past sp_BlitzIndex data for several clients, thinking about how I would have built rules to handle their index recommendations. I store my thoughts in a Github issue.

12:00-12:30 – Client call. I’m doing a 3-day SQL Critical Care® with a company next week, but looking at their DMV data, the cause of their really nasty RESOURCE_SEMAPHORE issue was immediately obvious. We talked through it so they could start researching the source of those queries before our engagement.

12:30-1:30 – Lunch. Erika & I headed out to Falafel & Grill. When you work at home, it’s nice to get out and enjoy some fresh air.

1:30-2:00 – Order taking. A few sales prospects emailed in about arranging future work, so I coordinated dates & contracts. I hesitate calling this sales because I don’t do a lot of salesing, just responding and going, “Yep, that’s a good fit, here’s a contract with our next available dates.” The vast majority of our work involves urgent performance emergencies, so we’re usually only booked about 2-3 weeks in advance – really short for a consulting firm. As a result, I’m totally at peace with only having 2-3 weeks of work on the books at any given time. In the past, our August drop-offs scared the hell out of me, but now at least I know to expect it somewhere in here. That’s why I’m doing the foundational design work for SQL ConstantCare®’s next set of recommendation rules now – so if Erik & Tara aren’t billable for a couple/few weeks in August, they can still be productive working on the rule design & execution.

2:00-3:15 – Design continues. Thinking about how I want to deliver index recommendations, I decide that we need to start including a text file attachment in the SQL ConstantCare® emails. I spend more time in sp_BlitzIndex and Github.

3:15-4:00 – T-SQL R&D teamwork. Erik’s working on a new training class module about parallelism, and he’s got a query that exhibits great demo-friendly behavior. I run a few tests with it on the Stack Overflow database and use sp_BlitzFirst to check wait stats, and we have a fun discussion about CXCONSUMER behavior, and how it might not be that harmless after all. We’re seeing some really odd, unexpected stuff on 2017, and I would love to dig deeper by trying earlier versions. (Later, looking at our Slack channel, I can see that he kept going, and I’m looking forward to seeing what he found.)

At the same time, Tara’s examining the data for her client tomorrow, and she spots a T-SQL anti-pattern in their queries. We all talk about a possible fix in Slack, and she starts working on a demo for their findings.

(At this point, you’ll notice that we’ve done prep work for clients ahead of their engagements – that’s because Richie wrote an app for us that sends us the results of diagnostic queries like sp_Blitz, sp_BlitzIndex, sp_BlitzCache, query plans, etc. That way, when we meet face-to-face with the clients, we’re already well-equipped to discuss the problems they’re facing. Steve asked us to write these blog posts so you could see what tools & techniques we use in our day jobs, and while you can’t get that app, you can get the exact same scripts we use every single day.)

Normally I would end my day here – I try to quit work around 4PM – but today is unusual in that it keeps going:

4:00-5:45 – Drive out to the burbs. I live in downtown Chicago, and traffic going out to the burbs is legendarily bad. It takes me 95 minutes to go 20 miles. There are only 2 reasons I head away from downtown: to go to the airport, or to speak at the…

5:30-8:15 – Chicago Suburban SQL Server user group. I was the first speaker to open the new group, and I present there one last time before moving to San Diego in a couple of weeks. Dinner was pizza at the user group.

Happy to have @BrentO back at #ChiSQL for one last in-person presentation before he flees Chicago winters. pic.twitter.com/nzYCX8moY1

— Andy Yun (@SQLBek) July 17, 2018

8:15-8:45 – Drive home. Atypically long day for me – if it wasn’t for the user group, I’d have been done at 4PM. See you tomorrow!