DEV Community: Dylan Miyake

Saying “Yes, And…” to Technology: Lessons from a Life of Curiosity, Hard Work, and Global Experiences

Dylan Miyake — Fri, 07 Mar 2025 15:34:09 +0000

In our ever-changing tech landscape, progress isn’t simply measured in code—it’s shaped by experiences, perspectives, and the willingness to say “yes, and…” to every opportunity that challenges us. This article explores how technology has evolved—from the era of magnetic tapes and early word processors to the instantaneous, AI-enhanced systems of today—and what it takes to truly understand and solve problems. Through stories from family, travel, and a diverse career, the piece illustrates how embracing hard work and varied experiences can fuel innovation.

A Glimpse into the Past: Family, Early Tech, and the Art of Saying “Yes, And…”

Lessons from a Xerox Legacy

Growing up, I heard countless stories about my father’s early days at Xerox in the 1970s. Imagine a time when a crucial part of a financial closing involved flying with massive reels of magnetic tape between offices. It was a period when every task demanded a hands-on approach—where every step was a testament to perseverance.

One “bring your kid to work day” at Xerox, I saw a computer that could have been straight out of a sci-fi movie—a machine reminiscent of the one that would later spark ideas about the graphical user interface. Later, a DEC VT-180 introduced me to the wonders of the Samna word processing application, giving me a taste of how far technology had come.

These early experiences were about more than just technology; they were lessons in dedication and the art of saying “yes” to challenges. Whether it was watching magnetic tape being ferried across the country or marveling at early computer interfaces, I learned that hard work combined with openness to new ideas creates the groundwork for innovation.

From Hexadecimal Code to Instantaneous Feedback

I still recall the days of painstakingly copying hexadecimal code from magazine printouts and saving it onto cassette tapes—each keystroke a mix of anticipation and hope. It was an era where progress moved in slow, deliberate steps.

Fast forward to today, and the process is almost instantaneous. Modern development environments deliver real-time feedback, and coding has transformed from a labor of love into an exercise in rapid iteration. The journey from laboriously entering code by hand to using AI-enhanced tools is a vivid reminder of how every experience, even the tough or tedious ones, teaches us something valuable.

Early Coding Camps and the Power of “Yes, And…”

One of the most memorable lessons in embracing the “yes, and…” mindset came at a fourth-grade coding camp. We learned Logo, programming a little turtle to draw patterns on a screen. What struck me was that curiosity was contagious—even the instructors often learned along with us.

This experience wasn’t just about the joy of programming; it was about the realization that when you say “yes” to learning, you unlock a world of possibilities. It taught me that even simple beginnings can lead to profound insights, a lesson that continues to resonate both in my work and in my role as a parent.

Expanding Horizons: How Global Experiences Shape a Problem-Solving Mindset

The World as a Classroom: From Brazil to Costa Rica

My life has been a journey across borders and cultures—a journey that has shaped the way I understand and approach problems. Early childhood in Brazil introduced me to a vibrant tapestry of language and tradition. Later, as an exchange student in Valencia, Spain, I learned firsthand how immersing yourself in a new culture can transform your perspective on life and work. Each new place was a lesson in adaptability and a reminder that every challenge is best met with curiosity and a willingness to engage.

Learning Japanese at the University of Hawaii and studying at Doshisha University in Kyoto deepened my appreciation for the nuances of language and cultural expression. A summer spent in Costa Rica, immersed in Spanish and local customs, reinforced a simple yet profound truth: to solve any problem effectively, you must understand its context—and sometimes that means living through the pain and the joy that comes with it.

The “Yes, And…” Philosophy in Travel and Technology

Travel teaches you that life is a series of “yes, and…” moments. Whether it was navigating unfamiliar streets in Valencia or negotiating language barriers in Japan, each experience underscored the importance of fully engaging with your environment.

In technology, as in life, truly understanding a problem means immersing yourself in it. If you want to build a robot that mops floors, you’ve got to understand what it takes to mop a floor. Mastering the basics isn’t about simply getting through the task—it’s about diving deep enough to capture every nuance, every difficulty, so that you can design a solution that’s not only effective but transformative.

The Shifting Landscape: Software Engineering Meets Software Architecture

The Evolution from Hands-On Coding to Holistic Design

For many years, software engineering was all about hands-on coding—writing, debugging, and making things work. I spent countless hours immersed in the nitty-gritty details of code, often feeling both the frustration and the satisfaction that came with each challenge. Back then, it was all about getting through the process, step by step, line by line.

Today, though, the role has evolved. With many routine tasks now handled by AI and modern development tools, the focus is shifting from simply writing code to designing systems that solve problems in a comprehensive way. Modern software architects look beyond the code; they’re involved in crafting the vision and strategy behind the technology. They understand that to truly innovate, you must be both a creator and a strategist—a person who sees the big picture and embraces every challenge with a “yes, and…” attitude.

Living the Pain to Solve the Pain

The idea of living the pain to solve the pain is central to effective problem solving. Whether it’s learning the ins and outs of a technology or understanding a customer’s real-world challenges, there’s no substitute for firsthand experience. It’s about embracing hard work and refusing to settle for the status quo. When you immerse yourself fully in the challenge, you learn the intricacies that allow you to build solutions that truly resonate.

For example, in the early days of my career, saving code to a cassette tape was fraught with uncertainty. Each operation was a lesson in patience and perseverance—a stark contrast to the instant gratification of today’s digital environment. But that process taught me the value of attention to detail and the importance of designing systems that are robust enough to handle both success and failure.

Designing for Today’s Challenges

The shift from routine coding to holistic system design means that modern technologists are expected to balance technical proficiency with strategic vision. The role of the software architect is to bridge the gap between the minute details of implementation and the broader needs of a business or a project. This means being in tune with the latest trends while also drawing on timeless principles—like the importance of understanding the problem fully before attempting to solve it.

By embracing a mindset of continuous learning and relentless curiosity, we open ourselves up to the possibility of innovation in every corner of our work. Whether we’re streamlining a complex database or rethinking a legacy system, the key is to approach every challenge with the same commitment: to do our best, learn from the experience, and then build something better.

Databases: The Unseen Backbone of Modern Innovation

Why Databases Remain Indispensable

Across every phase of technological evolution, one constant has been the critical role of databases. From the early days of FoxPro, where I first realized that automating data processes could transform business operations, to today’s era of cloud-based, real-time analytics, databases have been the silent engine powering innovation.

Databases aren’t just repositories of data—they’re strategic assets. They store the knowledge and insights that inform every decision, every design, and every strategy. The value of data lies not only in its quantity but in the depth of understanding it provides. By capturing and analyzing data effectively, we gain the ability to see patterns, predict trends, and make decisions that are both informed and impactful.

The Competitive Edge of Deep Data Understanding

In a world where many aspects of software can be replicated, the nuanced understanding of data remains a unique advantage. Competitors might mimic code or replicate features, but they can’t easily copy the insights that come from working closely with data. This competitive edge isn’t about having the latest technology—it’s about understanding the intricacies of your field, much like knowing every detail of how to mop a floor makes you a far better designer of a floor-mopping robot.

Building Scalable, Future-Proof Systems

Modern challenges demand systems that are not only efficient but also scalable and resilient. Gone are the days when saving data was a gamble with a cassette tape; today, we need architectures that can handle real-time processing, massive data volumes, and the integration of emerging technologies like AI and machine learning.

The shift toward holistic system design means that architects and engineers must work together to build infrastructures that are ready for the future. This collaboration is at the heart of today’s innovation—melding technical skill with strategic insight to create systems that stand the test of time.

Embracing a World of Experiences: The Power of Curiosity and Hard Work

The Value of Diverse Experiences

My journey has taken me far beyond the confines of a single desk or a single country. Growing up in Brazil, spending formative years as an exchange student in Valencia, studying Japanese at the University of Hawaii, exploring the traditions of Kyoto through Doshisha University, and even spending a summer in Costa Rica—all of these experiences have contributed to my understanding of the world and its myriad challenges.

Travel and cultural immersion have taught me that true problem solving requires more than technical know-how. It requires empathy, adaptability, and a deep understanding of the human context. Every new culture, language, or perspective enriches the way you think and approach a problem. These experiences drive home the idea that solving a problem isn’t just about applying a formula—it’s about understanding the real-world impact of your work.

Hard Work, Relentless Curiosity, and the “Yes, And…” Approach

The journey of learning is never a straight line. It’s filled with unexpected detours, moments of frustration, and the satisfaction of finally grasping a difficult concept. The key is to approach every challenge with a “yes, and…” attitude—acknowledge the difficulty, embrace it fully, and build on it. When you’re in the thick of a challenge, whether it’s debugging code or designing a new system, dive in wholeheartedly. Understand every detail, because that understanding is what leads to innovative solutions.

There’s a saying in improv that “yes, and…” means accepting what’s in front of you and then building on it. In technology, this translates into a deep commitment to living the pain of the problem in order to solve it. Whether you’re manually mopping a floor to understand its challenges or learning a new language to better connect with a global audience, every experience adds to your ability to create something truly transformative.

Looking Ahead: A Future Defined by Adaptability, Insight, and Continuous Innovation

The Changing Roles in a Dynamic Landscape

The shift from the days of laborious coding to the era of holistic system design is a testament to how far we’ve come—and how much potential still lies ahead. Today’s technologists are expected to balance deep technical skills with a broader strategic vision. The roles of software engineer and software architect are merging into a dynamic blend where understanding the problem is as important as solving it.

This evolution isn’t about discarding old methods; it’s about learning from every experience—whether it’s the painstaking process of saving code on a cassette or the instantaneous feedback of modern development tools. It’s about saying “yes” to each challenge, and then saying “and” to the endless possibilities that follow.

A Final Thought on Continuous Learning

Every day presents a new opportunity to learn, to challenge yourself, and to build something better than before. Whether you’re designing a system to streamline business operations or simply figuring out a more efficient way to complete a mundane task, the journey is defined by your willingness to dive in, learn from every setback, and continuously improve. When you fully understand the pain, you can build solutions that are not only innovative but also deeply effective.

This isn’t a guide on how to follow a single path; it’s a reflection on how varied experiences—from family stories at Xerox to adventures in far-flung countries—can shape a mindset that’s unafraid of hard work and driven by curiosity. In a world where technology and cultures intersect, the best solutions come from truly living the challenges and using every lesson to propel you forward.

From AngularJS to React: Lessons Learned on Our Journey

Dylan Miyake — Thu, 06 Feb 2025 14:55:15 +0000

Three years ago at ClearPoint Strategy, we made a bold move: transitioning our application from AngularJS to React. As someone who got his start with Macromedia ColdFusion (more on that later), diving into the React ecosystem was both exhilarating and humbling. Here’s a look back at our journey, the lessons we learned, and some code comparisons that highlight how much the landscape has evolved.

The Angular Factor: Why We Really Moved

It wasn’t just a matter of chasing the latest trends. One of the biggest catalysts behind our migration was a shift in the ecosystem itself—Google, the original creator of AngularJS, pivoted to a completely new framework: Angular. Although Angular shares a name with AngularJS, it’s a completely different beast built from the ground up with modern TypeScript and a reimagined component architecture. Continuing to invest in AngularJS just didn’t make sense when its creator had moved on, and we needed a robust solution that wouldn’t leave us stranded as the ecosystem evolved.

AngularJS vs. React: A Code Comparison

AngularJS Example

In AngularJS, creating a simple “Hello World” component often looked something like this:

Copy
// app.js (AngularJS)
angular.module('myApp', [])
  .controller('HelloController', ['$scope', function($scope) {
    $scope.greeting = "Hello, world!";
  }]);

And in your HTML, you might have:

<!-- index.html (AngularJS) -->
<div ng-app="myApp" ng-controller="HelloController">
  {{ greeting }}
</div>

AngularJS bundled much of the magic for you—you simply declared your module, controller, and data binding was taken care of with minimal boilerplate.

React Example

Jumping into React meant rethinking how we structure our UI. Here’s a comparable “Hello World” example in React:

// Hello.jsx (React)
import React, { useState } from 'react';

const Hello = () => {
  const [greeting] = useState("Hello, world!");
  return <div>{greeting}</div>;
};

export default Hello;

And then in your main entry file:

// index.jsx
import React from 'react';
import ReactDOM from 'react-dom';
import Hello from './Hello';

ReactDOM.render(<Hello />, document.getElementById('root'));

What’s Different?

Explicit Imports and Modules:

In AngularJS, much of what you needed was globally available (or provided by the framework). In React, you must explicitly import React, ReactDOM, and any additional libraries you need. This explicitness provides clarity—but it also means more setup and a steeper learning curve.

Component Structure:

AngularJS’s controllers and directives offered one way to manage UI logic, while React’s component-based architecture forces you to break your UI into small, reusable pieces.

Tooling and Configuration:

AngularJS came with a lot of built-in functionality, whereas React’s unopinionated nature requires you to make choices about routing, state management, and more. That freedom can be liberating, but it also means you must carefully choose and integrate the right tools.

Create-React-App: The Source of All Evil?

If there’s one tool that has sparked as much debate as React itself, it’s create-react-app (CRA). Designed to help developers get started quickly, CRA abstracts away the build configuration, making it seem like magic—until you need to customize it.

Some notable voices in the React community have been quite vocal about its drawbacks:

“Create-react-app gives you an easy start, but its hidden configuration can lead to unexpected pitfalls when you need more control.”
— Dan Abramov

“The convenience of CRA comes at a cost. When your project grows, that black-box build process can become a source of frustration rather than a help.”
— Kent C. Dodds

For many, this “magic” is the source of all evil. Developers end up chasing elusive bugs or struggling to optimize performance because the build process—and its hidden complexities—remains opaque. While CRA is fantastic for beginners and small projects, we quickly learned that as our application grew, the convenience turned into an obstacle that required us to eventually eject and face the underlying configuration head on.

Embracing Frameworks, Opinions, and Standard Libraries

One of our early lessons was that frameworks and opinions are important. While React gives you the freedom to choose your tools, having a set of standard libraries (like React Router, Redux, etc.) is critical for building a scalable application. Setting clear guidelines and choosing the right libraries early on saved us countless headaches down the road.

TypeScript Really Is Your Friend

Adopting TypeScript with React was another game changer. It provided type safety, improved the developer experience, and made our code more maintainable. A simple example:

// Hello.tsx (TypeScript + React)
import React, { FC } from 'react';

interface HelloProps {
  greeting: string;
}

const Hello: FC<HelloProps> = ({ greeting }) => {
  return <div>{greeting}</div>;
};

export default Hello;

This level of type assurance was something we wished we had from the start when working with AngularJS’s looser typing system.

Avoiding Re-render Pitfalls: Angular's Safeguards vs. React's Flexibility

AngularJS comes with an inherent safeguard when it comes to view updates. Its digest cycle ensures that model changes automatically propagate through the UI without much fuss. This built-in mechanism makes it harder to accidentally cause critical re-render mistakes.

In React, however, the rendering process is more explicit. It’s all too easy to introduce subtle bugs that cause unnecessary or even infinite re-renders if you’re not careful with state updates and component structure. For example, consider this pitfall:

// Pitfall: Unintended re-render loop in React
import React, { useState, useEffect } from 'react';

const PitfallComponent = () => {
  const [data, setData] = useState(null);

  // Without a dependency array, this effect runs after every render
  useEffect(() => {
    fetchData().then(newData => setData(newData));
  });

  return <div>{data ? data : 'Loading...'}</div>;
};

In this example, the missing dependency array in the useEffect hook causes the effect to run on every render, potentially leading to a re-render loop. AngularJS’s digest cycle, by contrast, would have managed updates more gracefully, reducing the likelihood of such pitfalls.

Another common mistake was the creation of inline functions inside render methods. Consider this scenario:

const ParentComponent = () => {
  const [count, setCount] = useState(0);

  // This inline function is recreated on every render
  const increment = () => setCount(count + 1);

  return (
    <div>
      <ChildComponent onClick={increment} />
      <p>{count}</p>
    </div>
  );
};

const ChildComponent = React.memo(({ onClick }) => {
  console.log("Child rendered");
  return <button onClick={onClick}>Increment</button>;
});

Even though ChildComponent is wrapped in React.memo, it still re-renders every time because the increment function is a new instance on each render. These kinds of subtle issues are less prevalent in AngularJS, where the binding system abstracts much of the re-render logic away from the developer.

We learned these lessons the hard way, and they became key drivers in how we structured our React components and state management strategies.

Tools That Saved the Day: Sentry.io

No matter how well you architect your application, bugs and unexpected behavior are inevitable. Integrating a tool like Sentry.io was a lifesaver for us. It allowed us to catch and resolve issues quickly, ensuring that our users had a smooth experience.

// Sentry setup in a React application
import * as Sentry from "@sentry/react";
import { Integrations } from "@sentry/tracing";

Sentry.init({
  dsn: "your DSN here",
  integrations: [new Integrations.BrowserTracing()],
  tracesSampleRate: 1.0,
});

This proactive monitoring became an essential part of our development workflow, catching errors that would have otherwise slipped through the cracks.

React: Easy to Start, Hard to Master

It’s true—React is incredibly easy to get started with. The "Hello World" example is just a few lines of code. But as our application grew, so did the complexity of our components, state management, and performance optimizations. The more we dug into React, the more we realized that truly mastering it takes time and continuous learning.

Final Thoughts

Looking back, our migration from AngularJS to React was filled with learning opportunities. We gained a deeper understanding of how important it is to lean on a strong ecosystem of tools and libraries, appreciate the benefits of strict typing with TypeScript, and recognize that while React’s simplicity is alluring, mastering its intricacies takes time.

Dylan Miyake
Co-founder of ClearPoint Strategy | MIT & Bowdoin Alum (Go U Bears)

Unlocking SQL Server Performance

Dylan Miyake — Thu, 06 Feb 2025 01:47:12 +0000

As an avid developer who’s always heads‑down in code and passionate about helping organizations execute strategy efficiently, I’m constantly on the lookout for tools that can help me optimize SQL Server performance. One tool that’s proven invaluable in this quest is SQL Sentry Plan Explorer.

Now, full disclosure: I’m not affiliated with SQL Sentry—not an employee, partner, or even a customer (SQL Sentry Plan Explorer is somehow completely free!). I’m simply a data and code geek who’s found that Plan Explorer cuts through the complexity of execution plans like a hot knife through butter.

In this post, I’ll take you on a deep dive into how Plan Explorer can help diagnose and resolve common query plan issues in SQL Server. We’ll explore real-world examples featuring index problems, key and RID lookups, scans versus seeks, and missing query hints. Whether you’re scaling an analytics application to serve thousands of customers or just trying to squeeze out a bit more performance, these insights should prove useful.

Understanding the Basics: What’s a Query Plan?

Before we jump into the specifics, let’s revisit what a query execution plan is. In SQL Server, every query you run is translated into an execution plan—a roadmap that shows how the database engine intends to retrieve your data. This plan includes operations like scans, seeks, joins, sorts, and lookups. Understanding this plan is key to diagnosing performance issues. That’s where SQL Sentry Plan Explorer shines: its intuitive visualizations make it easier than ever to pinpoint costly operations.

Common Query Plan Issues

1. Index Problems

Indexes are essential for query performance, but when they’re missing or misconfigured, performance can degrade significantly. Plan Explorer helps you quickly identify areas where SQL Server is performing full table scans rather than efficient index seeks.

Example: Missing Index Leading to a Table Scan

Imagine you have a table called Customers with millions of rows. Consider this simple query:

SELECT CustomerID, FirstName, LastName
FROM Customers
WHERE LastName = 'Smith';

If there’s no index on the LastName column, SQL Server may resort to a table scan—reading every row in the table—to find matches. In Plan Explorer, you might see an operator labeled “Table Scan” or “Index Scan” with a high cost relative to other operations.

Tip:

Create a Non-Clustered Index: Adding an index on LastName can transform that table scan into an index seek, significantly reducing the number of rows that need to be examined.

CREATE NONCLUSTERED INDEX idx_Customers_LastName
ON Customers (LastName);

After creating the index, rerun your query and examine the plan again. You should see a shift from a scan to a seek, reducing the cost and improving query performance.

2. Key Lookups and RID Lookups

Key Lookups

A key lookup occurs when SQL Server uses a non-clustered index to locate rows, but then must reference the clustered index (or the heap) to retrieve additional columns that aren’t covered by the index. This extra step can be costly, especially if it happens frequently in a query.

Example: Key Lookup in an Order Query

Consider the following query on an Orders table:

SELECT OrderID, OrderDate, TotalAmount, CustomerName
FROM Orders
WHERE OrderID = 10248;

Suppose you have a non-clustered index on OrderID, but it doesn’t include OrderDate, TotalAmount, or CustomerName. SQL Server will use the index to quickly locate the row for OrderID = 10248 and then perform a key lookup to fetch the additional columns.

In Plan Explorer, a key lookup operator might be flagged, indicating an opportunity for performance improvement.

Tip:

Covering Indexes: To avoid key lookups, you can create a covering index that includes all the columns needed for the query:

  CREATE NONCLUSTERED INDEX idx_Orders_OrderID
  ON Orders (OrderID)
  INCLUDE (OrderDate, TotalAmount, CustomerName);

This index helps SQL Server retrieve all required data from the index alone, bypassing the need for expensive key lookups.

RID Lookups

A RID (Row Identifier) lookup happens when a table doesn’t have a clustered index (i.e., it’s a heap) and SQL Server must locate a row using its physical address. Similar to key lookups, RID lookups can slow down query performance.

Tip:

Implement a Clustered Index: Converting your heap to a table with a clustered index can eliminate RID lookups. Identify a suitable column or set of columns that can serve as a unique and stable identifier, and create a clustered index on it.

  CREATE CLUSTERED INDEX idx_Orders_Clustered
  ON Orders (OrderID);

Once a clustered index is in place, SQL Server can more efficiently locate rows using index seeks rather than resorting to RID lookups.

Scans vs. Seeks: The Ongoing Battle

Understanding the difference between scans and seeks is fundamental for SQL Server optimization.

Table Scans and Index Scans

Table Scan: SQL Server reads every row in a table. This operation is expensive, particularly for large tables.
Index Scan: SQL Server reads through the entire index. While typically faster than a table scan, it’s still not as efficient as an index seek if only a subset of rows is needed.

Index Seeks

Index Seek: SQL Server uses the index to directly locate the rows needed for the query. This is the ideal scenario for performance.

Example: Scanning vs. Seeking in a Product Query

Consider the query:

SELECT *
FROM Products
WHERE ProductName LIKE 'A%';

Without an appropriate index, SQL Server might perform a scan. However, if you have an index on ProductName that supports range queries, SQL Server can perform an index seek.

Tip:

Optimize Your Queries: When possible, design your queries to take advantage of index seeks. Avoid leading wildcards in LIKE clauses (e.g., LIKE '%A') because they force SQL Server to scan the index or table.
Check Execution Plans Regularly: Use SQL Sentry Plan Explorer to quickly see whether your query uses a seek or a scan. If you notice scans where seeks are possible, consider modifying your indexes or query structure.

Missing Query Hints: When and How to Use Them

Query hints are directives you can add to your SQL queries to force a particular behavior in the query optimizer. While they should be used sparingly, they can sometimes lead to improved performance when SQL Server’s automatic choices aren’t optimal.

Example: Using NOLOCK

A common hint is NOLOCK, which tells SQL Server to perform a “dirty read” by not placing shared locks during the read operation:

SELECT *
FROM Orders WITH (NOLOCK)
WHERE OrderDate > '2020-01-01';

This hint can be useful in scenarios where data consistency isn’t a primary concern and you need to reduce locking contention. However, it should be applied judiciously since it can lead to reading uncommitted or inconsistent data.

Missing Index Hints

Sometimes, SQL Server might not pick up on an index that you know exists. In such cases, an index hint can force the optimizer to use a specific index:

SELECT OrderID, OrderDate
FROM Orders WITH (INDEX(idx_Orders_OrderID))
WHERE OrderID = 10248;

Tip:

Use Hints as a Last Resort: Rely on hints only after careful analysis, as they can sometimes lock your query into a less flexible execution plan that might not perform well as your data changes over time.
Don't Do This: OK, it's done a lot, and I've done it. But don't do this. I only included this example as it's one that so many people use. But if you're reaching for NOLOCK, think about refactoring your query, maybe using something like SNAPSHOT ISOLATION (but that's beyond the scope of this...)

Tips and Tricks for SQL Server Optimization

Here are some additional best practices to keep in mind when optimizing SQL Server queries:

Regularly Review Execution Plans:

Utilize SQL Sentry Plan Explorer to regularly review your query execution plans. Look for high-cost operations like scans, key lookups, or RID lookups. Identify any changes in patterns that might indicate a performance regression.
Monitor Index Usage:

Use SQL Server DMVs (Dynamic Management Views) to monitor index usage and fragmentation. This can help you identify whether an index is being used as expected or if it’s contributing to performance issues.
Maintain Statistics:

Ensure that statistics are updated regularly. Outdated statistics can cause the query optimizer to choose suboptimal plans.
Optimize Index Design:

Strike a balance between too many indexes (which can slow down data modifications) and too few (which can cause poor query performance). Regularly review your indexes and remove those that aren’t being used.
Consider Query Refactoring:

Sometimes a query can be rewritten to better align with your indexes. For example, breaking complex queries into simpler components or using common table expressions (CTEs) can sometimes lead to better execution plans.
Evaluate Hardware and Configuration:

Occasionally, performance issues stem from hardware limitations or configuration issues rather than query design. Ensure that your SQL Server instance is properly configured for your workload and that your hardware resources (memory, CPU, disk I/O) are adequate.
Leverage Community and Tools:

Engage with the SQL Server community through forums, blogs, and conferences. Tools like SQL Sentry Plan Explorer are developed and refined by experts who understand the intricacies of SQL Server performance tuning.
Automate Monitoring and Alerts:

Consider automating the monitoring of your SQL Server environment. Set up alerts for when query performance degrades, and use tools that can automatically capture and analyze execution plans.

A Real-World Scenario

Imagine you’re responsible for an analytics application (like ClearPoint Strategy) that serves thousands of customers on MS SQL Server. Over time, you notice that certain reports take longer to generate. You decide to use SQL Sentry Plan Explorer to analyze the query execution plans of your most frequently run queries.

Step 1: Identify the Bottleneck

In one of your queries, you notice a high-cost operator labeled “Key Lookup.” The query retrieves customer order information, and the key lookup indicates that the non-clustered index on OrderID isn’t covering all the required columns.

Step 2: Implement a Covering Index

Armed with this information, you create a covering index:

CREATE NONCLUSTERED INDEX idx_Orders_Covering
ON Orders (OrderID)
INCLUDE (OrderDate, TotalAmount, CustomerName);

After deploying this change, you rerun the query and inspect the execution plan again. SQL Sentry Plan Explorer now shows an index seek instead of a key lookup, and the query’s execution cost drops significantly.

Step 3: Optimize for Scans vs. Seeks

Another query that retrieves product information is performing an index scan instead of an index seek. You realize that the query uses a LIKE 'A%' predicate, and although there is an index on ProductName, the query optimizer isn’t taking full advantage of it due to the way the predicate is structured.

You consider adjusting the query or enhancing the index. By modifying the query to use a more sargable condition or adjusting the index design to better support range queries, you can coax SQL Server into using an index seek.

Step 4: Fine-Tune with Query Hints

In a scenario where the query optimizer is stubbornly choosing a suboptimal plan, you experiment with query hints. For example, you might try:

SELECT *
FROM Orders WITH (INDEX(idx_Orders_Covering))
WHERE OrderDate > '2020-01-01';

After thorough testing, you determine that while the hint improves performance for this specific query, it’s not a silver bullet for all queries. You document your findings, so that if the query pattern repeats, you have a proven strategy to refer to.

Conclusion

SQL Sentry Plan Explorer has become an indispensable part of my SQL Server optimization toolkit. Its ability to visualize execution plans in detail helps identify costly operations like index scans, key lookups, and RID lookups—insights that are critical when scaling applications to serve thousands of customers.

By understanding common pitfalls in query execution plans and employing best practices such as creating covering indexes, distinguishing between scans and seeks, and judiciously applying query hints, you can drastically improve the performance of your SQL Server applications. Regular monitoring, combined with the powerful features of Plan Explorer, ensures that you’re not caught off-guard by performance issues as your application scales.

Remember, performance tuning is both an art and a science. While tools like SQL Sentry Plan Explorer provide the technical insights, the true magic lies in interpreting those insights and making informed changes that align with your application’s unique workload.

Happy coding and optimizing!

—

Dylan Miyake

Co-founder of ClearPoint Strategy | MIT & Bowdoin Alum (Go U Bears)

Whether you’re a seasoned DBA or just starting out in SQL Server, I hope this exploration gives you the confidence and the tools to dive deep into your execution plans and emerge with a faster, more efficient system.