performance tuning

Books to Learn SQL Server Performance Tuning and Database Design

I get a lot of requests about which books are helpful to learn performance tuning and database design. I totally get that — I still like learning with books. It doesn’t mean training videos or blogs are any less cool. They can all work together. You don’t need to read all the books in each topic. Browse the book descriptions and look through the free PDFs to find the best starting place for your own goals. SQL Server Query Performance Tuning Books Grant Fritchey has written terrific books on learning performance tuning: SQL Server Query Performance Tuning (printed book only) SQL Server Execution plans (free PDF, or you can buy the printed book) Jonathan Kehayias and Erin Stellato wrote an e-book on wait statistics, which are critical for perf tuning a workload: SQL Server Performance Tuning Using Wait Statistics: A Beginner’s Guide (PDF only). Louis Davidson and Tim Ford wrote a book…
Read More

Estimated vs. Actual Number of Rows in Nested Loop Operators

This is one of those little details that confused me a ton when I was first working with execution plans. One problem with learning to work with plans is that there’s just SO MUCH to look at. And it’s a bit spread out. So, even when looking at a single tooltip, things can be confusing. Let’s talk about the nested loop operator, who can be particularly weird to understand. Meet our nested loop Here’s a nested loop from a SQL Server Execution plan: For every row that comes in from the top right index seek, SQL Server goes and does the bottom right index seek. Like this: I think the best way to explain this was tweeted by Andy Mallon: I thought the outer table was a pile of crackers. The inner a pile of cheese. And nested loops make snacks. — Andy Mallon (@AMtwo) August 16, 2016 But when you…
Read More

Teach Yourself SQL Server Performance Tuning (Dear SQL DBA Episode 12)

You’d love to have a job tuning SQL Servers, but you don’t have an environment to practice in. Here’s how to teach yourself performance tuning and prepare yourself to land and succeed in job interviews. This is a “listen-able” 20 minute video. Prefer a podcast instead? Find it at A written version of the discussion with clickable links is just under this video.   Dear SQL DBA, Is there a way I can gain SQL performance tuning experience if I don’t have access to a live production environment? I read lots of blogs and attend classes and conferences were I can, but I don’t feel confident. I know real experience is the best, but I’d like to do whatever I can, and I’d like to get a job tuning performance. Yours truly, Junior Tuner You’re right to ask this question, because job interviews focus heavily on experience It’s tough to get a job without…
Read More

Why an Upgrade can Cause Performance Regressions (Dear SQL DBA Episode 9)

You finally got approval to move to new hardware and a fresher version of SQL Server. After months of work,  you do the migration and then… performance gets worse. What can cause this, and what do you look for? This is a “listen-able” 27 minute video. Prefer a podcast instead? Find it at Show notes with clickable links are below the video. A free poster of the concepts is here. Here’s this week’s question: Dear SQL DBA, I recently went through a process of migrating databases on a SQL Server 2008 R2 stand alone instance to a SQL Server 2012 SP2 cluster. The cluster’s servers and SQL Server configurations were built to be as close to identical as possible to the previous instance (memory, cores, disk, maxdop, CTP, etc). After the migration, I noticed that CPU utilization jumped from the normal 25% to a consistent 75%. I did several other migrations…
Read More

Updating Statistics in SQL Server: Maintenance Questions & Answers

I’ve been asked a lot of questions about updating statistics in SQL Server over the years. And I’ve asked a lot of questions myself! Here’s a rundown of all the practical questions that I tend to get about how to maintain these in SQL Server. I don’t dig into the internals of statistics and optimization in this post. If you’re interested in that, head on over and read the fahhhbulous white paper, Statistics Used by the Query Optimizer in SQL Server 2008. Then wait a couple days and chase it with it’s charming cousin, Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator. I’m also not talking about statistics for memory optimized tables in this article. If you’re interested in those, head over here. Quick Links to Jump Around This Article General Advice on Statistics Maintenance Which Free Third Party Tools Help Update Statistics? What are Statistics? What Creates Column Statistics? What are…
Read More

Can I Force A Hinted Plan with Query Store in SQL Server 2016?

SQL Server 2016’s Query Store feature promises to be better than Plan Guides ever were. The Query Store lets  you track query performance, collect execution plans, and force a specific plan if you notice that a query is sometimes fast, and sometimes slow. But what if we want to hint a query? Can Query Store force a hinted plan? We’ve got a slow query that runs for 35 seconds. We can’t change the code for the query quickly– that’s going to take months. With manual testing, we discover that if we add a couple of trace flags with an OPTION QUERYTRACEON hint, we can drop the runtime of the query down to 17 seconds.  Alternately, we discover that if we add an index hint to the query to tell it not to use a nonclustered index, we get a parallel plan that’s 17 seconds. Can we get these plans into…
Read More