[Video] Office Hours: Ask Me Anything About SQL Server at the EVE Online Monument

Videos
0

I sat down at the EVE Online Monument in Reykjavik to answer the highly-voted questions you posted here.

Questions covered in the video:

  • 00:00 The EVE Online Monument
  • 00:48 Why is SQL Server using so much memory?
  • 02:30 Will table compression make a 2GB database faster?
  • 03:28 Will you be doing Office Hours long term?
  • 04:39 Should I upgrade SQL Server?
  • 06:00 Should I store JSON in SQL Server for a new application?
  • 07:40 What’s the best way to speed up page loads?
  • 09:33 What’s your favorite ETL tool?
  • 11:20 What are your thoughts on Oracle, DB2, MySQL, and Postgres?
  • 12:12 How big of a server should I use for a 100GB database?
  • 14:30 Will In-Memory OLTP help me on a 1GB database?
  • 15:30 What’s a good alternative to NOLOCK?
  • 16:35 How do I explain the legacy CE and backwards compat to a non-DBA?
  • 17:20 Should I change my inherited CHAR columns to VARCHAR?
  • 18:43 Alberto, Office Hours probably isn’t a good fit – you need consulting or training.
  • 19:20 Should I run antivirus on my SQL Servers?
  • 20:59 Why should I move from SQL Server 2016 to 2017?
  • 22:16 Since a backup file is bigger during busier times, should I back up when the database is idle?
  • 23:19 Who are your references in your field?
  • 25:06 Where you start when analyzing someone else’s queries if you don’t know their tables?
  • 26:15 Is there a module to write T-SQL in PowerShell? Do you teach your Mastering classes with PowerShell?
  • 28:38 Wrap-up

[Video] Office Hours: Ask Me Anything About SQL Server, Mink Edition

Videos
3 Comments

In this episode, I’ve got my own question: what’s this animal running around at my feet? Commenters on the YouTube video explained that it’s a mink. They were imported into Iceland in the 1930s for their fur, and they escaped out into the wild. The country’s been trying to reduce their population, and they’ve even been part of the COVID19 news.

Anyhoo, back to databases. The way these videos work is that you post your questions here, upvote the ones you’d like to see me talk through, and I do your bidding:

Questions we discussed in this episode:

  • 00:00 How can I fix a slow query with 30 left outer joins?
  • 03:20 How do I track down unparameterized queries?
  • 04:10 Can I force parameterization without using Optimize for Ad-Hoc?
  • 04:54 How can I measure latency of sync Always On Availability Groups?
  • 07:17 Should my monitoring software point at my AG listener or the replicas?
  • 08:50 What index reorganize/rebuild thresholds do you recommend?
  • 11:36 Why does Query Store show 5-6 plans for the same query?
  • 13:10 My SQL Server goes down on its own. How can I find out why?
  • 14:58 (I’m visited by a cruise ship and an unidentified animal)
  • 16:37 My databases are 1-3TB. What should I warn managers about?
  • 18:15 When I’m doing the DEATH Method, how should I treat unique indexes?
  • 19:46 How do I save sp_BlitzCache’s results to a table?
  • 20:22 sp_Blitz warns me that I have a high number of plans created recently.
  • 21:55 Is there a simple way to do inserts faster?
  • 23:27 Why should we stay on-premises instead of move to the cloud?
  • 25:30 If queries always use ORDER BY, can changing indexes change the query results?
  • 26:39 (Moving the camera to show the animal)
  • 27:40 Should we interview candidates by making them write queries in Notepad?
  • 31:12 Wrapping it up

[Video] Office Hours: Ask Me Anything About SQL Server

Videos
1 Comment

You’ve got questions, I’ve got answers. Welcome to another edition of Office Hours, where you post your questions here, upvote the ones you’d like to see me talk through, and I do your bidding:

Questions we covered this week:

  • 00:00 Introductions
  • 01:30 Are DBAs transitioning to other roles, and why?
  • 04:48 Why is it hard for DBAs with 35 years of experience to get clients?
  • 06:40 How can I track down slowness in the app if it isn’t SQL Server?
  • 10:17 I’m a backend/frontend/mobile developer. Am I spreading myself too thin?
  • 12:00 Are users with multiple languages a problem for the plan cache?
  • 13:54 My query errors out in batch mode, but works in row mode. Why?
  • 14:48 What’s the oldest SSMS bug that causes you the most frustration?
  • 16:14 When would you recommend not to use SQL Server?
  • 18:29 What was your favorite thing in Iceland?
  • 21:00 Does lock escalation work the same way in Azure SQL DB?
  • 24:04 Why isn’t my query going parallel?
  • 26:43 We revisit fragmentation for the 1,043rd time
  • 28:12 Why is my database stuck in restoring state?
  • 30:05 What certifications will grow my career?
  • 33:49 Do you like SQL Server 2019’s automatic index tuning?
  • 34:51 My new coworkers love query hints. What should I do?
  • 36:50 What should DBAs document?
  • 37:24 What is your favorite data auditing method?
  • 41:00 When should the primary keys not be the clustered index?
  • 42:00 How much C#/Java does a DBA need?
  • 43:06 What’s the best way to delete 90% of my data?
  • 44:05 Can I turn an execution plan back into T-SQL?
  • 45:45 How should I do real time updates to keep two servers in sync?
  • 46:46 Are you going to teach Power BI?
  • 48:35 Will Azure SQL DB replace SQL Server?
  • 51:17 Is the SQL job market good, and what will it look like in 5 years?
  • 53:10 Is it safe to use Accelerated Database Recovery?
  • 56:09 Am I crazy to drop foreign keys to make my database faster?
  • 57:43 Can I automate scripting out a database schema without tools?
  • 58:52 Recap

Most T-SQL Queries Don’t Even Try to Handle Errors.

T-SQL
45 Comments

David Tovee asked a great question in yesterday’s Mastering Query Tuning class. He asked his fellow students, “How many of you actually use TRY/CATCH?”

I turned it into a Twitter poll because I wanted to check a wider audience:

The poll results mirror the experience I see with clients: the vast majority of T-SQL code doesn’t have any error handling whatsoever.

When I do run across error handling in client queries, I get all excited and I ask about it. The answer is usually, “Oh yeah, that was implemented years and years ago by a developer who used to work here, and they left.” It’s often legacy leftovers from when TRY/CATCH was first introduced, and then…never revisited again, and rarely included with new queries.

If you’d like to get started with error handling, check out these resources:

Right about here is the part of the blog post where you might expect a finger-wagging lecture from me about how queries should always handle errors, but, uh…I don’t do error handling either. I know, right? But the thing is, I can’t remember the last time a client hired me to write a new query from scratch. If I never do something, then I won’t be good at it, and I certainly can’t lecture you to be good at it, either.

If you DO put error handling into your queries, though, take a moment to hug yourself. You rock, and you’re unusual amongst the wider audience. The world needs more people like you.


You Probably Shouldn’t Index Your Temp Tables.

Indexing
35 Comments

When you’ve got a process that uses temp tables, and you want to speed it up, it can be tempting to index the temp table to help work get done more quickly. However, in most cases – not all, but most – that’s a bad idea.

To explain why, I’m going to take a large Stack Overflow database and write a stored procedure:

The first statement loads about 10 million rows into a temp table, and the second statement only pulls out the rows that match who we’re looking for. Now, that probably seems dumb: you’re over there yelling, “Brent, why would you put millions of rows into a temp table that you don’t need?” The answer is that I need to write a blog post quickly in order to explain a concept that I’ve had to teach clients a few times. I can’t copy/paste their real code here. That would be Bad™. Instead, let’s just keep going with this game for a minute.

I’ll turn on time statistics so I can get a rough idea of where SQL Server is spending its time – the first statement or the second:

I don’t often use time statistics here on the blog, so a quick explanation: in the Messages tab in SSMS, you get a line for each statement in the batch, plus a total:

The first statement – loading the temp table – took about 4.5 seconds.

The second statement – finding Brent – took about a second.

Could an index on DisplayName speed up the second query?

To find out, let’s add a new version of our stored procedure – this time one that creates an index on DisplayName before our data is loaded:

Now, when we run the new query:

The time statistics paint a horrifying picture:

Sure, the second statement drops from 1,017 milliseconds to 75, but…who cares?!?! The additional overhead of building the index is much, much higher, making the query take ten times longer overall.

Part of the problem is that our heap (table) has to be loaded first, and then the data has to be sorted by DisplayName, and then an index has to be created on DisplayName. We can’t do them both in parallel at the same time because the nonclustered index has to be able to point back to a specific row in the heap – and to do that, we need its physical location, like I talk about in How to Think Like the Engine.

Fine. What about a clustered index?

We can reduce the overhead of the process by only having one structure to store the data. Instead of a heap plus a nonclustered index on DisplayName, we can just define a single structure for the temp table: a clustered index on DisplayName. It can’t be a unique index because multiple users share the same DisplayName, but that’s okay. Here we go:

And when we execute it:

At first, it looks way faster than the last method:

Execution time dropped from 57 seconds down to 18 seconds, but there’s a catch. A big part of the reason why it’s faster is that now the query is going parallel. Note that CPU time is higher than elapsed time – that’s your clue that the query went parallel across more CPU cores. Now we’re going to have a CPU problem if a bunch of these queries run simultaneously.

And it’s still not as fast as our original solution, the heap.

So how do we make temp tables faster?

Only load them with the data you actually need. When loading temp tables – or any objects, really – be ruthless about filtering as early as possible.

If you’re only going to access the data once, leave it as a heap. Indexes make the most sense when the temp table is going to be reused repeatedly across lots of statements that all do filtering or joining or sorting using the same keys.

Try CTEs instead. If you have a multi-step process that involves a lot of filtering and joining, let SQL Server recalculate where the filtering logic should happen. I am by no means saying that CTEs are always better than temp tables – often it’s the reverse – but if you’re hitting a performance tuning wall on queries that use temp tables, try converting them to CTEs. SQL Server will reorder operations – it doesn’t have to execute the first CTE first, then the second CTE second, and so forth.

 


Mark Your Calendars: Free Live SQL Server Training Classes in October & November.

This fall, I’m going to teach you the fundamentals of Microsoft database performance tuning, live, for free.

The first class is my How to Think Like the Engine class. It’s already free online in both video and blog post formats, but this fall I’m updating and expanding it to a 3-hour version. You could totally start watching the recorded version now, but…I know how it is. Some folks prefer watching live because they can ask questions as I go along:

Now, we start getting to the fun stuff – things that normally cost $89 for each class’s recordings. These are all-day classes. On Tuesdays, I’m teaching them in Europe-friendly times, and on Wednesdays, I repeat the same class in America-friendly times.

Register here to attend all of those, for free. The Americas-friendly classes will be 9AM-5PM Eastern US time, and Europe-friendly classes will be 8:00-16:00 UTC. (How to Think Like the Engine is only a half-day class though.)

After you’re registered for class, set up your server.

All of the Fundamentals courses except Fundamentals of Columnstore have the same prerequisites. Set yourself up a SQL Server:

  • SQL Server 2017 or newer, either Developer Edition or Evaluation Edition. Download pages are linked from SQLServerUpdates.com. Express Edition, Azure SQL DB, and Amazon RDS won’t work, unfortunately.
  • Use the default collation during install, SQL_Latin1_General_CP1_CI_AS (don’t get fancy with binary collations)
  • Apply the latest Cumulative Update
  • Install the most recent SQL Server Management Studio

To follow along with the demos, download the 50GB Stack Overflow 2013 database (10GB 7z file). I’ll be using the medium-sized 50GB StackOverflow2013 database, and it’s vital that you use the same one. Query tuning and parameter sniffing is all about getting different behavior based on your query’s parameters, so I need you to have the exact same data distribution that I’ll be working with onscreen.

Fundamentals of Columnstore has more ambitious prerequisites because columnstore is about bigger data. You don’t have to follow along with the class demos at all – it’s totally optional, and you can just watch me do them on the screen – but if you do want to follow along, here are the columnstore prerequisites.

Register now – attendance is limited to the first 1,000 students who get in. See you in class!


[Video] Office Hours: Ask Me Anything About Microsoft SQL Server

Videos
1 Comment

Got questions that aren’t a good fit for DBA.StackExchange.com, and you want my opinion? Post them here and upvote the questions you’d like to see me talk through. This weekend, I hopped into my home studio and did a half-hour session because so many good questions had piled up:

Here are the questions I answered in this session:

  • 00:00 Introductions
  • 01:39 Why is connecting to SQL Server sometimes slow?
  • 03:52 How do I know when my indexes are hurting me?
  • 05:54 Should I enable Optimize for Ad-Hoc Workloads?
  • 10:15 What do you think about running SQL Server in Kubernetes?
  • 13:50 Can I restore everything except one table?
  • 18:15 How can I monitor 1,000 SQL Server instances?
  • 24:04 How can I explain that performance tuning never ends?
  • 25:58 (I rant about how much I hate tech support)
  • 27:15 How do I write a DBA resume when I have 8 years experience?
  • 30:28 Any alternatives for SSMS on Mac OS?
  • 32:52 What if I want to jump from a DBA role to project management?
  • 34:32 If users query nested views, can I see the resulting T-SQL?
  • 35:39 Should I dynamically craft update statements per column?
  • 38:04 Wrap-up

Wanna join a live session? Subscribe to my free YouTube channel and you’ll get notifications whenever I go live. See you there!


[Video] Office Hours: Ask Me Anything About Microsoft SQL Server

Videos
2 Comments

We took a break from roaming around Iceland long enough to check in at our home studio. While we were here, I did a live Office Hours session, taking your questions about Microsoft SQL Server.

Questions I answered in the video:

  • 00:00 Introductions
  • 02:35 How can I tell if my SQL Server has too much memory?
  • 05:20 Does normalization have a big performance impact?
  • 08:08 What performance metrics should we show to the business?
  • 11:17 Are SQL Server’s index recommendations any good?
  • 12:37 Thank you for all you do for the community.
  • 13:24 Are GUIDs a good clustering key for high concurrency?
  • 15:54 Should I stack multiple SQL instances on the same physical server?
  • 17:41 Do foreign keys help with performance?
  • 21:05 Is there a benefit to having an identity column?
  • 22:25 Why is my query blocking itself?
  • 25:29 Should I separate my database into multiple databases?
  • 27:46 What’s the best data type for primary keys?
  • 28:13 Should I enable RCSI locally since I use Azure SQL DB for production?
  • 29:32 Is my SQL Server slow because of my network?
  • 32:40 How should we move documents out of the database?
  • 33:30 Which query should I tune first?
  • 35:50 Is SQL Server 2019’s UTF8 support a game changer?
  • 38:13 Which query filled up my TempDB?
  • 40:35 Will there be a SQL Server 2021?
  • 43:35 What’s the best way to implement version control?
  • 44:23 What performance change yields the best bang for the money?
  • 45:49 Is 1,000 logical reads a big deal?
  • 46:58 What security role lets people administer SQL Agent jobs?
  • 48:35 How can I determine what permissions an application needs?
  • 50:35 How should I configure quorum for my cluster?

If you’ve got something you’d like to see me cover, ask your questions here, and while you’re at it, upvote other questions that you’d like to see covered. I’ll take the highest-upvoted questions and talk through ’em in an upcoming webcast.


[Video] Office Hours: Húsavík Harbor Edition

Videos
6 Comments

Today’s session comes to you from the harbor in Húsavík, the whale watching capitol of Europe.

Questions I answered in the video:

  • 00:00 Introductions
  • 00:29 Is SQL Server 2019 ready for prime time now?
  • 02:47 Does WITH NOLOCK cause high CPU usage?
  • 04:53 Is MERGE a good practice or bad?
  • 06:36 OPENROWSET is so easy – why shouldn’t I use it?
  • 07:52 How can you tell if a server has enough CPUs?
  • 11:47 Discussing cargo cult programming
  • 15:25 Wrap-up

If you’ve got something you’d like to see me cover, ask your questions here, and while you’re at it, upvote other questions that you’d like to see covered. I’ll take the highest-upvoted questions and talk through ’em in an upcoming webcast.


Want a Better Work/Life Balance? Use Jira at Home. #TSQL2sday

I’m only half-joking. Hear me out.

If you’re frustrated at how much you work, and how little time you spend with your family & friends, think about how much planning and task management you’re required to do at work. You’ve probably got a ticketing system that tracks a backlog of work you need to do. Someone monitors it, and you have to report your status regularly to them. You get fancy burn down charts that track your progress.

You get what you measure.

You’re being closely measured at work, so you hustle your rear end off at work, trying to get your tasks done. You work later and later, and put in time on weekends. It eats into your personal time, but you feel obligated to do it because work has effectively gamified your life.

Track your issues at home, too.

No, I certainly don’t recommend using Jira – I wanna poke my eyes with a rusty fork whenever I have to use that thing – but use something.

For day-to-day tactical home productivity, I’ve been using RememberTheMilk.com for over a decade, and here’s how I use it. I got started by using the productivity philosophy Getting Things Done, but that’s overkill when you’re just getting started. The point is to use any categorized to-do system that’s accessible from anywhere (including your phone), and gives you high-level metrics of how many to-dos you have in each category.

For longer-term home productivity, I use Steve Kamb’s Epic Life Quest strategy. I make a list of things I wanna do in the future, keep track of what I’ve done, and each time I finish 5 of those tasks, I celebrate finishing one level of my life.

I’ve been using this approach for over a decade, and now I have a wonderful new work/life balance problem. I kept focusing on the life stuff that I wanted to accomplish, and in 2021, I haven’t gotten enough work done – because I’ve been letting my life tasks take over my work time. We moved to Iceland in January for a 6-9 month vacation, and since getting here, I’ve done way, way less work than normal. We’re moving back to San Diego in October, and I’ll be getting my work/life balance back under control.

Thanks to Tjay Belt for hosting this month’s T-SQL Tuesday. The topic was work/life balance, and if you’d like to read more tips from the database community, check out the comments on that post.


[Video] Office Hours: Arctic Circle Puffins Edition

Videos
4 Comments

Erika and I are on the road in Iceland, roaming around the countryside, and I’m taking you with us. We visited Grímsey, a tiny island on the Arctic Circle, home to gazillions of sea birds like puffins and arctic terns. I took about half an hour to answer your most highly-voted questions from here:

Questions covered in the video:

  • 00:00 Introductions
  • 01:43 Should developers create indexes for their queries?
  • 03:28 Do you recommend monitoring for resource-intensive queries?
  • 04:40 How should I set max server memory?
  • 07:30 How much free space in buffer pages is acceptable?
  • 09:42 How can I fix implicit conversion in a 3rd party app?
  • 11:55 How do you organize tasks for productivity?
  • 14:00 How do you manage your mental state for productivity?
  • 16:02 Why might too many indexes slow down selects?
  • 18:00 What’s better – queries in the app or in stored procedures?
  • 22:06 How do I prioritize DBA tasks?
  • 23:38 How do I find which queries are winning the deadlocks?
  • 24:35 How do I find out what my memory is being used for?
  • 26:10 Wrap-up
  • 27:40 Walking you over to the puffins

If you’ve got something you’d like to see me cover, ask your questions here, and while you’re at it, upvote other questions that you’d like to see covered. I’ll take the highest-upvoted questions and talk through ’em in an upcoming webcast.


[Video] Office Hours: Siglufjörður Airport Runway Edition

Videos
8 Comments

Erika and I are on the road in Iceland, roaming around the countryside, and I’m taking you with us. This session was shot in Siglufjörður, a beautiful little oceanfront fishing village in the north.

Yes, I’m on the airport runway. No, seriously. Here’s what it looks like from the air in the winter.

Questions covered in the video:

  • 00:00 Introductions
  • 00:45 What are the best practices for linked server security?
  • 04:20 Have you ever told a customer to change their collation?
  • 06:28 What compatibility level do you recommend?
  • 09:06 Should parallelism be 70% of my waits?
  • 11:36 Should I append GUIDs to my temp table names?
  • 13:30 Why is there a car hood in the video?
  • 14:25 Why are my TempDB write stalls slower than my user databases?
  • 18:34 Should I send an email for each failed login?
  • 19:43 Has your opinion of table-valued user-defined functions changed?
  • 20:36 How do I measure the overhead of Transparent Database Encryption?
  • 21:46 Can highly fragmented indexes cause PAGEIOLATCH waits?
  • 22:30 How do I track down which query is using a specific index?
  • 24:45 Wrap-up

If you’ve got something you’d like to see me cover, ask your questions here, and while you’re at it, upvote other questions that you’d like to see covered. I’ll take the highest-upvoted questions and talk through ’em in an upcoming webcast.


Who’s Hiring in the Database Community? August 2021 Edition

Who's Hiring
20 Comments

Is your company hiring for a database position as of August 2021? Do you wanna work with the kinds of people who read this blog? Let’s set up some rapid networking here. If your company is hiring, leave a comment.

The rules:

  • Your comment must include the job title, and either a link to the full job description, or the text of it. It doesn’t have to be a SQL Server DBA job, but it does have to be related to databases. (We get a pretty broad readership here – it can be any database.)
  • An email address to send resumes, or a link to the application process – if I were you, I’d put an email address because you may want to know that applicants are readers here, because they might be more qualified than the applicants you regularly get.
  • Please state the location and include REMOTE and/or VISA when that sort of candidate is welcome. When remote work is not an option, include ONSITE.
  • Please only post if you personally are part of the hiring company—no recruiting firms or job boards. Only one post per company. If it isn’t a household name, please explain what your company does.
  • Commenters: please don’t reply to job posts to complain about something. It’s off topic here.
  • Readers: please only email if you are personally interested in the job.

If your comment isn’t relevant or smells fishy, I’ll delete it. If you have questions about why your comment got deleted, or how to maximize the effectiveness of your comment, contact me.

I close the comments here after 1 week, and publish another post each month in the Who’s Hiring category so y’all can get the latest opportunities.

If you want to be notified as people leave new job comments here, there’s a “subscribe without commenting” option where you leave a comment.


[Video] Office Hours: Ask Me Anything About Professional Development

Videos
0

Erika and I are on the road in Iceland, roaming around the countryside, and I’m taking you with us. This week, we’re in Siglufjörður, a beautiful little oceanfront fishing village in the north. In the mornings, before Erika wakes up, I’m filming Office Hours videos to answer your questions that you post here.

Today’s session comes to you from a little forest, alongside a babbling brook, just downstream from a waterfall.

Questions I covered in the video:

  • 00:00 Introductions
  • 00:51 Would you ever do a video about your travels?
  • 03:20 What music are you listening to in Iceland?
  • 05:00 I’m a DBA, and developers rule my shop. When is enough enough?
  • 08:10 How do you develop your training skills?
  • 11:26 When is your next production DBA training course coming out?
  • 13:52 How can I deal with impostor syndrome?
  • 15:50 When will you offer another remote SSIS training class?
  • 17:18 Do you see many people using SQL Server on Linux or containers or ARM?

If you’ve got something you’d like to see me cover, ask your questions here, and while you’re at it, upvote other questions that you’d like to see covered. I’ll take the highest-upvoted questions and talk through ’em in an upcoming webcast.


[Video] Office Hours: Ask Me Anything About SQL Server

Videos
17 Comments

Erika and I are on the road in Iceland, roaming around the countryside, and I’m taking you with us. This week, we’re in Siglufjörður, a beautiful little oceanfront fishing village in the north. In the mornings, before Erika wakes up, I’m filming Office Hours videos to answer your questions that you post here. The first video comes to you from a park bench next to the harbor:

Questions I answered in the video:

  • 00:00 Introductions
  • 01:34 Do we need to do index maintenance on a 1TB database?
  • 04:16 Is it bad to have multiple SQL Server instances on one VM?
  • 06:40 Why isn’t there a good static code analyzer for T-SQL?
  • 09:50 Why are we getting out-of-memory errors with 780GB RAM?
  • 13:20 Brent talks to birds
  • 13:58 What should people be documenting about their SQL Servers?
  • 19:11 Should I disable automatic statistics updates?
  • 21:58 How much performance tuning is enough?
  • 24:52 Talking about Siglufjörður’s harbor

If you’ve got something you’d like to see me cover, ask your questions here, and while you’re at it, upvote other questions that you’d like to see covered. I’ll take the highest-upvoted questions and talk through ’em in an upcoming webcast.


Updated First Responder Kit and Consultant Toolkit for July 2021

I’m slowing the First Responder Kit update frequency down to once every 2 months. I know from firsthand experience working with clients that folks just can’t patch quickly enough, hahaha. Folks who want to be on the bleeding edge updates can always use the dev branch in Github, too, getting every new fix the moment it’s merged.

How I Use the First Responder Kit
Wanna watch me use it? Take the class.

To get the new version:

Consultant Toolkit Changes

  • Improvement: added “Buffer Pool Scan” to the list of things we show from the error log. (Thanks Erik Darling.)

I also updated it to this month’s First Responder Kit, but no changes to querymanifest.json or the spreadsheet. If you’ve customized those, no changes are necessary this month: just copy your spreadsheet and querymanifest.json into the new release’s folder.

sp_Blitz Changes

  • Improvement: new warning when target recovery interval <> 60. (#2938, thanks Erik Darling.)
  • Fix: sp_Blitz installation was failing on unpatched 2012/2014/2016 builds due to last month’s Amazon RDS tweaks. (#2921, thanks Gianluca Hotz.)
  • Fix: output to table wasn’t sorting by database, so if you were querying the output table and sorting by ID, you didn’t get exactly the same order as the interactive output. (#2937, thanks Johan Parlevliet.)
  • Fix: only alert about the Query Store cleanup bug on SQL Server 2016, not 2019. (#2946, thanks FlatlandR.)
  • Fix: avoid collation errors when pulling the #SkipChecks list from a linked server with a different collation. (#2949, thanks David Schanzer.)

sp_BlitzCache Changes

sp_BlitzFirst Changes

  • Improvement: add total thread time to wait stats views. Total thread time is the total amount of time spent executing queries, whether waiting on something or burning CPU, including parallel child threads. The Github issue is still open because we’ll be putting additional work & blogging into this over the coming weeks & months. It’s helpful when troubleshooting servers with plan cache amnesia, but I need to write more about how & why. (#2913, thanks Erik Darling.)
  • Fix: enabled the Sleeping Query with Open Transactions alert. (#2922, thanks Eitan Blumin.)
  • Fix: was showing multiple active backups if backup scripts were running from the context of a user database rather than master. (#2941, thanks Adrian Buckman.)

sp_BlitzIndex Changes

  • Fix: corrected “JIndex” to “Index” in one of the warnings. (#2927)
  • Fix: improve uptime measurement on Azure SQL DB. (#2933, thanks David Wiseman.)
  • Fix: the statistics section now shows up even if you’re calling sp_BlitzIndex across databases. (#2954, thanks MisterZeus.)

sp_BlitzWho Changes

  • Improvement: new @GetOuterCommand parameter. When set to 1, shows the outer calling query. (#2887, thanks Adrian Buckman.)

sp_DatabaseRestore Changes

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. Be patient: it’s staffed by volunteers with day jobs. If it’s your first time in the community Slack, get started here.

When you find a bug or want something changed, read the contributing.md file.

When you have a question about what the scripts found, first make sure you read the “More Details” URL for any warning you find. We put a lot of work into documentation, and we wouldn’t want someone to yell at you to go read the fine manual. After that, when you’ve still got questions about how something works in SQL Server, post a question at DBA.StackExchange.com and the community (that includes me!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you’re working with.


Upcoming Office Hours: Get Your Questions In Now

Company News

Office Hours - ask me anything.From time to time, when my incredibly busy (cough)vacation(/cough) schedule permits, I do a live Office Hours stream where I take your questions and answer ’em live.

I’m going to do a couple of them this week, but I’m going to do them disconnected. I’ll be recording them on the road – Erika and I are touring Iceland’s countryside again for the next couple of weeks.

Get your questions in here, and while you’re in there, upvote other questions that you’d like to see me discuss on air. I’ll take the highest voted ones and talk through those, and then publish the video later this week on my YouTube channel.

I’ve disabled comments on this post just to make it clear that your questions go over here. Ask away!

Update 2021-07-27: I’ve answered the first round. If your question disappeared from the list, it’s because I answered it in an upcoming video. I’ve recorded the videos, and I’ll upload ’em when I can figure out how to get them off my camera. <sigh> Forgot to bring an SD card reader with me on this road trip, and I’m not near an electronics store at the moment.


Tweets You Missed

Humor
11 Comments

You don’t follow me on Twitter because you’re not on Twitter, and I can’t say that I blame you. It’s a bunch of people yelling at each other. Come to think of it, I’m not even sure why *I’m* on there.

But anyway, here are a few things I’ve said on there lately that you’ll probably get a chuckle out of. Enjoy!

(Lest someone think I don’t like MySQL, I do! This site is powered by MySQL.)

I can’t promise gems like those very often, but if you wanna follow me, I’m @BrentO.


Database Performance Tuning is Getting Much Harder.

Cloud Computing
27 Comments

For the last twenty years, SQL Server performance tuning has relied on a few facts:

  1. SQL Server provided a lot of performance metrics with thorough documentation from both Microsoft and the community.
  2. You could fetch those metrics in a variety of ways, like Perfmon counters and dynamic management views.
  3. Many of the metrics were cumulative since the instance started up (index usage DMVs, wait stats) or since “the thing” happened (like the query plan went into cache.)
  4. Instance restarts rarely happened, so we had a lot of metric history available at any given time.
  5. Your SQL Server’s performance capacity was fixed: the core count and memory wasn’t changing.
  6. Your SQL Server’s behavior was fixed for long periods of time: no one was patching the engine without you being aware, and before the patch was applied, you could test it to understand what the patch affected.

Eyes up here, kidIn the cloud,
those aren’t facts anymore.

I’m not saying the cloud is bad – I freakin’ love it – but it’s different, and that’s why we have to keep adapting as performance tuners.

The first major shift was the original version of Azure SQL DB. It was effectively SQL Server, but only for one database at a time, and hosted & managed by someone else (Microsoft.) It was mostly SQL Server, but had a few minor key differences – one of which was that Microsoft could restart and/or patch your SQL Server instance at any time, without warning.

Microsoft put a lot of work into hot patching Azure SQL DB, and for them, the results are fantastic: they can keep your instance up to date and on stable hardware without having to communicate outages to you.

But Microsoft’s also been following your example and simply not documenting their changes. There’s no changelog for Azure SQL DB, no notification that your server was just patched, and no warning that behaviors have changed. In theory, there are no bugs – but as we can clearly see from the repeated problems with Cumulative Updates, there are one hell of a lot of bugs left in the database engine.

Each new cloud service
changes facts you relied on.

Azure SQL DB Elastic Pools made it easier to tune with your wallet: group a bunch of databases together, buy hardware capacity for the entire pool, and then if any one database needed a burst of power temporarily, that was fine. That was completely revolutionary. (By that I mean, it’s exactly how SQL Servers have worked for the last two decades. Whatever.)

Azure SQL DB Serverless automatically scales capacity up and down based on the running workload. That means even if you’re logging every performance metric, and you understand what they all mean, that’s still not enough: you need to be able to interpret them at different points in time based on the amount of compute capacity that was available at that time.

For example, say the running workload 30 minutes ago demanded a lot of capacity, so Azure automatically spun up more horsepower, but now there isn’t much of a workload, and so a query that ran slowly 1 hour ago (at low capacity) is running super fast now (at high capacity.) We can’t just look at query runtimes as an indicator of query performance because they vary too much, and they don’t indicate query plan quality – just wallet depth.

Azure SQL DB Hyperscale reinvented the way SQL Server’s storage works. If you’re still thinking of monitoring in terms of disk queue length and buffer cache hit ratio, you can throw that knowledge out the window when you move to Hyperscale.

This is a great time to be
a performance tuner.

I got into databases because I hated learning languages. I have so much respect for developers who can jump from one language to the next, constantly learning new syntax. I burned out quickly when I tried that, and I wanted something where the language stayed the same for decades. That’s databases.

But I do love learning, and it’s a hell of an awesome time to be a database performance tuner. There’s so much to learn all the time with every new technology that comes out.

The hardest part is figuring out where to place your learning bets. There are hundreds of talking heads in the database community that say, “You should totally learn Technology X or else you’re gonna be a dinosaur.” Well, given our limited hours of free time per week, we can’t learn everything – not to the depth that will really pay off in our careers – so what do we learn? Which technologies are gonna take off, and which technologies are gonna be dinosaurs themselves? It’s even more complex when you try to learn emerging technologies because the product you learn today can evolve dramatically within the next 1-2 years, rendering a lot of your learnings obsolete.

I don’t have answers, but I’m excited to see how the next few years shake out.


Why Are Linked Server Queries So Bad?

Development
90 Comments
And your penmanship is terrible.
No, I don’t want to be your valentine.

Remember when you were in school, and you had a crush on someone? You would write a note asking them to be your valentine, and you’d ask a mutual friend to pass the note over to them.

The adult equivalent is linked server queries.

When your query needs to get data that’s actually stored on a totally different SQL Server, it’s tempting to use linked server queries. They’re really easy to write: after setting up the linked server, just put the server and database name at the front of your query, and SQL Server takes care of everything for you by connecting to the server that has the data you want.

I’ll demo an example using the open source Stack Overflow database. In this example, the Users table is stored locally, but I need to fetch data from the much larger Posts table, which is stored on another server.

I’m looking for the most recent posts written by the users with a low number of votes. Performance is catastrophic, taking minutes of query runtime – and no rows are even returned. What two problems are causing this?

Problem #1: linked server queries can copy entire tables across the network.

In this query, SQL Server believes that LOTS of users are going to match, so it decides to just go start fetching rows from the Posts table across the network. Read the query plan from right to left, top to bottom – the thing at the top right was the first thing SQL Server chose to do:

The first thing SQL Server decided to do was a Remote Query – scanning the Posts table across the network, starting from the most recent posts first. The remote SQL Server sends rows, and for each row, the local SQL Server checks to see if the matching user has a low number of votes.

This plan would work great – if SQL Server’s assumptions about a large number of matching Users rows were correct. In some cases, when SQL Server’s assumptions match, queries perform just fine. (That’s really a good chunk of query tuning right there, and as I talk about in my Fundamentals of Query Tuning class, large variances between estimated and actual rows are usually the place to focus on when you’re tuning.)

Unfortunately, that assumption is not correct.

In fact, no users match our criteria.

So the local SQL Server keeps on dragging rows across the network from the remote server, checking its owner one at a time, and eventually exhausts the entire content of the Posts table. Eventually, the plan finishes, and here’s the actual plan:

The linked server query copied the entire Posts table across the network wire, one row at a time.

Yes, that’s 33 minutes and 29 seconds.

Problem #2: linked servers don’t cache data.

If you run the same linked server query a dozen times – even if the rows aren’t changing, even if the database is read-only, even if all twelve queries run at the same time, SQL Server makes a dozen different connections over to the linked server and fetches the data from scratch, every single freakin’ time.

This is another great example of a development pattern that works fine in development, especially with small database sizes – but then falls down dramatically at scale, with larger databases and more concurrent queries.

Even worse, it penalizes both servers involved with the linked server query. It’s hard on the local server, and it’s hard on the remote server that holds the single source of truth for the table.

There are many more reasons linked server queries perform poorly – but those two alone are showstoppers.

So when are linked servers okay?

They’re fine for one-off utility queries, things you only have to do a couple of times. For example, say someone really screws up a table, and you need to restore that table’s contents. Since SQL Server still can’t restore a freakin’ table, a common approach is to:

  • Restore the database backup onto a different server
  • Pull or push just the rows you need (or all of them) across a linked server connection

That way, you don’t risk accidentally restoring the backup over the entire production database, and you can take your time picking and choosing the specific rows you want.

Otherwise, if you need data from a different SQL Server, I’ve got some tough advice: connect to the server that has the data you want. If you need fast results, that’s the most surefire game in town.

And for the record, about that note your friend passed to me, the answer is no, I do not want to be your valentine. But if maybe you asked me directly, the answer might be different. There’s a lesson in there.