Blog

  1. Home
  2. Blog
  3. Page 8

Meme Week: SQL Server 2012 as of 2022

This week, I’m writing blog posts that can be summed up in one image. Here’s the first one:

You could probably stop reading right there, but if you’re not convinced, allow me to elaborate.

SQL Server 2012 support ends July 12.

No, there’s not a year in that sentence, because it’s this year.

No, there’s no qualification about “mainstream support” because that’s the extended support end date.

Sure, there are extended security updates for a few more years, but those are only security bugs. Within 141 days, your SQL Server 2012 boxes are going to be unsupported, period, full stop, end of sentence. If you call Microsoft for support, they’re going to give you best effort, but at the end of the day, you shouldn’t be surprised if they tell you that the “fix” is to upgrade your SQL Server.

And you know what? I think that’s completely fair. SQL Server 2012 is a decade old at this point.

It wasn’t a bad version!

Okay, maybe it's deniable.
Undeniably attractive. Okay, maybe a little deniable.

I don’t want you to think I was disappointed in 2012. To the contrary, I think it was a groundbreaking release, especially looking back at 2008 and 2008R2. My 2008 R2 review noted that it was really just more of the same from 2008, whereas 2012 was really different.

In 2012, I was incredibly excited when Always On AvailabilityGroups (did I capitalize and space that right?) revolutionized high availability and disaster recovery. However, the SQL Server 2012 execution was outright terrible.

2012’s columnstore indexes? No way, Jose: they made the table read-only.

But this stuff represented great down payments on technology that Microsoft has continued to invest in. Today, in 2022, columnstore indexes and AGs are solid features that…

(listens to earpiece)

One moment please…

(continues listening to earpiece)

I’m being informed that Always On Availability Groups are still painful, but at least they’re widespread. (Did I phrase that right?)

Look, stop distracting me. The point is SQL Server 2012 is bad in 2022. If you’re still running 2012 in production today, it’s time to start having the discussion with the business. Here’s how I’d start: “Is it okay if application ___ is running on an unsupported version of SQL Server?”


What Trace Flags Are You Using, and Why?

Most of the time, when you need to change SQL Server’s behavior – like how it handles memory or parallelism – there are easy-to-use switches to get the job done. In SQL Server Management Studio, just right-click on the server’s name, go into Properties, and there are all kinds of things you can mess with. Most of the time, you’re going to make things worse, but whatever.

Every now and then, when you work with Microsoft support, you might hit a specialized situation where you need a very unusual change in SQL Server’s behavior. These settings aren’t well-documented, nor are they easy to enable via the GUI. These settings are typically implemented via trace flags.

Trace flags are edge cases. At least, that’s the idea. If you read through Konstantin Taranov’s excellent open source trace flag documentation, you’ll see why.

Back in the 2000s, a few settings turned out to be fairly important, like how TempDB handles page allocations. For a while back then, folks would recommend that everybody implement trace flags like 1117 and 1118. Microsoft took note of that, and eventually changed SQL Server’s normal behavior so that those trace flags weren’t necessary for TempDB anymore.

Konstantin’s documentation has a list of recommended trace flags, but I’m curious: if you’re running a trace flag that is NOT in Konstantin’s list of defaults, which one is it, and what prompted you to turn it on?


[Video] Office Hours: Q&A on the Beach at Sunset

Videos
0

You posted and voted on questions at https://pollgab.com/room/brento and I answer ’em on the beach. It’s not your eyes: the camera’s hunting for focus a lot in this video.

Here’s what we covered:

  • 00:00 Introductions
  • 01:01 RoJo: Please comment on the Cloud. Too much push at my company to the Cloud without backing to do so. Lots of issues, costs, delays to get there. Supposed savings (less maintenance) seem non-existent. Less performant. We already own SQL server so ‘renting’ a new location seems like waste
  • 02:58 Latka Gravas: Hi Brent, my coworker has sp_whoisactive logging to a table every 30 seconds. Is this is an acceptable amount of observer overhead?
  • 04:57 dan heile: for the answer question sessions – we have an archive database that is growing in size. we were going to look at row or page level compression but the sys engineers said that with our pure or netapp flash storage that all compression is done at the san level. thought?
  • 07:27 Dan Griswold: Hi Brent, do you know of any gotchas when upgrading to a new SQL Server server version and then running the old database in compatibility mode. I’m considering using this method to get off my older servers and then worry about upgrading the compatibility mode later. Thanks!
  • 08:42 Desi: I am seeing too many “sleeping” connections in SQL from application. Can this be a cause of concern? SQL version is 2017 Enterprise edition
  • 09:24 Gustav: Thank you for the highly informative Office Hours sessions. The sunrises are an additional bonus. Will there be a “Watch Brent Code” stream at some point considering your schedule? Those too are great to watch. How you “tackle” a challenge that needs to be solved.
  • 10:28 Hatsune: Does parameterizing TOP ( SELECT TOP (@MaxRows) ) contribute to potential query performance problems?
  • 11:25 Frank Poncherello: Hi Brent, Is Resource Governor ever a good traffic cop for a busy multi-tenant OLTP system with multi-terabyte DB?
  • 13:22 Career pro tip
  • 13:43 Ahmet: Do you know of a good page that shows the new features by SQL Server version?
  • 14:30 Nedim: We sometimes see high disk IO utilization for one of our 15 SAN drives but not sure which queries are driving the IO. Is there a good way to determine which queries are targeting tables for a given SAN drive for a particular point in time?
  • 15:35 Mike: Hi Brent, what are your thoughts regarding onprem SQL Server backup to azure storage functionality? Any issues with it?
  • 17:20 Don’tBotherAsking: Hi Brent, love your work. A friend improved a slow running vendor query for the business with a covering index. Query now completes in fraction of the time. But query optimizer now says reason for early termination is ‘time out’ instead of ‘good enough’. Is this fix sub-optimal?
  • 18:20 Sunset

Fundamentals Week: Your First Official SQL Server Class.

Company News
1 Comment

You love learning from me. You’ve attended some of my free online streams, read my blog posts, and you use the First Responder Kit. You enjoy my laid-back, humorous approach to sharing what I’ve learned over the years.

You love live classes. You’ve tried watching recordings or stepping through demos yourself, but…you just can’t block out the time, and you can’t stay motivated while you’re watching a recording. You like the fast-paced energy of seeing me live, asking me questions, and seeing me respond to your chats.

You hate conferences that feel like Zoom meetings. You’ve tried attending a few online conferences, but they felt just like sitting through one meeting after another. They didn’t dive deeply into any one subject – they just hopped around from one topic to another, and many of those topics just weren’t relevant to you. They were good for free stuff, but…

You’re ready for Fundamentals Week. In March, you’ll spend a week with me learning about indexes, query tuning, columnstore, how I use the First Responder Kit, and more. I’m teaching it two weeks in a row – one week in US-friendly times 9AM-5PM Eastern, and one week in Europe-friendly times 8:00-16:00 UTC.

To keep prices low, recordings are not included – this event is really just for folks who like to learn live. If you want the recordings, check out my Recorded Class Season Pass.

The class lineup and registration is open now. See you there!


Who’s Hiring in the Database Community? February 2022 Edition

Who's Hiring
31 Comments

The pandemic is causing so many folks to rethink what they do for a living, where they do it, who they do it for, and in light of yesterday’s salary survey results, how much money they earn doing it. It’s been really fulfilling to hear stories of employers who found candidates here, and candidates who found a new opportunity. So let’s keep ‘er going for a while longer.

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

Each month, I publish a new post in the Who’s Hiring category here so y’all can get the latest opportunities.

If you want to be notified as people leave new jobs here, there’s a “subscribe without commenting” option where you leave a comment. I don’t use that for subscribing you to anything else.


[Video] Office Hours at Sunrise (No Really)

Videos
0

I’ve got a really sensitive lens (Sigma 16mm f/1.4) on my camera, so often when I record these at sunrise, it’s already too bright for you to see the sun. This time, I went down to the beach a little earlier, and through the video, you can see the light really pick up.

Here’s what we covered:

  • 00:00 Introductions
  • 00:31 Radek: Hi Brent, do you know a person who, is doing exactly what you do, but for production DBAs, not performance tuning folks? I cannot find up-to-date sessions/courses dedicated to DBAs that cover that part of the job
  • 02:14 Arnold: What are the top causes of data corruption in SQL Server?
  • 03:28 marcus the german: Hi Brent, a friend of mine 😉 wants to know if it’s possible to tell which page is on which datafile (db with multiple datafiles)?
  • 04:38 Hamid: What are the pros / cons of putting responder kit / sp_whoisactive in master database as opposed to some kind of dedicated DBA database?
  • 06:04 Hilal: What are the risks of killing the SPID associated with a long running transaction? Is terminating the offending app any better?
  • 07:42 Meiko: Hi Brent, I think sp_blitzFirst is reporting “Forwarded Fetches/Sec High” for temp tables created by sprocs / jobs on our system. How do we find the offending jobs / sp’s amongst our many jobs / sp’s?
  • 09:16 Mark: Hi Brent, what are the pros / cons of running two SQL instances on the same bare metal hardware?
  • 10:09 Preben: Hi Brent, have you ever used delayed durability to significantly reduce writelog waits? Are there any undocumented issues with it?
  • 11:29 I don’t wanna DBA Today: I want to setup a read-only SQL Server to run reports off of since we are currently crushing our production server. We have 2016 Standard and were looking at the best option, be it Basic Always On versus Replication. Only a subset of the production DBs will be used. Thanks!
  • 12:17 Wrap-up, fishing in Cabo

If you’d like to ask your own question, post it at https://pollgab.com/room/brento/ and upvote the ones you’d like to see me cover at my next Office Hours.


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

Videos
0

You ask questions at https://pollgab.com/room/brento and I answer ’em from the beach in Cabo San Lucas:

Here’s what we covered:

  • 00:00 Introductions
  • 00:27 Paul: What are your thoughts on being a Microsoft MVP?
  • 04:33 Lance: I have a Server running SCCM that gets ‘unsafe assembly’ errors. Are unsafe assemblies something that needs to be worried about?
  • 04:52 Arslan: Hi Brent, what are the top issues you see related to customers running SQL CLR?
  • 06:02 Anil: Can bare metal SQL Server safely utilize NAS storage (slower / cheaper) for data files or is it recommended to only use SAN storage (faster / more expensive)?
  • 07:13 Hasan: Hi Brent, what are the origins of SQL Bits? Do you have any epic stories from SQL Bits?
  • 10:02 Jim: How does PostgreSQL query tuning compare with SQL Server query tuning?
  • 10:35 Samir: Do we ever have to worry about disk (not page) fragmentation with SQL Server?
  • 11:48 Wrap-up

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

Videos
4 Comments

I watched the sun rise from my balcony in Cabo this morning and answer your questions from https://pollgab.com/room/brento.

Here’s what we covered:

  • 00:00 Introductions
  • 00:42 Student: Hi Brent! Why is full backup sometimes slow and sometimes fast in similar situations? This problem will be resolved after restarting the server but it comes back again.
  • 02:58 Frank Peters Netherlands: What is your opinion of the Polybase feature in SQL 2019? I read it could possibly replace ETL and perform better than Linked Servers due to being able to scale out. Or are there still some major (performance) drawbacks?
  • 04:50 Scooter: Hi Brent, not a question… Just wanted to say how much we appreciate your training, office hours, and tools. Thanks for all you do for the SQL Server community.
  • 05:30 Paul: Hi Brent, what are the common performance issues you run into with queries generated by ORM’s such as Entity Framework? How do you deal with them?
  • 06:46 Mattia Nocerino: DB is 130 GB, biggest table (log) 56 GB. Inserting 1 row at a time, no updates/deletes. Columnscore gave me a D. SELECTs are mostly executed on recent data. Thinking about keeping last year worth of data and move the rest to another DB, to improve RTO. What is your recommendation
  • 08:32 Saverio: Nowadays it is common for (my friend’s) servers to have almost exclusively SSDs instead of HDDs. What are your thoughs about maintenance (Statistics Update and Index Rebuild) from this point of view? It’s still a big concern or something less important?
  • 09:41 Myron: Which is better in your opinion Azure, VM or physical hardware for a new SQL Server install?
  • 10:55 Youssef: What are your recommendations for keeping SQL agent jobs in sync between primary and secondary always on servers?
  • 12:07 Mr. Griffith: Have you ever been hired as an expert witness in a lawsuit? We’ve got a vendor telling us NOLOCK is the solution to the blocking problems for our mission-critical application..
  • 12:38 Byron: What are the top mistakes you see from clients with respect to sql backups and integrity checks?
  • 14:58 Erdem: Hi Brent, is it ok to use ‘DBCC DROPCLEANBUFFERS’ to simulate running a cold – slow query on production?
  • 16:00 Maciej: Hi Brent, my friend recently learned (to his own surprise) that Microsoft fixed (in SSMS 18.9) the bug with IntelliSense error message while connecting to SQL Server using DAC. Do you have a bug in SSMS and/or SQL Server that you treat (at this point) as an old friend? 😉
  • 18:26 Old DBA: I had a coworker tell me that stored procedures are no longer the way people are doing sql and that we need to stop. I feel he is absolutely wrong, what are your thoughts?
  • 20:31 Maksim Bondarenko: Hi Brent! My friend says that when we use RCSI we shouldn’t use FILLFACTOR=100 because 14-byte version pointers added to the modified rows. What do you think about that?
  • 22:07 Theo: How often do you recommend taking inventory of all company SQL servers and their configuration?
  • 23:25 Fred: To what extent should the developers be involved in creation of new NC indexes on prod? Let them run sp_blitzindex?
  • 25:33 Wrap-up

When Should You Use DESC in Indexes?

Indexing
8 Comments

The short answer is that if your query orders columns by a mix of ascending and descending order, back to back, then the index usually needs to match that same alternating order.

Now, for the long answer. When you create indexes, you can either create them in ascending order – which is the default:

Or descending order:

If your query orders the data in descending order, but your index is in ascending order, like this:

SQL Server can still use the index. I’ll demonstrate using the Stack Overflow database:

SQL Server can scan the index backwards, from the highest reputation to the lowest reputation value. You can see proof by right-clicking on the Index Scan and clicking Properties:

There’s a wee bit of a gotcha: backwards range scans can’t go parallel, as Paul White mentions in this post, but that’s rarely a dealbreaker.

In simple scenarios like this, you don’t need to screw around with DESCending order on your indexes.

Things change when you order by multiple columns in alternating orders.

Let’s say you want to build a leaderboard of the top Stack Overflow users. You want them in Reputation order descending, highest reputation first, and you want a tiebreaker column. If two people have the same Reputation score, you want to sort them by DisplayName. You’ve created an index to help:

Now things are a little different, as you can see in the actual execution plan:

SQL Server flat out ignored the index, read all 9 million rows in the table, and sorted all of them. The stats IO & time is horrifying, too:

For those of you who are horrified by this plan and don’t believe me, you’re welcome to reproduce it at home. I’m using a large Stack Overflow database, SQL Server 2019, 2019 compat mode.

SQL Server *COULD* use the index for this, mind you, as we can see with a hint, but the actual execution plan becomes even more terrifyingly insane:

And the statistics are bonkers:

You and I would design a better plan.

The execution plan you and I *WISH* we would get out of SQL Server is:

  1. Scan the index from highest reputation to lowest, reading the first 100 rows and then immediately stopping. All 100 of the rows will match – remember, our query doesn’t even have a WHERE clause. Just read the first 100 rows, and call it a day.
  2. Sort those 100 rows by our tiebreaker column, DisplayName.
  3. Do 100 key lookups to satisfy the SELECT *.

However, it’s possible that the top 200 rows (not 100) all have exactly the same Reputation score. To handle that edge case scenario, we’d have to modify our execution plan a little:

  1. Scan the index from highest reputation to lowest, reading the first 100 rows
  2. Check the 101st row, and see if its Reputation matches the 100th row. If it does, go read another row from the index. Repeat that process until there are no more ties.
  3. Sort the 100-110 (or 150 or whatever) rows by our tiebreaker column, DisplayName.
  4. Do 100 key lookups to satisfy the SELECT *.

That would work, but…SQL Server just doesn’t build queries that way. The operator that sorts the data is a different operator than the one that’s reading the data:

The sort happens long after the data is read. And there’s another problem: key lookups are usually bound directly to their index operations. I think I can count on one hand the number of times in my life when I’ve seen SQL Server do an index operation (like a seek or scan), then another operation (like a sort), and then go back to the same table to do a key lookup. (Usually it involves an indexed view – more on that in another post.)

So if SQL Server uses the index’s sort of Reputation, then it insists on doing these stupid key lookups at the same time, reading more 8KB pages than there are in the table.

The fix: use DESC in the index to remove the sort.

If we build the sort into the index itself, then we don’t have to worry about tiebreakers on the TOP 100 ORDER BY Reputation DESC, DisplayName sort. Let’s try an index with explicit ordering built in:

This actual execution plan is way more elegant:

The query uses our new DESC index and runs way faster with zero CPU:

The takeaway: if your query orders columns by a mix of ascending and descending order, back to back, then the index usually needs to match that same alternating order. I don’t see this problem too often, and I can see why the missing index recommendations don’t fire off here. Querying by alternating sort orders on a single table is fairly unusual.

Alternating sort orders on multiple tables is a different story altogether. I describe that problem in the Indexing for Joins module in Fundamentals of Index Tuning class.


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

Videos
1 Comment

Let’s pick up right where we left off yesterday. I’ve got more time and champagne, so let’s keep going through your top-voted questions from PollGab.com/room/brento.

Here’s what we covered:

  • 00:00 Introductions
  • 00:39 Wilma: Poor code & tuning issues. Evidence = Database Performance Analyzer & sp_blitz. App is the customer’s idea. The dev has got golden balls (but writes poor queries), server, network and SQL always to blame. Any suggestions on how to present my findings with out losing a customer?
  • 03:15 Doug: What are the common gotcha’s associated with running SQL in a VM environment?
  • 04:36 Richard: Is it ever OK to use a non-unique date/time column (CreationDate in this case) for a new clustered index on a pre-existing HEAP table?
  • 05:10 Kyle: Does the 5 x 5 rule for new Non-Clustered indexes apply to both OLTP and OLAP tables?
  • 06:17 Dwight: What are the ways to find all queries targeting a given table?
  • 07:54 Bruce: Hello Brent, when should you play a good cop vs bad cop towards the dev executing performance destabilizing queries on the production sql server?
  • 11:11 BiggieDB: Hi Brent and all the party people! I am working on tuning up code and see the developer put, on every join, forceseek hint. I know we are bossing the optimizer around there, but is there ever a good case to use this technique?
  • 12:14 Bruce: Hello Brent, what are the hidden costs of PostgreSQL?
  • 13:40 Dwight: Hello Brent, what is the Microsoft true-up process like for SQL server? 14:30 SwedeDBA: Hi Brent, I have a friend that is searching for unused indexes on a DB in an availability group with readable secondaries. He wonders if the result from SP_BlitzIndex shows all index usage on the readable secondaries so he can securely drop the unused indexes? Thanx man!
  • 15:00 Bill: Hello Brent, have you ever considered working for Microsoft?
  • 21:00 Wally: Hi Brent, is there a good way to find root cause for when page life expectancy drops significantly? Is this a worthy tracking metric?
  • 21:59 Shawn: What are your favorite non technical books?
  • 25:34 Wrap-up, discussion of Dead by Daylight

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

Videos
0

I’m back in San Diego, so let’s sit out on the balcony, enjoy a tasty beverage, and go through your top-voted questions from PollGab.com/room/brento.

Here’s what we covered:

  • 00:00 Carl: Hi Brent, what is your opinion of the graph db support starting in SQL2017? Is this another doomed SQL feature?
  • 02:06 Bob: Brent, sometimes when we run sp_BlitzCache for a given sp, the top resultset shows that we couldn’t find a plan for this query but the bottom resultset shows prioritized findings such as Functions, blocking, etc. Can the bottom resultset findings still be relied upon?
  • 03:00 juggler314: My friend has some old tables with no great candidates for a clustered index which are currently heaps. Is it better to use something like a timestamp which may not be unique (but already exists and is used), or just add an auto-increment internal field to be the key?
  • 04:52 Jim: Hi Brent, do you have any recommended one stop tools/scripts that capture server configuration (db files, db settings, memory configuration, os version, sql version, etc) for DR purposes? Love your office hours.
  • 07:28 Ken: Hi Brent, is there a good way to handle multi db backup/restores when the transactions span multiple db’s?
  • 09:48 Drowning in SQL Pool: Hi Brent, when it comes to upgrading compatibility levels on databases after you have left an upgrade stew for a few weeks, Should you upgrade system databases? and if so should you do these first, wait a week and then do user databases? Or tackle these all at the same time?
  • 10:35 Chris: Hi Brent, what are the pros/cons of using NUMA based servers for bare metal SQL?
  • 12:25 Todd LeBod: Hi Brent, do you know of anyone (successfully) using Stretch Database, and is Microsoft still pushing this (making enhancements, bug fixes, etc)?
  • 14:11 Wrap-up, discussing Cabo and San Diego

Updated First Responder Kit and Consultant Toolkit for January 2022

In this release, sp_Blitz shows some information about clusters and AGs, sp_AllNightLog and sp_DatabaseRestore get some love, and more.

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

To get the new version:

Consultant Toolkit Changes

I 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_AllNightLog Changes

  • Enhancement: now works in any database as long as Ola Hallengren’s scripts are installed in the same database. (#3009, thanks Nick Fotopoulos.)
  • Enhancement: there’s a new configuration record to allow data/log files to be moved to the instance’s default directory. (#3046, thanks Dale Hirt.)

sp_Blitz Changes

  • Enhancement: now displays information about clusters and Availability Groups. (#2976, thanks Ragatilao.)
  • Fix: xp_regread no longer errors out if registry keys aren’t found. (#2836, thanks ScottL1969 and Andreas Jordan.)

sp_BlitzIndex Changes

  • Enhancement: faster visualization of nonclustered columnstore indexes on wide tables. (#3043, thanks Razvan Socol.)
  • Fix: total_forwarded_fetch_count column wasn’t included in the results if you saved the results to a table. (#3053, thanks Pollus Brodeur and Vladimir Vissoultchev.)

sp_DatabaseRestore Changes

  • Enhancement: the @StopAt parameter is now a real point in time restore, not just stopping at the transaction log before that point, but going up to that moment in time in the last log backup. (#3038, thanks Daniel van der Meulen.)

Bonus changes: Anthony Green kept the SQL Server versions file up to date, and Konstantin Taranov improved the repo’s readme.md.

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.


Error Handling Quiz Week: Where Are You Handling Errors?

T-SQL
11 Comments

This week’s series on error handling has been an eye opener for many of us. We’ve laughed. We’ve cried. We’ve screamed in horror. We’ve read the documentation.

I don’t blame you. This is a really confusing topic, and for many of us, it’s the first time we’ve actually looked at the mechanics of how transactions, TRY/CATCH, and error handling work. I know it’s the first time because a while back, I asked you how often your newly written code checks for errors. Most of the time, we’re not doing it at all.

That was enlightening (and a bummer) for sure, but I followed it up with another poll. When we DO check for errors, where are we doing it – in a TRY/CATCH block, in some other form of T-SQL, or in the app code?

When you think about the results, remember that this is nowhere near a valid survey – it’s just a quick question to open a discussion. This poll is actually why I wrote the blog post series – I had a pretty good hunch people didn’t understand how TRY/CATCH really works, and given that most of you aren’t using it, I figured I should talk about how complex it is.

The responders aren’t a wide sample. Most of my audience consists of database people, not pure application developers. Also keep in mind that if you’re writing C# code that accesses the database via Entity Framework or some other ORM, you’re not writing T-SQL queries either. In that case, error handling would obviously be in the app code, not TRY/CATCH or other forms of T-SQL.

I had to have the “I don’t check for errors” answer given how widespread the no-checking answers were on the prior poll. It helps remind the rest of my audience what kind of real-world code is out there, and it gives the YOLO crowd a way to answer without pestering me for replies.

Twitter polls only let you pick one answer, not mark-all-that-applies, and many folks responded with replies saying that they sometimes check in different places (or multiple places) depending on the kind of queries they’re writing. That’s great! And given what we saw with the complexity today, you can see why it’s helpful to try to catch errors in more than just one place.

As an industry, I think we’ve got some work to do around improving our handling of errors, transactions, and database failovers. I’m not saying every query needs to automatically retry deadlocks, handle database failovers, and survive out-of-drive-space issues, but there are a few easy things we can do to make our apps more graceful and fault-tolerant.


Error Handling Quiz Week: Making a Turkey Sandwich with XACT_ABORT

T-SQL
29 Comments

CAN YOU BELIEVE THAT HOT MESS IN YESTERDAY’S POST?!?

I know, right? You thought that by combining try/catch with a transaction, you’d get robust error handling.  Instead, you ended up with half the tables populated, and a leftover open transaction. You’d already forgotten Tuesday’s post in which I pointed out that TRY/CATCH doesn’t catch low severity or high severity errors.

TRY/CATCH and transactions are turkeys.

And two turkeys don’t make an eagle.

For a better experience, you need the Turkey Sandwich approach to transaction and error handling. You have to surround those turkeys with two key pieces of code:

We start with the XACT_ABORT option which increases our odds that entire transactions are rolled back or forwards together. However, that alone still may not be enough – remember Tuesday’s post where I talked about how some catastrophic errors skip the CATCH block altogether. We have to finish our turkey sandwich with an additional line to check to see if any transactions still remain, and if so, roll them back.

We’ll do yet another round of clearing the tables, running our proc, waiting a few seconds, canceling it, and then checking the table contents:

FINALLY, we achieve our goal: when the proc is canceled or fails, neither of our tables have any rows in them.

You are not a chef.
You just made your first Turkey Sandwich.

This is not the end of your culinary journey. You’ve merely learned that TRY/CATCH and transactions are turkeys, and you have to make a turkey sandwich by framing them with additional options and code. There is way more work involved.

The next step in your learning journey is to read Erland Sommarskog’s Error and Transaction Handling in SQL Server: Part 1, and then Part 2, and then Part 3. My blog posts this week have not even covered everything in Part 1! My job this week was really just to challenge what you thought you knew about error and transaction handling, and convince you that you need to dig into Erland’s posts.

Also, put down that jar of NOLOCK. That’s not mayonnaise.


Error Handling Quiz Week: Combining Transactions And TRY/CATCH

T-SQL
18 Comments

In yesterday’s epic cliffhanger, you might have been shocked to discover that a plain ol’ transaction does not solve our problem – and in fact, it makes things worse. With yesterday’s code, we still got rows inserted into the Parent table, no rows in the Child table, and a nasty surprise that our session will have to deal with down the road.

The size of the turkey might have become apparent when you tried to close your query. I’m using SQL Server Management Studio, and when I try to close that query’s tab, I get a popup warning:

Let’s look at our code again to understand what happened:

When we executed the proc, waited a few seconds, and then clicked cancel, we only canceled the execution of the waitfor. The transaction was still left open.

I’ve seen this error in so many applications, I can’t even begin to tell you. But here’s the crappy part: it’s almost impossible to track down where the bug is. All it takes is to make this coding mistake just once, and the transaction will be left open for other parts of code to deal with. I’ve seen so many cases where applications leave transactions open for days, and nobody’s sure exactly where the transaction was started.

The solution is to combine TRY/CATCH and a transaction. Before I get to the code, though, I’ll make sure to run this a few times just to make doggone sure I didn’t leave any transactions open in this window:

Let’s do a transaction in a TRY/CATCH block.

I know, I know, the code is a little wordy given that all we’re tryin’ to do is a pair of inserts, but here we go:

You know the routine: clear out the tables, run the stored procedure, wait a couple of seconds, cancel it, and check the contents of our tables:

And before you try it, for the last time this week, take a guess at what’s going to happen:

  • We’ll have no new rows in either Parent or Child, and no open transactions
  • We’ll have a new row in Parent, but not Child, and an open transaction

And then run it yourself to see whether your hunch was correct. After your experimentations, make sure to close your SSMS window juuuust in case – we’ve been playing around with a lot of transactions this week.

The fun continues in the next post.


Error Handling Quiz Week: Will a Transaction Help?

In yesterday’s post, we tried a TRY/CATCH, and those of us who actually ran the demo scripts were disappointed. We found that a TRY/CATCH alone doesn’t fix it: we end up getting rows in the Parent table, but no rows in the Child table. It turns out there are some gotchas in Books Online’s summary of what TRY/CATCH does:

A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

The devil is in the details. If you scroll further down in the documentation – look, I’m not saying you’re actually going to do that, because we both know damn well that you don’t read the documentation – there are gotchas like:

TRY…CATCH constructs do not trap the following conditions:

  • Warnings or informational messages that have a severity of 10 or lower.
  • Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
  • Attentions, such as client-interrupt requests or broken client connections.
  • When the session is ended by a system administrator by using the KILL statement.
Jokes as dark as my shades

So to recap, TRY/CATCH doesn’t catch small problems or big problems – only medium-sized problems.

Okay, that’s a bit of a turkey.

In our scenario this week, I’m causing the error by canceling the query (a client-interrupt request) – but no, we can’t just say, “Never abort your queries.” I’m pro-choice: people deserve the right to end their queries at any stage of their development. I also believe in the death penalty: DBAs deserve the right to kill your queries when they’re causing terrible harm to society at large.

The business is asking us to make sure that the data gets into both the Parent and Child tables, or neither. We’re gonna need something more powerful than TRY/CATCH.

Let’s try a transaction instead.

Microsoft’s documentation on SQL Server transactions starts out with the most important stuff:

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

That, not try/catch, is probably a better fit for what we’re trying to do. Let’s frame our two insert statements with transactional code:

And as before, run the proc, wait a few seconds, cancel it, and then see what we’ve got:

As with yesterday’s demo, I’m not going to show you the results here. Instead, guess what’ll happen before you run it:

  1. We’ll have no new rows in either Parent or Child, or
  2. We’ll have a new row in Parent, but not Child, or
  3. We’ll have new rows in both Parent and Child

And then run it yourself to see whether your hunch was correct. Just as a reminder, only do these experimentations in a development environment, not production, especially if you happen to have RCSI or SI enabled in production.

To understand what’s happening and why, check out the next post in this series.


Error Handling Quiz Week: Tryin’ TRY/CATCH

T-SQL
34 Comments

Let’s say we have two tables, Parent and Child, and we need to guarantee that they both get populated at once. We’ll write a single stored procedure to do both inserts:

I put a WAITFOR in there, but that isn’t the problem – I’m just using that to demonstrate why the code isn’t production-ready. If I execute the stored procedure, wait a few moments, and then click cancel – what happens?

The results show that a row was added in the Parent table – but not in the Child table:

Well, that’s not good: the business has asked us to guarantee that we either insert both rows, or neither. Our code can’t be doing a half-*ss job. We either gotta have all *ss or no *ss in our back end systems.

This isn’t just about canceling queries.

This problem pops up in many scenarios, like these:

  • Our query loses a deadlock
  • Our query gets killed because it’s blocking others
  • The server runs out of drive space
  • Our application calls multiple statements (rather than a proc) and the app fails midway through

Foreign keys don’t fix this kind of problem, either – in fact, in many situations, foreign keys even cause problems. That’s outside of the scope of this week’s posts, though: this week, I need to teach you why and how to make a turkey sandwich.

Let’s try a TRY/CATCH.

According to the TRY/CATCH documentation – don’t worry, I know it’s a long page, but this important part is right up there at the top of the page so you can ignore it more quickly:

A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

Let’s implement it in our code:

And then try the same trick – run it, wait a few seconds, then cancel it, and see what’s inside the tables:

I’m not going to show you what happens, dear reader, because this is a short demo, and I want you to run it yourself to see what happens. Guess what’ll happen before you run it:

  1. We’ll have no new rows in either Parent or Child, or
  2. We’ll have a new row in Parent, but not Child, or
  3. We’ll have new rows in both Parent and Child

Then run the query yourself to see what actually happens. In this next post post, we’ll continue the experiments.


Registration is Open Now for My SQLBits Workshop!

SQLBits
2 Comments

Registration just opened up for my SQLBits workshop on Mastering Parameter Sniffing! Move fast – due to COVID, they’re spacing out the attendees, so there are even less seats available than usual. My workshops usually sell out quickly, so if you want a seat, you’ll need to hustle in.

Here’s the session abstract:

You’re a database developer or DBA with at least 3 years experience performance tuning queries and indexes.

You already know you have parameter sniffing issues, and now you need to figure out how to fix it. In this one-day class, you’ll learn how to reduce the blast radius with index changes, query tuning, and database-level settings. You’ll also see firsthand how SQL Server 2017, 2019, and 2022 try to reduce it with adaptive joins, adaptive memory grants, automatic tuning, and caching multiple plans.

Join me at SQLBits on Tuesday, 8 March, at ExCel in London for a one-day workshop. I’ll be there in person – my flights are already booked, and I’m excited to see everybody in person again. The conference is taking great precautions: masks are required at the event, tables will be spaced apart, and proof of double vaccination, Covid-19 antibodies, or a negative lateral flow test (within the last 48-hours) will be required.

This course is 100% demos: the only slides are the introduction and recap. The rest of the time, I’ll be working live in SQL Server Management Studio and SQL Server 2019 (or 2022 if Microsoft releases the public bits quickly enough.) You can even follow along in your laptop if you bring SQL Server 2017 or newer and the 50GB Stack Overflow 2013 database.

I’m taking the best modules of my 3-day Mastering Parameter Sniffing class and doing it as a training day session. We’re going to be moving quickly and covering a lot of ground, and I know it can be tough to keep up. That’s why attendees will also get one year’s access to the full 3-day Mastering Parameter Sniffing recordings, too! That’s a £695 value, and it’s free with your workshop admission. (You’ll get the access in class.)

Register now. See you in London!


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

Videos
3 Comments

Let’s get together at sunrise in Cabo San Lucas, Mexico and talk through your highest-upvoted questions from https://pollgab.com/room/brento.

Here’s what we covered:

  • 00:00 Introductions
  • 00:33 AtHomeWithCOVID: Historical advice is to spread multiple datafiles/filgroups across disks. Now, storage is allocated from a balanced SAN as one drive, e.g “D” data, “L” Logs Are there any gains in having multiple datafiles/filegroups in this scenario? (Ignore tempfiles for obvious reasons)
  • 03:04 Dariusz Danielewski: Brent, in what kind of scenario would you recommend a table to have non-clustered primary key and a clustered non-unique index on a set of other columns? What would be the physical organization of such table and what type of queries would benefit from it? Thanks, D-Squared
  • 04:00 SQL mial: Hi Brent, according to you what are the best monitoring solutions that can be used at the same time for cloud VMs, azure sql, aws rdc and on premise.
  • 05:05 Wally: Hi Brent, planning on migrating from SQL2014 to SQL2019 but keeping 2014 compat level after the move. Do we still need to run Microsoft Data Migration assistant on the 2014 server to spot any potential migration issues?
  • 07:23 Doug: Hello Brent, sp_blitzbackups shows that it would take around 24 hours to restore our large multi-terabyte db full backup. Do you have any tips for reducing the restore time (i.e. switch to third party backup software, etc)?
  • 09:44 Koritt: Hi Brent, what are your best practices for SQL Data Compression? Industry advice once was that fewer data pages saved I/Os and meant more pages cached in RAM; are those benefits still compelling in these days of SSDs and 100’s GB of RAM, or are the D/U/I impacts too great?
  • 12:21 MojitoDBA: App with implicit transactions causes huge VersionStore and tempdb 2TB and still growing due to sleeping transactions. And Oracle is not an option cos we’re still waiting to be self-managed and to come with a wine tap for that price. Is it good idea job to kill such sleep tran over 1h
  • 14:30 Mehdi: In the stack overflow database, how many indexes are there in the user’s table?
  • 15:55 George Bluth: Hello Brent, will you share your pros/cons of being a DBA vs being a developer?
  • 18:13 Wrap-up

New Year’s Resolution: Lose Weight in Your Database

Indexing
14 Comments

Made a New Year’s resolution to lose weight this year? Forget taking it off your waistline – take it off your database instead with:

sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2, @SortOrder = ‘size’

Or:

sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2, @SortOrder = ‘rows’

This produces an inventory of all the indexes sorted by size on disk or the number of rows, descending:

You can copy/paste that into Excel, send it around amongst your teams, and start having discussions like:

  • “Why do we have a 50GB heap called Sales_Backup_BeforeTheDeploy?”
  • “Why do we have a 200GB table called ClickLog with over a billion rows? I thought we were purging that table nightly?”
  • “Weren’t we supposed to archive the SalesHistory table?”

Things to be aware of when you’re reading the results:

  • The Rows & Reserved MB columns are way off to the right. I just rearranged the SSMS output for this screenshot. That’s one of the reasons I typically do this analysis in Excel – that, and the fact that I like to highlight offensive rows.
  • The Last User Seek, Last User Scan, and Last User Lookup columns can help identify when the index was last read, but it’s not entirely accurate for a lot of reasons that we discuss in this module of the Mastering Index Tuning class.
  • The Last User columns are also incremented by any query, including things like your homegrown fragmentation scripts checking the contents of a row or a homegrown ETL package. Even if it’s run automatically by your apps, that’s still a “user” query to SQL Server.

That’s it. That’s the whole post. See, my blog post lost weight too.