This is another in an occasional series aimed at the beginning or involuntary DBA. The first can be found here. With this series, I hope to present the basics of SQL Server in a fun yet informative manner. This is heavily influenced by my hobby of playing Lord Of The Rings Online and by my being a nerd in general. This chapter deals with improving performance by separating your data, log, and tempdb files onto different drives.
Sr. DBA inhaled deeply and she felt the tension escape with her long, slow exhale. It was a warm day, the sun was shining in a cloudless sky, and a soft breeze from the north kept things from getting too hot. She was out on a consulting job, summoned by a data farmer who was having problems reaping the fruits of his labors. From the letter he had sent her, it sounded like a very simple case. Too simple, even, for her to bring along Jr. DBA. In fact, she could have sent him to solve this on his own, but truthfully, she wanted the chance to get out into the fresh air. The case sounded too simple to even earn her any XP, but she was already at the level cap anyway, so earning more XP wasn’t even a consideration anymore. She just enjoyed the work and helping people.
She spotted the farmer leaning on a wooden fence that surrounded his field. She walked over and introduced herself. “Tell me more about your problem,” she said after the introductions were done.
“Well, I’m a new owner of this system here,” he said, tipping his head towards the field. “The vendor I bought this from said this field array subsystem was the fastest one available. I paid a lot of coin for this. But I’m not getting anywhere near the performance he said I would get. I can’t figure it out.”
Sr. DBA looked out over the field. It was one big field, divided into many sectors. Off to the left side, were several trees, many heavily laden with fruits of various colors, some with nuts, and a few with flowers. A few sectors over, she saw row after row of logs laying on the ground. They snaked back and forth, taking up most of the field. And finally, off in the distance, she saw a small building that looked like a workshop or a small cottage. A few feet away from where they stood was a gate. Every few seconds, it would open and a basket of fruit or nuts would appear, and the gate would close again. As she watched, one of the farmer’s workers walked over to the basket, picked it up, and carried it away. She saw a line of many workers standing around looking bored.
“I’m paying them to do nothing,” the farmer continued, noticing her gaze. “I was so sure this new system would be faster, that I hired extra help. But it’s not faster at all. And the vendor I bought this thing from has left town. I can’t get any help from him.”
Sr. DBA smiled reassuringly. “Don’t worry,” she told the farmer. “This is indeed a fast system. We just need to get things set up correctly.” She reached into the satchel she had hanging from a strap across her shoulder and rooted around until she found what she wanted. She pulled out a small leather bag with some writing on it. Before she pulled out a pair of glasses from the bag, the farmer saw it said dm_os_wait_stats across the front. She handed the glasses to the farmer. “Here,” she said. “Put these on and look at your field.”
The farmer slipped the glasses on and turned his gaze to his field. He was amazed at what he saw. Over by the trees, he saw a line of baskets, some of which were empty and some of which had items in it. Floating in the air over them, he saw a word he didn’t know: PAGEIOLATCH. As he watched, he saw a small gnome wearing a blue jacket and pointy red hat run to the front of the line of baskets, pick one up, and run over to the sector of the field where the logs were. The gnome bent over the end of the log, appeared to write something on the log, then ran back over to the trees. He then searched for a particular tree, shimmied up the trunk, search for a particular branch, then a particular leaf. He then plucked what appeared to be an apple from next to the leaf and replaced it with a slightly different apple that he pulled out of the basket he had picked up earlier. The gnome then shimmied back down the tree and stuck the basket outside the gate before running off to grab another basket.
The farmer was dumbstruck. He didn’t have any idea how his operation actually worked. He just knew he put orders in baskets and got stuff out. Standing there with his mouth open, he saw the gnome do something different this time. As before, the gnome grabbed a basket from the front of the line of baskets, but this time, instead of going to the log area, he went directly to the trees. He climbed several different ones, grabbing pieces of fruit off of some, a few nuts off of others, and then ran over to the workshop at the far side of the field. He disappeared inside for a few moments, then ran back out again, taking the basket to the gate and placing it outside. Curious, the farmer peeked inside. The basket held what looked like an apple pie with walnuts on top.
He took the glasses of and handled them back to Sr. DBA. He didn’t know what to say.
She saw his confused look and smiled. It was always a shock the first time you saw how the internals of the database server worked. “Let me explain what you just saw,” she told him. “You have one big field and you are using it for everything. Even though you have a very fast Rows-per-Watershed head (or R/W head as we like to say – that’s the little gnome you saw running around), there is only one of him and he’s got to do everything. So when someone just needs a piece of data from one of your b-trees, he’s got to run over there, find the piece, put it in a basket, and deliver it to the gate. If someone needs to change a piece of data, the little guy has to get that change order, run over to the log and write down what he’s doing (so if he dies before he is finished, his replacement gnome knows where to pick up), then go make the change, and then report back that the change was made. And finally, if someone wants to combine various pieces of data, he’s got to go collect the data, carry it all over to the workshop at the far side of the field, mash them all together according to the instructions in the basket, then bring the result back to the gate. As you can see, he’s a very busy guy. And even though he’s very fast, it still takes him time to do everything.”
She reached into her satchel again and took out another pair of glasses, this one from a bag that was labeled dm_io_virtual_file_stats. She put them on and glanced out over the field. Numbers floated in the air over everything. One number stood out because it was so large. It was labeled io_stall. As she watched, it ticked up by a few thousand. She took the glasses off. “Yes,” she said, “your poor little gnome is definitely overworked.”
“Wait a minute. I bought this field because I was told this was the fastest model available. Now you’re telling me my small operation is overworking it?”
“Well,” Sr. DBA said. “It is a very fast model. But even the fastest system will not function well if it is not set up correctly. What you need to do is spread things out a bit so other gnomes can help. I’m assuming you got some other fields with your purchase?”
“Yes,” the farmer answered, pointing west to some other empty fields. “Those two over there.”
“Good. Let me make some changes here and I’ll have you running faster in no time. But be warned, what I am about to do will require that no one can use your field until I am done and I must kick everyone out before I start.”
The farmer nodded and gestured for Sr. DBA to do her stuff.
First, Sr. DBA needed to move the logs off to their own field. She cast her first group of spells:
ALTER DATABASE FruitsOfLabor
MODIFY FILE ( NAME = ‘FruitsOfLaborLog’, FILENAME = ‘E:\FruitsofLabor.ldf’)
ALTER DATABASE FruitsOfLabor
At this point, Sr. DBA paused and spent several minutes carrying logs from the field in front of them over to one of the empty fields to the west. When all the logs were moved, she returned to her place next to the farmer and cast another spell:
ALTER DATABASE FruitsOfLabor
That done, she moved on to the next step – moving the workshop. This at least, required no heavy lifting on her part. The workshop was rebuilt every time the server was restarted.
ALTER DATABASE tempdb
MODIFY FILE (NAME = ‘tempdev’, FILENAME = ‘F:\tempdb.mdf’)
ALTER DATABASE tempdb
MODIFY FILE (NAME = ‘templog’, FILENAME = ‘F:\templog.ldf’)
She then asked the farmer to stop and restart his server farm. While he did this, Sr. DBA slipped on her dm_io_virtual_file_stats glasses again. As the server came to life, she saw a workshop pop up in the far field. The old one still remained, but she would tell the farmer he could tear it down and use the space for more trees since it would not be used anymore. Soon, numbers started appearing the air over the various objects in the fields. The number she was interested in from before, io_stalls, was low and not increasing much. Some increase was to be expected, but it was not growing anywhere near as fast as it was when she first arrived. She handed her pair of dm_os_wait_stats glasses to the farmer.
He put them on and looked over the fields. The first thing he noticed was that baskets were appearing at the gate much more rapidly now. His extra workers were no longer standing around gossiping about the latest news from the big city of Denali. Instead, they were hustling back and forth, picking up baskets of data and delivering them to the people who ordered them. He turned his view to the fields and was surprised at the changes he saw. In his field with the trees, a gnome was still running up various trees and collecting fruits and nuts. But now, when the basket was full, he tossed the basket over the fence to a gnome waiting in the other field, who caught the basket and took it into the workshop. A short while later, he came out and deposited the basket at the gate. In the meantime, the first gnome had picked up a basket with an orange in it, tossed the basket to the gnome in the field with the log, who caught it, scurried over to the log and made some markings on it, then scurried back and tossed the basket back to the first gnome, who caught it, ran up a tree, and replaced an orange there with the one from the basket. All three gnomes were working in harmony and baskets were flying through the air in an amazing display aerial acrobatics.
Shaking his head, he returned the glasses to Sr. DBA. “I’ve never seen such a thing in my life,” he said.
“I think you’ll find your operation will run much faster now,” Sr. DBA said. “I’ve spread the workload over three fields. Since each field has its own gnome, any single one isn’t over worked and they can coordinate their jobs to get things done faster.”
“Thank you!” the data farmer said, his eyes welling with tears. “You’ve saved me tons of coin,” he said, while dropping a few into her hand.
Sr. DBA smiled. She enjoyed helping people. (And, truth be told, she enjoyed earning coin too.) “My pleasure,” she said.