SQL Server

Key Lookups and ColumnStore Indexes

Beavis v. Butthead I was a bit surprised that this was a possibility with ColumnStore indexes, since “keys” aren’t really their strong point, but since we’re now able to have both clustered ColumnStore indexes alongside row store nonclustered indexes AND nonclustered ColumnStore indexes on tables with row store clustered indexes, this kind of stuff should…
Read More

Answering Questions For Fun And No Profit

I love answering questions about SQL My forum of choice is dba.stackexchange.com because I find the voting and point system with badges for being a decent internet person addictive. If you like more traditional forums, SQLServerCentral.com has a lot of really smart people on it as well. When I post questions or answers, I try…
Read More
Brent Ozar reading

What Would You Put in SQL Server 2019?

If you were leading Microsoft’s development programs, what would you add or change in SQL Server 2019? Forget licensing or pricing changes – that’s dictated by the marketing teams, and those decisions are made really close to the release date. Instead, let’s think bigger: what are the really big improvements you’d make that would really…
Read More

The Ghosts of Temp Tables Past

True story You may find it hard to believe, but I recently had to fix a small bug in sp_BlitzCache and sp_BlitzQueryStore. Since both stored procedures have similar functions, they also share some temp table names (mainly the ones for parsing down XML nodes to more manageable chunks). In the window where I was making…
Read More

Optional Parameters and Missing Index Requests

That’s when it all gets blown away At one point or another in everyone’s SQL-querying career, they end up writing a query that goes something like this: Transact-SQL SELECT something FROM stuff WHERE (@thing1 is NULL or whatever = @thing1) AND ... 1234 SELECT somethingFROM stuffWHERE (@thing1 is NULL or whatever = @thing1)AND ... These…
Read More

How Much Can One Column Change A Query Plan? Part 2

What happened in Part 1? Join Elimination, naturally. Until the end. My copy of the Stack Overflow database doesn’t have a single foreign key in it, anywhere. If we go down the rabbit hole a couple steps, we end up at a very quotable place, with Rob Farley. 2. Duplicated rows Not necessarily duplicated completely,…
Read More
Pocket Square

[Video] Office Hours 2017/08/30 (With Transcriptions)

This week, Brent, Erik, and Richie discuss Availability Groups, versions of sp_BlitzIndex™, SQL Server installation issue, backing up SSRS reports, SQL Server on Linux, failovers, detaching/attaching databases, extended events, career progression of a DBA, and more. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to our podcast to listen…
Read More

How Much Can One Column Change A Query Plan? Part 1

Beyond Key Lookups I’m going to show you two queries, and two query plans. The two queries are only different by one selected column, but the two query plans are wildly different. Unless I’ve talked to you in the last few days, these may surprise you as well. Here’s the first one. Transact-SQL DECLARE @DisplayName…
Read More

An Expensive Reason To Avoid AGs In Azure

Cash Rules Most people, when they get through paying for Azure, and SQL Server Enterprise Licensing, are left with a hole in their wallet that could only be filled with something that says “Bugatti”, and has a speedometer with an infinity sign at the end. Recently, while working with a client, I found out that…
Read More

[Video] Office Hours 2017/08/23 (With Transcriptions)

This week, Brent, Erik, Tara, and Richie discuss index fragmentation, Azure disk speeds, Availability Groups, database restores, optimize for ad hoc setting, forced parameterization, BI consultants, index views, SPN registrations, and more. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go. Enjoy…
Read More

ColumnStore Indexes: Rowgroup Elimination and Parameter Sniffing In Stored Procedures

Yazoo Over on his blog, fellow Query Plan aficionado Joe Obbish has a Great Post, Brent® about query patterns that qualify for Rowgroup Elimination. This is really important to performance! It allows scans to skip over stuff it doesn’t need, like skipping over the dialog in, uh… movies with really good fight scenes. Car chases?…
Read More

What If Week: What Would You Add?

For this week’s What If series, we’re exploring what would happen if you had access to SQL Server’s source code – like if you got a job at Microsoft, signed a partner NDA, if the code leaked, or if it went open source. Today’s question is, “What would you fix or add?” Erik says: Skipping…
Read More

sp_AllNightLog: Creating Jobs Like I’m The President

Look, we need these things The setup for sp_AllNightLog creates jobs for four separate activities 1 job to poll for new databases to back up (primary) 1 job to poll for new databases to restore (secondary) 10 jobs to poll for backups to take (primary) 10 jobs to poll for restores to… whatever (secondary) And,…
Read More