[Video] Office Hours: Arctic Circle Puffins Edition

Videos
3 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

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
25 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
83 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.


SQL ConstantCare® Population Report: Summer 2021

Ever wonder how fast people are adopting new versions of SQL Server, or what’s “normal” out there for SQL Server adoption rates, hardware sizes, or numbers of databases? Let’s find out in the summer 2021 version of our SQL ConstantCare® population report.

Out of the 3,808 servers sending in data recently, the most popular version of SQL Server is still 2016:

When you group versions together it makes for interesting metrics:

  • Now that SQL Server 2016 mainstream support is over today, only 34% of installs are under mainstream support with 2017 & 2019.
  • The combination of 2016, 2017, and 2019 make up 67% of the market, but…
  • The combination of 2014, 2016, and 2017 make up 74% of market share right now, exactly the same as 6 months ago! That’s kinda wild.

SQL Server 2008 and 2008R2’s market share stayed about the same, but 2012 went down by about 1/4, and 2014 down by about 1/10th. 2016 and 2017 stayed stable.

The newer products are at the top of this chart, and the new data’s at the right, so you can see the new stuff gradually pushing down the old stuff over time:

SQL Server 2019’s adoption has almost doubled over the last 6 months. In the last report, I predicted that come mid-2021, 2019 (currently 11%) would have more market share than SQL Server 2012 (currently 9%), and that turned out to be true.

Azure SQL DB’s share has actually gone way down, not up, but I don’t think that’s an indication of the wider market. I’ve had a few Software-as-a-Service (SaaS) companies try SQL ConstantCare® and bail because they get too many recommendations, since we send them for each connection. There’s probably a small market for a SaaS-focused monitoring tool that consolidates database metrics across all your databases and gives centralized recommendations about overall index usage and query workloads, and I’ll blog about that at some point. SQL ConstantCare® definitely isn’t that tool, and I don’t intend to build that tool, either.

SQL Server 2016 is still the juggernaut, with 1 in 3 instances overall.


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

Who's Hiring

Is your company hiring for a database position? 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.


Get Ready: SQL Server 2016 Mainstream Support Ends in One Week.

SQL Server 2016
8 Comments
Brent Ozar, bearer of bad news

Who am I kidding? You’re not really going to get ready, but let’s talk about this anyway.

SQL Server 2016 mainstream support ends Tuesday, July 13, 2021.

According to Microsoft’s SQL Server support policy, that means from here on out, Microsoft will only deliver security patches, not functional, performance, or stability patches. You can still call Microsoft for help, but if you find a performance issue and they need to build code for a fix, you’ll only be able to get it on the two mainstream supported versions – only SQL Server 2017 and 2019.

To make things even more interesting, SQL Server 2017’s mainstream support is only good through October 2022, and SQL Server 2019 through January 2025.

Microsoft’s current support policy is that new versions only get mainstream support for 5 years. We still don’t have any public previews of the next version of SQL Server, let alone a 2021 release date. Even if Microsoft released the first public betas tomorrow, and gave us RTM this winter, that means that starting October 2022, we’ll be back to only 2 mainstream support versions: 2019, and whatever vNext is.


Stop Procrastinating: Master Index Tuning in 1 Month for $195.

Indexing
3 Comments

You’ve conquered my Fundamentals of Index Tuning and Fundamentals of Columnstore classes, and you’re ready to take it to the next level.

Mastering Index TuningIt’s time for you to join my Mastering Index Tuning class. When I teach it live – the next one is July 19-21 – it’s 3 days of lectures and hands-on labs where you learn:

  • How to deduplicate & prune an existing database with too many indexes
  • How to use sp_BlitzIndex to design the right nonclustered indexes for specific queries
  • How to assess a large workload and prioritize which tables/indexes to work on first
  • How to convert badly-performing recommended indexes into blazing-fast hand-tuned indexes
  • How to know when filtered indexes, indexed views, and indexed computed columns will pay off
  • How to measure whether your index changes made things better, or worse

Because this one’s tougher, I’m going to give you a little more time and a few options:

This sale ends Saturday. I hope this is as much fun for y’all as it is for me – I love seeing hundreds of y’all commit to upping your game dramatically in a short period of time. There’s no time like the present, so let’s do this! See y’all in class.

Update: sale’s over!


Store Files in a File System, Not a Relational Database.

Architecture
32 Comments

Since the dawn of time, database vendors have called out to developers, “Store everything inside the database. You know you want to. Here, we’ll make it easy for you by adding data types like binary and features like filestream.”

Ah, but this siren song leads you to your eventual doom.

Users wanna store historical versions of files. In the beginning, the design requirements might agree to only save the most recent version of a file, but that’s gonna change. Users are going to wanna save every version over time – and even when they don’t, they’re going to create “new” files by saving them under an ever-so-slightly different name. You know that 2022_Budget_Final_v2_with_Changes.xlsx file, and all its grandparents? Yeah, they’re all living in the database, forever.

Users create new versions frequently. You know how you click File, Save all the time because you’re used to crappy applications that crash and lose your progress? Now think about that hitting a relational database server every time, multiplied by all of the users who are working in this system.

Trust me, I have the scars

Users don’t purge anything. Over time, the files look like an episode of Hoarders. Nobody can clean things out because nobody knows what’s safe to delete, so “we’d better keep it all just to be safe.”

Files are huge. Relational databases work great with tables broken up into rows and columns. When we insert rows or update columns, we’re only affecting a relatively small amount of data each time, making it easy to track in transaction logs and write to storage quickly. Not so with files, which usually contain incompressible things like images.

A bigger database means slower backups. Not just your full backups, either – we’re talking slower transaction log backups, too. As your users constantly save new versions of files, those versions get written into the transaction log, and that’s gotta get backed up. (Filestream users: before you get all excited that your feature is “different”, go double-check your log backup sizes, because filestream doesn’t work the way you think it works.)

A bigger database means tougher HA & DR. Not only do we have to back up the transaction log, but we have to push those transactional changes over to our other replicas. This huge log volume means it’s very challenging to do synchronous replicas in other data centers, and it’s very easy for other data centers to get way behind on transactions. When a user adds a 100MB file, and then another user tries to insert a single row, that row has to wait until the 100MB file synchronizes first. The low-value file storage can cause much worse recovery time objectives for our more valuable data.

Database servers suck at re-synchronizing data. When replication breaks, or you need to re-add an Availability Group replica (or log shipping or replication or whatever), SQL Server isn’t smart enough to only synchronize the different data, or sync data from the closest replica. This isn’t a big deal with small transactional tables, but throw terabytes of file data in, and resynchronization is a nightmare.

Finally, database servers are an expensive, challenging bottleneck. If you make a list of all the servers in your company, and rank them by things that make you tear your hair out, the relational database servers are probably at the top of every list. Their licensing and support costs are expensive, they’re hard to performance tune, and you just don’t have that many people on staff who are truly experts on them.

All of this might come as a surprise if you’ve only been reading the brochure from the database vendors. That’s because they want you to store files in the database. Can you think about why they might be motivated to sing you that siren song? They make more money when you’re locked into their system for everything.

There’s a better way:
store files in a file system.

File systems have been around for a really long time, and they have tools to help address these challenges. Best of all, they’re insanely, insanely inexpensive, especially compared to relational databases.

When you think that storing files in a relational database is the right answer, stop for a second, write up your list of requirements, and go talk to your systems administrator.

Because if you’re not doing a relational join with it,
it doesn’t belong in a relational database.


Lock Escalation Sucks on Columnstore Indexes.

Columnstore Indexes
1 Comment

If you’ve got a regular rowstore table and you need to modify thousands of rows, you can use the fast ordered delete technique to delete rows in batches without hitting the lock escalation threshold. That’s great for rowstore indexes, but…columnstore indexes are different.

To demo this technique, I’m going to use the setup from my Fundamentals of Columnstore class:

  • An 4-core, 30GB RAM VM with the data & log files on ephemeral (fast) SSD
  • SQL Server 2019 Cumulative Update 11
  • The Stack Overflow public database as of 2018-06 (about 180GB)
  • The dbo.Votes table – which has 151M rows

I’ll create a new copy of the Votes table with a clustered columnstore index:

Let’s say we’ve decided to no longer use VoteTypeId 12, Spam, and we need to delete those 29,852 rows.

The bad way: plain ol’ delete

I’m doing this inside a transaction only so we can slow it down to see the locks that are held open:

The locks output from sp_WhoIsActive @get_locks = 1 looks long, but only because there are so many rowgroups involved. I’ve trimmed it here to just show a few rowgroups:

The important lock to be aware of is the HOBT X: that’s an exclusive lock on the table. That’s the part that I need to avoid: in high concurrency situations, it may not be easy to get that kind of a lock on the table.

The good way: Fast Ordered Deletes

After rolling back that transaction, let’s try again with the fast ordered deletes technique. This time around, I’m using a CTE rather than the view technique I used in that last blog post:

This time, I don’t even have to trim sp_WhoIsActive’s output. It’s way shorter because there are less rowgroups involved:

Note these lines in particular:

Awww yeah: we only have intent-exclusive locks on the object, and the exclusive locks only involve 1,000 of the keys. Yay! We’ve avoided table-level locking, so now we get better concurrency on the object.

But…it doesn’t really work.

The real test of the fast ordered delete technique is the ability to run queries on the other 150,783,380 rows that we’re not deleting. (Remember, we’re only deleting 1,000 rows.) And here’s the bummer: all of these queries are blocked:

I can get single rows if I specify the Id, and the row isn’t in a locked rowgroup:

But if I specify a row in a locked filegroup, that row is locked – even if the VoteTypeId isn’t 12. This is blocked:

I can prove that it isn’t VoteTypeId by running it with NOLOCK:

Columnstore lock escalation gets ugly.

As far as our users are concerned, even with just 1,000 rows involved in a transaction, the entire table is practically locked for most kinds of queries. Queries can only succeed if SQL Server can do rowgroup elimination to bypass the locked rowgroups altogether – but even that is practically impossible because even the simplest queries, like top 10 by Id ascending, won’t eliminate rowgroups here.

This is one of the many reasons columnstore isn’t really a good fit for most transactional workloads. Don’t get me wrong – I looove columnstore indexes – but if your workloads involve concurrency, and even more so if they involve modifying thousands of rows at a time, you’re going to need to do special table design like partitioning and sliding window loads in order to get the performance you’re already used to with rowstore indexes.

Fundamentals of Columnstore IndexesFor more learning, check out:


Learn Fundamentals of Columnstore for $1.

Last week, I gave you a 1-week, $1 deadline, and 777 of you took me up on it. That’s awesome! I love having fun with stuff like this.

Let’s do it again! You’ve got 1 week to learn the Fundamentals of Columnstore for $1, and sales close Saturday. Here’s the abstract:

Fundamentals of Columnstore IndexesYour report queries are too slow.

You’ve tried throwing some hardware at it: your production SQL Server has 12 CPU cores or more, 128GB RAM, and SQL Server 2016 or newer. It’s still not enough to handle your growing data. It’s already up over 250GB, and they’re not letting you purge old data.

Will columnstore indexes help? To find out, you’ll learn:

  • How columnstore data is stored, and how that impacts your architecture choices
  • How to do a hands-on self-assessment of your workloads, data, and server to figure out whether columnstore indexes make sense for you
  • Why partitioning makes so much sense for columnstore indexes
  • How to do a proof-of-concept implementation with initial creation, querying, ongoing loads, and index maintenance

Normally, you’d have to pay $89 to access this course for a year. However, I know how it goes: you buy the course, it sits in your account, and you don’t actually finish it because you don’t have the time. Well, not today, bucko: you can buy the course for $1, but you only have access to it for 1 week, so you better move it or lose it.

You don’t have to follow along with the demos in this course, but if you want to, you’re going to need a beefy SQL Server: 4 cores, 30GB RAM minimum, and the ~200GB 2018-06 Stack Overflow database specially prepped for my Mastering training classes. Here’s how to set up your machine.

Oh and I don’t know why I’m even mentioning this, but it just so happens that if you do that setup, you’ll be ready to go for my Mastering Index Tuning class. The next live one of those is July 19-21st. There’s probably no reason that I’m mentioning that, right? It’s just a coincidence that if you’ve been following along with the $1 sale, you’ve done all of the prerequisites for that class. It’s probably nothing. Disregard. You can just close this and go on with your day.

Update: sale’s over! Congrats to the hundreds of folks who moved quickly and got in on the deal.


Free Video: 500-Level Guide to Career Internals: Building a Brand

Professional Development, Videos
0

Last week, I shared my 300-Level Guide to Career Internals post & video. We talked about how the world is changing so quickly, and how it’s a great time to adapt your career to accomplish the things in life that are important to you. One of the possible outcomes that we discussed was working for yourself.

If you want to work for yourself, you need to be able to sell yourself.

I know, I hate it too – it feels gross when I write it that way. As kids, we were taught to be humble, but that only takes you so far when you need to bring business in to pay the bills. You’ll have an easier time selling your services if people recognize you by name, seek you out by name, and associate your name with high-quality work. Very few other people are going to toot your horn for you – you’re gonna have to do a little tooting yourself.

Customers seek out brands like Lego, Adidas, and Coke, and that’s not just because of the quality of their products. It’s also because of the b-word: branding.

In this video, you’ll learn my 3-word exercise for building your brand, and then learn how to become known for that brand in our industry without spending any money on advertising. If you use this exercise and then stay true to it in your blog posts, tweets, and presentations, you’ll have customers pouring in without having to make a single phone call or email.

Feel free to ask followup questions here in the comments. I don’t get notified when folks ask questions in the YouTube comments on the SQLBits channel, so I don’t respond quickly there, but I respond here.

If you like this video, check out the SQLBits YouTube channel. They’re releasing all of last year’s conference videos online for free in YouTube. When you subscribe to their channel, you’ll automatically get notifications as new videos get uploaded.


Adding Managed Instances to SQL Server Distributed Availability Groups

SQL Server Always On Availability Groups help you build a more highly available database server by spanning your database across two or more SQL Server instances. When the primary goes down, the secondary can take over. You can also scale out reads to the secondary servers.

Distributed Availability Groups take this a step further and let you build an Availability Group of Availability Groups, hahaha, spanning a database across different clusters. In theory, this makes version upgrades possible with lower downtime: the individual AGs can be different SQL Server versions, and you can do rolling version upgrades. (That’s the theory: in practice, it’s a hell of a lot of work.)

That’s where Azure SQL Managed Instances come in. They’re basically an Availability Group that’s hosted and managed by Microsoft up in Azure, but the next version of SQL Server rather than the one you’re currently running on premises. In theory, you could have an on-premises Availability Group running SQL Server 2019, and run a Distributed Availability Group up to Azure SQL Managed Instances.

In practice, this isn’t supported today.

However, SQL Server 2019 Cumulative Update 11 added a few new rows in sys.messages that indicate Microsoft’s working on this, though:

  • 47500: Manual seeding is not supported option for secondary AG ‘%.*ls’ configuration when secondary participant in distributed availability group is Azure SQL Managed Instance.
  • 47501: Synchronous commit is not supported option for the initial secondary AG ‘%.*ls’ configuration when secondary participant in distributed availability group is Azure SQL Managed Instance.
  • 47502: Cannot create distributed availability group ‘%.*ls’ when local AG ‘%.*ls’ contains more than one database in cases when secondary participant is Azure SQL Managed Instance.
  • 47503: AG ‘%.*ls’ already contains one database and adding more is not supported because AG participates in distributed availability group ‘%.*ls’ with secondary on Azure SQL Managed Instance.
  • 47504: Error related to distributed availability group ‘%.*ls’ with secondary participant on Azure SQL Managed Instance.

If this feature is ever released publicly – sometimes Microsoft builds things but doesn’t actually release them publicly, like snapshot materialized views – then these limitations point to the use case scenario for the feature. The above-listed limitations make the feature only useful for one thing: one-time, one-way, single-database migrations from conventional SQL Server to Azure SQL Managed Instances. You wouldn’t really wanna have an Availability Group where you couldn’t have synchronous commits or multiple databases.


Learn Fundamentals of Index Tuning for $1.

Indexing
28 Comments

You’re lazy and overconfident.

You’ve been reading this blog for a while, and you’ve been telling yourself, “Yeah, I’m pretty good at this database thing. I’ve got years of experience under my belt. I know the fundamentals.”

You’ve told yourself, “Yeah, someday I’ll get to Brent’s How to Think Like the Engine class,” but…you’ve never actually done it. You’ve never watched the whole video start to finish, nor read the 16-part blog series start to finish. And you sure haven’t made your way up to my Fundamentals of Index Tuning class.

I feel you. I procrastinate a lot too.

You know what helps? A time deadline. Sometimes, I just can’t get up off my lazy rear to finish something unless a clock is ticking loudly in my ear.

Need a deadline? One week. $1.

This week only, you can buy one week’s access to Fundamentals of Index Tuning for $1.

Normally, you’d have to pay $89 to access the course for a year. However, I know how it goes: you buy the course, it sits in your account, and you don’t actually finish it because you don’t have the time. Well, not today, bucko: you can buy the course for $1, but you only have access to it for 7 days, so you better move it or lose it.

To follow along with the demos, you’ll need a SQL Server 2017 or 2019 instance and the 50GB Stack Overflow 2013 database. Any hardware capacity is fine – we’re not going to be pushing the server too hard during these demos. (My Mastering classes are another story.)

Buy it now for just $1, and skip the Netflixing for a week. Let’s get ‘er done and raise your skills. At the end of a week, you’ll still be lazy – but at least you’ll be a little less overconfident.

Update: the sale is over, and 777 of y’all joined in. Let’s see how y’all do on completing the course!


Menu