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…
Earlier this week, I showed you two nearly identical queries and their estimated plans: They look pretty darned identical – so how much worse can one query be? Here’s how much worse: Top 100: 51.0 seconds duration, 141.6 seconds CPU time Top 101: 160.0 seconds duration, 244.0 seconds CPU time That’s right: adding just one…
Last week, Erik showed two queries that were aaaaalmost identical, with only one extra column – and the execution plans were dramatically different. Adding just one eensy column made all the difference in the world. Now, check out these two queries – the first asks for top 100, and the second asks for top 101:…
My public SQL Server database copy of the Stack Overflow data dump only includes clustered indexes by default. I want to keep your database size as small as possible for quick downloading. But like any database – when you create it, should you add your own indexes to make queries go faster? The answer lies…
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…
For our annual company retreat, we flew to Seattle, boarded the Ruby Princess, and visited Juneau, Skagway, Ketchikan, and Victoria. No stories or explanations – just photos. Enjoy! Now, where should we go next for 2018? Don’t tell us your town – that’s too obvious. Tell us…
It’s one of those fierce religious wars that divides families and tears loved ones apart. First, if two tables are related, should we tell SQL Server about it? If SQL Server knows that there’s a relationship, then it can make better decisions when building execution plans. The problem is that SQL Server has to be…
In our work as consultants, we see a lot of shops that have a monitoring tool, and yet they’re still not able to get to the root cause of a SQL Server performance or reliability issue. In many of these cases, the problem isn’t the people: the problem is that they bought a tool that doesn’t…
In our new 2-day hands-on lab course, Performance Tuning By Example, students have a series of performance tuning goals they have to accomplish. For example, in one scenario they have to fix the performance problem only using indexes, and in another they’re only allowed to change code (but not indexes.) In each lab, they have…
Most of the time, SQL Server performance tuning starts with your top wait stats. Run sp_BlitzFirst @SinceStartup = 1, look at your top couple of wait types, and that’s where to focus your efforts. However, even small amounts of certain wait types can indicate big problems. When these waits strike, it can feel like the…
Everybody wants an easy button to keep their SQL Server data safe at rest. Always Encrypted can keep your most sensitive data – think credit cards and social security numbers – safe by encrypting them in the database driver, running on the app server. That way, when it gets to the SQL Server, it’s already…
It’s that time again! Register for free now to attend next Friday, September 1. Here’s the lineup: 8AM Eastern – Why Defragmenting Your Indexes Isn’t Helping – Brent Ozar (@BrentO – BrentOzar.com) 10AM – How to Evaluate if SQL 2016 In-Memory OLTP is Right for Your Workload – Ned Otter (@NedOtter – NedOtter.com) Noon –…
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 look at first?” Brent says: I’d…
Want to present at the upcoming GroupBy December 1 & 8 online conference? Read the abstracts submitted so far Submit your own abstract Readers will comment on your abstract, helping you improve it (so don’t kill yourself working really hard on the first draft – think of it as a living abstract that you’ll work…
Sometimes I need to reset stuff during performance training classes. I know some of you teach classes, too, and some of you just like doing crazy stuff. So here you go, a stored procedure to lose weight fast: CREATE PROCEDURE [dbo].[DropIndexes] AS SET NOCOUNT ON CREATE TABLE #commands (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Command…
For the next 10 days, we’ll still be publishing scheduled blog posts, but we’ll be responding a little slower than usual, and probably a little more relaxed as well. That’s right – it’s time for our annual company retreat. This year, we’re back aboard a cruise ship – this time taking the Ruby Princess from…
Using the good ol’ Stack Overflow public database, get the execution plan for this query – either estimated or actual, doesn’t matter: SELECT Id FROM dbo.Users WHERE LastAccessDate = ‘2016/11/10’; In the execution plan, SQL Server asks for a missing index on the LastAccessDate field: If you right-click on the plan and click Show Execution…
You’re kinda-sorta comfortable doing performance tuning on SQL Server. You’ve read a few blogs, you monitor Page Life Expectancy, you rebuild your indexes, and you add an index here or there. However, you haven’t been to a day-long performance tuning class yet, and you’re wondering what you’re missing. Thing is, in SQL Server, performance tuning…
In my work with clients and classes, some blog posts come up a LOT. Forcing a Parallel Query Execution Plan by Paul White – Paul’s posts usually cover execution plan components in incredible detail, but the real gem in this one is the section called “Parallelism-Inhibiting Components.” If your T-SQL includes this stuff, the whole…
So far in this series, I’ve shown you how to corrupt a clustered index, then how nonclustered indexes can be corrupted independently. If you haven’t read those, you should start there first. Let’s start again with our 50Ways database, but this time we’re going to be good and make sure that we’ve got checksums enabled,…
