However, the point of renaming the value was not to annoy you. The point of renaming the value was to call your attention to places where pointer truncation is likely to occur. In this case, it’s the final parameter, the original 64-bit window procedure. The build break is telling you that you are probably passing a 32-bit value as something that should be 64-bit. In this case, because it was being cast to (LONG). You are expected to upgrade the GWL_WNDPROC to GWLP_WNDPROC and at the same time upgrade the cast from (LONG) to (LONG_PTR).
Organizationsareincreasinglylookingforgreatercontroloverextensionswithindevelopmentenvironments. Driven by security,compliance,andinternalgovernancerequirements,teamswantmorevisibilityintohowdevelopersdiscoverandacquireextensions.
To address these needs, we’re excited to begin previewing Private Marketplace support in Visual Studio.
Private Marketplace for Visual Studio
For organizations familiar with Private Marketplace in VS Code,PrivateMarketplace in Visual Studio provides a similar curatedextensionacquisitionexperience whilepreservingthefamiliarVisualStudioworkflow.
WithPrivateMarketplace for Visual Studio,organizationscan:
TL;DR: Complex data flows are often difficult to understand when hidden in spreadsheets or traditional charts. See how a React Sankey Diagram transforms California’s energy data into an interactive visualization, helping you uncover flow patterns, energy losses, and key insights with validation, tooltips, responsive layouts, and export support.
Imagine you’re reviewing an energy report and trying to answer a simple question:
Where does the energy actually go?
You may have the numbers, but tables and traditional charts often make it difficult to follow how energy moves through the system. A bar chart can compare values, but it can’t easily show how those values split, combine, or eventually reach their destination.
This is exactly the type of problem that a Sankey Diagram solves.
A Sankey Diagram visualizes movement between connected stages. The width of each connection represents the quantity being transferred, allowing viewers to immediately identify major contributors, distribution patterns, and losses throughout a process.
In this tutorial, we’ll use a simplified California energy dataset to build a React Sankey Diagram with Syncfusion. While the data is intentionally simplified, the implementation approach is the same one you can use in production dashboards, reporting systems, analytics portals, or operational monitoring applications.
By the end, you’ll have a reusable React component that makes complex flow-based data significantly easier to understand.
What you’ll build
We’ll create an interactive Sankey Diagram that shows:
Energy originating from sources such as Solar, Wind, Nuclear, Geothermal, Biomass, Coal, Petroleum, and Natural Gas.
Energy flowing into Electricity Generation.
Distribution across Residential, Commercial, Industrial, and Transportation sectors.
Final allocation into Energy Services and Rejected Energy.
Source-colored links for easier visual tracking.
Hover tooltips and visible node labels.
A responsive layout that adapts to different screen sizes.
Built-in printing and PNG export options.
Data validation that detects:
Duplicate node IDs,
Broken source or destination references, and
Invalid flow values.
The final result isn’t just a demo visualization; it’s a foundation you can extend for reporting, operations monitoring, and analytics applications.
When a Sankey Diagram makes sense
A Sankey Diagram works best when the primary goal is understanding how something moves through a process.
Instead of focusing on comparisons or trends, it helps answer questions such as:
Where do the largest inputs come from?
How is a resource distributed?
Where do losses or bottlenecks occur?
Which paths carry the most volume?
For scenarios involving movement, allocation, or transformation of data, resources, or energy, a Sankey Diagram often communicates relationships more effectively than traditional charts.
Tip: If you’re comparing categories, use a bar chart. If you’re showing changes over time, use a line chart. Sankey Diagrams are most useful when the flow itself is the story.
Why use Syncfusion for a React Sankey Diagram?
You can build a Sankey Diagram from scratch using D3 or SVG, which gives you complete control over the visualization. However, that flexibility comes with the responsibility of maintaining the rendering logic, interactions, and responsive behavior yourself.
Syncfusion React Sankey Diagram offers a component-based alternative that helps you go from structured data to a production-ready visualization much faster. It includes built-in support for labels, legends, tooltips, responsiveness, printing, and exporting, reducing the amount of code you need to build and maintain.
Choose Syncfusion when:
You want to quickly transform data into a reusable dashboard component.
Your application already uses Syncfusion React components and benefits from a consistent look and feel across the UI.
You prefer a ready-made solution that includes common visualization features out of the box.
A custom D3 or SVG implementation may be a better fit only when your application requires a highly specialized layout or unique interactions that are not available through the component API.
Prerequisites
Before you begin, make sure you have Node.js installed, an existing React project, and a basic understanding of React and TypeScript.
Visualizing California’s energy flow pattern using a React Sankey Diagram
Now that the project setup is ready, let’s start building the diagram itself.
We’ll use a simplified California energy dataset to keep the example focused on the implementation rather than the data. Along the way, we’ll define the data structure, add a validation layer to catch common mistakes, and then render the complete Sankey Diagram with labels, tooltips, legends, printing, and export capabilities.
Step 1: Install the Syncfusion package
To get started, install the Syncfusion React Charts package:
npm install @syncfusion/ej2-react-charts --save
This package includes the React Sankey Diagram component along with supporting services for features such as tooltips, legends, and exporting.
Step 2: Understand the Sankey data model
Before moving into the chart configuration, it’s helpful to understand how Sankey data is organized.
Every Sankey Diagram is built from two core pieces:
Nodes represent the entities in your system.
Links represent the amount moving between those entities.
In our example, energy sources such as Solar and Natural Gas are nodes. The energy flowing between those sources and other stages in the system is represented by links.
Once you understand these two building blocks, creating and troubleshooting Sankey Diagrams becomes much easier.
Each node must have a unique id (string). You can also use offset (number) to adjust its vertical position and color (string) to customize its appearance.
Each link connects two nodes and requires a sourceId (string), targetId (string), and value (number). The source and target IDs must match existing node IDs, while the value determines the thickness of the link.
Refer to the following image for a better understanding.
Sankey Diagram nodes and links visualized as connected stages of flow
Important: Every sourceId and targetId must match a node id exactly. The strings are case-sensitive, so “Natural Gas” and “natural gas” are treated as two different node references.
Step 3: Prepare the California energy-flow dataset
For this tutorial, we’ll use a simplified version of a California energy-flow model.
The dataset is intentionally structured to mirror how energy moves through a real system. Energy starts at primary sources such as Solar, Wind, Petroleum, and Natural Gas. Some of that energy passes through Electricity Generation before reaching end-use sectors such as Residential, Commercial, Industrial, and Transportation. Finally, the flow is divided into useful output and energy losses.
This structure provides sufficient complexity to demonstrate how Sankey Diagrams handle branching paths, aggregation points, and losses without introducing unnecessary noise in the example.
Dataset note: The values used here have been simplified for demonstration purposes. Lawrence Livermore National Laboratory (LLNL) publishes detailed state-level energy-flow charts. If you’re building a production solution, replace these values with official data and reference the appropriate chart year.
If you’ve ever spent time debugging a chart that refuses to render, you already know that data issues are often the culprit.
A single typo in a node ID, a missing reference, or an invalid value can silently break the visualization. These problems are easy to introduce, especially when chart data comes from APIs, spreadsheets, or transformed datasets.
To avoid that frustration, let’s add a small validation step before rendering. The following helper function checks for:
However, it does not replace full data-quality checks, such as:
Balance validation,
Circular-flow detection, or
Source-data auditing.
But it does catch the kinds of mistakes that commonly lead to broken visualizations and unnecessary debugging sessions.
Step 5: Render the React Sankey Diagram
With the dataset prepared and validated, we can finally render the diagram.
The component below brings everything together. It validates the dataset, renders the Sankey Diagram, enables tooltips and legends, and adds export and print actions, allowing the chart to be used in both interactive dashboards and reporting scenarios.
import React, { useRef } from 'react';
import {
SankeyComponent,
SankeyNodesCollectionDirective,
SankeyNodeDirective,
SankeyLinksCollectionDirective,
SankeyLinkDirective,
Inject,
SankeyTooltip,
SankeyLegend,
SankeyExport
} from '@syncfusion/ej2-react-charts';
// Include the EnergyNode, EnergyLink, validateSankeyData, nodes, and links
// declarations from the earlier sections in the same file, or import them
// from a separate data module.
export default function CaliforniaEnergySankey() {
const sankeyRef = useRef<SankeyComponent | null>(null);
const validationError = validateSankeyData(nodes, links);
const exportAsPng = () => {
sankeyRef.current?.export?.('PNG', 'california-energy-sankey');
};
const printChart = () => {
sankeyRef.current?.print?.();
};
if (validationError) {
return <div role="alert">{validationError}</div>;
}
return (
<section
role="region"
aria-label="California energy flow Sankey chart"
aria-describedby="california-energy-sankey-description">
<p id="california-energy-sankey-description">
This Sankey chart shows energy moving from primary sources through
electricity generation and end-use sectors into energy services and
rejected energy. The values are simplified for this tutorial.
</p>
<div style={{ marginBottom: '12px' }}>
<button type="button" onClick={exportAsPng}>
Export as PNG
</button>
<button
type="button"
onClick={printChart}
style={{ marginLeft: '8px' }}
>
Print
</button>
</div>
<SankeyComponent
ref={sankeyRef}
id="california-energy-sankey"
width="100%"
height="560px"
title="California Energy Flow Example"
subTitle="Tutorial dataset adapted from LLNL energy-flow concepts"
linkStyle={{
opacity: 0.65,
curvature: 0.55,
colorType: 'Source'
}}
labelSettings={{ visible: true, fontSize: 12 }}
tooltip={{ enable: true }}
legendSettings={{
visible: true,
position: 'Bottom',
itemPadding: 8
}}
>
<Inject
services={[
SankeyTooltip,
SankeyLegend,
SankeyExport
]}/>
<SankeyNodesCollectionDirective>
{nodes.map((node) => (
<SankeyNodeDirective
key={node.id}
id={node.id}
offset={node.offset}
color={node.color}/>
))}
</SankeyNodesCollectionDirective>
<SankeyLinksCollectionDirective>
{links.map((link, index) => (
<SankeyLinkDirective
key={`${link.sourceId}-${link.targetId}-${index}`}
sourceId={link.sourceId}
targetId={link.targetId}
value={link.value}/>
))}
</SankeyLinksCollectionDirective>
</SankeyComponent>
</section>
);
}
Refer to the following image.
Visualizing California’s energy flow data using the React Sankey Diagram
Step 6: Customize the Sankey Diagram for better readability
At this point, the chart is functional, but a few small adjustments can make it much easier to read, especially as the dataset grows.
For production use, consider the following improvements:
Reduce node overlaps by using the offset property to adjust the vertical position of closely placed nodes.
Apply source-based link colors by setting colorType to Source. This helps users follow flows more easily. The opacity and curvature properties control link transparency and shape.
Keep labels visible so values and flow paths remain easy to understand. Enable tooltips to display additional flow details during interactions and add a legend below the diagram to help users identify categories and node colors.
In addition to improving readability, you can make the chart easier to share and analyze by enabling printing and export capabilities. Use the Sankey component reference to export the diagram as a PNG image or open the browser’s print interface. Ensure that SankeyExport is injected into the component.
Note: JPEG, SVG, and PDF export formats may also be available. Verify support in the specific Syncfusion package version used by your application before enabling it in production.
Step 7: Load data from JSON or an API
The sample data in this tutorial is hardcoded for simplicity, but most production applications load chart data from a service or API.
A good practice is to keep the visualization component separate from the data retrieval logic. Fetch the data, validate it, and render the chart only after it’s ready.
type EnergyFlowResponse = {
nodes: EnergyNode[];
links: EnergyLink[];
};
async function loadEnergyFlowData(): Promise<EnergyFlowResponse> {
const response = await fetch('/data/california-energy-flow.json');
if (!response.ok) {
throw new Error('Unable to load energy-flow data.');
}
return response.json();
}
If you’re working with remote data, consider adding:
A loading state while data is being fetched.
An error state when requests fail.
Validation before rendering.
Memoization for larger datasets to avoid unnecessary recalculation during re-renders.
These small additions can make a noticeable difference in performance and maintainability as the application evolves.
Common issues and fixes
Most Sankey chart issues are typically related to data quality, component configuration, or sizing.
Missing links or tooltips: Verify that node references match exactly and that all required services are injected.
Overlapping or clipped content: Increase the chart or container height, and adjust node offsets, label size, padding, or overflow styles.
Inconsistent totals: Validate input and output values at each stage of the flow to ensure data accuracy.
Blank chart in hidden tabs: Refresh the chart after the tab becomes visible.
Print or export not working: Confirm that the component reference is available before calling print or export methods.
Once your chart is working as expected, it’s important to ensure that it’s accessible to all users.
Accessibility considerations
Keep labels visible whenever possible to help users understand the flow of data.
Avoid relying solely on color to communicate meaning, as some users may have difficulty distinguishing colors.
Provide a tabular view of the data alongside the chart to improve accessibility and make the information easier to consume.
Other use cases
Beyond energy data, Sankey Diagrams are useful whenever you need to explain how something moves through a system.
For example:
Tracking users through a signup funnel.
Visualizing API request routing across microservices.
Understanding cloud cost allocation across teams.
Mapping warehouse inventory movement.
Showing how support tickets flow between departments.
Analyzing ETL pipelines and data movement.
A common real-world example is cloud cost reporting. Teams often know their total spending but not how it is distributed across services, environments, and business units. A Sankey Diagram makes those relationships obvious within seconds.
Frequently Asked Questions
How do I debug missing links in a React Sankey Diagram?
Ensure that every sourceId and targetId matches a valid node id. Because node references are case-sensitive, even minor spelling differences can prevent links from rendering.
How do I export a React Sankey Diagram?
Create a component ref and call the export() method. Also, ensure that the SankeyExport service is injected into the React Sankey Diagram.
Can I use API data in a React Sankey Diagram?
Yes. Load the API data, map it to nodes and links, validate it, and then render the React Sankey Diagram after the data is ready.
Is the California energy-flow data used in this React Sankey Diagram official?
No. This React Sankey Diagram uses a simplified sample dataset. For production use, replace it with official data and cite the original source and publication year.
Turning complex data flows into visualizations people can actually understand
Building a Sankey Diagram isn’t just about creating a more attractive chart. It’s about helping users understand how resources, transactions, requests, or energy move through a system.
In this example, we used California energy data to visualize how energy is generated, distributed, and ultimately consumed or lost. The same implementation pattern works for business analytics, cloud-cost reporting, API traffic analysis, logistics tracking, and many other scenarios where understanding movement is more important than comparing static values.
If you’re already using Syncfusion in your React applications, the Sankey Diagram component offers a practical way to add interactive flow visualizations without having to maintain custom rendering logic. You can explore additional capabilities in the latest version of Essential Studio® through the License and Downloads page or try the components with a 30-day free trial.
In my free How to Think Like the Engine class, I gloss over the entire concept of B-trees and just hand-wavily say “SQL Server can seek to the page that has the row you’re looking for.” That’s always kinda bothered me, so I finally gave in to temptation and whipped up an animated example of a clustered index on the Users table:
Every index has a single 8KB page at the top of the B-tree, and it’s kinda like the table of contents.
It annoys the bejeezus out of me that every B-tree diagram I’ve ever seen is always shaped like a pyramid, with the “root page” at the top. Do database people ever actually touch grass?!? The root is down deep in the earth, party people. It’s not called a B-pyramid. Whatever. I didn’t wanna fight convention and I just stuck with it, with the root at the top.
In this case, the root page is in data file #1, and it’s the 100th 8KB page in the file, thus the label 1:100. SQL Server pages are always labeled like that – and the rest of the pages in the diagram all start with 1: for that reason. You can tell this index lives in the 1st data file, and that our database (like most) probably only has one data file, since everything is in file 1.
When our query runs, and SQL Server decides to use this index to get the data, the first thing it has to do is open up that root page. We’re looking for Id 12345, so SQL Server looks at the root page to ask, “Which leaf page has row 12345?” The root page itself doesn’t know that in this case – although it could, in much smaller tables – but here, we’ve got enough rows that the root page needs to point to other directory pages.
The root page knows that if it’s looking for Ids 1-500,000, that’s on page 1:200. In our case, those numbers are nice and round, but they would never be that round in real life, because your tree isn’t going to be this balanced, and you can fit oddball numbers of rows on pages. More on that over the coming weeks. (Don’t wanna overwhelm you with diagrams today.) So our process pops over to page 1:200 in order to keep tracking down our row.
The Intermediate Pages are Like Tour Guides.
The root page, like all the other pages, is only 8KB. It can’t fit a complete directory of the ranges of every value on every 8KB page in the Users table, all on one 8KB page. You can only write so small on a grain of sand. So rather than pointing directly at the specific 8KB data page that holds Id 12345, it has to point at another directory page, called an intermediate page.
The middle two sets of pages on our fancy diagram are intermediate pages, which serve as further directories. “Oh, you’re looking for ladies’ intimates? No judgment, sir, head over in that direction over there. Yes, yes of course it’s a gift, no need to explain.”
The more data pages your object has, and the fewer rows you can fit on each data page, the more levels of intermediate trees you’re going to need in order to go from the root page down to the data page. These page reads are considered part of your logical reads: while they’re cached in memory, SQL Server has to keep popping them open for every seek that it does. This is why you’ll see a single row index seek take a whole handful of logical reads, sometimes more, on larger tables: it takes that long to traverse the tree.
The Data Page is the Finish Line.
Finally, on intermediate page 1:411, we’re able to see that rows from 12,001 to 16,000 are stored on page 1:301, and we’re able to open the page that actually has the data we’re looking for.
The pages with the actual data are called leaves, and the leaves are at the bottom of the diagram, on the ground, because they’re computer science professors, not biologists. Or human beings.
Nonclustered Indexes Work the Same Way
Say we have a nonclustered index on the Location column, and we wanna find the people who live in Helsinki:
Now instead of looking for a particular number, we’re looking for a string, and these strings are organized in alphabetical order, so essentially it works the same as looking for a number. Helsinki is alphabetically between Geneva and Jakarta, so the intermediate tour guide pages direct us towards page 1:931.
Here’s where things get a little tricky: note that on the data pages, SQL Server isn’t storing the pages where the two people in Helsinki live. Instead, it lists their Id values. Our table has a clustered index on Id, so now when we want to do a key lookup, we’re going back to the top of this blog post, and we’re doing a clustered index seek on their Ids.
Essentially, an index seek + a key lookup = two index seeks: one on the nonclustered index, and one on the clustered index for the key lookup. Each index (the clustered and each nonclustered index) has its own root page and intermediate pages.
You might be thinking, “Wait, couldn’t we save a lot of time by, on the nonclustered leaf pages, saving the file_number:page_number where the corresponding clustered index row lives?” Well, you could save time on reads, but then you would spend a lot of time updating all of the indexes whenever we had to move a clustered index row around from one page to another. More on that in another post.
This Animated Diagram Has a Lot of Implications.
For example, look at the animation again, this time focusing on the page numbers. They’re scattered all over the file, and that’s completely fine. There’s no reason whatsoever that all these pages have to live next to each other in the data file. The index pages up at the top are very likely cached in RAM anyway, and RAM isn’t short for RAMEN. The R has a meaning, dear reader. Computers don’t need these pages next to each other in order to access them quickly.
This isn’t the only way to access the leaf pages, either. If you know you’re gonna hit all of them, you can start at either end, and read from one leaf page to the next. The leaf pages are doubly linked lists: every page has a pointer to the previous page and the next page, links if you will, so that you can keep paging down through the table, scanning it, without dealing with the intermediate pages.
These pointers also help when you need to read ranges of data. For example, if you want the Ids between 9500 and 13800, you can do a seek to 9500, and then jump from page to page without going back up the tree:
You’re gonna love my Fundamentals training classes. In Fundamentals of Index Tuning 2026, I use animations like these to explain composite indexes, page splits, index reorganization & rebuilds, b-tree hotspots, and more. The live enrollments are closed now because the classes are underway (literally, I’m teaching Fundamentals of Query Tuning today), but you can stream the recordings for $495.
And if you’re already a subscriber, good news: the updated Fundamentals of Index Tuning 2026 is already watchable in your account! Get in there and take advantage of the training you paid for. Or your manager paid for. Or your last manager, if you’re the kind of person who steals staplers from the office, which I wholeheartedly endorse.
Erik Darling here with Darling Data, the one and only, the original monitoring tool mogul, just kidding, that doesn’t make any sense. In today’s video, we are going to finish up talking about the query align and design thing series of videos by talking about missing index requests. Before we get into anything else, this is where I get to share a funny story with you.
In one of my consulting engagements many years ago, I was talking to a developer about indexes. And they said, Erik, I’m going to be honest with you. And I said, this is the first time for everything, isn’t there?
And they said, I don’t really know anything about indexing. I just follow whatever the missing index requests say. And I was like, back then I thought, oh, so you just follow whatever the computer says, and now you don’t know anything about this important thing. And I was like, wow, that’s just like today.
Anyway, it’s a good thing you all got AI ready with me, so now you don’t have that problem. But in today’s video, we’re going to talk about how missing index requests will often leave much to be desired. We’re not going to do all of the content because this is, of course, a video.
It’s a small snippet from the larger Learn T-SQL with Erik course, which if you would like to buy that course, if you think this material might be worth your time and money, aside from these free passing moments on YouTube, you can purchase the course down in the video description.
There’s a coupon code on there for $100 off. I don’t know. Saving $100 is kind of cool. But there are also other links. You can hire me for consulting.
Perhaps you would like me to look at your missing index requests and make fun of them. Or give you better missing index requests, which I can do. I am capable of that. But instead of offloading that to the robots, you would be offloading that to me.
But at least I’m happy to teach you about these things. You can also buy my other training, become a supporting member of the channel for as low as $4 a month. If you just like this material enough to say thanks here and there for $4, you can do that.
You can also ask me office hours questions. And of course, as always, please do like, subscribe, tell a friend. Tell a family member.
I don’t know. If you think this content just absolutely sucks, tell your worst enemy and drag them down into the abyss with you. If you would like a free SQL Server performance monitoring tool, I have one.
Offer one. It is at my GitHub repo. The link is down again in the video description. Absolutely free, open source.
All of the performance monitoring metrics that you would ever care to have in front of you. And give your robot friends. A way to look at, because again, we are offloading everything to the machine. So let the robots figure it out.
And then you, I don’t know, I guess go do stuff. I mean, when you’re watching this, I will be at Data Saturday Croatia like tomorrow basically. So that’s fun.
I think anyway. But I’m not there now. I’m at home now recording this ahead of time so that the content flows while I’m traveling. But after that, I will be home for a bit and then I will be in Seattle in November.
So yeah, again, up in the air on how we’re going to fill that time. We might need to think of some things to do. But now it is time to continue losing our summer minds in this summer heat and talk about missing indexes.
So on missing indexes, generally, if you’re looking just at a query plan, you only see one up at the top. But there might be many. The impact number that you see is an estimate in how much it will reduce another estimate.
And that is a cost. So the impact number is really an estimate of an estimate. It’s a very, very fuzzy number.
Uses is more based on the plan cache than anything else, like actual queries using something. And the thing to keep in mind here is that they are quite opportunistic and they are not very thoughtful. Missing index requests occur as part of the process.
They are part of query optimization. And if you let the optimizer spend a long time thinking about indexes during query optimization, you would probably be unhappy at how long it takes for the optimizer to produce a query plan for you. So the missing index requests are a bit like a dating game where the optimizer is like, well, I really want an index that’s six feet, makes six figures, and has a six pack of Miller Lite Natty Ice.
Steel Reserve. But I don’t know. So it just figures out, well, I have this index, but I would prefer this index.
So it’s almost like a little bit of infidelity there, if you ask me. So you just really don’t want it to take a long time to do that. And if you see a missing index request, the best way to validate it is to run the query that is requesting it, get an actual execution plan, and then look at, if the slow part of the query plan lines up with the index that SQL Server is asking for.
In a lot of examples that I go through in my Stack Overflow database, SQL Server will ask for a missing index on like a scan of the users table, which is only a couple million rows.
And you would go from like, let’s say, 100 milliseconds to like 20 milliseconds. It’s not a big meaningful difference. You want to make sure that that index takes some meaningful chunk of time out of your query by being there.
Because indexes, they need to pay for themselves a little bit. Because once you create an index, now that index is costing you in transaction log space, in space in the buffer pool, in writes, in locking.
So there’s all sorts of costs to having that index, and you want to make sure that that index pays for itself by making queries faster. But there are some issues with missing index requests.
And one of them is that they don’t really understand selectivity at all. What you get from them is key columns based on the WHERE clause. And to some extent, like the other things, but really the key of the missing index request is just like the WHERE clause stuff that you see.
And then like anything else just goes in the includes as a mishmash. It doesn’t matter if you’re joining, sorting by it, grouping by it. It does not, SQL Server does not pay much attention to that.
Equality predicates will always go first in the missing index request, and inequality predicates will always go second. Anything else ends up in the includes. The order of columns within the equality and inequality predicate chunks, or groups, some people might call them, has nothing to do with the current predicates at all.
It just orders them by each column’s ordinal position in the table. There is a great question by a fellow named Brian Reebok, who I haven’t spoken to in a while, but I hope he’s doing well, over here.
If you ever feel like reading it, just click that link on your screen with your forehead. It’s really hard. Just click it with your nose or something, right?
You can do it. But by ordinal position, I mean this. When you create a table, the order that you create, the order that you list off your columns in, SQL Server, that is the ordinal position in the table.
So like in the POST table, these are the ordinal positions. It would make a lot of sense if I just said c.columnId. I typed it right, there we go.
If I did this, then you would see this is the ordinal position. So SQL Server will order the columns in your equality and inequality predicates by this, not by how selective anything is.
So sometimes that might work out in your favor. Because if we run this, answer count of 518, one row has that. POST type ID 1 has 6 million rows.
So if we look at the execution plan, there is indeed a missing index request. And answer count comes first, qualifies for one row. And if we created this index, we would be able to seek right to that answer count and then find the POST type ID associated with it.
So that would be cool. That would be a very efficient seek. Seeking through 6 million rows, maybe a little bit less so. You don’t know, right?
You never can tell. Other times, that might not pay off. For example, if we asked for a POST type, a parent ID of 0, which has 6 million rows, and a POST type ID of 8, which has 8 rows, SQL Server will say, Hey, give me a missing index on parent ID and then POST type ID.
Which is, again, not what we would want, right? Because if we were designing indexes, we would most likely want to have the more selective predicate out in front.
Now, this missing index request, I believe, because we have, again, we have two inequality predicates here, one on creation date and one on score.
So these are going to be grouped together. Before, we had inequality predicates, so those were grouped together. But now, these inequality predicates, this is obviously nonsense. And the reason why I say that is because not a whole lot of POSTs have a score greater than or equal to 25,000.
But every single POST in the POST table is between 2,000, 7,000, 1,000, 1,000, and 2,013, 1,231. At least in my copy of the Stack Overflow database, it is where the world ended on January 1st of 2014.
There are no POSTs beyond that date. So every single POST in this table is between those dates. Almost no POSTs have a score of 25,000 or greater than or equal to, right?
So if we look at the execution plan, we again have a missing index request. But SQL Server has said, No, give it to me on creation date and then score. Well, okay.
Let’s do what SQL Server asks. Let’s add this index on creation date and then score. So that’s what we’re doing. Here we’re also going to include owner user ID so we don’t have to deal with anything else.
I can’t remember if owner user ID was in the includes up there. I didn’t pay that much attention. But with that index in place, if we run these three queries, what we will see are three execution plans.
And none of these take terribly long. But notice that we scan through all of these. They are parallel plans.
And each one, let’s just say, they take close enough to 500 milliseconds across the board. Is this a particularly good strategy? I don’t know. Because we read 17 million rows from the table.
Because our leading key of the index, our initial seek predicate, was everything. And then we have this residual predicate come back to me, my love, where score is greater than or equal to either 5,000 or 10,000 or 25,000.
And all of those numbers are more selective than the entire creation date range in the table. But again, this is stuff we don’t want the optimizer thinking about at run time.
This is not something we’re like, optimizer, but this is a huge date range. We don’t want it thinking about those things. We want it thinking, come up with a good query plan. All right.
So, but that’s where we come in, right? Where we’re going to create, we’re going to reverse that index and we’re going to make one on score and then creation date, right? Because we are going to evaluate our data and our query.
And we’re going to say, I think our predicate on score is way more selective. And we’re going to be right, aren’t we? Are we ever wrong?
No. If we were ever wrong, it would be a terrible demo. Or I don’t know, maybe it’d be a very amusing demo for you. But now when we run these three queries, which are identical to the previous three queries that we ran, we will get much tidier, much more efficient query plans that seek and seek and seek.
And these all take zero milliseconds. Why? Because we sock to a smaller range of data first, and then we applied our range predicate, our larger range predicate second.
So when it comes to missing index requests, there are a lot of things for you to watch out for and be careful for. If anything, don’t take them literally. There are many other, there are many things where, you know, you should maybe not take them literally, but maybe take them as sort of like an instruction or something where you might say, hey, SQL Server is throwing a missing index request.
Perhaps there is something I should pay attention to here. Perhaps it is trying to hint me towards something. What’s that, Lassie? Timmy’s in the well? You go over there and like, you know, I don’t know, maybe Timmy’s in the well.
Maybe he’s, maybe Lassie’s just messing with you because Lassie knows every time she, she barks, you go and look in the well and she’s like, it’s funny to me. Dogs, you know, what are you going to do? Anyway, lots of things to take as a grain of salt with missing index requests.
Again, you may use them as a hint or an indicator that something about your query, the indexing for your query could be improved, but there are many things that you need to be careful of and many things that you should know about indexing before you go and create those missing index requests.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I will see you next Tuesday. I should stop saying that for office hours.
Hopefully I make it home alive from Croatia and all that stuff, you know, air travels. It’s been a little weird lately. But anyway, thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Say you need outside help. You’ve exhausted all the things that you could do and, still, nothing works. So, you decide that calling in for help is the next sensible thing to do. Let’s say this is the first time you’re asking for somebody’s help. You decide which consultant to work with. The hardest part, if you’re doing this for the first time, is how to start that conversation.
The first conversation is the most critical one. Obviously, you cannot just turn over your SQL Server instance and wait for the bill. Some prep changes what you get out of that first conversation. It means spending less time covering the basics and more time focusing on the problem that made you pick up the phone in the first place.
Here’s what’s worth having ready before you make the call.
(And, not incidentally, if you are a new SQL Server DBA hire, you can use the following to get a baseline understanding of the environment you’re now facing.)
Determine what’s wrong with your server
Here’s the thing. Your consultant is a highly technical person, but you don’t always have to speak in technical terms. Explain the pain points from the users’ perspective. Is the UI slow? Are users experiencing timeout issues? Is a button not responding in a timely manner? Look at the problem through the users’ eyes and understand their pain.
An important thing to take note of is what feels slow or broken. Is it specific like a report? A batch job that has been failing to meet the SLA? Is it the whole application? Ingestion pipeline failing to finish at all?
Another important thing to take note of is the timeline. When did this start happening? Is it after a vendor update? Is it after releasing a new ingestion tool? After a SQL patch? After a migration? After a new hire?
Also, I’d add who is feeling the pain and who is affected the most. Maybe it’s one or two people in the Accounting department. Maybe it’s the CFO trying to run a quarterly report. Or perhaps it’s your customers or partners who rely on access to your data outside your company.
That gives the consultant some context to work with.
You know your business better than any consultant walking through the door. That context is where a good diagnosis starts.
Understand your data landscape
You don’t have to know every nook and cranny of your SQL Server environment, whether it’s running on-prem or in the cloud. But you should have a baseline understanding of how things are put together.
Things like SQL Server versions and editions, if you know them, can provide useful information about your environment. Have an approximate idea of how many servers are affected, or maybe the percentage compared to the total number of servers. Is the issue only happening on your on-prem servers, or are your cloud instances affected as well?
Those are a few important things for the consultant to know as a starting point.
One thing worth checking first
If you check nothing else, check this: do your backups actually work? Or do you even have the right backup strategy in place?
If you don’t have RPO and RTO defined (or worse, don’t know what those mean), put that at the top of your discussion list with your consultant. This is one of the most important things you need to get right.
Not “is the backup job running?” That’s not the question. The real question is whether you have ever restored a backup and confirmed that it actually works.
A backup that has never been tested is just a hope, not a safety net.
Okay, somebody said that before. I’m just borrowing it here because it perfectly describes why testing your backups matters.
If you’re not sure, that’s okay. That’s exactly the kind of thing you want to identify early. And honestly, it’s one of the first things I would want to understand as well.
Sort out access and ownership
Well, how can the consultant help if they don’t have the access level needed to do the work or accomplish what you brought them in to do?
Nothing slows down a first week like waiting on permissions. Before you bring anyone in, it helps to know who owns the SQL Server environment today (sa is the wrong answer). Who can grant the access they need? Does it require a chain of approvals from higher-ups? Or is it an open-for-all kind of thing? The consultant needs to know if it’s the latter.
If you’re on a tight schedule or everything is already on fire, you don’t want to delay troubleshooting and resolution just because of access issues.
If you’re technical, go deeper maybe
If you or someone on your team is hands-on, here’s the bigger picture a DBA would typically look at. None of this is required. It is just useful information to have if you can pull it together.
Hopefully, Query Store is enabled. And if you’re lucky, it has enough history captured that you can correlate the data with the actual issues you’re seeing.
Disk latency, specifically read/write latency, is another useful piece of information if you have it. Ideally, if you have results from CrystalDiskInfo or a similar disk benchmark, that can give the consultant an idea of what they’re dealing with when it comes to storage performance. Maybe storage is part of the problem.
Is TempDB configured according to known best practices? Is Instant File Initialization enabled? What about Lock Pages in Memory? How are MAXDOP and Cost Threshold for Parallelism configured?
What does the disk subsystem look like? Where are your data and log files stored? Are your system databases sitting on the OS drive? How much memory is allocated to SQL Server?
These are the kinds of details that can help a SQL Server consultant understand the environment before they start troubleshooting.
You may also want to run tools like Brent Ozar’s sp_Blitz or Glenn Berry’s SQL Server Diagnostic Queries. If those don’t mean anything to you, that’s okay. These are just examples of tools that can provide useful information to a SQL Server consultant.
Just be prepared
The best thing to remember is this: be prepared.
The better prepared you are, the less time is spent having someone learn your environment, and the sooner you can get to the actual problem.
Whether you work with an outside consultant or someone from your own team, walking in with your symptoms, your environment details, and the information you already have gives everyone a much better place to start.
Log in
The middle two sets of pages on our fancy diagram are intermediate pages, which serve as further directories. “Oh, you’re looking for ladies’ intimates? No judgment, sir, head over in that direction over there. Yes, yes of course it’s a gift, no need to explain.”
