Question: What is the performance difference between rebuilding indexes with parallelism set to 1 and…
Some 15 years ago, way before I even thought of data and all questions that come with it, I read a book called ”Travels with Charley”. Long story short – in the book the author describes his travels with his companion – a French poodle. A part of this book which I distinctly remember is the episode where the author and the dog go to the Sequoia National forest and the dog refuses to acknowledge the giant trees and seems to be unwilling to ”salute” them (as the author remarks). And yes, the trees are so big that one can hardly see that they are trees.
Years later, after I started working as a developer (this is what a developer does: moves data around, with or without modifying it), I realized that the data is not only an endless amount of letters and numbers; it is more than that. Data is what you make out of it.
Nowadays, the data is growing fast and I see so many companies wondering what to do with it. Many people do not understand the line between archived data, analysis data and current ”hot” data. Furthermore, only a few people understand the performance impact of data residing on the wrong type of RAID, the data not being partitioned properly, and the data being indexed poorly (either too much or not enough).
About 10 years ago things were different: the question back then was ’how to save the data?’. Today the question has changed: ’How do we save the data so we can make use (and sense) of it?’ And it is true: as the technology has improved tremendously, the ideology behind the logistics is following a few steps behind.
We often take walks in the database forest, me and my friend Barley (a fictional character, of course, for the sake of this blog post 🙂 ). And most of the walks are like a visit of an enchanted forest with dangers, monsters and shadows…
Think about the design: in some forests (databases) one cannot even walk in a straight line (in any direction) without a machete. Depending on the design, one might not even find the right direction and end up walking in a circle for a very long time. It is very hard to get through it without getting yourself exhausted. And if you add to that the fact that the data nowadays can be the size of a baobab tree, cutting it with a machete might not be feasible.
Think about the T-SQL design: it is like a map of the forest, like a navigation tool, but the problem is that the more cryptic and complicated it is – the grater the chances are of getting lost.
Think about the Indexing strategy: this is like a cheat-sheet containing shortcuts; or at least ideas for shortcuts. As we all know, depending on the terrain and other factors, the shortest distance between two points is rarely a straight line.
Think about the hardware: inexperienced users are usually tempted to purchase the best hardware there is (according to the budget they have available) and it comes to them as a surprise that the expected performance improvement is … how should I say it… not happening. The hardware in this case is like buying a F1 car and trying to drive it through the database forest; well, first you have to chop your way through the branches (and baobabs), then you have to find the right direction, then you have to find the right shortcut… and all of this has to happen before you can even think of utilizing some of the F1 car’s power.
And the worst part is, that my friend Barley not only gets carried away and after a while he seizes to grasp the concept of difference between trees, but also the fact that he forgets that he is surrounded by trees. (In a database world we think in terms of ’not differentiating between analytical and transactional’, ’not seeing the chances for improving the design, the queries and the indexes’, ’not understanding the proper configuration of hardware and software’). This is how Barley ends up sitting in a F1 car in the middle of a forest, pressing the gas pedal and not moving…
And here is how it goes from here: Barley gets a lecture on priorities, navigation and design, and then after some hard work, he gets to walk freely in the forest, without the fear of shadows and falling trees.
For lectures, guidance and any other questions, you can contact us here.
By the way, the author of ”Travels with Charley” finds out a way to make the dog ”salute” the giant trees. A great way, at that.