Carrying Baggage Through Query Plans: Why Wide Queries Get Heavy

I see this pattern repeatedly: a “wide” query that returns many columns and less than 100k rows runs slowly. SQL Server gets slow when it drags large amounts of baggage through the entire query plan, like a solo traveler struggling with massive suitcases in an airport instead of picking them up close to their destination.

SQL Server often minimizes data access by grabbing all the columns it needs early in query execution, then doing joins and filters. This means presentation columns get picked up early.

Continue reading

Learner's Guide to SQL Server Query Tuning

Following on from my Learner’s Guide to SQL Server Performance Triage, I’m tackling Query Tuning. In this guide, I’m experimenting with an outline style rather than expanding each paragraph.

Continue reading

How to Cause a Simple Spill to tempdb

Sometimes it’s useful to know how to cause a problem.

Maybe you’ve never encountered the problem, and want to get hands-on experience. Maybe you’re testing a monitoring tool, and want to see if a condition flags an alert. Maybe you’re testing out a new client tool, and want to see how it displays it.

Continue reading

SQL Operations Studio: Keyboard Shortcuts, Actual Plans, & More

Last week I posted a quiz on SQL Operations Studio, a free, multi-platform tool from Microsoft..

This tool is under active development and the features are improving by the day – which makes it a great time to start trying out the tool and see what you like: because you can suggest changes!

Continue reading

Find the Scalar Function Call: What It Means When It Hides in Probe Residual

User defined functions are fairly simple to create in SQL Server, but figuring out exactly how they are being used can take a little time - even in a simple execution plan. This is because the functions can be tucked away into lots of different operators in the plan, even join operators.

The good news: once you learn what to look for, it’s not terribly complicated. There are some fancy terms involved, but the definitions are pretty simple.

Continue reading

Register for My Free Session on Execution Plan Forcing

I am excited to be giving a free online session as part of the Idera Live Virtual Conference, 2018!

Continue reading

Do Index Changes Remove Execution Plans from Cache?

• Impacted query plans are NOT removed from cache at the time you change an index. The next time the query is executed, it will be recompiled, and the new plan and related execution information will be placed in cache.
• This is true whether or not the index is used by the query.
• Even if the index is on columns in the table which are not referenced by the query, the query will recompile on the next run.

To see this in action, you can play along with this sample script.

Continue reading