If you were leading Microsoft’s development programs, what would you add or change in SQL Server 2018? 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…
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…
Boy, have I got a weird challenge for you, dear reader. When Erik & I started building Expert Performance Tuning for SQL Server 2016 & 2017, our PASS Summit pre-con, I talked with the PASS staff about the attendance record for pre-cons. I thought, “Wouldn’t it be cool if we had the biggest pre-con ever?” Turns…
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: SELECT something FROM stuff WHERE (@thing1 is NULL or whatever = @thing1) AND … These are often called optional parameters, and if you spend any time looking at…
Life Is Messy Demo queries have this nasty habit of being clean. Even using a pit of despair like Adventure Works or World Wide Importers, it’s easy to craft demo queries that fit the scenario you need to make yourself look like a genius. Stack Overflow, in all its simplicity, makes this even easier (lucky…
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,…
When Windows Server 2012 came out with Core, I heard some rather suspicious things at conferences like: “I’ll be able to take way less patching outages!” “It’ll be so much faster because it has less overhead!” “Everyone will learn automation and be more powerful!” “It’s the way of the future! Learn it or your career…
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…
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. DECLARE @DisplayName NVARCHAR(40)…
How bizarre So there’s this funny thing about SQL Server: many units of measure boil down to 8k pages. That’s the size of a data page, so when you measure reads, or size, sometimes the only thing you can do is convert that to MB or GB, or if you’re super lucky, TB. But transaction…
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…
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. If…
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?…
You still won’t use them And I predict that Extended Events will remain as niche-only use for specific troubleshooting scenarios. Very few monitoring tools use them, and the ones that do still rely on traces and DMV queries for the bulk of their collection. That’s not a good sign, in 2017, considering how long Extended…
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…
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 change?” Erik says: Dynamic…
Top Secret For the sake of any lawyers out there reading, I want to start by saying we do not now, nor have we ever possessed SQL Server’s source code. I wouldn’t even know what to do with it if I did — I doubt it’s written in crayon. Heck, I’m not even competent enough…
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,…
There’s no Top in the title And that’s because a TOP without an ORDER BY is non-deterministic, and you’ll get yelled at on the internet for doing that. This is just a short collection of things that I’ve done in the past, and still find people doing today when troubleshooting performance. Sure, this list could…
This week, Brent, Erik, and Richie discuss unit testing, tSQLt, availability groups, duplicate indexes, reporting services 2016 on availability groups, troubleshooting Entity Framework queries, joining views in tables, SQL Server caching issues, and why procedures have plans inside the cache. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or…
