A Fine Slice Of SQL Server
I can imagine someone reading that and not seeing the gravity of the situation. “Hey, 500 rows isn’t that many – we have modern hardware!”
I thought it was worth writing about a real world situation where this can get seriously out of hand.
Read on for a scenario in which 64 rows turns into 100,000 rows pretty quickly.
Comments closedMichael Bourgon notices a difference between Azure SQL DB and Managed Instance/box product:
I was trying to get some Xquery parsing working, and ran into a weird error:
"CONDITIONAL failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'"Weird. Doubly so because this code has worked on-prem for a decade, and the Managed Instance is running it now.
Read on for the solution.
Comments closedChad Callihan looks at buffer pool extension:
Perhaps you started out with X amount of memory when your SQL server was brought online and over time, with additional load and activity on that SQL server, users are not quite getting the type of performance they’re used to getting. Sure, you can buy more memory. What if that’s not an option?
If you’re running low on memory and need a little boost, enabling buffer pool extension can take advantage of an SSD as an “extension” for the buffer pool.
This is one of those interesting features that probably help a small number of customers but shouldn’t be generally useful. That’s because even with SSD performance improvements, memory is still a couple orders of magnitude faster, so as long as you have the ability to increase RAM, that brings much better performance.
Comments closedBob Dorr troubleshoots a performance problem:
When the temporary table is bound to the user transaction it is both created and destroyed as part of the transaction. The same logic in a procedure attempts to avoid the creation and destruction, for each execution, by using temporary table caching.
From the issue I was debugging, the user transaction scope mattered because creation and destruction of metadata may be an expensive operation.
This post ties into two separate things: first, how temp objects tie to specific sessions; and second, the cost of creating and destroying temporary objects. For the latter, a couple quick pieces of advice:
Amy Esselman re-designs a mess of a chart:
When faced with any unfamiliar but complicated graph, it can be helpful to think about it piece by piece to gain a better understanding of what’s being communicated. That way, we’ll have a better handle on how we can improve the overall visual.
The goal of this chart is to allow managers to compare their store’s performance against its forecasted range and the actual performance of other stores in the region.
Click through for the full process.
Comments closedLuis Bermudez takes us through the process of tuning one flavor of neural network:
We made our own implementations of OGB leaderboard entries for two popular GNN frameworks: GraphSAGE and a Relational Graph Convolutional Network (RGCN). We then designed and executed an iterative experimentation approach for hyperparameter tuning where we seek a quality model that takes minimal time to train. We define quality by running an unconstrained performance tuning loop, and use the results to set thresholds in a constrained tuning loop that optimizes for training efficiency.
Read on to see how they did it.
Comments closedErik Darling runs through an exercise:
The more columns you have in a table, the more potential column combinations there are for indexes. Much like columns, indexes tend to get added following the path of least resistance.
Very rarely does someone consider current indexes when deciding to add an index.
Erik’s process is a good one. The real pain comes when there are 40-50 indexes on a table (seriously…) and there are a lot of similar-but-not-quite-similar-enough indexes.
Comments closedWolfgang Strasser looks at Microsoft Purview:
I was ready for a nice relaxing evening today, when an email appeared in my inbox “Azure Purview is now Microsoft Purview!”
Initially I thought… yeah.. “just another Microsoft product name renaming” .. but when I read through it more in depth I found out, that this is NOT just a renaming.
Read on to understand what it includes.
Comments closedPrometheus is an open-source monitoring solution that our Linux team has been using for several years. More recently, we began using it for our Windows-based servers too. (I’ll post a writeup about Prometheus in the future)
One of the obstacles to implementing Prometheus monitoring on our Windows servers was finding and installing an agent. We ultimately decided to use the windows_exporter agent available in the Prometheus Community on GitHub. The exporter is free to use under an MIT license and supports an extensive list of WMI metrics that are grouped into Collectors.
Read on for more info, including ways to avoid common errors.
Comments closedPaul Andrew shares some deep thoughts:
For part 7 of this series, I want to explore what else could be delivered in our Azure Data Mesh if we continue our established thinking around the planes of interaction for our data products. As with part 6, we are still missing good Azure Resources that can deployed for certain situations. However, I want to frontload some concepts now, so we are ready if/when a suitable technical answer arrives in the cloud.
Note that this is all speculative. It’s interesting speculation, though.
Comments closed