In my most recent post, I touched upon the importance of SQL Server Statistics by way of an analogy – in terms of comparing the importance of up-to-date statistics with the process of ‘sizing up’ which kinds of ‘retrieval’ mechanisms to use vs. the kinds of problems that can occur when incorrectly-sized retrieval mechanisms are used. In this post, however, I wanted to talk about the importance of statistics in another way – as they related to acting kind of like ‘road-signs’ that can help boost throughput through your system when it’s under heavy load.
Locking and Blocking Problems – Symptoms of Poor Architecture
As essential and as amazing as statistics are, no amount of them can overcome poor architectural or coding decisions that result in locking and blocking problems. Similarly, even the most powerful hardware available (in terms of raw power and capacity) can be rendered nearly moot when systemic problems stemming from locking and blocking problems are encountered – because these problems stem from logical/design flaws in code that cause hardware to, effectively, remain idle while concurrent operations ‘squabble’ about who gets exclusive access to which resource.
Yet, while statistics (and more powerful hardware) can’t correct locking/blocking problems stemming from poorly designed systems, they CAN ameliorate the impact of these problems – and, in some cases, even make them lie far-enough under the surface that they appear to go away. (Though they’ll crop up later when there’s greater load/concurrency on your system – so they’re not truly gone. Instead, it’s more like they’ve dropped out of sight and are lurking beneath the surface – ready to strike when you least want them to.)
Phoenix vs LyoN
For all intents and purposes, Phoenix is a ‘brand new’ city. Sure, it may have have been around for a long time, but it’s only started to seriously grow in the past few decades – which has allowed it to benefit from lots of city-planning and semi-controlled growth. Phoenix also benefits from being in a desert – where there’s just gobs of empty space that it can grow into and utilize for things like transportation.
Consequently, the few times I’ve been there, I’ve always been amazed at how uniform and efficient road signs, traffic signals, and basic navigational aids have been.
On the other hand, Lyon (in France) has been around since at least 43 BCE – and is sandwiched between the junction of two massive rivers.
It has, therefore, grown up ‘organically’ over literally millennia, and – as you might guess – it’s roads are quite a bit harder to travel and navigate compared to a city like Phoenix. Still, it’s NOT like Lyon doesn’t have any sort of city planning or engineering surrounding transportation – as it most certainly does. It also has road signs, traffic signals, and other navigational aids. But, if you’re solely looking at ease of navigation, Phoenix comes out as being much easier to transit.
Statistics as Traffic Signals in Highly Concurrent Environments
In many ways, locking and blocking problems are a lot like roads, traffic signals, intersections, and what happens when there’s a lot of traffic. And, in that sense, statistics end up being analogous to the role that traffic signals and signs can play in a busy intersection.
So, for example, imagine a really busy intersection of two major roads in a bigger metropolitan area – along with an intersection for an Interstate running over/under this same intersection and corresponding on/off-ramps looping in and out of this intersection.
When there’s not much load on the system, then statistics won’t be AS important – just as traffic signals and such won’t be AS important at, say, 3AM – because a slight slow-down here/there by the occasional query or vehicle isn’t going to be such a big issue.
On the other hand, now assume that it’s rush hour. Cars are packed everywhere. Then assume a truck driver needs to figure out how to get on the North-Bound lane of the interstate – only he/she is in 3 lanes of traffic approaching the intersection – and it’s NOT clear whether they’re in a situation where they need to be in the RIGHT lane or the LEFT lane to get to where they need to go.
In a case like this, where the road signs are lacking/crappy/etc. and the driver isn’t quite sure what they need to do WELL ahead of time, there’s a bigger potential for them to make a mistake in judgment – where they end up in the wrong lane and then have to ‘jump lanes’ to get where they need to be. And, again, IF this were 3AM? Not such a big deal. YEAH, the driver might curse a bit and have to make lane changes – but the impact of this problem wouldn’t really impact too many other drivers.
On the other hand, if they’re making an ‘emergency’ lane change across three lanes of backed up traffic in rush hour, then that’s going to throw a wrench in everything. Or, more specifically, everyone in those three lanes may have to wait a lot longer to get where they need to go – and that, in turn, can even have a cascade-effect.
Stated differently: architectural problems represent situations where developers have made poor decisions about how to interact with data – and while road-signs, traffic signals, and other navigational aides MAY help to decrease the number of ‘collisions’ that might occur during rush hour, the sad reality is that no amount of SIGNS will fix the problem – because there’s simply too much traffic being crammed into a tight spot. That, and once a collision, break-down, or pile-up occurs, everything will come to a standstill.
Still, up until the system fully breaks down, the role of traffic signs and navigational aids is that they continue to help ameliorate the underlying architectural problem – such that you simply can’t discount the importance of these navigational aids. And, that, in turn is a decent enough analogy of what kind of impact properly updated and defined statistics can do on a highly concurrent system – in that they CAN speed up individual operations and actions (sometimes by exponential amounts) which means that fewer ‘queries’ are in the same ‘intersection’ at the same time, which, in turn, decreases the potential for pile-ups, gridlock, and other ugliness. But, as beneficial as statistics (and/or accompanying indexes) can be in cases like this, they do not fix or address the underlying architectural issues. Instead, they act more like a ‘lubricant’ that ‘speeds’ operations through a tight-squeeze so that there’s a decreased potential for conflicts at lower levels of concurrency.
More Resources on Statistics
In my last post on statistics I posted some links to additional resources – and wanted to do the same here. Accordingly, Holger Schmeling has a great 2-part series on SQL Server Statistics – where most of that content has been refined and dropped into a great, free, eBook that you can access at the links listed below:
- Queries, Damned Queries and Statistics: A great overview of how statistics work and how they’re used by the Optimizer.
- SQL Server Statistics: Problems and Solutions: A look at some more advanced topics and details into how to deal with common problems that you may bump into with SQL Server Statistics.
- SQL Server Statistics: A link to Holger’s eBook (via Red Gate Books).
Photo Credits
Phoenix Road Signs
Montée de la Grande Côte (Lyon)
Traffic Headed Into Lyon
Locking and Blocking – French Rond Point
