Free Live Class This Week: Fundamentals of Columnstore

Company News
1 Comment

Your 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.

Fundamentals of Columnstore IndexesWill columnstore indexes help?

In one day, 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

Join me live, for free, on Tuesday 8:00-16:00 UTC in Europe-friendly times, and again Wednesday in Americas-friendly times, 9AM-5PM Eastern. Register here.


Is Microsoft SQL Server 2022 a Big Release?

SQL Server 2022
30 Comments

Normally when y’all post questions here on Pollgab, I let ’em pile up and I eventually do an Office Hours webcast where I talk through my answers. I check in on the list every couple of days just out of curiosity, though, and one of ’em jumped right out at me.

The question, posed by Brent’s Tasty Beverage (nicely done) was:

My friends feel announcement from MS regarding SQL22 were only relatively small changes (since we didn’t see too much of multiple plans technically or demo), nothing groundbreaking or revolutionary. What are your thoughts?

There are a few questions here.

Should a Microsoft SQL Server release be groundbreaking or revolutionary? No. And I mean, no isn’t even strong enough of a word – absolutely, positively hell no. Microsoft SQL Server is one of the world’s most popular relational databases, trusted by companies from banks to hospitals to manufacturers to corner shops. This isn’t some tiny project used by a few hobbyists. Whatever Microsoft ships in SQL Server is going to be installed by thousands of companies and relied on for decades. If you want something groundbreaking or revolutionary, check out the poop that gets flung at the wall the new services that get created in the cloud. When Microsoft creates something brand new in the cloud, that’s where they can experiment with crazy groundbreaking stuff like CosmosDB. If 14 people use it and get burned, life goes on. (I’m being humorous here – at Microsoft’s scale, anytime they sign their name to anything, even an open source project, they’re signing a check for a ton of support workload and technical debt.)

Are SQL Server 2022’s changes relatively small? Oh my gosh, no. Even when I look at just the four big-picture changes that I listed in the What’s New in SQL Server 2022 post:

  • Failover from SQL Server 2022 to Azure SQL DB Managed Instances and back, plus related, the ability to restore versionless databases from Azure SQL DB Managed Instances down to on-premises SQL Server 2022
  • Azure Synapse Link to avoid complex ETL jobs between SQL Server and Azure Synapse
  • SQL Server Ledger – blockchain immutable histories of tables
  • Parameter-sensitive plan optimization that caches multiple plans per stored procedure

Those are way, way bigger than they look at first glance, and I’m really excited at each of them. I don’t think Microsoft will do a perfect job of shipping each of them by any means, nor am I saying I’m going to agree with how much (or little) work they put into each feature, but all four of those are bigger, harder work than they look like at first glance, and they have the potential to be really big. It’s going to be up to us – the real world users – as to how well they’re adopted, and in the following thousands of words – there’s going to be a lot, because I just opened this bottle of wine – I’m going to talk about which ones I think will catch on.

Did Microsoft demo much of the changes? From a very high level yes, and I’m perfectly fine with that because this isn’t SQL Server 2021. I don’t expect the features to be fully baked yet, and I expect the team to still be frantically making decisions about what parts they want to show, and which parts they want to hide under the rug. I’m not saying the code isn’t fully baked – by now, it is – but the decisions about marketing the features are just beginning. Plus, when the features are just unveiled, the team hasn’t had time to build solid marketing demos yet. They’re still focused on building the features and fixing the bugs. (And sometimes, early-announced features don’t even make the final release build – remember Always On Availability Groups for Kubernetes?)

I love, LOVE this question from Brent’s Tasty Beverage – and I’m laughing now as I write this because I’m under the influence of a Tasty Beverage – Famous Gate by Domaine Carneros, one of my favorite wineries – because the question is thought-provoking. Let’s talk through all the thoughts it provokes.

I think Microsoft made good choices,
and they’ll make more sense long term.

When I judge a release, I think about a few criteria:

  • Is Microsoft trying to tackle real problems?
  • Do I think the overall strategy of the solution is good? (Forget small implementation details for now – just are we on the right track?)
  • Do I think a lot of users are actually going to adopt the solution?

For example, if I think back a decade ago when Microsoft first released Always On Availability Groups, I thought they were trying to tackle the right problems, and I thought the solution was good, and I thought a lot of people could actually adopt the solution. On the flip side, when 2008R2’s data-tier applications come out, I told you that there was nothing to see here, move along.

So with those criteria in mind, let’s think about the four SQL Server 2022 features I mentioned.

Failover back/forth from SQL Server 2022 and Azure SQL DB Managed Instances

Is Microsoft trying to tackle a real problem? Absolutely. For decades, people have asked, “How do I restore a database to an earlier version of SQL Server?” And for decades, we simply haven’t had an answer. This posed a real problem when Microsoft started hosting databases in the cloud under the brand name of Azure SQL DB because they kept saying, “Azure SQL DB is the next version of SQL Server.” When the cloud is running vNext, that also means you can’t restore the databases back down to your local machine.

Do I think the overall strategy of the solution is good? Yes, and it wasn’t as easy as it looks. Microsoft had to do a spectacular amount of work to plan out a long-term solution: they had to standardize on a file format that would work for multiple versions in a row, not just today but for the coming decades. My mind boggles at the meetings and consensus that’d be required by this. This isn’t five people coming to an agreement in a Github issue – this is dozens or hundreds or thousands of stakeholders around the world coming to an agreement about a balance between the agility of changing the database file format versus the stability of a future-compatible file format. SQL Server 2022’s announcements by no means make it easier to restore a SQL Server 2022 database down to 2019, nor should it. It’s hard enough just to get the cloud and the on-premises databases to agree on a file format, and I’m completely in awe that they managed to pull this off. It’s hard.

Do I think a lot of users are going to adopt it? Well, here’s where we hit a problem. The solution relies on not just an Availability Group, but a Distributed Availability Group (DAG). The demos look point-and-click easy, but:

  • They skip over a lot of stuff (like provisioning the Azure SQL DB Managed Instance and getting connectivity working between your on-premises SQL Server and the Managed Instance)
  • The demos look like one database per Availability Group and Distributed Availability Group, which is going to be really painful management at scale
  • When things go wrong, troubleshooting a DAG is awful. The DAG documentation page looks impressively long at first until you realize that’s pretty much all there is, and heaven forbid something go wrong with your DAC. These things are troubleshooting nightmares. Throw in a mix of an on-premises AG mixed with the DAG up in Azure, and you’ve got the devil’s cookbook.

But despite these gotchas, I’m relatively happy with the big picture direction. Building v1 of anything is tough. This isn’t v1 of Availability Groups, but it’s v1 of something very ambitious. I like it. It also has the possibility of increasing Azure SQL DB Managed Instance adoption as a new easy default option for disaster recovery. (In reality, it’s gonna be anything but easy if they don’t start putting a hell of a lot more work into manageability though.)

Azure Synapse Link

Is Microsoft trying to tackle a real problem? Yep. The pain: ask any enterprise report writer, and they’ll tell you that they wish they had more real-time access to data stored everywhere. The instant they have to start dealing with extract-transform-load processes, bam, there goes weeks or months of lead time. Add a new column to a source system? Forget it. It’ll take forever to get into enterprise-level reports. Microsoft is recognizing a very serious pain.

Do I like their solution? I don’t play in this playground – moving data around between databases – so I’m not qualified to judge it. However, the documentation for Azure Synapse Link for Azure Cosmos DB includes this gem:

Synapse Link is not recommended if you are looking for traditional data warehouse requirements such as high concurrency, workload management, and persistence of aggregates across multiple data sources.

Well, that doesn’t sound good.

But as a DBA, if someone told me they wanted to enable this feature, I think I’d be okay with it. The alternatives are things like Change Data Capture, Change Tracking, or rolling your own ETL solution, and frankly, all of those are more work than Azure Synapse Link sounds like. (And all of those solutions have performance overhead.) I see warning signs, but not for the database itself, so … okay with me!

Do I think a lot of users are going to adopt it? I have no idea, but given that limitation above, sure seems like it would backfire hard as it grows. But like I said, I don’t play in this playground.

SQL Server Ledger

Is Microsoft trying to tackle a real problem? Yes. I know, blockchain is a meme by now, but there are legitimate business needs for a bulletproof history of a row. Take airlines: when there’s a crash and a particular part is suspect, they need to know the complete history of that particular part, and they need to know that the history hasn’t been fudged by a supplier or a mechanic.

Do I like their solution? Yes. There’s a slight challenge because any solution here is going to involve keeping ledgers of the transactions involved for a long period of time. Think about that for a second. (And while you’re thinking, make a note of what you think about, and keep that log around for forever, ha ha ho ho.) In our industry, we’ve already got a ton of problems with people trying to shrink databases. Imagine if the transaction log never went away – and think about the space requirements you’d be dealing with. This is going to present a space problem for the coming decades. To understand the implications of how it would work, check out Azure SQL Database ledger.

Do I think it’ll get widespread adoption? For the people who need it, yes. For the people who like this kind of thing, this is the kind of thing they like. Due to the size requirements, this is never gonna be a switch that just gets flipped on everywhere, but where you need it, you need it. I also think this is the kind of feature that can actually increase license sales because if it works to the point where it’s legally defensible in court, then it’s an edge that less expensive databases don’t have. It can increase licensing sales.

Parameter-sensitive plan optimization

Is Microsoft trying to tackle a real problem? Yes, I know dang well that this is a huge problem out in the real world because my Fundamentals of Parameter Sniffing and Mastering Parameter Sniffing classes are amongst my biggest sellers. (There’s a reason I didn’t teach my FPS class for free this month.)

Do I like their solution? I think it’s a great down payment. It’s only focusing on equality searches (like @Country = ‘US’ versus @CountryCode = ‘Iceland’), not range searches like @StartDate and @EndDate. It’s also only looking at outliers that are immediately obvious by examining the statistics histogram, which still only contains 201 buckets – so it doesn’t catch problems with not enough buckets, outdated statistics, joins, etc. But you know what? That’s totally okay! It’s a hell of a down payment, and I think it’s gonna help way, way more than it hurts. You can learn more about it by watching Pedro Lopes’ demo-filled session on it and the other query plan improvements.

Do I think it’ll get widespread adoption? Yes, because it kicks in automatically at compat level 160 (2022) and it doesn’t require Query Store to be turned on. It doesn’t need to fix every parameter sniffing problem – even if it just fixes 1 in 10 parameter sniffing problems, that’s forward progress, and I’ll totally take it.

This is the one feature that by itself won’t directly increase licensing or Azure revenue. (Indirectly, it might: it proves that Microsoft can continue to iterate over the engine to make queries faster, and that might make someone more likely to host their new application in Microsoft databases. I wouldn’t count on that to happen quickly, though.) I’m happy with the mix of down payment features and features that will make folks like us happy.

I like the direction of all of these.

And these are only some of the highlights – there are more features, and I’m sure we’ll hear more about ’em over time.

Are they groundbreaking or revolutionary? No, I’d say they’re more evolutionary – they represent logical next steps, even if they’re big next steps, for a big, stable relational database platform.

And that’s totally okay.

SQL Server isn’t supposed to implement dramatically different behavior by default, because default behavior is risky. SQL Server is massively popular, and when you’ve got a database platform this big, you need to make conservative bets rather than betting the farm on something risky. I, for one, am glad that Microsoft continues to make the right conservative bets.


Updated First Responder Kit and Consultant Toolkit for November 2021

First Responder Kit Updates
0

Just like you, the First Responder Kit has been pretty much stable for the last couple of months. There have been a few small changes, but nothing too big. There are a few pending pull requests that folks are working on around an open source setup checklist, better support for Ola Hallengren’s scripts, and a new stored procedure to get cluster information. If you want to influence how the production versions of those shape up, head over to the pending pull requests, look at the related issues, and give your feedback.

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

To get the new version:

Consultant Toolkit Changes

This month’s update does change the spreadsheet. I don’t take that lightly – I’d rather let y’all have very customized versions of the spreadsheet – so the changes are really minimal:

  • In the “Uptime” tab, there are a few automatically generated notes that can show up starting in row 26. They’re designed to be notes that you can copy/paste directly into an email if a prospective client sends you data for a server because, in fact, that’s exactly what I’m doing! For example, one possible message is, “This server was restarted in the last 24 hours. Gather data again when the server has been up for at least one business day because most of the performance and reliability data is erased when the server is restarted.”
  • In the “Databases” tab, there are two new columns for Azure Edition and Azure Service Objective, both taken from sys.database_service_objectives. Just be aware that in Azure SQL DB, cross-database queries aren’t possible with three-part names, so we only get the info on the current database.

sp_Blitz Changes

sp_BlitzCache Changes

  • Fix: when called by sp_BlitzFirst with @OutputType = ‘none’, don’t output a result set. (#2998, thanks Andreas Jordan.)

sp_BlitzIndex Changes

  • Bug: Mode 4 wasn’t scripting out the definitions of unique constraints. (#3006, thanks Erik Darling.)

sp_BlitzWho Changes

  • Fix: new @GetLiveQueryPlan parameter, defaults to 0. On some builds of SQL Server 2019, we’ve seen access violations parsing the XML in live query plans. That’s a bug in SQL Server, and we tried to file an issue for it on Connect, and then we tried to file an issue on Feedback.Azure.com, and then we gave up. If you want the live query plan and you aren’t worried about SQL Server access violations, turn this switch on. (#3011, thanks Greg Dodds.)

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.


Contest: SQL Server 2022 Needs a Slogan.

SQL Server
307 Comments

Now that Microsoft has announced that SQL Server 2022 is coming, it’s time for us to talk about a shameful truth:

SQL Server doesn’t sell itself.

Remember when everybody was talking about how MongoDB was Web Scale™? Or when Oracle was Unbreakable™? Even if you were making fun of it, the point was that you were talking about it, and that’s a buzz that SQL Server just doesn’t have.

I believe that you, dear reader, have the answer. I bet that you can come up with a snappy slogan. (The best I could do was “No one out-SQLs the Server,” and let’s be honest, that’s not really good.)

This week, while you’re learning about SQL Server 2022’s cool stuff in all the free training sessions, think about how we can market this thing and sell the heck out of some licenses.

Leave your best slogan in the comments before Saturday morning. Saturday morning, I’ll close the comments down, pick my favorite entries, and publish ’em in a post on Monday. My 3 favorite entries will win a Level 3 Bundle with a Live Class Season Pass, Recorded Class Season Pass, Consultant Toolkit, and SQL ConstantCare.

I’ve got a full cup of coffee here. Make me spit it on my monitor, people.


There is a Ton of Free SQL Server Training Coming Up.

Conferences and Classes
2 Comments

If you don’t get smarter soon, it’s your own damn fault.

Oh sure, I’m running a Black Friday sale, but even if you’re as broke as SSMS’s live query plans, you can still afford this free stuff I’m doing:

Weds-Thurs: Quest Empower 2021 – free conference, and I’ve got a session with Pinal Dave on Wednesday where we’ll cover tips & tricks for SQL Server performance.

Weds-Fri: PASS Data Community Summit – every one of these in the past has been expensive, and this year thanks to Red Gate, it’s totally free. I’m doing the community keynote on Friday, talking about 5 ways the cloud impacts your career. Microsoft has several SQL Server 2022 sessions scheduled already, too – after you’ve registered, open the session catalog and search for 2022.

Nov 16-17: Fundamentals of Columnstore – your report queries are too slow, and regular indexes aren’t enough. You’re on SQL 2016 or newer, and your data’s up over 250GB. Find out if columnstore indexes can help. Find out in this one-day class – I’m doing it on Nov 16th in Europe-friendly times, 8:00-16:00 UTC, and again Nov 17th in Americas-friendly times, 9AM-5PM Eastern.

Nov 23-24: Fundamentals of TempDB – you want to learn how to troubleshoot temp table plan reuse, the version store, latch contention, and where to put the files. Find out in this one-day class – I’m doing it on Nov 23rd in Europe-friendly times, 8:00-16:00 UTC, and again Nov 24th in Americas-friendly times, 9AM-5PM Eastern.

Dec 9: How to Size Storage for SQL Server – In this one-hour session, I will show you how to measure your existing workloads, predict how multiple databases will interact when they’re consolidated, and use all this to gauge what kind of storage you’ll need in your next server.


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

Videos
0

I sat down on the edge of Brooklyn, overlooking the Manhattan skyline, to take your highest-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

  • 00:00 Introductions
  • 00:42 Dylan: Do you have any experience with sys.dm_db_tuning_recommendations? To be clear, I am not referring to Azure SQL or Automatic Index Tuning, but rather Query Store Forcing/Unforcing plans automatically. Even if not set to automatic, have you seen clients manually utilize this data?
  • 02:27 Borninthematrix: Hi Brent, we frequently get deadlock on thread errors as opposed to on a specific resource. I can use sp-blitzlock to pull out the query plan, but do you have any tips on how we can pin point the specific part of the query plan that encountered the deadlock on thread error?
  • 03:26 Stranger: Is data warehouse design a DBA skill? Or is it a BI expert skill? 04:31 Stuart: I need to delete millions of rows using a criteria with a join, so I can’t use the CTE/view technique. What do I do?
  • 05:49 DrDrakken: Hi Brent! My friend has a query that consists of several subqueries and takes some minutes to execute. There is multiple usage of getdate() in the where clauses. Does this mean that not all where clauses will use the exact same time? And would it be better do use a variable?
  • 06:38 Accidental DBA: Hi Brent, what is the setting we need to enable in SQL 2019 to see the actual query plan in sp_blitzwho?
  • 07:22 DBA Champion: Hi Brent! Is there a way to see, who upvoted or downvoted my posts on dba.stackexchange.com ? Recent backup of dba.stackexchange.com, is it downloadable somewhere ?
  • 09:12 camaro322hp: Hi Brent. My friend is loading MSSQL tables from Oracle tables with over 100 million records. What’s a good way to determine which records have changed since the last pull? The source data does not have a last update date field and any records can change at any time.
  • 10:04 BorisDB: Hey Brent, what’s the best way to migrate just the SQL schema?
  • 10:50 Stuart: Hi Bent! Totally off topic this but myself and some coworkers were having a wager as to what floor your apartment is on, based on the view from your camera during your recent session intermissions. Most popular is 28 – are we close?!
  • 11:07 Ron: Hello Brent. I see you have a DBATools database. What do YOU keep in there?
  • 12:04 That’s all folks: Hi Brent, our system engineers are considering setting up a single big storage bay with tiering enabled to host all of our VMs (including sql servers). What’s your thought on that given we have ~50 vms and ~100 DBs and high concurrency (reads) 1 week per month “only”?
  • 13:19 Accidental DBA: Hi Brent, do you have any thoughts on using SQL CLR to perform cache invalidation using SQL triggers?
  • 14:10 Wrap-up

[Video] Office Hours Speed Round: Quick Answers

Videos
1 Comment

Think fast! Not all of the questions at https://pollgab.com/room/brento have long-winded answers. In this 5-minute session, I crank through a bunch of straightforward questions:

  • 00:00 Introductions
  • 00:32 Mike: Hi Brent, the delete job is blocking for around 20 minutes our update or inserts calls from api which is expected to be completed with few seconds. How can we avoid it?
  • 01:01 Filip Holub: Hi Brant, Do you have any information about release new SQL Server version? Or your opinion?
  • 01:33 Mike: We have an ETL job which loads the data to a temp table table1_x, once the load is completed table_x is renamed to actual prod table table1. As the prod table is connected to multiple jobs it is unable to acquire lock which is needed to rename causing blocking. How do I avoid it?
  • 02:14 Jose: How do you debug Stored Procedures with Azure SQL Databases?
  • 02:38 Gustav Mulder: Hi Brent, have you ever worked with Apache Kafka to move data from a production db to another SQL instance running BI tools?
  • 03:00 JacksonvilleJohn: Hi Brent, what are some good use cases for the new last-writer-wins replication feature in SQL Server 2019? Can we now have tables synch across different geographic locations? Would that be better than an AAG for a DR site?
  • 03:48 Faruk Ami: Hi Brent, is there support for First Responder Kit stored procedures in Azure sql?
  • 04:25 Vengeful Flamingo: Hi Brent! What’s your experience with delayed durability and how would you approach deciding to enable it? It helped address WRITELOGs & ETL perf issues, even though the log is on flash & wasn’t overwhelmed.

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

Who's Hiring
20 Comments

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


What’s New in SQL Server 2022

SQL Server, SQL Server 2022
64 Comments

Today at Microsoft Ignite, Microsoft started dropping new details on the next version of SQL Server, 2022. Here’s a 13-minute video from Bob Ward explaining and demoing some of the new features:

The new features include:

  • Failover back/forth from SQL Server 2022 and Azure SQL DB Managed Instances, including restoring versionless databases from Azure SQL DB Managed Instances down to on-premises SQL Server 2022
  • Azure Synapse Link integration to avoid big ETL jobs between SQL Server and Azure Synapse
  • SQL Server Ledger – blockchain immutable histories of tables
  • Parameter-sensitive plan optimization that caches multiple plans per stored procedure

There’s no release date yet, but you can apply for the early adoption program now.


5 Ways to Measure a Query

Execution Plans
2 Comments

In my free How to Think Like the Engine class, we start out by measuring query performance using logical reads. That’s the number of 8KB data pages that SQL Server has to read in order to find your query’s results. That’s the measure I use the most because generally speaking, the less data your server has to read, the faster your query will finish.

However, sometimes you have to choose between a couple of different queries or plans that produce roughly similar numbers of logical reads. Other times, you need to focus on reducing a different bottleneck on a server. In these cases, here are some other popular ways to measure your query.

1. TempDB spills – some query plan operations require memory in order to run. Ideally, SQL Server grants enough memory for the query’s operations to happen completely in RAM. When that doesn’t happen, operators like sorts will have a yellow bang on them, and when you hover your mouse over the operator, you’ll see a warning about the operator spilling to disk. In cases like this, I may need to tune the query to get SQL Server to allocate more memory, or change the way the operations get done so that they require less memory – like building indexes to remove sorts. To measure TempDB spills, start by looking at the actual (not estimated) execution plans, or run sp_BlitzCache @SortOrder = ‘spills’.

2. Unused memory grants – I just got done talking about what happens when SQL Server doesn’t grant enough memory to running queries, but what happens when it grants too much? A single query can grab 25% of the available memory, and it doesn’t take a whole lot of 25% grants before you’re fresh out of memory. In these cases, I need to look at the properties of the actual (not estimated) execution plan, figure out why SQL Server is granting too much memory, and reduce those requirements. To find the queries involved, run sp_BlitzCache @SortOrder = ‘unused grant’.

Mastering Query Tuning3. Parallelism – when your query has a lot of CPU-intensive work to do, it might go faster if that work is split across multiple CPU cores. However, there are drawbacks with spinning up multiple worker threads for a query, like having the same memory grant split across multiple cores, too. If only one core ends up processing data, you can end up with a parallel query that spills to disk, but a single-threaded query that does all its work in memory. We discuss this in more detail in the Mastering Query Tuning class.

4. CPU time – visible in the Messages tab of SSMS by running SET STATISTICS TIME ON. I discuss this one only briefly in How to Think Like the Engine because it varies so doggone much: run the same query ten times in a row, and you’ll get ten different amounts of CPU time consumed. I focus on this when I’m tuning a server facing high CPU usage, especially when the top wait type is SOS_SCHEDULER_YIELD. When I’m comparing query plans, I’m less worried about 5-10% differences that can happen every time a query runs, and more worried about order-of-magnitude differences.

5. Elapsed time (duration) – similar to CPU time, this one can be all over the map, jumping up and down dramatically each time a query runs. For details about why, see my post 15 Reasons Your Query Was Fast Yesterday, But Slow Today. This one is my absolute last resort because of how wildly unreliable and unrepeatable it is, and like CPU time, I really only focus on order-of-magnitude differences.

To learn more, join me here in a totally free webcast today & tomorrow of my Fundamentals of Query Tuning class:

To follow along with the demos and ask questions during class, read these prerequisites. If you can’t make it live, check out the Black Friday bundle deals on my recorded classes.


Free Webcast Coming Up: How to Size Storage for SQL

Storage
0

Storage sizing isn’t just about space: it’s about latency and throughput.

When you move to new hardware or the cloud, and when you try to consolidate SQL Servers, you can’t just add up the database sizes and call it a day. Storage sizing isn’t just about space: it’s about latency and throughput. So how can we look at an existing SQL Server and translate that into requirements?

In this one-hour session, I will show you how to measure your existing workloads, predict how multiple databases will interact when they’re consolidated, and use all this to gauge what kind of storage you’ll need in your next server.

In this session I will take into consideration:

  • Database consolidation
  • Data warehousing scenarios
  • Backups
  • CHECKDB as well as other throughput intensive workloads

I’m doing this webcast in partnership with Pure Storage, which means we get to give away fun stuff. Be one of the first 100 to register and attend, we will send you a $10 gift card for lunch of your choice! PLUS one lucky attendee will win the grand prize: a Tile Mate Slim 4-pack combo. The winner will be announced at the end of the webinar – must be present to win.

Register here for free.


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

Videos
5 Comments

I’m up bright and early in Malibu, so let’s talk through your highly upvoted questions from https://pollgab.com/room/brento.

  • 00:00 Introduction
  • 01:04 JD: Have you ever encountered a very weird case during query / performance tuning that you just couldn’t get the query across the finish line, specifically just because SQL Server didn’t want to behave nicely? 🙂 What did you end up doing to overcome it?
  • 03:15 Accidental DBA: Hi Brent, do you have any recommended DMV queries you like to run for the scenario when SQL server is on fire and sp_blitzfirst is very slow in returning results?
  • 04:34 Ashok Sharma: Hi Brent, I am moving all my databases from on-premises to Azure VM (not managed instance). What is the best method to migrate SSISDB and reports DB to Azure? Which backup / restore work? I am on SQL 2016 SE
  • 05:55 Zatrikion: Hi Brent, Is parameter sniffing the same on SQL in the cloud (azure) and on an physical SQL server? Or do they “work” differently?
  • 07:08 Dmitriy: When migrating from stand-alone SQL Server to Azure SQL DB, what’s the most efficient solution to replace cross-database queries?
  • 08:10 Accidental DBA: Hi Brent, do you have any guidelines for determining how much RAM the SQL Server should have? DB is 7 terabytes.
  • 09:39 Null Pointer Exception: Thanks for everything you do for the community! Random question… any ideas/theory on why SQL Server doesn’t support temp tables in views? Would make it so much easier to give the optimize views for performance.
  • 10:53 Jiri: Working with for smaller companies I mostly see apps where SQL Server is in fact an application server. Means all business logic is in the database objects and the app is just a GUI. What do you mean about such architecture and what approach it requires concerning SQL Server?
  • 13:10 Kapil Swamy: What is the best way to identify potential botllneck in performing troubleshooting
  • 14:13 Little Bobby Tables: Yo Brent, issue I have with log shipping. I rebuild indexes on my largest tables monthly. (Standard Edition) This creates very large .trn backups ~8GB. Backups every 10min. T-log restore fails on these 30% of the time; requiring Full backup restore. Ever seen this issue?
  • 15:57 Pat: Hi Brent, Accidental DBA here. The main alert I get in my monitoring solution is Baseline OS paging. Range today, for ex, is 2 to 12,671. Also disk queue length at times, today 1 to 6. Googling to see if they’re important yields “it depends” . How can I tell if these are “bad”?
  • 18:18 Mike: Favorite subject “Transactional Replication” What entry or setting points the publisher to the distributor in the master database?
  • 18:53 George: Hi Brent, I need to implement a logging process for one of our accounting solutions and the processes in it. Can you suggest a good lightweight tool or methodology that can handle this and scale well ?
  • 19:27 Magnus: Hi Brent, I work at an MSP as an infrastructure DBA. We don’t do any performance tuning, and almost no t-sql code writing. I’ve only worked as a DBA in this environment(4 years). When is it time to leave, and when will I regret not leaving? Also,I Just signed up for your courses!
  • 24:08 Wrap-up

[Video] Office Hours at Silver Strand State Beach: Ask Me Anything About SQL Server

Videos
0

It’s a beach day! Let’s hang out at Silver Strand State Beach and I’ll take your highly voted questions from https://pollgab.com/rooms/brento.

Here’s what we covered:

  • 00:00 Introductions
  • 00:45 Dylan: Why does an UPDATE on a 2nd key column in an index (100% FF) cause a “bad” page split; the first column is 100% unique. I noticed the tran log was doing a MODIFY operation vs INSERT/DELETE when the second column was moved to the INCLUDEs. Would you say this is expected behavior?
  • 02:05 Mehdi: Hi Brent! Why having non-aligned indexes on partitioned tables can cause inefficient query plans and CPU pressure?
  • 03:47 Bill: We have an application about to come online and I want to baseline the sql server. What is the best way to create a baseline? Do you have any recommended tools or articles?
  • 05:18 Bob: Hi Brent , What is your DBMaintenance recommnendation for VLDB ?
  • 06:03 PostNo: Is it hard to adjust being back in the states? If so, what’s been the hardest part?
  • 07:59 Woodard: I keep getting questioned on why I only let Veeam do copy_only backups and insist on native transaction log backups outside of Veeam. I don’t trust any system where I can’t touch the actual log backup files at will, and we use those backups every day. Is this uncommon?
  • 10:12 Oleg: Hi Brent, for brand new database that is in development, would you recommend to create indexes ahead of time based by guessing or applying DEATH method after it was in production for a while?
  • 11:50 sudo DBA: Hello Brent, You made an interesting point about writing a blog about what you know about SQL Server without worrying whether similar content is available online or not. What blog sites do you recommend to post my SQL Server related issues I have faced and resolved? Thank you.
  • 14:05 Greg: We have a 2TB prod db with 7 years data. We wish to reduce this to a few months but still need to hold 6 years data to meet compliance. Would you build an archive strategy on prod or trust the upstream data warehouse will have your back.
  • 16:17 Recap and demoing Helmut’s robotic top

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

Videos
2 Comments

It’s time for the first live Office Hours in my new home studio in San Diego! I took your highest-upvoted questions from https://pollgab.com/room/brento/ and answered ’em:

Here’s what we covered:

  • 00:00 Welcome
  • 01:30 Accidental DBA: Hi Brent, do you have a recommended checklist for trubleshooting occasional slow INSERT performance?
  • 03:19 Mark: How do companies deal with ever growing databases and writing to very large tables? Have you heard of ways to splitting your data, allowing smaller ‘active’ data on fast storage, while older data reside on cheaper storage?
  • 05:27 Kristófer Ólaffson: My TempB files fill the drive they are on about once a month. Currently sitting at 700GB combined. I have around 3TB of data on this server. I’ve been increasing the size of the drive TempDB sits on, but are there some tips in writing sql to not fill the TempDB as much?
  • 07:28 Rick: Hi Brent, I have a table in a client database with ~1million rows and a statistic that hasn’t been updated since 2018 with a modification counter of over 30.5 million. Auto-Stats update is enabled. Why would this statistic be getting updated?
  • 09:15 Dinis: Hi Brent, if I want to have maximum security in encryption, I need to have a combination of TDE, Encrypted Connections and Always Encrypted (for some sensitive columns). Is that correct?
  • 11:00 John James: My team wants to start collecting index data and use your DEATH method to send out suggestions to teams. Our company restarts all servers weekly. Does it make sense to collect the data before the servers restarts and aggregate it?
  • 12:33 Mahesh: Which is the best storage configuration, HDD (inserted locally into server) create one large virtual disk and make partition like data, log, back or create a separate virtual disk for data and like wise of log and back. What do you recommend. In my case we are using RAID 5.
  • 14:30 Tim Lara: Hi Brent, if I have a bunch of “identical” servers, but some of them perform very poorly on certain queries, while others run the same queries just fine, how can I tell what’s different between the “good” and the “bad” servers?
  • 16:08 Frank: Hi Brent, I have two high availability groups in a 2-node-sql-cluster. In case of a failover sometimes only one HA-group moved to the other node. What do I have to do so that BOTH groups always fail over together to the same node?
  • 17:46 Tom: What are the common roles of responsibility you have seen between programmers and DBAs? I am an accidental and only DBA in a dev shop of 24 and our roles have not been defined so I find it difficult to balance between what I think I should do and what I really should do.
  • 20:34 WillemHenk: Is switching from PostgreSql to SQL Server a big step? I find some articles regarding differences, but I was curious about your experience.
  • 23:50 Mr. Griffith: I understand you frown on in-place upgrades to SQL Server. Something about “juggling crown jewels”. Does the same rule apply to in-place upgrades for Windows Server?
  • 25:21 Jaden: My company using the SQL Server and they want to save the database into JSON. Why they do it and what changes to me?
  • 26:35 DBA Girl: Hi Brent! Our Company creates indexes for all foreign keys on a table. For some tables, though, this can create 20 or more indexes. How do you balance the recommendation of indexing those columns with the performance degradation of having too many indexes on a table?
  • 29:19 Bandar Almutairi: I came across a weird situation where queries against one table is way slower when ran from the readable secondary. It’s fixed after rebuilding the primary index on primary. My question is why isn’t it as slow on primary? Execution plans looked identical.
  • 32:42 Mark: We have some huge table. After huge inserts into the table the query plan takes index scan rather than seek(before it took). updatestat might fix that table but breaks other query. I can’t change query because a tool handles that. Do you have any idea why that happens. Thanks!
  • 34:26 LittleBobbyTable5: Hi Brent, When optimising queries using HINTS and OPTIONS I am hitting Bitmap Create part that are really slowwww. What voodoo optimisation technique should I employ to avoid this slow Bitmap creation. Could you show us a quick example please.
  • 36:40 Farshid: Hi Brent, Have you ever had a customer which you tried to convince them to switch from Oracle to SQL Server?
  • 39:00 Mikkel (Denmark): Some times the QueryText in the BlitzCache result is truncated. And it is not the full text in the QueryPlan. Is it possible to get the server to save the full query text?
  • 40:00 Jay: Is the CDC (change data capture) a viable strategy to publish “event” data to external systems instead of publishing from application code?
  • 41:17 Oleg: if you have to choose to tune query, one where SQL overestimates and another underestimates number of rows, which one would you prefer to tune and which to leave as is?

SQL ConstantCare® is Now Available in the UK & EU.

SQL Server
2 Comments

Earlier this year, we opened up training class sales to the EU and UK. Now, we’re taking it to the next level: my monitoring service SQL ConstantCare® is available in the EU and UK, too!

SQL ConstantCare

With SQL ConstantCare®, you install an app on your desktop that checks in with your SQL Servers once a day, sends us diagnostic data, and then you get one email a day with the most important tasks to make your SQL Server faster and more reliable. Just the important stuff – none of the fluff that causes you to set up an email rule to put everything from the monitoring tool into a folder.

It’s cost-effective, too: for just one price, you can monitor all of your SQL Servers.

But here’s the odd part: I don’t want you to buy it right now.

Because the whole reason we put this work in was for our upcoming Black Friday Sale. In the past, it hasn’t been open to the EU & UK due to the GDPR and VAT paperwork hassles. This year, it’s gonna be wide open to everyone. Next Tuesday, I’ll announce the bundle lineups, and my European friends will finally join in on the fun.

In the meantime, if you’re curious about how it works, check out the frequently asked questions and read about how we collect, transmit, and store your data. Because this is a cloud-based service and the servers are in the United States, I know it’s not going to be a good fit for everyone. I only want you to buy it if it’s a good fit for you and your company.

And not until November 1. See you then!


Congratulations to the Scholarship Class of 2021.

Company News
11 Comments

To celebrate the holiday season, we give back to those who spend their entire year giving back in their own communities. We go looking for data professionals who make a difference, and we try to make a difference in their lives too.

Pocket Square
The scholarship program makes the heart part happy.

This year, we’re proud to announce a new round of over 40 winners. We can’t mention ’em all, but here are just a handful of groups that we’re proud to help:

ACTED – saving lives and supporting people in meeting their needs in hard to reach areas, the most vulnerable amongst populations that have suffered from conflict, natural disaster, or socio-economic hardship.

Africa Health Research Institute – working towards optimal health and well-being of under-resourced populations.

Astor Services for Children & Families – providing children’s mental health, child welfare, and early childhood development services in New York’s Mid-Hudson Valley and the Bronx.

Charities Aid Foundation – a charity, bank, and champion for better giving, helping donors, companies, and charities make a bigger impact.

Children’s Miracle Network Hospitals – to provide the best care for kids, children’s hospitals rely on donations and community support, as Medicaid and insurance programs do not fully cover the cost of care.

CODAC – provides specialty care for mental illness, addiction, and trauma.

Comic Relief – working towards a just world, free from poverty.

DRF Air Rescue – one of the leading air rescue organizations in Europe.

El Rio Health – providing health care for all people in Tucson, including the underserved patient population.

Hope & Home – trains and actively supports Colorado Springs foster families.

Irish Cancer Society – transforming the experiences and outcomes of people affected by cancer through advocacy, support services, and research.

Magic City Acceptance Center – dedicated to providing a brave and inclusive space for the LGBTQ community in Alabama.

Mencap – valuing and supporting people with a learning disability, and their families and carers.

Pierce College – established in 1865 to provide career-focused education for soldiers returning from the Civil War and was one of the country’s first schools to embrace women as students.

Samaritan’s Purse – international relief and development organization that works through local churches amongst communities in need in countries across Sub-Saharan Africa, Eastern Europe, and Central Asia.

St John Ambulance – volunteer-led health and first aid charity responding to emergencies, supporting communities, and saving lives.

TNTP – working to end the injustice of educational inequality by providing excellent teachers to the students who need them the most and by advancing policies and practices that ensure effective teaching in every classroom.

WWF UK – putting nature first, making our food system sustainable, tackling climate crisis, and ensuring thriving habitats and species.

This season, take a few moments to think about how lucky you are, and how you can reach out to those around you to help them move forward, too.

Happy holidays.


Free #SQLPASS Keynote: 5 Ways the Cloud Impacts Your Career

#SQLPass
5 Comments

Succeeding as a data professional is all about placing the right bets at the right times. The more you know about what’s happening around you, the better bets you can place.

I’ve been working with databases for over two decades. In this session, I’ll invite you into my home to have a frank, honest discussion about where our industry has been recently, and where it’s heading next. We’ll look at how that will impact the available jobs in our industry, and how it impacts where you focus your spare training time. We’ll even look at job ads and resumes in an effort to help you stand out.

In one hour, I can’t make you a pro at everything – but I can help you understand where you need to be a pro, and how to make your resume reflect that.

So where are we going to get together? At the keynote for the PASS Community Summit. It’s a totally free event on November 8-12, and that’s a bargain, because in the past you’ve had to pay thousands of dollars to attend. Register now for free, and I’ll see you on Friday, November 12th.

Now, uh, that we’ve got the professional part out of the way, wanna see the outtakes from when I tried to film that promo? Of course you do:

Those were shot during the day, of course – I ended up having to go back and record another version at night because my daytime versions were such a hot mess, hahaha.

Update 2021/11/17: the full keynote recording is now available on YouTube:


Behind-the-Scenes Home Studio Tour

Videos
13 Comments

Whenever I move to a new place and set up a new home studio, I give you a walkthrough so you can get a rough idea of how I work. I just finished setting up my new San Diego home base, and here’s a tour of what it looks like both as an attendee, and behind the scenes:

You can see more details about my hardware, software, and config in my past Home Office posts, but here’s a quick overview:

Wanna see how it looks? Join me for free classes next week!


I’m Not Ready for Maskless Conferences Serving Finger Food.

At a conference in Belgium this week, Steve Jones posted a video:

My first reaction was, “That’s an awful lot of people in really close proximity, talking loudly at each other, and none of them are wearing masks.”

And then I saw the finger food. No sneeze guards. Just get your dirty fingers in there and serve yourself.

I feel sick to my stomach just watching that. I’m not ready for that.

Let’s set aside any discussion of this specific conference. The organizers chose to do what they chose to do, and I’m not here to say whether that’s right or wrong. I don’t want to turn this into any blame about the organizers or the attendees. I only want to have a discussion about my own personal readiness for conferences right now, and the kinds of precautions I’d want organizers to take before I’d attend.

A mask is like a seat belt.

When you get behind the wheel of a car, you put your seat belt on. Years ago, seat belts were controversial, but today, we just accept it as the right thing to do for ourselves and our loved ones, and we buckle up. It’s a quick, easy, harmless measure for most people to do, and it’s a litmus test for whether someone should really be behind the wheel of a car.

Similarly, because wearing a mask indoors is so easy and harmless for most people, I get nervous when people won’t do it. I start to question their judgment about other less easy-to-see things, like whether they washed their hands, avoided shaking hands with people, and whether they’d stay home if they had COVID symptoms. I hate to say this, but I even question whether or not they’re telling the truth when they say they’re vaccinated or already had COVID.

Now, don’t get offended: I’m not talking about you, dear reader.

You and I have years of history and friendship. When it comes to masks, I know that you know that I have asthma, and my health is already at risk. I’m doing the best I can to protect myself, of course – I wear a mask, wash my hands, and I’m vaccinated twice over with both Pfizer and Moderna. However, that still isn’t a bulletproof defense: vaccines aren’t 100% effective, and there are plenty of examples of breakthrough cases. I know that you and I have a good understanding that if I saw you, and you weren’t wearing a mask, and you told me you had a medical reason not to wear a mask, I’d probably give you a pass because I’d just be so excited to see you. It’s been so long, hasn’t it? Come on over here and give me a hug, you big lug.

A stranger who won’t wear a seat belt
makes me nervous.

I’m not talking about you – I’m talking about strangers and the people that I don’t know as well. You know the ones I’m talking about – the ones who make incredibly bad decisions with their databases every single day. They only run CHECKDB once a week, but they’re deleting log backups older than 24 hours. They don’t even understand the process of how to recover from corruption, let alone rehearse it on a regular basis. They’re running around without scissors every day.

It’s bad enough that they take database risks, but if a stranger takes a risk with my health, I start to get really nervous. I don’t want to go near them, but people will default to getting close to each other. Event organizers probably aren’t going to say, “You need to distance by default, and only go closer to people when they have verbally invited you to get within a meter of them.” They’re going to assume consent – which is an awfully odd default in the day and age when we’re trying to get people to understand that consent is not the default answer: you have to ask for consent first.

And of course, those people are the ones breathing on the finger food. Coughing on the finger food. Plucking one of the finger foods with their unwashed hands, and moving the food around.

A room full of them?
Forget it. I’m out.

When I see a roomful of strangers meeting each other for the first time, and they’re taking good precautions like wearing masks and socially distancing, I get excited. I’m much more confident that they’re making good decisions, and that they’ve also washed their hands, avoided handshakes, and got plenty of antibodies running through their veins. I know they’re not only doing their part for themselves, but for others as well.

But on the other hand, when I see that video at the start of the post – a roomful of strangers indoors, networking closely with each other, eating finger food out on public display with no sneeze guard, not wearing masks….

As much as it pains me to say it, I can’t go into that room. I would do a U-turn and leave immediately.

Because they’re strangers, I can’t trust their judgment. They might have taken every precaution – but I’m just not comfortable making that assumption.

If you’re okay with it,
that’s fine. I respect you.

You, dear reader, are well-qualified to make your own judgments about your health, my health, and the health of those around you. You’ve done your own research, and I respect that you’re making well-informed decisions based on science, not feelings. You’re not just going to a conference maskless because you’re desperate to recreate 2019 – you’re going to a conference maskless because you believe it’s the right thing for you, your family, and those around you.

Again, I’m not talking about you.

I’m talking about the roomful of maskless strangers.

They’re the reason why I’m not ready to go to in-person conferences yet, even though I’m vaccinated, and even though I’m wearing a mask.

How about you? Are you ready to go back to a conference that doesn’t require masks, doesn’t default to social distancing, and serves food the same way we did before the pandemic?


This is the Last Year You Can Buy a Live Class Season Pass.

Company News
8 Comments

When you buy my Live Class Season Pass, you can attend all of my live online training classes for a year straight, plus watch the recordings.

It’s an excellent deal: go to your manager just once, get approval just once, and then spend the entire year learning how to master SQL Server. You don’t have to shoehorn specific dates into your calendar – just drop in whenever your schedule permits. When an emergency pops up at work, no worries – you can catch up with the recordings after hours, or watch ’em later.

In order to make it work for your schedule, I teach all of my Mastering classes at least 6 times a year: 3 times in Americas-friendly time zones, and 3 times in Europe-friendly time zones. I basically spend about 1/4 of my year teaching.

Now, I love teaching, and I have a ton of fun with it. You’ll see the joy and excitement in my eyes if you drop into my free classes happening right now, and depending on when you’re reading this, there might even be one streaming right now. I know y’all have fun with it, too, because students return again and again to sharpen their skills throughout the year. I love seeing their techniques improve on the labs over time.

But…I’ve been teaching a heck of a lot.

And I’m gonna be honest with you, dear reader: I like not working.

Well, by that I mean I like not having any specific day/time events on my calendar. I don’t mind working – I actually love what I do – but I like the flexibility of being able to run off wherever I want, whenever I want, and go have an adventure. It’s hard to do that when I have so much training time blocked out on my calendar, so far in advance. Starting a year from now, in November 2022, I’m going to mostly focus on recorded classes rather than live ones. I’m going to continue to produce new material, but I’m going to teach the live versions much, much more rarely.

So if you’ve always wanted to get in on my live classes, the clock starts November 1, and ends December 1: