Product has been added to your cart.

dba

SQL Server 2017: Less xp_cmdshell?

Last Updated July 20, 2017

SQL Server, SQL Server 2017

Pokin’ SQL Server 2017 RC1 dropping recently reminded me of a couple things I wanted to blog about finding in there. One that I thought was rather interesting is a new iTVF called dm_os_enumerate_filesystem. It looks like a partial replacement for xp_cmdshell in that, well, you can do the equivalent of running a dir command,…

ColumnStore Indexes And Recursive CTEs

Last Updated July 26, 2017

Columnstore Indexes, Indexing, SQL Server

Alone Together When I think about SQL Server features, I often picture a high school cafeteria. AGs are making fun of Mirroring, index rebuilds are walking around taking everyone’s lunch, dta is making a glue sandwich, and no one knows who Service Broker is even though they’ve been going to school together since Kindergarten. At…

sp_BlitzCache: Eventual Compatibility With Azure

Last Updated November 16, 2017

Execution Plans, Microsoft Azure, sp_BlitzCache

Better than nothing Yesterday it was announced that global temp tables were available in public preview for Azure. That means that unmodified versions of sp_BlitzCache (which uses a global temp table to hold a whole bunch of information) will be compatible up there. This is a nice addition for Azure, and an even nicer addition…

A Better Way To Select Star

Last Updated November 20, 2018

Development, Execution Plans, Indexing, SQL Server

Mindless Self Promotion I liked writing this blog post so much that I wrote an entire presentation on it. If you’d like to see it at GroupBy, click the link and vote. Update: You can watch the video for this session here. Much has been written about this It’s probably one of the lowest hanging…

There’s Something Funny About Variable Assignment

Last Updated June 21, 2017

Humor, SQL Server

Guess who I’m going to give you three queries, and you have to guess what the output will be before you run them. Here they are: Transact-SQL DECLARE @DatabaseName NVARCHAR(256) = N'' SELECT @DatabaseName = d.name FROM sys.databases d WHERE d.name = 'master' AND compatibility_level = 130 ORDER BY d.database_id PRINT @DatabaseName SELECT @DatabaseName =…

Trainable SQL Servers

Last Updated June 14, 2017

Production Database Administration

Funny thing The human body is pretty good at adaptation. When you get sick or injured, your body responds and (most of the time) heals itself up. When you stress your body in a certain way, over time, your body will feel less stressed by the same level of that activity. If you do 10…

Query Plans: Trivial Optimization vs Simple Parameterization

Last Updated June 16, 2017

Execution Plans, Indexing, SQL Server

Facemaking You know when you think you know something, and the obviousness of it makes you all the more confident that you know it? That’s usually the first sign that there’s a giant gotcha waiting for you. And that’s what happened to me over the weekend. The setup I answered a question. And after I…

Hyper-V: Getting Your Host And Guest Connected

Last Updated June 3, 2017

SQL Server, Virtualization

Because I always forget stuff like this I have a confession to make, dear readers: I am a horrible VM admin. I’m also not great at networking. When I go to set up a VM to do something like install SQL vNext, I want to be able to talk to it. Why bother? Well, SSMS…

Direct Seeding: I’ve Got A Wizard And You Don’t

Last Updated May 26, 2017

Always On Availability Groups, High Availability

A commenter commented That the “New AG Wizard” in SSMS 2017 had surfaced the Direct Seeding mode for AGs. I was pretty psyched about this because I think it’s a great feature addition to AGs that can solve for a pretty big hump that people run into when they create databases regularly. It used to…

Bad Idea Jeans: Multiple Index Hints

Last Updated May 23, 2017

Bad Idea Jeans, T-SQL

I hate that you can do this Here’s the thing: I’m mostly writing this because I didn’t know you could do it. But it’s cool, because it’ll reinforce some other concepts, and I’ll show you why you shouldn’t do it. I’m talking, of course, about index hints. To be more specific, hinting multiple indexes on…

Do SQL Server 2017’s Adaptive Joins Work with Cross Apply or Exists?

Last Updated May 19, 2017

Adaptive Joins, SQL Server 2017

I think I’ve mentioned that the most fun part of new features is testing them with old ideas to see how they react. It occurred to me that if Adaptive Joins didn’t work with APPLY, I might cry. So, here goes nothin’! Cross Simple Cross Apply…ies can use Adaptive Joins, though at first glance there’s…

How Scalar Functions Can Stop You From Getting Adaptive Joins

Last Updated May 1, 2021

Adaptive Joins, Execution Plans, SQL Server 2017

If you’re not aware of the performance problems scalar valued functions can (and often do) cause, well, uh… click here. We’ll talk in a few days. If you are, and you’re worried about them crapping on Adaptive Joins, follow along. The big question I had is if various uses of scalar valued functions would inhibit…

SQL Server 2017: Interleaved MSTVFs Vs Inline Table Valued Functions

Last Updated May 12, 2017

Adaptive Joins, Execution Plans, SQL Server 2017

But is it faster? Now, I know. There are very few “always” things out there in SQL Server. This is also true for functions. A lot of the time — I might even say most of the time, inline table valued functions are going to be faster that scalar and multi statement table valued functions.…

SQL Server 2017: Interleaved Execution for MSTVFs

Last Updated May 15, 2017

Execution Plans, SQL Server, SQL Server 2017

What I don’t want you to take away from this Is that I want you to start using Multi Statement Table Valued Functions all over the place. There are still problems with them. Backed by table variables Lots of hidden I/O cost Number of executions may surprise you One important current limitation (May-ish of 2017)…

Breaking: SQL Server 2016 SP1 CU2 Supports ENABLE_PARALLEL_PLAN_PREFERENCE Hint

Last Updated May 13, 2017

Execution Plans, SQL Server, SQL Server 2017

Hot Diggety! After reading an excellent tip by Dmitry Pilugin about the new hint for forcing a parallel plan in SQL Server 2017, I figured I’d test it out on SQL Server 2016’s latest CU to see if it had been back ported yet. Good news! It has. Obviously. That’s the title of the blog…

SQL Server 2017: Statistics Information Comes To Query Plans

Last Updated May 15, 2017

Execution Plans, SQL Server 2017, Statistics

Warm fuzzies I think this is how my mom would have felt if I went to college. You see, once you stop cargo culting around index fragmentation, and disk queue length, you start to realize which things are actually important to query performance. Things like statistics, and cardinality estimation. And, sure, having the right indexes…

Adaptive Joins And SARGability

Last Updated May 12, 2017

Adaptive Joins, SQL Server 2017

There’s a famous saying Non-SARGable predicates don’t get missing index requests. And that’s true! But can they also stifle my favorite thing to happen to SQL Server since, well, last week? You betcha!© One Sided I’m going to paste in some queries, each with something non-SARGable. There are two tables involved: Users and Posts. Only…

Adaptive Joins And Local Variables

Last Updated May 12, 2017

Adaptive Joins, SQL Server 2017

With new features I really love kicking the tires to see how they work with existing features, and if they fix existing performance troubleshooting scenarios. One issue that I see frequently is with local variables. I’m not going to get into Cardinality Estimator math here, we’re just going to look at Adaptive Join plan choice…

The 2017 Adaptive Join Optimization Eats Bad TSQL For Breakfast

Last Updated May 12, 2017

Adaptive Joins, SQL Server 2017

Cheeky If you thought that title sounded familiar, you sure were right. That’s called a classical reference. So here we are, three whole years later, and yet another improvement to the engine promises to fix performance issues forever and ever. While this isn’t exactly an entirely new cardinality estimator, it’s an entirely new branch in…

Look Ma, Adaptive Joins

Last Updated May 12, 2017

Execution Plans, SQL Server 2017

This probably won’t seem like a big deal soon But I just got the optimizer to pick an Adaptive Join! It took a few tries to figure out what would cause some guesswork to happen, but here it is. I hope Joe Sack has strong ribs. And here’s the tool tip info! Dynamic Duo This…