The Magic Beans : By Tyla and Andrew Baker

This was Tyla’s idea (my daughter); we wrote a book together… I figured I would just post it here. When my kids where small I used to make up stories; this was one of them..

The Magic Beans

Book 1: The Land of Candy

Chapter 1: The Long Summer

The sun was relentless, a heavy, golden weight pressing down on the countryside. It was the kind of summer that stretched endlessly, with no breeze to stir the trees and no clouds to break the monotony of the bright blue sky. In the middle of this, four siblings were sprawled in various corners of the yard, their faces etched with boredom.

Andy, the oldest at 12, lay on the grass with his hands behind his head, staring at the sky as if willing something interesting to happen. Beside him, his younger brother Matt, 11 and ever-practical, was sketching something in a small notebook, though even he seemed uninspired.

“Why don’t we go climb the old oak tree?” Andy suggested, finally breaking the silence.

“Because we’ve already climbed it a hundred times,” Rachael shot back, rolling her eyes. At 10, she was the only girl and the loudest of the group, always quick to offer her opinion. She leaned against the fence, absently tossing a small rock back and forth between her hands.

Tom, the youngest at 8, perked up at the mention of the tree. “We could see if the nest has new eggs!” he said brightly, but when no one responded, his enthusiasm faded.

“I’m bored,” Rachael declared, throwing the rock into the bushes. “There’s nothing to do.”

Andy sighed. It was true—summer in their little farmhouse always started out feeling like an adventure but quickly turned into endless days of nothing. Their parents were busy with chores and errands, and the kids were left to entertain themselves. Normally, they were good at it. They built forts, created treasure hunts, and explored the woods behind the house. But today, even their imaginations seemed to have dried up under the heat.

“Well, we could go into town,” Matt suggested hesitantly.

“With what money?” Rachael asked.

“Mom said we’re supposed to stay here anyway,” Andy reminded them. He didn’t love being in charge, but since their parents had left him responsible, he took the job seriously—most of the time.

Tom wandered over to the house, dragging his feet. “I’m hungry,” he mumbled.

“You’re always hungry,” Rachael teased, but she followed him anyway. Soon, all four of them were crammed into the kitchen, rummaging through the pantry.

Andy leaned against the counter, watching his siblings. Despite their constant bickering, they usually managed to have fun together. But today, something felt off. It was like the summer itself had run out of ideas.

That’s when Rachael said, “Why don’t we explore the attic?”

Matt looked up from the jar of peanut butter he was examining. “The attic? Why?”

“Why not?” Rachael countered. “We haven’t been up there in years. Who knows what we’ll find?”

Andy hesitated. The attic was dusty, cramped, and full of forgotten junk, but at least it would give them something to do.

“Fine,” he said. “Let’s check it out.”

Chapter 2: The Attic

The attic was even dustier than they remembered. Andy sneezed as he pushed open the hatch, and a cloud of stale air greeted them.

“Gross,” Rachael muttered, but she climbed the ladder anyway, determined to prove her idea wasn’t boring.

Tom was the most excited, scrambling up behind her. “What do you think we’ll find? Treasure? Old toys? A secret map?”

“Probably just a bunch of junk,” Matt said, but he followed the others up, holding his notebook close in case he found something worth drawing.

The attic was dimly lit, with only a single bulb hanging from the ceiling. Boxes were stacked haphazardly, and cobwebs draped the corners like decorations. Rachael immediately started poking through an old trunk, pulling out a moth-eaten scarf and a stack of yellowed letters.

“Look at this!” she exclaimed, holding up a photo of their mom as a child.

“Cool,” Andy said, but his attention was drawn to something else. In the far corner of the attic, beneath an old rocking chair, the floorboards seemed uneven.

“What’s that?” he said, pointing.

The others followed his gaze.

“What’s what?” Rachael asked.

“The floor,” Andy said. “It looks… loose.”

The four of them crowded around as Andy crouched down and pressed on the wood. Sure enough, it shifted slightly under his hand.

“Help me move this chair,” he said.

Together, they pushed the rocking chair aside, revealing a small, hidden compartment in the floor. Andy pried it open, and inside was a dusty cloth bag tied with a golden string.

“What do you think it is?” Tom whispered, his eyes wide.

Andy pulled it out carefully and set it on the floor. The bag was heavier than it looked, and when he untied the string, the siblings gasped.

Inside were dozens of small, shiny beans, each one a different color. They shimmered like jewels in the dim light, and when Andy touched one, it felt warm, almost alive.

“They’re… beans?” Matt said, confused.

“Magic beans,” Rachael said immediately.

“You don’t know that,” Matt argued.

“Well, what else would they be?” she shot back.

Tom reached out to touch one, but Andy stopped him. “Wait. We don’t know what they do.”

“They’re just beans,” Rachael said, rolling her eyes.

But there was something about the way they glowed, the way the bag seemed too perfect, too mysterious. The kids stared at the beans in silence, their earlier boredom completely forgotten.

“What should we do with them?” Tom asked finally.

Andy glanced at the others, feeling a strange mix of excitement and nervousness. Whatever these beans were, they weren’t ordinary. And something told him their summer was about to get a lot more interesting.

Chapter 3: The First Bean

The bag of beans sat on the attic floor like it didn’t belong there, shimmering faintly in the dull light. The siblings crouched around it, wide-eyed and unsure.

“Maybe it’s treasure,” Tom suggested, his voice hushed.

“It’s not treasure,” Matt replied, frowning. “It’s a bag of beans. Probably some farmer’s weird collection.”

“Farmers don’t hide beans under floorboards,” Rachael pointed out, picking one up. It was a bright, sparkling green, and it seemed to pulse softly in her hand.

“Don’t eat it!” Andy exclaimed.

“I wasn’t going to,” she snapped, but her curiosity was written all over her face. “Don’t you think it’s strange? Why would someone hide this? It’s not normal.”

Tom’s eyes sparkled. “What if they’re magic?”

Andy laughed nervously, though his stomach churned with unease. “Magic isn’t real, Tom. They’re probably just old and weird-looking.”

“Then why are you so nervous?” Rachael asked slyly, tossing the bean from hand to hand.

Andy had no answer. His gut told him these beans were different, but he didn’t want to admit it. “Let’s put them back,” he said finally. “We shouldn’t mess with stuff we don’t understand.”

“Oh, come on!” Rachael said. “What’s the worst that could happen?”

She popped the bean into her mouth before anyone could stop her.

“Rachael!” Andy shouted, lunging forward, but it was too late. She swallowed it with a dramatic gulp and stood there, waiting.

For a moment, nothing happened.

“See?” she said, smirking. “It’s just a—”

A strange, shimmering light enveloped her. It swirled around her like a whirlpool, glittering with colors that didn’t seem possible. Her eyes went wide, and then—

She disappeared.

The boys stared at the empty spot where their sister had been.

“What just happened?” Matt whispered, his voice trembling.

“She’s gone!” Tom cried.

Andy’s heart was racing. “She’s not gone. She… she has to be somewhere. We have to find her.”

“How?” Matt asked, but Andy wasn’t listening. He grabbed one of the beans—a deep red one—and looked at his brothers.

“I’m going after her,” he said firmly.

“Are you crazy?” Matt said, panicking. “You don’t even know where she went!”

“Stay here if you want,” Andy said. “But I’m not leaving Rachael alone.” He put the bean in his mouth, swallowing before he could second-guess himself.

The shimmering light surrounded him, and then he was gone.

Chapter 4: The Portal

Andy felt like he was spinning through a tunnel made of light and color. It was both exhilarating and terrifying. He tried to scream, but no sound came out.

Suddenly, he landed with a thud on soft, springy ground. For a moment, he just lay there, catching his breath. Then he sat up and gasped.

He was surrounded by the strangest, most beautiful landscape he’d ever seen. Trees made of cotton candy stretched high into the sky, their fluffy pink branches swaying gently. A river of cola bubbled nearby, its fizzy aroma filling the air. The ground was a checkerboard of chocolate and caramel, and towering lollipop flowers sprouted everywhere.

“Andy!”

He turned to see Rachael running toward him, her face lit with excitement.

“Rachael! You’re okay!”

“Of course I’m okay! Look at this place!” She twirled around, her arms outstretched. “Isn’t it amazing?”

“It’s… unbelievable,” Andy admitted, though his stomach was still tied in knots. “But we don’t know if it’s safe.”

“It’s candy, Andy!” she said, rolling her eyes. “How dangerous could it be?”

Before he could answer, there was a loud pop, and Matt and Tom appeared in quick succession.

“Whoa!” Matt exclaimed, looking around in awe.

Tom’s jaw dropped. “This is the coolest thing ever!”

“Andy,” Matt said, his voice steady despite his wide eyes, “what is this place?”

“I don’t know,” Andy admitted. “But we need to stick together and figure out how to get back.”

“Get back?” Rachael said, incredulous. “Why would we leave? This place is incredible!”

Andy frowned, but before he could argue, Tom ran off toward the cola river. “It’s fizzy!” he shouted, dipping his hands into the bubbling liquid.

“Tom, wait!” Andy yelled, chasing after him.

The siblings spent the next hour exploring the candy land. They climbed the cotton candy trees, tasted gumdrop bushes, and even found a fountain that spouted orange soda. But as magical as it was, Andy couldn’t shake the feeling that they didn’t belong there.

Chapter 5: The Candy Creatures

As the sun dipped lower, casting long shadows across the candy landscape, the siblings gathered under a licorice tree to rest.

“Do you think we’re the first people to come here?” Tom asked, his mouth sticky with caramel.

“Maybe,” Rachael said, leaning back against the tree. “Or maybe this place has been here forever, waiting for someone to find it.”

Andy glanced around uneasily. “We should head back soon. Mom will notice if we’re gone too long.”

“But how do we get back?” Matt asked. “We don’t even know how we got here.”

Before Andy could answer, a rustling sound came from the bushes.

“What was that?” Tom whispered, his eyes wide.

The rustling grew louder, and then a creature emerged. It looked like a cross between a rabbit and a marshmallow, with soft, pillowy fur and long, floppy ears.

“It’s so cute!” Rachael said, reaching out to touch it.

But before she could, more creatures appeared—hundreds of them. They surrounded the siblings, their eyes glowing faintly in the dim light.

“Um, Andy?” Matt said nervously.

“I see them,” Andy replied, stepping in front of his siblings protectively.

The creatures didn’t seem hostile, but their sheer number was overwhelming. One of them hopped closer, sniffing the air. Then, to everyone’s surprise, it spoke.

“Why are you here?” it asked in a soft, musical voice.

The siblings exchanged stunned looks.

“We… we don’t know,” Andy said finally. “We found some beans, and they brought us here.”

The creature tilted its head, its glowing eyes narrowing. “You shouldn’t be here. This place is not for humans.”

Andy swallowed hard. “We didn’t mean to—”

“You must leave,” the creature interrupted. “Before it’s too late.”

“Too late for what?” Rachael asked, but the creature didn’t answer.

Instead, it hopped away, and the others followed, disappearing into the bushes as quickly as they had come.

Chapter 6: Searching for a Way Back

The marshmallow-like creatures had vanished into the candy forest, leaving the children feeling uneasy despite their incredible surroundings. The air, once filled with the sweet aroma of sugar, now felt tense. Andy glanced at his siblings, trying to mask his growing worry.

“We need to figure out how to get back,” he said, his tone firm. “Mom’s going to call us for dinner soon, and we can’t explain any of this to her.”

“But we just got here!” Rachael protested, crossing her arms. “We can’t leave without exploring more. Don’t you want to know what else is out there?”

“I do,” Matt admitted, staring at the cola river. “But those creatures said this place wasn’t for humans. What if it’s dangerous?”

Tom, chewing on a piece of licorice he’d yanked from a tree, shrugged. “It doesn’t feel dangerous. Besides, we have to try everything while we can. What if we can’t come back?”

Andy sighed, rubbing his temples. He felt the weight of responsibility on his shoulders. Being the oldest, it was always up to him to keep everyone safe. “We’ll look around a little more,” he conceded, “but we need to stick together and stay alert.”

The group wandered deeper into the candy land, following a path of glowing jellybeans embedded in the ground. They passed fields of chocolate daisies, their petals melting slightly in the warm sunlight, and a sparkling fountain that sprayed shimmering lemonade.

“Okay, this is pretty amazing,” Andy admitted as they reached a meadow of marshmallow clouds. Each cloud floated just above the ground, soft and bouncy like giant pillows. Tom immediately launched himself onto one, giggling as it sank beneath his weight.

“This is the best day ever!” Tom declared, bouncing higher and higher. Even Matt couldn’t resist, jumping onto a nearby cloud and laughing when it wobbled under him.

Andy couldn’t help but smile, but his nerves didn’t fade. “We can’t get too distracted,” he reminded them. “Keep an eye out for anything that looks like a way back.”

“Maybe another bean will work,” Rachael suggested, pulling the pouch out of her pocket. “We could try eating one and see what happens.”

“Not yet,” Andy said quickly, holding up a hand. “We need to be careful. We don’t know what these beans do—or if they’ll even take us home.”

“But what if they open another portal?” Rachael argued. “It’s worth a shot.”

Before Andy could respond, a low rumble echoed through the meadow. The marshmallow clouds quivered, and the siblings froze.

“Did anyone else hear that?” Matt whispered, his voice tight.

The rumble grew louder, and suddenly, the ground beneath them began to shake. The jellybean path cracked, and from the distance, they saw something massive moving toward them—a rolling wave of caramel, thick and sticky, pouring out from a hidden valley.

“Run!” Andy shouted.

The siblings bolted, the caramel wave surging closer with every second. It smelled delicious, but they didn’t have time to admire it. Rachael grabbed Tom’s hand, pulling him forward as his smaller legs struggled to keep up.

“There!” Matt pointed to a grove of peppermint trees ahead. “We can climb those!”

The children scrambled up the nearest trees, their hands sticking to the cool, minty bark. They clung to the branches as the caramel flood rushed past below, coating everything in its path.

“That was close,” Andy said, his chest heaving. “Too close.”

“Still think this place isn’t dangerous?” Matt asked, raising an eyebrow.

Rachael scowled. “It’s not dangerous—it’s just… unpredictable.”

Andy shook his head. “This isn’t a playground, Rachael. We need to get out of here.”

Chapter 7: Following the Jellybean Path

When the caramel wave finally receded, the children climbed down from the peppermint trees, their legs wobbly and their clothes speckled with sticky spots. The jellybean path was now barely visible under the hardened caramel, but the faint glimmer of the beans gave them a direction to follow.

“I think the path might lead us back,” Andy said, inspecting the beans. “Whoever hid those beans in our attic must have made a way to return.”

“Or it could lead us deeper into the candy world,” Tom said hopefully. “Maybe to a castle made of cake!”

“Or a volcano of hot fudge,” Matt added, half-joking.

“Let’s just focus on getting home,” Andy insisted. “We can come back later—maybe with a plan.”

As they followed the path, the terrain began to change. The trees grew taller and darker, their cotton candy leaves turning deep shades of purple and blue. The air smelled less sweet, and a strange, soft humming noise filled the silence.

“Do you hear that?” Rachael asked, her voice barely above a whisper.

Andy nodded. “Stay close.”

The path led them to the edge of a vast clearing, where a massive structure loomed in the distance. It was a towering mountain made entirely of gumdrops, their surfaces glittering like jewels in the dim light.

“That’s incredible,” Matt said, his jaw dropping. “I’ve never seen anything like it.”

“Do you think there’s something inside?” Tom asked eagerly.

Andy hesitated. The mountain looked beautiful, but there was something unsettling about its perfect symmetry. “Let’s stick to the path,” he said finally. “We don’t want to get lost.”

But as they began walking again, a sudden voice stopped them in their tracks.

“You’re not supposed to be here.”

They spun around to see a figure emerging from the shadows—a tall, thin man made entirely of peppermint sticks, his candy cane arms crossed over his chest. His red-and-white stripes seemed to glow faintly in the dim light.

“How did you get here?” he demanded, his voice sharp.

The children exchanged nervous glances. Finally, Andy stepped forward. “We didn’t mean to come here,” he said. “We found some beans, and they brought us—”

“The beans?” The peppermint man’s eyes widened, and his expression softened slightly. “You found the beans?”

Andy nodded cautiously. “We just want to get back home.”

The man studied them for a long moment before sighing. “Follow the path,” he said, his tone serious. “It will lead you to the portal. But be quick—the longer you stay here, the harder it will be to leave.”

“What happens if we stay too long?” Rachael asked.

The peppermint man didn’t answer. He turned and disappeared into the shadows, leaving the children more determined than ever to find their way back.

Chapter 8: A Sweet Escape

The peppermint man’s words echoed in Andy’s mind as the siblings hurried along the jellybean path. The ground felt firmer now, the caramel flood having hardened into a sticky crust around them. The forest’s vibrant colorsdimmed as if the candy world itself was urging them to leave.

“What do you think he meant?” Rachael asked, her voice tinged with worry. “About it getting harder to leave?”

“Maybe this place doesn’t want us to go,” Matt said, glancing over his shoulder. “What if the portal disappears?”

“That’s not going to happen,” Andy said firmly, though he wasn’t entirely sure. “We just need to stick together and keep moving.”

The path led them to a clearing where the jellybeans formed a spiral, glowing brighter than ever. In the centerof the spiral, a swirling portal shimmered—a mix of rainbow colors that seemed to pulse with energy.

“There it is!” Rachael exclaimed, running ahead.

“Wait!” Andy shouted, grabbing her arm before she could reach it. “What if something’s guarding it?”

As if on cue, the ground beneath the portal began to shake. From the shadows of the peppermint forest emerged a creature unlike anything they’d seen before. It was made entirely of chocolate—smooth and shiny, with molten eyes and jagged chunks of dark chocolate forming its claws.

The chocolate guardian let out a low growl, moving closer with slow, deliberate steps. The ground quaked with each step, and the children instinctively huddled together.

“What do we do?” Matt whispered, clutching Andy’s arm.

“I’ll distract it,” Andy said, his voice steady despite the fear in his eyes. “You guys get to the portal.”

“No way,” Rachael said. “We’re not leaving you behind!”

“We don’t have time to argue,” Andy said. “Just trust me!”

Before anyone could stop him, Andy grabbed a branch from a nearby peppermint tree and waved it at the creature. “Hey! Over here!” he shouted, backing away from the portal. The chocolate guardian roared and lunged toward him.

“Go!” Andy yelled.

Rachael, Matt, and Tom hesitated for only a moment before racing toward the portal. Just as they reached it, the chocolate guardian turned, sensing their movement. It roared again, but Andy swung the peppermint branch, striking its leg. The creature stumbled, molten chocolate splattering the ground.

“Andy, hurry!” Rachael shouted from the portal’s edge.

Andy dropped the branch and sprinted toward his siblings. The guardian lunged after him, but before it could grab him, Andy leaped into the portal.

Chapter 9: Back to Reality

The world spun around them as the children tumbled out of the portal, landing in a heap on the wooden floorboards of the attic. For a moment, none of them spoke. They just lay there, catching their breath and staring at the dusty ceiling.

“We made it,” Andy said finally, his voice shaky.

Tom sat up, looking dazed. “That was… amazing,” he said. “Can we go back?”

“No way,” Matt said, shaking his head. “That was way too close.”

Rachael pulled the pouch of beans from her pocket, holding it up. “We don’t have to go back there,” she said. “We have a whole bag of beans. We could go anywhere.”

Andy sat up, brushing dust off his clothes. “First, we cover these back up,” he said firmly. “Mom can’t find them.”

“Speaking of Mom,” Matt said, glancing at the attic door. “I think I just heard her call for dinner.”

“Do you think she’d believe us if we told her where we were?” Tom asked, grinning.

“No way,” Rachael said, laughing. “She’d think we were crazy.”

The siblings quickly pushed the loose floorboard back into place, covering the beans and patting the wood flat. As they headed downstairs, Rachael turned to Andy with a sly smile.

“Tomorrow,” she whispered, “we should try another one.”

Andy hesitated, then grinned. “We’ll see.”

Chapter 10: A Plan for Tomorrow

At the dinner table, the children exchanged glances, struggling to act normal as their mom served spaghetti. Every so often, Tom would stifle a giggle, and Matt would nudge him under the table.

“What’s so funny?” their mom asked, raising an eyebrow.

“Nothing,” Tom said quickly, his face turning red.

“Just tired from playing outside,” Andy added, forcing a yawn. “We were… really busy today.”

Their mom gave them a suspicious look but didn’t press further. As they ate, the children couldn’t stop thinking about the beans. What would the next one do? Where would it take them? The possibilities seemed endless.

After dinner, they gathered in Andy’s room, whispering excitedly about their next move.

“We have to be more careful next time,” Andy said. “No running off, no distractions. We stick together.”

“Do you think all the beans are portals?” Rachael asked. “Or do they do different things?”

“Only one way to find out,” Tom said with a grin.

“Tomorrow,” Andy said. “We’ll meet in the attic after breakfast. And no one says a word to Mom.”

“Deal,” the others agreed.

As they went to bed that night, the children couldn’t help but feel a mix of excitement and nervousness. The beans were full of mystery, and they knew their adventures were just beginning.

Chapter 11: The Bean of Whispers

The next morning dawned bright and early, but to the four siblings, it felt like any other day. They knew better. After wolfing down breakfast—much to their mom’s surprise—they retreated to the attic, hearts pounding in anticipation.

Andy knelt by the loose floorboard and carefully pried it up. The pouch of beans glimmered in the dim light, each one a tiny mystery waiting to be solved.

“All right,” he said, looking at his siblings. “One bean. We need to make sure it’s safe.”

“Safe?” Rachael scoffed. “Yesterday was awesome!”

“Awesome, but dangerous,” Andy shot back. “Let’s try not to almost get eaten this time.”

Matt reached into the pouch and pulled out a bean. This one was deep blue, with a swirling silver streak running through it. He held it up, turning it in the light.

“Looks harmless enough,” he said.

Tom hesitated. “What if it doesn’t open a portal this time? What if it… I don’t know, explodes or something?”

“Only one way to find out,” Rachael said, snatching the bean from Matt. Before anyone could stop her, she popped it into her mouth and chewed.

“Rachael!” Andy exclaimed.

But before he could scold her, the air around them began to hum. A faint whispering sound filled the attic, growing louder with each passing second. The whispers seemed to swirl around them, forming a cyclone of sound.

“What’s happening?” Matt asked, gripping Tom’s arm.

Rachael’s eyes widened as the whispering voices suddenly formed into words.

“Come… explore… secrets await…” they murmured, the voices overlapping in an eerie harmony. A portal appeared before them, shimmering with a silvery-blue glow.

Andy looked at his siblings. “Are we really doing this again?”

“Of course we are!” Rachael said, already stepping toward the portal. “Come on!”

Matt and Tom exchanged nervous glances but followed her. Andy sighed, shaking his head as he stepped through last.

Chapter 12: The Whispering Woods

The siblings emerged into a dim, misty forest. The trees were unlike any they had seen before—tall and thin, with glowing blue leaves that seemed to hum softly. The ground was carpeted with soft moss that glimmered faintly in the shadows.

“Where are we?” Tom whispered.

“It feels… alive,” Rachael said, brushing her fingers against the trunk of a tree. It pulsed under her touch, as if responding to her presence.

Andy knelt to examine the moss. “This place is weird. Even the ground looks like it’s glowing.”

Matt stepped closer to a cluster of bushes. “Hey, look at these!” he called. The bushes were covered in tiny berries, each one the size of a marble and glowing softly like tiny lanterns.

“Don’t eat those,” Andy warned. “We don’t know what they’ll do.”

Tom looked around nervously. “Do you hear that? It’s like… the forest is talking.”

The siblings fell silent, and sure enough, they could hear faint whispers coming from all around them. It was as if the trees themselves were speaking, their voices blending into an eerie symphony.

“This place gives me the creeps,” Matt muttered. “Let’s find a way out.”

But as they turned to retrace their steps, the path they had come from was gone. The forest seemed to shift and change around them, the trees rearranging themselves as if guiding—or trapping—them.

“We’re lost,” Rachael said, her voice tinged with panic. “What do we do now?”

Andy took a deep breath, trying to stay calm. “We stick together. And we keep moving. There has to be a way out.”

Chapter 13: The Tree of Secrets

As they wandered deeper into the forest, the whispers grew louder, almost as if they were being drawn toward something. Finally, they emerged into a clearing where a massive tree stood at the center.

The tree was unlike any they had seen before. Its trunk was silver, its bark etched with swirling patterns that seemed to shift and move. Its branches stretched high into the sky, and its leaves glowed with a soft, golden light.

“What is this?” Matt asked, staring up at the tree in awe.

Rachael stepped closer, her eyes fixed on the swirling patterns in the bark. “It’s beautiful,” she said. “Like it’s alive.”

As she reached out to touch the tree, a voice echoed through the clearing, deep and resonant. “Who dares approach the Tree of Secrets?”

The siblings froze, their eyes darting around. “Who said that?” Andy asked.

“I did,” the voice replied. It seemed to come from the tree itself, its words vibrating through the air. “Why have you come to my forest?”

“We didn’t mean to,” Andy said quickly. “We just… found a portal, and it brought us here.”

The tree seemed to hum thoughtfully. “Few are allowed into the Whispering Woods. You must prove yourselves worthy to leave.”

“Prove ourselves?” Rachael repeated. “How?”

The tree’s bark shifted, forming a face that looked down at them with piercing golden eyes. “By facing the truth within your hearts,” it said. “Each of you must choose a branch and climb. Only then will you find your way home.”

The siblings exchanged nervous glances. “Climb the tree?” Matt asked. “Are you serious?”

“It’s the only way,” the tree said. “But beware—what you find at the top may not be what you expect.”

Chapter 14: The Challenge Begins

The siblings stared at the massive tree, its glowing branches stretching endlessly into the misty sky. The face etched into its bark seemed alive, its golden eyes unblinking.

“Climb the tree?” Tom asked, his voice trembling slightly. “That sounds… kind of dangerous.”

Andy placed a hand on his younger brother’s shoulder. “We’ll be fine. We just have to trust each other and stick together, okay?”

The tree’s voice rumbled again, as if laughing softly. “Trust alone will not suffice. Each of you must climb separately. Your paths will not cross.”

“What?” Matt exclaimed. “That’s ridiculous! We’re not splitting up!”

“It’s the only way,” the tree replied calmly. “The secrets you seek—and the truth you must face—are yours alone.”

Rachael stepped forward, staring at the swirling patterns on the tree’s bark. “Fine,” she said, determination flashing in her eyes. “If this is the only way out, I’ll go first.”

“Rachael, wait—” Andy started, but she was already reaching for a low branch.

The moment her hand touched the bark, a golden light surrounded her. Before the others could react, she was gone—vanished into thin air.

Chapter 15: Rachael’s Journey

Rachael blinked as the world around her shifted. She was no longer in the clearing. Instead, she found herself on a narrow branch, high above the forest floor. The tree stretched endlessly upward, and glowing steps spiraledaround its trunk, beckoning her to climb.

As she ascended, whispers filled the air. At first, they were faint and unintelligible, but gradually, they grew clearer.

“Always so brave… always so bold… but what are you hiding?”

Rachael frowned, gripping the branch tighter. “I’m not hiding anything,” she said aloud, though her voice wavered slightly.

The whispers laughed softly. “A brave face… a strong front… but even the strongest feel fear.”

As she climbed higher, the air around her grew warmer. She reached a platform nestled in the branches, where a mirror stood suspended in midair. Its surface shimmered, showing her reflection—but not as she was.

The Rachael in the mirror looked unsure, her shoulders slumped and her eyes filled with doubt.

“What do you see?” the whispers asked.

Rachael swallowed hard, her heart pounding. “I see… me.”

The whispers grew louder, more insistent. “The real you? Or the you that hides behind courage?”

For a moment, Rachael couldn’t speak. The reflection seemed to reach out to her, as if daring her to admit the truth.

Finally, she took a deep breath. “Okay,” she said. “Maybe I’m scared sometimes. Maybe I don’t always know what I’m doing. But that doesn’t make me weak.”

The mirror shattered, and the whispers fell silent. A golden path appeared before her, leading upward.

Chapter 16: Andy’s Turn

Meanwhile, Andy stood at the base of the tree, staring at the spot where Rachael had disappeared.

“She’s okay,” he said, more to himself than to his brothers. “She’s tough.”

“Then it’s my turn,” Matt said, stepping forward.

“No,” Andy said firmly. “I’m the oldest. I go next.”

Before Matt could argue, Andy reached for a branch. Like with Rachael, golden light enveloped him, and he vanished.

When the light faded, Andy found himself in a different part of the tree. The branches here were thick and gnarled, twisting together to form a labyrinth.

“Great,” he muttered, running a hand through his hair. “A maze. Just what I needed.”

As he began to navigate the labyrinth, he heard faint whispers echoing through the branches.

“Always in charge… always responsible… but what happens when you let go?”

Andy’s jaw tightened. “I don’t let go,” he said aloud. “That’s my job—to look out for everyone.”

The whispers chuckled. “And who looks out for you?”

Andy stopped in his tracks. The question hit him harder than he expected. “I… I don’t need anyone to look out for me,” he said.

“Don’t you?” the whispers asked.

The maze shifted around him, the branches curling into walls that seemed to press closer. For the first time, Andy felt a pang of fear.

“I can do this,” he muttered, forcing himself to move forward. “I have to.”

As he reached the center of the labyrinth, he found a small, glowing orb suspended in the air. The whispers quieted as he reached out to touch it.

The moment his fingers brushed the orb, a warmth filled his chest. The branches parted, revealing a path upward.

Chapter 17: Matt Faces His Doubts

Back at the base of the tree, Matt and Tom stood in silence.

“What do you think is happening up there?” Tom asked.

Matt shrugged, though his stomach churned with nerves. “No idea. But it looks like it’s my turn.”

Tom hesitated. “Be careful.”

Matt forced a grin. “I’m always careful.”

He reached for the tree, and the golden light whisked him away.

Matt found himself on a narrow bridge, stretching endlessly across a dark void. The air was thick and heavy, and the only sound was the faint creak of the bridge beneath his feet.

“Afraid to fall… afraid to fail…” the whispers said, echoing all around him.

“I’m not afraid,” Matt said, though his voice shook.

The whispers laughed. “A brave front… but what lies beneath?”

As he took a cautious step forward, the bridge began to sway. Matt’s heart raced as he looked down, but there was nothing below—only darkness.

“Great,” he muttered. “This is fine. Totally fine.”

But with each step, the whispers grew louder, their words cutting deeper.

“You’ve always been in the middle… never the leader, never the baby. Always in-between.”

Matt clenched his fists. “That’s not true.”

“Isn’t it?” the whispers asked. “What are you, if not second-best?”

The words stung, and Matt stopped in his tracks. He wanted to argue, but part of him wondered if the whispers were right.

After a long moment, he straightened his shoulders. “I’m me,” he said firmly. “And that’s enough.”

The bridge steadied beneath him, and a glowing staircase appeared at the end, leading upward.

Chapter 18: Tom’s Choice

Tom stood alone at the base of the tree, staring up at its glowing branches. He had never felt so small—or so scared.

“I don’t want to go,” he whispered.

But the tree’s voice rumbled softly. “You must. The others are waiting.”

Tom swallowed hard, his hands trembling. “What if I can’t do it?”

The tree’s golden eyes seemed to soften. “You are braver than you know, young one. Trust yourself.”

Taking a deep breath, Tom reached for the tree. The golden light surrounded him, and he was gone.

Chapter 19: Tom’s Journey

When Tom opened his eyes, he found himself standing on a winding pathway that glowed faintly under his feet. The air shimmered with golden dust, and strange shapes darted just out of view—shadows of creatures he couldn’t quite see.

Tom’s legs wobbled as he took a hesitant step forward. The path seemed to stretch on forever, curling and twisting like a ribbon. The silence pressed against his ears, broken only by the sound of his footsteps.

“Where… where is everyone?” he whispered, hoping for an answer.

A faint chuckle echoed around him, low and soft. “Alone at last, little one. No brothers, no sister. Just you.”

Tom froze, his breath catching in his throat. “Who’s there?”

The chuckle grew louder, more playful. “Why so scared? You’re always the quiet one, aren’t you? Always trailing behind. Always the youngest.”

Tom’s fists clenched, his fear mingling with frustration. “That doesn’t mean I can’t do this!”

The path beneath his feet wavered, as though testing him. “Oh? And what is it you think you can do, little Tom? Without them to help you?”

Tom took a deep breath, his heart pounding. “I can be brave. I don’t need them to hold my hand all the time. I’m stronger than you think.”

The path steadied, glowing brighter as Tom’s confidence grew. Ahead, a doorway appeared, its frame carved with intricate patterns that pulsed with light.

“Then prove it,” the voice said, fading into silence.

Tom hesitated for only a moment before stepping forward. He didn’t know what was on the other side of the door, but he was determined to find out.

Chapter 20: The Reunion

One by one, the siblings emerged from the golden pathways, finding themselves in the heart of the tree’s glowing branches. The air was thick with warmth, and the golden leaves sparkled like stars.

“Andy!” Rachael called, running toward her brother.

“Rach!” Andy turned, relief flooding his face. “Are you okay? What happened?”

“I don’t even know,” Rachael said, glancing around. “But it was… weird. The tree made me face—”

“Your fears,” Matt interrupted, stepping into the clearing. He looked pale but determined. “I think it made all of us face something.”

Rachael nodded. “Where’s Tom?”

“Right here!” Tom’s voice rang out as he appeared at the edge of the clearing. He looked smaller than usual, but there was a new spark of determination in his eyes.

Andy ran to his youngest brother, kneeling to meet his gaze. “You did it, buddy. You made it.”

Tom nodded, swallowing hard. “It wasn’t easy… but I think I’m okay now.”

The tree’s voice echoed around them, warm and soothing. “You have proven yourselves, young ones. Your courage and strength have brought you here.”

Andy looked up at the glowing branches. “But why? Why did we have to go through all of that?”

The tree chuckled softly. “Because every journey begins with understanding oneself. Now, the path forward is clear.”

As the tree spoke, a new portal appeared in the centerof the clearing, swirling with colors that seemed to pulse in time with the siblings’ hearts.

“Another portal,” Matt said, his voice tinged with both excitement and caution. “Are we ready for this?”

Rachael smirked. “We just climbed a magical tree and faced our deepest fears. I think we can handle whatever’s next.”

Andy stood, his expression serious. “Let’s do this together. No one gets left behind.”

The siblings joined hands, stepping into the portal as one.

Chapter 21: The Land of Giants

The world spun around them in a kaleidoscope of colorsbefore they landed with a thud on soft, mossy ground. When they looked up, their jaws dropped.

Massive trees stretched into the sky, their trunks as wide as houses. Giant flowers bloomed in vibrant shades, their petals big enough to use as umbrellas. In the distance, a butterfly the size of a car fluttered lazily through the air.

“This place is… huge,” Matt said, his voice filled with awe.

Rachael grinned. “It’s like the opposite of Candy Land. Everything here could crush us!”

Tom clung to Andy’s side, his eyes wide as he stared at a towering blade of grass. “I don’t like this. What if we get stepped on?”

Andy crouched down, patting his brother’s shoulder. “We’ll be careful. Just stick close to me, okay?”

As they ventured deeper into the land, they discovered that everything seemed alive. The flowers swayed as if watching them, and the ground rumbled faintly beneath their feet.

At one point, they stumbled upon a massive nest filled with eggs the size of barrels.

“Let’s not stick around to find out what laid those,” Rachael whispered, pulling Matt away.

But despite the looming danger, the siblings couldn’t help but marvel at the beauty of the giant world. The air was filled with the sweet scent of enormous flowers, and the ground sparkled with tiny crystals that glittered like stars.

After what felt like hours, they found a massive staircase carved into the side of a hill. At the top, a glowing archway shimmered.

“That’s our way out,” Andy said, pointing.

“Let’s go before something finds us,” Matt added, glancing nervously at the shadow of a large bird circling above.

One by one, they climbed the staircase, their hearts racing. As they passed through the archway, the giant world faded, and they were back in the clearing beneath the magical tree.

Chapter 22: Back to Reality

The siblings stumbled out of the portal, their legs shaky and their hearts pounding.

“Okay,” Matt said, collapsing onto the ground. “That was officially the craziest thing we’ve done.”

Rachael laughed, though it was tinged with exhaustion. “And we still have a whole bag of beans left.”

Tom looked up at the tree, which had returned to its silent, watchful state. “Do you think we’ll ever go back to the giant place?”

Chapter 23: A Quiet Evening

The siblings sat around the dinner table, trying their best to act as though nothing out of the ordinary had happened. Their mother served plates of spaghetti, humming softly to herself, oblivious to the wild adventure her children had just experienced.

“So,” their mom said, glancing at them, “what have you four been up to today? Playing in the backyard again?”

“Uh-huh,” Andy said quickly, shoving a forkful of spaghetti into his mouth to avoid saying too much.

“Yep!” Rachael added, a little too enthusiastically. “Just the usual stuff. Nothing exciting.”

Tom kept his head down, twirling his spaghetti nervously. Matt, sitting beside him, kicked him lightly under the table as a reminder to stay calm.

Their mom raised an eyebrow but didn’t press further. “Well, I’m glad you’re getting along. Seems like you’re spending more time together lately.”

Andy exchanged a glance with his siblings. “Yeah, I guess we are.”

As they finished dinner and helped with the dishes, the four of them felt a growing sense of anticipation. The bag of beans was waiting upstairs, tucked safely beneath Andy’s bed. They knew it wouldn’t be long before their next adventure.

Chapter 24: The Missing Bean

Later that night, Andy crept into his room to check on the bag of beans. The others were still brushing their teeth, and the house was quiet except for the sound of the wind outside.

He reached under his bed, pulling out the bag carefully. His heart dropped when he opened it.

“Guys!” he whisper-shouted, rushing to the bathroom.

Rachael poked her head out, her toothbrush still in her mouth. “What?”

“One of the beans is missing!”

Matt and Tom appeared behind her, their faces mirroring Andy’s concern.

“Are you sure?” Rachael asked, spitting toothpaste into the sink.

Andy held up the bag. “Count them. There were twelve, and now there are eleven.”

Tom’s eyes widened. “You don’t think… it’s gone somewhere on its own, do you?”

“Beans don’t just disappear,” Matt said, though he didn’t sound entirely convinced.

The four of them returned to Andy’s room, sitting in a circle on the floor. They stared at the bag, trying to piece together what had happened.

“Do you think one of us took it?” Rachael asked hesitantly.

Tom shook his head quickly. “Not me!”

Andy sighed. “It doesn’t matter how it happened. What matters is figuring out if it caused something.”

The siblings sat in silence for a moment before Rachael spoke up. “What if the missing bean opened a portal somewhere else? What if it’s still open?”

They looked at one another, the same thought forming in all their minds.

“We have to find it,” Andy said firmly.

Chapter 25: Midnight Search

Armed with flashlights and determination, the four siblings crept through the darkened house. The soft hum of the refrigerator and the occasional creak of the floorboards were the only sounds as they searched for any sign of the missing bean.

They started in the kitchen, checking every corner and cabinet. Then they moved to the living room, lifting couch cushions and peering behind the TV.

“Nothing,” Rachael whispered, her frustration growing.

“Wait!” Tom whispered suddenly, pointing toward the back door. “Do you see that?”

A faint glow seeped through the crack beneath the door.

Andy’s heart raced as he motioned for the others to follow. They opened the door slowly, stepping out into the cool night air.

In the middle of the backyard, a shimmering portal hovered above the grass. It pulsed with soft, golden light, casting eerie shadows on the surrounding trees.

“There it is,” Matt whispered.

Andy tightened his grip on the flashlight. “We have to close it before someone—or something—comes through.”

“Do we even know how to close it?” Rachael asked.

Andy hesitated. “No. But we’re going to figure it out.”

Chapter 26: Through the Portal Again

As the siblings approached the portal, the golden light grew brighter. It seemed to sense their presence, swirling faster and faster.

“We should just go through,” Rachael said, her voice tinged with excitement.

Andy frowned. “That’s not the plan. We’re supposed to close it, not explore it.”

“But what if the missing bean is on the other side?” Matt pointed out.

Tom tugged on Andy’s sleeve. “What if it’s dangerous?”

Andy sighed, torn between caution and curiosity. Before he could decide, Rachael stepped forward, disappearing into the light.

“Rach!” Andy yelled, but it was too late. She was gone.

Matt and Tom exchanged nervous glances before Andy nodded. “Let’s go.”

One by one, they stepped into the portal, the golden light swallowing them whole.

Chapter 27: A City of Clocks

The siblings emerged in a strange, bustling city filled with the sound of ticking. Everywhere they looked, there were clocks—on buildings, in windows, hanging from trees. The air hummed with their steady rhythm.

“Where are we?” Rachael asked, her voice filled with awe.

“I don’t know,” Andy replied, staring at the massive clock tower in the distance. Its hands moved erratically, as if time itself was out of control.

Tom pointed to a group of small, mechanical creatures scurrying along the cobblestone streets. They looked like tiny robots, each carrying gears and springs.

“This place is weird,” Matt said, stepping closer to one of the creatures. It paused, looking up at him with glowing eyes before darting away.

As they explored the city, they realized that time behaved strangely here. Clocks sped up and slowed down without warning, and the sun seemed to rise and set in mere moments.

“We need to find the bean and get out of here,” Andy said. “This place isn’t safe.”

Rachael nodded. “But where do we start looking?”

As if in answer, the ground beneath their feet began to tremble. The siblings turned to see the massive clock tower shaking, its gears grinding loudly.

“I think we just found our answer,” Matt said grimly.

Would you like me to continue directly from here, building up to the climax and resolution? Let me know if there are specific elements you’d like to include in the ending!

Andy shrugged. “Maybe. But there are so many other places we haven’t seen yet. Who knows where the next bean will take us?”

Before anyone could say more, a familiar voice called out in the distance.

“Kids! Dinner’s ready!”

The siblings froze, their eyes wide.

“Mom!” Rachael hissed. “She can’t find us here!”

Andy quickly scooped up the bag of beans, tucking it under his arm. “Let’s go. We’ll figure out what to do with the beans later.”

They raced back toward the house, their hearts pounding. As they reached the porch, Andy turned to the others, a mischievous grin spreading across his face.

“Well,” he said, “we’ve got a whole bag of magic beans. Let’s see where they take us next.”

The others nodded, their excitement bubbling despite their exhaustion.

And as they sat down to dinner, the bag of beans hidden safely under Andy’s bed, they couldn’t help but wonder what adventures awaited them next.

Chapter 28: Inside the Clock Tower

The siblings stared up at the massive clock tower, its looming shadow stretching over the strange city. The trembling ground beneath them stilled for a moment, and then a loud chime echoed through the air, as if the tower were calling them.

“We have to go in,” Andy said, his voice firm.

Rachael nodded. “If the missing bean is anywhere, it’s in there.”

Tom hesitated, looking at the tower’s crooked doors, which creaked open on their own. “What if we get stuck inside?”

“We won’t,” Andy said, though his voice wavered slightly. “We stick together, no matter what.”

The four stepped cautiously through the doorway. Inside, the air smelled of oil and metal, and the walls were covered in spinning gears of all shapes and sizes. Each tick of the clocks reverberated through the space, making it feel alive.

“Look!” Rachael pointed to a spiral staircase that wound its way up the tower. “That must lead to the top.”

Without another word, they began to climb. The stairs groaned under their weight, and every step felt heavier than the last.

“This place is like a giant puzzle,” Matt murmured, running his hand along the intricate carvings on the railing.

“And we’re about to solve it,” Andy said, determination in his eyes.

Chapter 29: The Guardian of Time

At the top of the staircase, the siblings found themselves in a circular room filled with glowing hourglasses. Each one floated in midair, their sands shifting and shimmering in impossible colors.

“This is… amazing,” Rachael whispered, her voice hushed with wonder.

“Or terrifying,” Tom muttered, sticking close to Matt.

In the center of the room stood a tall, shimmering figure. Its form seemed to shift constantly, like it was made of liquid gold and silver. Its face was blank, but its presence was commanding.

“Who dares disturb the balance of time?” the figure said, its voice deep and echoing.

The siblings froze. Andy stepped forward cautiously. “We—we’re looking for something. A magic bean. It’s missing from our bag.”

The figure tilted its head, and for a moment, the room went completely silent. Then it spoke again. “A bean of great power has entered this realm. Its presence threatens the delicate flow of time.”

“We didn’t mean for it to come here,” Rachael said quickly. “We just need to find it and take it back.”

The figure gestured toward the hourglasses. “The bean has embedded itself within the hourglass of eternity. Removing it will be no simple task.”

Andy squared his shoulders. “We’ll do whatever it takes.”

The figure waved its hand, and one of the hourglasses floated toward them. Inside, the missing bean glowed brightly, its light pulsating in time with the ticking clocks.

“You must retrieve it,” the figure said. “But beware. Time will not give it up easily.”

Chapter 30: The Race Against Time

As soon as Andy reached for the hourglass, the room erupted into chaos. The ticking grew louder, and the walls of the tower began to shift, gears grinding and spinning faster than ever before.

“Get it, Andy!” Rachael shouted over the noise.

Andy grabbed the hourglass, but the moment his fingers touched it, the entire tower seemed to tilt. The siblings were thrown to the floor as the gears around them sparked and clashed.

“Time is unraveling!” the guardian warned. “You must act quickly!”

“How do we get the bean out?” Matt yelled.

Andy examined the hourglass, his hands trembling. The sands inside swirled violently, and the bean was trapped in the center.

“There’s a crack here,” Andy said, pointing to a tiny fracture in the glass. “Maybe we can break it!”

“Break it? Won’t that make things worse?” Tom asked, his voice high with panic.

“We don’t have a choice!” Andy shouted.

Rachael grabbed a small gear that had fallen from the wall and handed it to Andy. “Do it!”

Andy took a deep breath and struck the hourglass with the gear. The glass shattered, and the bean flew into the air, its golden light blinding them for a moment.

The tower groaned, and the siblings felt a sudden rush of wind as everything around them seemed to freeze.

“It’s the portal!” Matt shouted, pointing to a glowing doorway that had appeared in the center of the room.

“Go!” Andy yelled, clutching the bean tightly as they scrambled toward the portal.

Chapter 31: Back to the Backyard

The siblings tumbled out of the portal and landed unceremoniously on the grass in their backyard. The night air was cool and still, a stark contrast to the chaos they had just escaped.

Andy sat up, clutching the bean in his hand. It no longer glowed, but its surface was warm and smooth.

“We did it,” Rachael said, her voice breathless. “We got it back.”

“But what about the clock tower?” Tom asked. “What if we messed something up?”

Andy shook his head. “The guardian will fix it. It’s not our job to worry about that.”

They sat in silence for a moment, the enormity of their adventure sinking in.

Chapter 32: Mom’s Voice

Just as they were catching their breath, they heard their mom’s voice calling from the house.

“Kids! Time for bed!”

Panic set in immediately.

“She can’t know!” Rachael whispered, her eyes wide.

Andy quickly shoved the bean back into the bag and tucked it under his arm. “We need to act normal. Come on!”

They raced into the house, their hearts pounding as they tried to keep their excitement hidden.

Chapter 33: A Secret Kept

As they climbed into bed that night, the siblings couldn’t stop whispering about their adventure.

“I can’t believe we went to a city of clocks,” Matt said.

“And met a time guardian,” Rachael added.

Tom hugged his knees to his chest. “Do you think the other beans are that powerful?”

Andy pulled the bag out from under his bed and set it on his lap. “There’s only one way to find out.”

Rachael grinned. “You mean we’re doing this again?”

Andy looked at his siblings, his eyes sparkling with determination. “We’ve got a whole bag of beans. Let’s see where they take us next.”

Chapter 34: To Be Continued

The siblings drifted off to sleep that night, dreaming of portals and magical lands. The bag of beans lay safely under Andy’s bed, its secrets waiting to be discovered.

And somewhere, in a realm they had yet to explore, the next adventure was already beginning.

End of Book One.

Windows Powershell: Fake Blue Screen of Death (BSOD)

I have no idea why you need this, but if you’re unlucky enough to be on a windows device then you may as well get used to this, so here is a powershell for a BSOD..


while ($true) {
    [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
    [System.Windows.Forms.MessageBox]::Show("Blue Screen of Death!")
    Start-Sleep -Seconds 10
}

Mac OSX: Altering the OS route table to re-direct the traffic of a website to a different interface (eg re-routing whatsapp traffic to en0)

This was a hard article to figure out the title for! Put simply, your mac book has a route table and if you want to move a specific IP address or dns from one interface to another, then follow the steps below:

First find the IP address of the website that you want to re-route the traffic for:

$ nslookup web.whatsapp.com
Server:		100.64.0.1
Address:	100.64.0.1#53

Non-authoritative answer:
web.whatsapp.com	canonical name = mmx-ds.cdn.whatsapp.net.
Name:	mmx-ds.cdn.whatsapp.net
Address: 102.132.99.60

We want to re-route traffic the traffic from: 102.132.99.60 to the default interface. So first lets find out which interface this traffic is currently being routed to?

$ route -n get web.whatsapp.com
   route to: 102.132.99.60
destination: 102.132.99.60
    gateway: 100.64.0.1
  interface: utun0
      flags: <UP,GATEWAY,HOST,DONE,WASCLONED,IFSCOPE,IFREF>
 recvpipe  sendpipe  ssthresh  rtt,msec    rttvar  hopcount      mtu     expire
       0         0         0        34        21         0      1400         0

So this is currently going to a tunnelled interface called utun0 on gateway 100.64.0.1.

Ok, so I want to move if off this tunnelled interface. So lets first display the kernel routing table. The -n option forces netstat to print the IP addresses. Without this option, netstat attempts to display the host names.

$ netstat - rn | head -n 5
Active Internet connections
Proto Recv-Q Send-Q  Local Address          Foreign Address        (state)
tcp4       0    126  100.64.0.1.64770       136.226.216.14.https   ESTABLISHED
tcp4       0      0  100.64.0.1.64768       whatsapp-cdn-shv.https ESTABLISHED
tcp4       0      0  100.64.0.1.64766       52.178.17.3.https      ESTABLISHED

Now we want to re-route whatsapp to the default interface. So lets get the IP address of the default interface.

$ netstat -nr | grep default
default            192.168.8.1        UGScg                 en0
default                                 fe80::%utun1                            UGcIg               utun1
default                                 fe80::%utun2                            UGcIg               utun2
default                                 fe80::%utun3                            UGcIg               utun3
default                                 fe80::%utun4                            UGcIg               utun4
default                                 fe80::%utun5                            UGcIg               utun5
default                                 fe80::%utun0                            UGcIg               utun0

We can see that our en0 interface is on IP address: 192.168.8.1. So lets re-route the traffic from Whatsapp’s ip address to this interace’s IP address:

$ sudo route add 102.132.99.60 192.168.0.1
route: writing to routing socket: File exists
add host 102.132.99.60: gateway 192.168.8.1: File exists

Now lets test if we are routing via the correct interface:

$ route -n get 102.132.99.60
   route to: 102.132.99.60
destination: 102.132.99.60
    gateway: 192.168.8.1
  interface: utun6
      flags: <UP,GATEWAY,HOST,DONE,STATIC>
 recvpipe  sendpipe  ssthresh  rtt,msec    rttvar  hopcount      mtu     expire
       0         0         0         0         0         0      1400         0

Finally delete the route and recheck the routing:

$ sudo route delete 102.132.99.60
delete host 102.132.99.60

$ route -n get 102.132.99.60
   route to: 102.132.99.60
destination: 102.132.99.60
    gateway: 100.64.0.1
  interface: utun6
      flags: <UP,GATEWAY,HOST,DONE,WASCLONED,IFSCOPE,IFREF>
 recvpipe  sendpipe  ssthresh  rtt,msec    rttvar  hopcount      mtu     expire
       0         0         0         0         0         0      1400         0

Technology Culture: The Sinking Car Syndrome

This is (hopefully) a short blog that will give you back a small piece of your life…

In technology, we rightly spend hours pouring over failure in order that we might understand it and therefore fix it and avoid it in the future. This seems a reasonable approach, learn from your mistakes, understand failure, plan your remediation etc etc. But is it possible that there are some instances where doing this is inappropriate? To answer this simple question, let me give you an analogy…

You decide that you want to travel from London to New York. Sounds reasonable so far…. But you decide you want to go by car! The reasoning for this is as follows:

  1. Cars are “tried and tested”.
  2. We have an existing deal with multiple car suppliers and we get great discounts.
  3. The key decision maker is a car enthusiast.
  4. The incumbent team understand cars and can support this choice.
  5. Cars are what we have available right now and we want to start execution tomorrow, so lets just make it work.

You first try a small hatchback and only manage to get around 3m off the coast of Scotland. Next up you figure you will get a more durable car, so you get a truck – but sadly this only makes 2m headway from the beach. You report back to the team and they send you a brand new Porsche and this time you give yourself an even bigger run up at the sea and you manage to make a whopping 4m, before the car sinks. The team now analyse all the data to figure out why each car sunk and what they can do to make this better. The team continue to experiment with various cars and progress is observed over time. After 6 months the team has managed to travel 12m towards their goal of driving to New York. The main reason for the progress is that the sunken cars are starting to form a land bridge. The leadership has now spent over 200m USD on this venture and don’t feel they can pivot, so they start to brainstorm how to make this work.

Maybe wind the windows up a little tighter, maybe the cars need more underseal, maybe over inflate the tyres or maybe we simply need way more cars? All of these may or may not make a difference. But here’s the challenge: you made a bad engineering choice and anything you do will just be a variant of bad. It will never be good and you cannot win with your choice.

The above obviously sounds a bit daft (and it is), but the point is that I am often called in after downtime to review an architecture to find a route cause and suggest remediation. But what is not always understood is that bad technology choices can be as likely to succeed as driving from London to New York. Sometimes you simply need to look at alternatives, you need a boat or a plane. The product architecture can be terminal, it wont ever be what you want it to be and no amount of analysis or spend will change this. The trick is to accept the brutal reality of your situation and move your focus towards choosing the technology that you need to transition to. Next try and figure out how quickly can you can do this pivot…

Ms Sql Server 2019 Diagnostic Query

Finding issues in SQL Server is not alway that easy. It can be NUMA issues, it can be DBCC settings, it can even be the CU (eg CU19). A friend sent me a very useful query a few years ago that really helped me fault find these issues. It was written by Glenn Berry, but I lost the query. Luckily I came across his useful site again tonight. I am posting this query here so that I don’t loose this useful resource again. You can get this query and many other useful diagnostic queries here: Glenn Berry


-- SQL Server 2019 Diagnostic Information Queries
-- Glenn Berry 
-- Last Modified: November 2, 2023
-- https://glennsqlperformance.com/ 
-- https://sqlserverperformance.wordpress.com/
-- YouTube: https://bit.ly/2PkoAM1 
-- Twitter: GlennAlanBerry

-- Diagnostic Queries are available here
-- https://glennsqlperformance.com/resources/

-- YouTube video demonstrating these queries
-- https://bit.ly/3aXNDzJ


-- Please make sure you are using the correct version of these diagnostic queries for your version of SQL Server


-- If you like PowerShell, there is a very useful community solution for running these queries in an automated fashion
-- https://dbatools.io/

-- Invoke-DbaDiagnosticQuery
-- https://docs.dbatools.io/Invoke-DbaDiagnosticQuery


--******************************************************************************
--*   Copyright (C) 2023 Glenn Berry
--*   All rights reserved. 
--*
--*
--*   You may alter this code for your own *non-commercial* purposes. You may
--*   republish altered code as long as you include this copyright and give due credit. 
--*
--*
--*   THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
--*   ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
--*   TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
--*   PARTICULAR PURPOSE. 
--*
--******************************************************************************

-- Check the major product version to see if it is SQL Server 2019 CTP 2 or greater
IF NOT EXISTS (SELECT * WHERE CONVERT(varchar(128), SERVERPROPERTY('ProductVersion')) LIKE '15%')
	BEGIN
		DECLARE @ProductVersion varchar(128) = CONVERT(varchar(128), SERVERPROPERTY('ProductVersion'));
		RAISERROR ('Script does not match the ProductVersion [%s] of this instance. Many of these queries may not work on this version.' , 18 , 16 , @ProductVersion);
	END
	ELSE
		PRINT N'You have the correct major version of SQL Server for this diagnostic information script';
	

-- Instance level queries *******************************

-- SQL and OS Version information for current instance  (Query 1) (Version Info)
SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];
------

-- SQL Server 2019 Builds																		
-- Build			Description							Release Date	URL to KB Article								
-- 15.0.1000.34		CTP 2.0								9/24/2018
-- 15.0.1100.94		CTP 2.1								11/7/2018
-- 15.0.1200.24		CTP 2.2								12/6/2018
-- 15.0.1300.359	CTP 2.3								3/1/2019
-- 15.0.1400.75		CTP 2.4								3/26/2019
-- 15.0.1500.28		CTP 2.5								4/23/2019
-- 15.0.1600.8		CTP 3.0								5/22/2019
-- 15.0.1700.37		CTP 3.1								6/26/2019
-- 15.0.1800.32		CTP 3.2								7/24/2019
-- 15.0.1900.25		RC1/RC1 Refresh						8/29/2019
-- 15.0.2000.5		RTM									11/4/2019
-- 15.0.2070.41		GDR1								11/4/2019		https://support.microsoft.com/en-us/help/4517790/servicing-update-for-sql-server-2019-rtm 
-- 15.0.4003.23		CU1									 1/7/2020		https://support.microsoft.com/en-us/help/4527376/cumulative-update-1-for-sql-server-2019
-- 15.0.4013.40		CU2									2/13/2020		https://support.microsoft.com/en-us/help/4536075/cumulative-update-2-for-sql-server-2019
-- 15.0.4023.6		CU3									3/12/2020		https://support.microsoft.com/en-us/help/4538853/cumulative-update-3-for-sql-server-2019
-- 15.0.4033.1		CU4									3/31/2020		https://support.microsoft.com/en-us/help/4548597/cumulative-update-4-for-sql-server-2019
-- 15.0.4043.16		CU5									6/22/2020		https://support.microsoft.com/en-us/help/4552255/cumulative-update-5-for-sql-server-2019
-- 15.0.4053.23		CU6									 8/4/2020		https://support.microsoft.com/en-us/help/4563110/cumulative-update-6-for-sql-server-2019
-- 15.0.4063.15		CU7									 9/2/2020		-- CU7 was removed by Microsoft
-- 15.0.4073.23		CU8									10/1/2020		https://support.microsoft.com/en-in/help/4577194/cumulative-update-8-for-sql-server-2019
-- 15.0.4083.2		CU8 Security Update				    1/12/2021		https://support.microsoft.com/en-us/help/4583459/kb4583459-security-update-for-sql-server-2019-cu8
-- 15.0.4102.2		CU9									2/11/2021		https://support.microsoft.com/en-in/help/5000642/cumulative-update-9-for-sql-server-2019
-- 15.0.4123.1		CU10								 4/6/2021       https://support.microsoft.com/en-us/topic/kb5001090-cumulative-update-10-for-sql-server-2019-b6b696ec-6598-48d9-80ee-f1b85d7a508b
-- 15.0.4138.2		CU11								6/10/2021		https://support.microsoft.com/en-us/topic/kb5003249-cumulative-update-11-for-sql-server-2019-657b2977-a0f1-4e1f-8b93-8c2ca8b6bef5
-- 15.0.4153.1		CU12								 8/4/2021		https://support.microsoft.com/en-us/topic/kb5004524-cumulative-update-12-for-sql-server-2019-45b2d82a-c7d0-4eb8-aa17-d4bad4059987
-- 15.0.4178.1		CU13								10/5/2021		https://support.microsoft.com/en-us/topic/kb5005679-cumulative-update-13-for-sql-server-2019-5c1be850-460a-4be4-a569-fe11f0adc535							
-- 15.0.4188.2		CU14							   11/22/2021		https://support.microsoft.com/sl-si/topic/kb5007182-cumulative-update-14-for-sql-server-2019-67b00a61-4f30-4a36-a5db-b506c47e563b
-- 15.0.4198.2		CU15								1/27/2022		https://support.microsoft.com/en-us/topic/kb5008996-cumulative-update-15-for-sql-server-2019-4b6a8ee9-1c61-482d-914f-36e429901fb6
-- 15.0.4223.1		CU16								4/18/2022		https://support.microsoft.com/en-us/topic/kb5011644-cumulative-update-16-for-sql-server-2019-74377be1-4340-4445-93a7-ff843d346896
-- 15.0.4236.7		CU16 Security Update				6/14/2022		https://support.microsoft.com/en-us/topic/kb5014353-description-of-the-security-update-for-sql-server-2019-cu16-june-14-2022-f0afe659-bd19-4c87-a417-a4c67a47e644
-- 15.0.4249.2		CU17								8/11/2022		https://support.microsoft.com/en-us/topic/kb5016394-cumulative-update-17-for-sql-server-2019-3033f654-b09d-41aa-8e49-e9d0c353c5f7
-- 15.0.4261.1		CU18								9/28/2022		https://support.microsoft.com/en-us/topic/kb5017593-cumulative-update-18-for-sql-server-2019-5fa00c36-edeb-446c-94e3-c4882b7526bc
-- 15.0.4280.7		CU18 GDR							2/14/2023		https://support.microsoft.com/en-us/topic/kb5021124-description-of-the-security-update-for-sql-server-2019-cu18-february-14-2023-cfb75a0a-33dc-4e05-8645-4cf16fcec049
-- 15.0.4298.1		CU19								2/16/2023		https://support.microsoft.com/en-us/topic/kb5023049-cumulative-update-19-for-sql-server-2019-b63d7163-e2e7-46f7-b50a-c3d1f2913219
-- 15.0.4312.2		CU20								4/13/2023		https://support.microsoft.com/en-us/topic/kb5024276-cumulative-update-20-for-sql-server-2019-4b282be9-b559-46ac-9b6a-badbd44785d2
-- 15.0.4316.3		CU21								6/15/2022		https://learn.microsoft.com/en-us/troubleshoot/sql/releases/sqlserver-2019/cumulativeupdate21
-- 15.0.4322.2		CU22								8/14/2023		https://learn.microsoft.com/en-us/troubleshoot/sql/releases/sqlserver-2019/cumulativeupdate22
-- 15.0.4326.1		CU22 + GDR							10/10/2023		https://support.microsoft.com/en-us/topic/kb5029378-description-of-the-security-update-for-sql-server-2019-cu22-october-10-2023-f4b5c5fb-b4cd-4599-8e5b-2a54dab85a33
-- 15.0.4335.1		CU23								10/12/2023		https://learn.microsoft.com/en-us/troubleshoot/sql/releases/sqlserver-2019/cumulativeupdate23		

-- How to determine the version, edition and update level of SQL Server and its components 
-- https://bit.ly/2oAjKgW	

-- SQL Server 2019 build versions
-- https://bit.ly/3EzGQZV

-- Performance and Stability Fixes in SQL Server 2019 CU Builds
-- https://bit.ly/3712NQQ

-- What's New in SQL Server 2019 (Database Engine)
-- https://bit.ly/2Q29fhz

-- What's New in SQL Server 2019
-- https://bit.ly/2PY442b

-- Announcing the Modern Servicing Model for SQL Server
-- https://bit.ly/2KtJ8SS

-- Update Center for Microsoft SQL Server
-- https://bit.ly/2pZptuQ

-- Download SQL Server Management Studio (SSMS)
-- https://bit.ly/1OcupT9

-- Download and install Azure Data Studio 
-- https://bit.ly/2vgke1A

-- SQL Server 2019 Configuration Manager is SQLServerManager15.msc

-- SQL Server troubleshooting (Microsoft documentation resources)
-- http://bit.ly/2YY0pb1


-- Get socket, physical core and logical core count from the SQL Server Error log. (Query 2) (Core Counts)
-- This query might take a few seconds depending on the size of your error log
EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';
------

-- This can help you determine the exact core counts used by SQL Server and whether HT is enabled or not
-- It can also help you confirm your SQL Server licensing model
-- Be on the lookout for this message "using 40 logical processors based on SQL Server licensing" 
-- (when you have more than 40 logical cores) which means grandfathered Server/CAL licensing
-- This query will return no results if your error log has been recycled since the instance was last started



-- Get selected server properties (Query 3) (Server Properties)
SELECT SERVERPROPERTY('MachineName') AS [MachineName], 
SERVERPROPERTY('ServerName') AS [ServerName],  
SERVERPROPERTY('InstanceName') AS [Instance], 
SERVERPROPERTY('IsClustered') AS [IsClustered], 
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], 
SERVERPROPERTY('Edition') AS [Edition], 
SERVERPROPERTY('ProductLevel') AS [ProductLevel],				-- What servicing branch (RTM/SP/CU)
SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel],	-- Within a servicing branch, what CU# is applied
SERVERPROPERTY('ProductVersion') AS [ProductVersion],
SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion], 
SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion], 
SERVERPROPERTY('ProductBuild') AS [ProductBuild], 
SERVERPROPERTY('ProductBuildType') AS [ProductBuildType],			  -- Is this a GDR or OD hotfix (NULL if on a CU build)
SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference], -- KB article number that is applicable for this build
SERVERPROPERTY('ProcessID') AS [ProcessID],
SERVERPROPERTY('Collation') AS [Collation], 
SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled], 
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],
SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],
SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled], 
SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus],
SERVERPROPERTY('InstanceDefaultDataPath') AS [InstanceDefaultDataPath],
SERVERPROPERTY('InstanceDefaultLogPath') AS [InstanceDefaultLogPath],
SERVERPROPERTY('InstanceDefaultBackupPath') AS [InstanceDefaultBackupPath],
SERVERPROPERTY('ErrorLogFileName') AS [ErrorLogFileName],
SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version],
SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported],
SERVERPROPERTY('IsPolybaseInstalled') AS [IsPolybaseInstalled],				
SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS [IsRServicesInstalled],
SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS [IsTempdbMetadataMemoryOptimized];	
------

-- This gives you a lot of useful information about your instance of SQL Server,
-- such as the ProcessID for SQL Server and your collation
-- Note: Some columns will be NULL on older SQL Server builds

-- SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') is a new option for SQL Server 2019

-- SERVERPROPERTY (Transact-SQL)
-- https://bit.ly/2eeaXeI



-- Get instance-level configuration values for instance  (Query 4) (Configuration Values)
SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);
------

-- Focus on these settings:
-- automatic soft-NUMA disabled (should be 0 in most cases)
-- backup checksum default (should be 1)
-- backup compression default (should be 1 in most cases)
-- clr enabled (only enable if it is needed)
-- cost threshold for parallelism (depends on your workload)
-- lightweight pooling (should be zero)
-- max degree of parallelism (depends on your workload and hardware)
-- max server memory (MB) (set to an appropriate value, not the default)
-- optimize for ad hoc workloads (should be 1)
-- priority boost (should be zero)
-- remote admin connections (should be 1)
-- tempdb metadata memory-optimized (0 by default, some workloads may benefit by enabling)

-- sys.configurations (Transact-SQL)
-- https://bit.ly/2HsyDZI


-- Returns a list of all global trace flags that are enabled (Query 5) (Global Trace Flags)
DBCC TRACESTATUS (-1);
------

-- If no global trace flags are enabled, no results will be returned.
-- It is very useful to know what global trace flags are currently enabled as part of the diagnostic process.

-- Common trace flags that should be enabled in most cases
-- TF 3226 - Suppresses logging of successful database backup messages to the SQL Server Error Log
--           https://bit.ly/38zDNAK   

-- TF 6534 - Enables use of native code to improve performance with spatial data. This is a startup trace flag only
--           https://bit.ly/2HrQUpU         

-- TF 7745 - Prevents Query Store data from being written to disk in case of a failover or shutdown command
--           https://bit.ly/2GU69Km

-- TF 8121 - Fixes a system-wide low memory issue that occurs when SQL Server commits memory above the maximum server memory under the memory model with the Lock Pages In Memory (added in CU15)
--           https://learn.microsoft.com/en-US/troubleshoot/sql/releases/sqlserver-2019/cumulativeupdate15#bkmk_14421838




-- DBCC TRACEON - Trace Flags (Transact-SQL)
-- https://bit.ly/2FuSvPg



-- SQL Server Process Address space info  (Query 6) (Process Memory)
-- (shows whether locked pages is enabled, among other things)
SELECT physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)],
	   locked_page_allocations_kb/1024 AS [SQL Server Locked Pages Allocation (MB)],
       large_page_allocations_kb/1024 AS [SQL Server Large Pages Allocation (MB)], 
	   page_fault_count, memory_utilization_percentage, available_commit_limit_kb, 
	   process_physical_memory_low, process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);
------

-- You want to see 0 for process_physical_memory_low
-- You want to see 0 for process_virtual_memory_low
-- This indicates that you are not under internal memory pressure
-- If locked_page_allocations_kb > 0, then LPIM is enabled

-- sys.dm_os_process_memory (Transact-SQL)
-- https://bit.ly/3iUgQgC

-- How to enable the "locked pages" feature in SQL Server 2012
-- https://bit.ly/2F5UjOA

-- Memory Management Architecture Guide
-- https://bit.ly/2JKkadC 



-- SQL Server Services information (Query 7) (SQL Server Services Info)
SELECT servicename, process_id, startup_type_desc, status_desc, 
last_startup_time, service_account, is_clustered, cluster_nodename, [filename], 
instant_file_initialization_enabled
FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);
------

-- Tells you the account being used for the SQL Server Service and the SQL Agent Service
-- Shows the process_id, when they were last started, and their current status
-- Also shows whether you are running on a failover cluster instance, and what node you are running on
-- Also shows whether IFI is enabled

-- sys.dm_server_services (Transact-SQL)
-- https://bit.ly/2oKa1Un


-- Last backup information by database  (Query 8) (Last Backup By Database)
SELECT ISNULL(d.[name], bs.[database_name]) AS [Database], d.recovery_model_desc AS [Recovery Model], 
    d.log_reuse_wait_desc AS [Log Reuse Wait Desc],
	CONVERT(DECIMAL(18,2), ds.cntr_value/1024.0) AS [Total Data File Size on Disk (MB)],
	CONVERT(DECIMAL(18,2), ls.cntr_value/1024.0) AS [Total Log File Size on Disk (MB)], 
	CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Log Used %],
    MAX(CASE WHEN bs.[type] = 'D' THEN bs.backup_finish_date ELSE NULL END) AS [Last Full Backup],
	MAX(CASE WHEN bs.[type] = 'D' THEN CONVERT (BIGINT, bs.compressed_backup_size / 1048576 ) ELSE NULL END) AS [Last Full Compressed Backup Size (MB)],
	MAX(CASE WHEN bs.[type] = 'D' THEN CONVERT (DECIMAL(18,2), bs.backup_size /bs.compressed_backup_size ) ELSE NULL END) AS [Backup Compression Ratio],
    MAX(CASE WHEN bs.[type] = 'I' THEN bs.backup_finish_date ELSE NULL END) AS [Last Differential Backup],
    MAX(CASE WHEN bs.[type] = 'L' THEN bs.backup_finish_date ELSE NULL END) AS [Last Log Backup],
	DATABASEPROPERTYEX ((d.[name]), 'LastGoodCheckDbTime') AS [Last Good CheckDB]
FROM sys.databases AS d WITH (NOLOCK)
INNER JOIN sys.master_files as mf WITH (NOLOCK)
ON d.database_id = mf.database_id
LEFT OUTER JOIN msdb.dbo.backupset AS bs WITH (NOLOCK)
ON bs.[database_name] = d.[name]
AND bs.backup_finish_date > GETDATE()- 30
LEFT OUTER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
ON d.name = lu.instance_name
LEFT OUTER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK)
ON d.name = ls.instance_name
INNER JOIN sys.dm_os_performance_counters AS ds WITH (NOLOCK)
ON d.name = ds.instance_name
WHERE d.name <> N'tempdb'
AND lu.counter_name LIKE N'Log File(s) Used Size (KB)%' 
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ds.counter_name LIKE N'Data File(s) Size (KB)%'
AND ls.cntr_value > 0 
GROUP BY ISNULL(d.[name], bs.[database_name]), d.recovery_model_desc, d.log_reuse_wait_desc, d.[name],
         CONVERT(DECIMAL(18,2), ds.cntr_value/1024.0),
	     CONVERT(DECIMAL(18,2), ls.cntr_value/1024.0), 
         CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100
ORDER BY d.recovery_model_desc, d.[name] OPTION (RECOMPILE);
------

-- This helps you spot runaway transaction logs and other issues with your backup schedule


-- Get SQL Server Agent jobs and Category information (Query 9) (SQL Server Agent Jobs)
SELECT sj.name AS [Job Name], sj.[description] AS [Job Description], 
sc.name AS [CategoryName], SUSER_SNAME(sj.owner_sid) AS [Job Owner],
sj.date_created AS [Date Created], sj.[enabled] AS [Job Enabled], 
sj.notify_email_operator_id, sj.notify_level_email, h.run_status,
RIGHT(STUFF(STUFF(REPLACE(STR(h.run_duration, 7, 0), ' ', '0'), 4, 0, ':'), 7, 0, ':'),8) AS [Last Duration - HHMMSS],
CONVERT(DATETIME, RTRIM(h.run_date) + ' ' + STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0),' ','0'),3,0,':'),6,0,':')) AS [Last Start Date]
FROM msdb.dbo.sysjobs AS sj WITH (NOLOCK)
INNER JOIN
    (SELECT job_id, instance_id = MAX(instance_id)
     FROM msdb.dbo.sysjobhistory WITH (NOLOCK)
     GROUP BY job_id) AS l
ON sj.job_id = l.job_id
INNER JOIN msdb.dbo.syscategories AS sc WITH (NOLOCK)
ON sj.category_id = sc.category_id
INNER JOIN msdb.dbo.sysjobhistory AS h WITH (NOLOCK)
ON h.job_id = l.job_id
AND h.instance_id = l.instance_id
ORDER BY CONVERT(INT, h.run_duration) DESC, [Last Start Date] DESC OPTION (RECOMPILE);
------

--run_status	
-- Value   Status of the job execution
-- 0 =     Failed
-- 1 =     Succeeded
-- 2 =     Retry
-- 3 =     Canceled
-- 4 =     In Progress


-- Gives you some basic information about your SQL Server Agent jobs, who owns them and how they are configured
-- Look for Agent jobs that are not owned by sa
-- Look for jobs that have a notify_email_operator_id set to 0 (meaning no operator)
-- Look for jobs that have a notify_level_email set to 0 (meaning no e-mail is ever sent)
--
-- MSDN sysjobs documentation
-- https://bit.ly/2paDEOP 

-- SQL Server Maintenance Solution (Ola Hallengren)
-- https://bit.ly/1pgchQu  

-- You can use this script to add default schedules to the standard Ola Hallengren Maintenance Solution jobs
-- https://bit.ly/3ane0gN


-- Get SQL Server Agent Alert Information (Query 10) (SQL Server Agent Alerts)
SELECT name, event_source, message_id, severity, [enabled], has_notification, 
       delay_between_responses, occurrence_count, last_occurrence_date, last_occurrence_time
FROM msdb.dbo.sysalerts WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);
------

-- Gives you some basic information about your SQL Server Agent Alerts 
-- (which are different from SQL Server Agent jobs)
-- Read more about Agent Alerts here: https://bit.ly/2v5YR37 



-- Host information (Query 11) (Host Info)
SELECT host_platform, host_distribution, host_release, 
       host_service_pack_level, host_sku, os_language_version,
	   host_architecture
FROM sys.dm_os_host_info WITH (NOLOCK) OPTION (RECOMPILE); 
------

-- host_release codes (only valid for Windows)
-- 10.0 is either Windows 10, Windows Server 2016 or Windows Server 2019
-- 6.3 is either Windows 8.1 or Windows Server 2012 R2 
-- 6.2 is either Windows 8 or Windows Server 2012


-- host_sku codes (only valid for Windows)
-- 4 is Enterprise Edition
-- 7 is Standard Server Edition
-- 8 is Datacenter Server Edition
-- 10 is Enterprise Server Edition
-- 48 is Professional Edition
-- 161 is Pro for Workstations

-- 1033 for os_language_version is US-English

-- SQL Server 2019 requires Windows Server 2016 or newer 

-- Hardware and Software Requirements for Installing SQL Server
-- https://bit.ly/2y3ka5L

-- Using SQL Server in Windows 8 and later versions of Windows operating system
-- https://bit.ly/2F7Ax0P 


-- SQL Server NUMA Node information  (Query 12) (SQL Server NUMA Info)
SELECT osn.node_id, osn.node_state_desc, osn.memory_node_id, osn.processor_group, osn.cpu_count, osn.online_scheduler_count, 
       osn.idle_scheduler_count, osn.active_worker_count, 
	   osmn.pages_kb/1024 AS [Committed Memory (MB)], 
	   osmn.locked_page_allocations_kb/1024 AS [Locked Physical (MB)],
	   CONVERT(DECIMAL(18,2), osmn.foreign_committed_kb/1024.0) AS [Foreign Commited (MB)],
	   osmn.target_kb/1024 AS [Target Memory Goal (MB)],
	   osn.avg_load_balance, osn.resource_monitor_state
FROM sys.dm_os_nodes AS osn WITH (NOLOCK)
INNER JOIN sys.dm_os_memory_nodes AS osmn WITH (NOLOCK)
ON osn.memory_node_id = osmn.memory_node_id
WHERE osn.node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);
------

-- Gives you some useful information about the composition and relative load on your NUMA nodes
-- You want to see an equal number of schedulers on each NUMA node
-- Watch out if SQL Server 2019 Standard Edition has been installed 
-- on a physical or virtual machine with more than four sockets or more than 24 physical cores

-- sys.dm_os_nodes (Transact-SQL)
-- https://bit.ly/2pn5Mw8

-- How to Balance SQL Server Core Licenses Across NUMA Nodes
-- https://bit.ly/3i4TyVR



-- Good basic information about OS memory amounts and state  (Query 13) (System Memory)
SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)], 
       available_physical_memory_kb/1024 AS [Available Memory (MB)], 
       total_page_file_kb/1024 AS [Page File Commit Limit (MB)],
	   total_page_file_kb/1024 - total_physical_memory_kb/1024 AS [Physical Page File Size (MB)],
	   available_page_file_kb/1024 AS [Available Page File (MB)], 
	   system_cache_kb/1024 AS [System Cache (MB)],
       system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
------

-- You want to see "Available physical memory is high" for System Memory State
-- This indicates that you are not under external memory pressure

-- Possible System Memory State values:
-- Available physical memory is high
-- Physical memory usage is steady
-- Available physical memory is low
-- Available physical memory is running low
-- Physical memory state is transitioning

-- sys.dm_os_sys_memory (Transact-SQL)
-- https://bit.ly/2pcV0xq



-- You can skip the next two queries if you know you don't have a clustered instance


-- Get information about your cluster nodes and their status  (Query 14) (Cluster Node Properties)
-- (if your database server is in a failover cluster)
SELECT NodeName, status_description, is_current_owner
FROM sys.dm_os_cluster_nodes WITH (NOLOCK) OPTION (RECOMPILE);
------

-- Knowing which node owns the cluster resources is critical
-- Especially when you are installing Windows or SQL Server updates
-- You will see no results if your instance is not clustered

-- Recommended hotfixes and updates for Windows Server 2012 R2-based failover clusters
-- https://bit.ly/1z5BfCw


-- Get information about any AlwaysOn AG cluster this instance is a part of (Query 15) (AlwaysOn AG Cluster)
SELECT cluster_name, quorum_type_desc, quorum_state_desc
FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);
------

-- You will see no results if your instance is not using AlwaysOn AGs


-- Good overview of AG health and status (Query 16) (AG Status)
SELECT ag.name AS [AG Name], ar.replica_server_name, ar.availability_mode_desc, adc.[database_name], 
       drs.is_local, drs.is_primary_replica, drs.synchronization_state_desc, drs.is_commit_participant, 
	   drs.synchronization_health_desc, drs.recovery_lsn, drs.truncation_lsn, drs.last_sent_lsn, 
	   drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn, 
	   drs.last_hardened_time, drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size, 
	   drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate, 
	   drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.database_state_desc 
FROM sys.dm_hadr_database_replica_states AS drs WITH (NOLOCK)
INNER JOIN sys.availability_databases_cluster AS adc WITH (NOLOCK)
ON drs.group_id = adc.group_id 
AND drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag WITH (NOLOCK)
ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar WITH (NOLOCK)
ON drs.group_id = ar.group_id 
AND drs.replica_id = ar.replica_id
ORDER BY ag.name, ar.replica_server_name, adc.[database_name] OPTION (RECOMPILE);

-- You will see no results if your instance is not using AlwaysOn AGs

-- SQL Server 2016 � It Just Runs Faster: Always On Availability Groups Turbocharged
-- https://bit.ly/2dn1H6r


-- Hardware information from SQL Server 2019  (Query 17) (Hardware Info)
SELECT cpu_count AS [Logical CPU Count], scheduler_count, 
       (socket_count * cores_per_socket) AS [Physical Core Count], 
       socket_count AS [Socket Count], cores_per_socket, numa_node_count,
       physical_memory_kb/1024 AS [Physical Memory (MB)], 
       max_workers_count AS [Max Workers Count], 
	   affinity_type_desc AS [Affinity Type], 
       sqlserver_start_time AS [SQL Server Start Time],
	   DATEDIFF(hour, sqlserver_start_time, GETDATE()) AS [SQL Server Up Time (hrs)],
	   virtual_machine_type_desc AS [Virtual Machine Type], 
       softnuma_configuration_desc AS [Soft NUMA Configuration], 
	   sql_memory_model_desc, 
	   container_type_desc -- New in SQL Server 2019
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);
------

-- Gives you some good basic hardware information about your database server
-- Note: virtual_machine_type_desc of HYPERVISOR does not automatically mean you are running SQL Server inside of a VM
-- It merely indicates that you have a hypervisor running on your host

-- sys.dm_os_sys_info (Transact-SQL)
-- https://bit.ly/2pczOYs

-- Soft NUMA configuration was a new column for SQL Server 2016
-- OFF = Soft-NUMA feature is OFF
-- ON = SQL Server automatically determines the NUMA node sizes for Soft-NUMA
-- MANUAL = Manually configured soft-NUMA

-- Configure SQL Server to Use Soft-NUMA (SQL Server)
-- https://bit.ly/2HTpKJt

-- sql_memory_model_desc values (Added in SQL Server 2016 SP1)
-- CONVENTIONAL
-- LOCK_PAGES
-- LARGE_PAGES
   

-- Get System Manufacturer and model number from SQL Server Error log (Query 18) (System Manufacturer)
EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';
------ 

-- This can help you determine the capabilities and capacities of your database server
-- Can also be used to confirm if you are running in a VM
-- This query might take a few seconds if you have not recycled your error log recently
-- This query will return no results if your error log has been recycled since the instance was started


-- Get BIOS date from Windows Registry (Query 19) (BIOS Date)
EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\BIOS', N'BiosReleaseDate';
------

-- Helps you understand whether the main system BIOS is up to date, and the possible age of the hardware
-- Not as useful for virtualization
-- Does not work on Linux


-- Get processor description from Windows Registry  (Query 20) (Processor Description)
EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';
------

-- Gives you the model number and rated clock speed of your processor(s)
-- Your processors may be running at less than the rated clock speed due
-- to the Windows Power Plan or hardware power management
-- Does not work on Linux

-- You can use CPU-Z to get your actual CPU core speed and a lot of other useful information
-- https://bit.ly/QhR6xF

-- You can learn more about processor selection for SQL Server by following this link
-- https://bit.ly/2F3aVlP




-- Get information on location, time and size of any memory dumps from SQL Server  (Query 21) (Memory Dump Info)
SELECT [filename], creation_time, size_in_bytes/1048576.0 AS [Size (MB)]
FROM sys.dm_server_memory_dumps WITH (NOLOCK) 
ORDER BY creation_time DESC OPTION (RECOMPILE);
------

-- This will not return any rows if you have 
-- not had any memory dumps (which is a good thing)

-- sys.dm_server_memory_dumps (Transact-SQL)
-- https://bit.ly/2elwWll



-- Look at Suspect Pages table (Query 22) (Suspect Pages)
SELECT DB_NAME(sp.database_id) AS [Database Name], 
       sp.[file_id], sp.page_id, sp.event_type, 
	   sp.error_count, sp.last_update_date,
	   mf.name AS [Logical Name], mf.physical_name AS [File Path]
FROM msdb.dbo.suspect_pages AS sp WITH (NOLOCK)
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON mf.database_id = sp.database_id 
AND mf.file_id = sp.file_id
ORDER BY sp.database_id OPTION (RECOMPILE);
------

-- event_type value descriptions
-- 1 = 823 error caused by an operating system CRC error
--     or 824 error other than a bad checksum or a torn page (for example, a bad page ID)
-- 2 = Bad checksum
-- 3 = Torn page
-- 4 = Restored (The page was restored after it was marked bad)
-- 5 = Repaired (DBCC repaired the page)
-- 7 = Deallocated by DBCC

-- Ideally, this query returns no results. The table is limited to 1000 rows.
-- If you do get results here, you should do further investigation to determine the root cause

-- Manage the suspect_pages Table
-- https://bit.ly/2Fvr1c9


-- Read most recent entries from all SQL Server Error Logs (Query 23) (Error Log Entries)
DROP TABLE IF EXISTS #ErrorLogFiles;
	CREATE TABLE #ErrorLogFiles
	([Archive #] INT,[Date] NVARCHAR(25),[Log File Size (Byte)]INT)

INSERT INTO #ErrorLogFiles
([Archive #],[Date],[Log File Size (Byte)])
EXEC master.sys.xp_enumerrorlogs;

DROP TABLE IF EXISTS #SQLErrorLog_AllLogs;
	CREATE TABLE #SQLErrorLog_AllLogs
	(LogDate DATETIME ,ProcessInfo NVARCHAR(12), LogText NVARCHAR(4000))

DECLARE @i INT = 0;
DECLARE @sql NVARCHAR(200) = N'';
DECLARE @logCount INT = (SELECT COUNT(*) FROM #ErrorLogFiles);

WHILE (@i < @logCount)
    BEGIN
        IF(@i in (SELECT [Archive #] FROM #ErrorLogFiles))
            BEGIN
                SET @sql = N'INSERT INTO #SQLErrorLog_AllLogs (LogDate, ProcessInfo, LogText)
                             EXEC master.sys.sp_readerrorlog ' + CAST(@i AS NVARCHAR(2)) + N';'
                EXEC master.sys.sp_executesql @sql;
            END
        SET @i += 1;
    END

SELECT TOP(1000)LogDate, ProcessInfo, LogText 
FROM #SQLErrorLog_AllLogs WITH (NOLOCK)
ORDER BY LogDate DESC OPTION (RECOMPILE);

DROP TABLE IF EXISTS #ErrorLogFiles;
DROP TABLE IF EXISTS #SQLErrorLog_AllLogs;
GO
------


-- Get number of data files in tempdb database (Query 24) (TempDB Data Files)
EXEC sys.xp_readerrorlog 0, 1, N'The tempdb database has';
------

-- Get the number of data files in the tempdb database
-- 4-8 data files that are all the same size is a good starting point
-- This query will return no results if your error log has been recycled since the instance was last started



-- Find unequal tempdb data initial file sizes (Query 25) (Tempdb Data File Sizes)
-- This query might take a few seconds depending on the size of your error log
EXEC sys.xp_readerrorlog 0, 1, N'The tempdb database data files are not configured with the same initial size';
------

-- You want this query to return no results
-- All of your tempdb data files should have the same initial size and autogrowth settings 
-- This query will also return no results if your error log has been recycled since the instance was last started
-- KB3170020 - Informational messages added for tempdb configuration in the SQL Server error log in SQL Server 2012 and 2014
-- https://bit.ly/3IsR8jh


-- File names and paths for all user and system databases on instance  (Query 26) (Database Filenames and Paths)
SELECT DB_NAME([database_id]) AS [Database Name], 
       [file_id], [name], physical_name, [type_desc], state_desc,
	   is_percent_growth, growth, 
	   CONVERT(bigint, growth/128.0) AS [Growth in MB], 
       CONVERT(bigint, size/128.0) AS [Total Size in MB], max_size
FROM sys.master_files WITH (NOLOCK)
ORDER BY DB_NAME([database_id]), [file_id] OPTION (RECOMPILE);
------

-- Things to look at:
-- Are data files and log files on different drives?
-- Is everything on the C: drive?
-- Is tempdb on dedicated drives?
-- Is there only one tempdb data file?
-- Are all of the tempdb data files the same size?
-- Are there multiple data files for user databases?
-- Is percent growth enabled for any files (which is bad)?


-- Drive information for all fixed drives visible to the operating system (Query 27) (Fixed Drives)
SELECT fixed_drive_path, drive_type_desc, 
CONVERT(DECIMAL(18,2), free_space_in_bytes/1073741824.0) AS [Available Space (GB)]
FROM sys.dm_os_enumerate_fixed_drives WITH (NOLOCK) OPTION (RECOMPILE);
------

-- This shows all of your drives, not just LUNs with SQL Server database files
-- New in SQL Server 2017

-- sys.dm_os_enumerate_fixed_drives (Transact-SQL)
-- https://bit.ly/2EZoHLj



-- Volume info for all LUNS that have database files on the current instance (Query 28) (Volume Info)
SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name, 
CONVERT(DECIMAL(18,2), vs.total_bytes/1073741824.0) AS [Total Size (GB)],
CONVERT(DECIMAL(18,2), vs.available_bytes/1073741824.0) AS [Available Size (GB)],  
CONVERT(DECIMAL(18,2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %],
vs.supports_compression, vs.is_compressed, 
vs.supports_sparse_files, vs.supports_alternate_streams
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs 
ORDER BY vs.volume_mount_point OPTION (RECOMPILE);
------

-- Shows you the total and free space on the LUNs where you have database files
-- Being low on free space can negatively affect performance

-- sys.dm_os_volume_stats (Transact-SQL)
-- https://bit.ly/2oBPNNr



-- Drive level latency information (Query 29) (Drive Level Latency)
SELECT tab.[Drive], tab.volume_mount_point AS [Volume Mount Point], 
	CASE 
		WHEN num_of_reads = 0 THEN 0 
		ELSE (io_stall_read_ms/num_of_reads) 
	END AS [Read Latency],
	CASE 
		WHEN num_of_writes = 0 THEN 0 
		ELSE (io_stall_write_ms/num_of_writes) 
	END AS [Write Latency],
	CASE 
		WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 
		ELSE (io_stall/(num_of_reads + num_of_writes)) 
	END AS [Overall Latency],
	CASE 
		WHEN num_of_reads = 0 THEN 0 
		ELSE (num_of_bytes_read/num_of_reads) 
	END AS [Avg Bytes/Read],
	CASE 
		WHEN num_of_writes = 0 THEN 0 
		ELSE (num_of_bytes_written/num_of_writes) 
	END AS [Avg Bytes/Write],
	CASE 
		WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 
		ELSE ((num_of_bytes_read + num_of_bytes_written)/(num_of_reads + num_of_writes)) 
	END AS [Avg Bytes/Transfer]
FROM (SELECT LEFT(UPPER(mf.physical_name), 2) AS Drive, SUM(num_of_reads) AS num_of_reads,
	         SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,
	         SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,
	         SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall, vs.volume_mount_point 
      FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
      INNER JOIN sys.master_files AS mf WITH (NOLOCK)
      ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
	  CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) AS vs 
      GROUP BY LEFT(UPPER(mf.physical_name), 2), vs.volume_mount_point) AS tab
ORDER BY [Overall Latency] OPTION (RECOMPILE);
------

-- Shows you the drive-level latency for reads and writes, in milliseconds
-- Latency above 30-40ms is usually a problem
-- These latency numbers include all file activity against all SQL Server 
-- database files on each drive since SQL Server was last started

-- sys.dm_io_virtual_file_stats (Transact-SQL)
-- https://bit.ly/3bRWUc0

-- sys.dm_os_volume_stats (Transact-SQL)
-- https://bit.ly/33thz2j


-- Calculates average latency per read, per write, and per total input/output for each database file  (Query 30) (IO Latency by File)
SELECT DB_NAME(fs.database_id) AS [Database Name], CAST(fs.io_stall_read_ms/(1.0 + fs.num_of_reads) AS NUMERIC(10,1)) AS [avg_read_latency_ms],
CAST(fs.io_stall_write_ms/(1.0 + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_write_latency_ms],
CAST((fs.io_stall_read_ms + fs.io_stall_write_ms)/(1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_io_latency_ms],
CONVERT(DECIMAL(18,2), mf.size/128.0) AS [File Size (MB)], mf.physical_name, mf.type_desc, fs.io_stall_read_ms, fs.num_of_reads, 
fs.io_stall_write_ms, fs.num_of_writes, fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls], fs.num_of_reads + fs.num_of_writes AS [total_io],
io_stall_queued_read_ms AS [Resource Governor Total Read IO Latency (ms)], io_stall_queued_write_ms AS [Resource Governor Total Write IO Latency (ms)] 
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_latency_ms DESC OPTION (RECOMPILE);
------

-- Helps determine which database files on the entire instance have the most I/O bottlenecks
-- This can help you decide whether certain LUNs are overloaded and whether you might
-- want to move some files to a different location or perhaps improve your I/O performance
-- These latency numbers include all file activity against each SQL Server 
-- database file since SQL Server was last started

-- sys.dm_io_virtual_file_stats (Transact-SQL)
-- https://bit.ly/3bRWUc0


-- Look for I/O requests taking longer than 15 seconds in the six most recent SQL Server Error Logs (Query 31) (IO Warnings)
CREATE TABLE #IOWarningResults(LogDate datetime, ProcessInfo sysname, LogText nvarchar(1000));

	INSERT INTO #IOWarningResults 
	EXEC xp_readerrorlog 0, 1, N'taking longer than 15 seconds';

	INSERT INTO #IOWarningResults 
	EXEC xp_readerrorlog 1, 1, N'taking longer than 15 seconds';

	INSERT INTO #IOWarningResults 
	EXEC xp_readerrorlog 2, 1, N'taking longer than 15 seconds';

	INSERT INTO #IOWarningResults 
	EXEC xp_readerrorlog 3, 1, N'taking longer than 15 seconds';

	INSERT INTO #IOWarningResults 
	EXEC xp_readerrorlog 4, 1, N'taking longer than 15 seconds';

	INSERT INTO #IOWarningResults 
	EXEC xp_readerrorlog 5, 1, N'taking longer than 15 seconds';

SELECT LogDate, ProcessInfo, LogText
FROM #IOWarningResults
ORDER BY LogDate DESC;

DROP TABLE IF EXISTS #IOWarningResults;
------  

-- Finding 15 second I/O warnings in the SQL Server Error Log is useful evidence of
-- poor I/O performance (which might have many different causes)
-- Look to see if you see any patterns in the results (same files, same drives, same time of day, etc.)

-- Diagnostics in SQL Server help detect stalled and stuck I/O operations
-- https://bit.ly/2qtaw73


-- Resource Governor Resource Pool information (Query 32) (RG Resource Pools)
SELECT pool_id, [name], statistics_start_time,
       min_memory_percent, max_memory_percent,  
       max_memory_kb/1024 AS [max_memory_mb],  
       used_memory_kb/1024 AS [used_memory_mb],   
       target_memory_kb/1024 AS [target_memory_mb],
	   min_iops_per_volume, max_iops_per_volume
FROM sys.dm_resource_governor_resource_pools WITH (NOLOCK)
OPTION (RECOMPILE);
------

-- sys.dm_resource_governor_resource_pools (Transact-SQL)
-- https://bit.ly/2MVU0Vy



-- Recovery model, log reuse wait description, log file size, log usage size  (Query 33) (Database Properties)
-- and compatibility level for all databases on instance
SELECT db.[name] AS [Database Name], SUSER_SNAME(db.owner_sid) AS [Database Owner],
db.[compatibility_level] AS [DB Compatibility Level], 
db.recovery_model_desc AS [Recovery Model], 
db.log_reuse_wait_desc AS [Log Reuse Wait Description],
CONVERT(DECIMAL(18,2), ds.cntr_value/1024.0) AS [Total Data File Size on Disk (MB)],
CONVERT(DECIMAL(18,2), ls.cntr_value/1024.0) AS [Total Log File Size on Disk (MB)], 
CONVERT(DECIMAL(18,2), lu.cntr_value/1024.0) AS [Log File Used (MB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %], 
db.page_verify_option_desc AS [Page Verify Option], db.user_access_desc, db.state_desc, db.containment_desc,
db.is_mixed_page_allocation_on,  
db.is_auto_create_stats_on, db.is_auto_update_stats_on, db.is_auto_update_stats_async_on, db.is_parameterization_forced, 
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on, db.is_auto_close_on, db.is_auto_shrink_on, 
db.target_recovery_time_in_seconds, db.is_cdc_enabled, db.is_published, db.is_distributor, db.is_sync_with_backup, 
db.group_database_id, db.replica_id, db.is_memory_optimized_enabled, db.is_memory_optimized_elevate_to_snapshot_on, 
db.delayed_durability_desc, db.is_query_store_on, 
db.is_temporal_history_retention_enabled, db.is_accelerated_database_recovery_on,
db.is_master_key_encrypted_by_server, db.is_encrypted, de.encryption_state, de.percent_complete, de.key_algorithm, de.key_length
FROM sys.databases AS db WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
ON db.name = lu.instance_name
LEFT OUTER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK)
ON db.name = ls.instance_name
LEFT OUTER JOIN sys.dm_os_performance_counters AS ds WITH (NOLOCK)
ON db.name = ds.instance_name
LEFT OUTER JOIN sys.dm_database_encryption_keys AS de WITH (NOLOCK)
ON db.database_id = de.database_id
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%' 
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ds.counter_name LIKE N'Data File(s) Size (KB)%'
AND ls.cntr_value > 0 
ORDER BY db.[name] OPTION (RECOMPILE);
------

-- sys.databases (Transact-SQL)
-- https://bit.ly/2G5wqaX

-- sys.dm_os_performance_counters (Transact-SQL)
-- https://bit.ly/3kEO2JR

-- sys.dm_database_encryption_keys (Transact-SQL)
-- https://bit.ly/3mE7kkx


-- Things to look at:
-- How many databases are on the instance?
-- What recovery models are they using?
-- What is the log reuse wait description?
-- How full are the transaction logs?
-- What compatibility level are the databases on? 
-- What is the Page Verify Option? (should be CHECKSUM)
-- Is Auto Update Statistics Asynchronously enabled?
-- What is target_recovery_time_in_seconds? (should be 60 for user databases)
-- Is Delayed Durability enabled?
-- Make sure auto_shrink and auto_close are not enabled!

-- is_mixed_page_allocation_on is a new property for SQL Server 2016. Equivalent to TF 1118 for a user database
-- SQL Server 2016: Changes in default behavior for autogrow and allocations for tempdb and user databases
-- https://bit.ly/2evRZSR

-- A non-zero value for target_recovery_time_in_seconds means that indirect checkpoint is enabled 
-- If the setting has a zero value it indicates that automatic checkpoint is enabled

-- Changes in SQL Server 2016 Checkpoint Behavior
-- https://bit.ly/2pdggk3


-- Missing Indexes for all databases by Index Advantage  (Query 34) (Missing Indexes All Databases)
SELECT CONVERT(decimal(18,2), migs.user_seeks * migs.avg_total_user_cost * (migs.avg_user_impact * 0.01)) AS [index_advantage], 
CONVERT(nvarchar(25), migs.last_user_seek, 20) AS [last_user_seek],
mid.[statement] AS [Database.Schema.Table], 
COUNT(1) OVER(PARTITION BY mid.[statement]) AS [missing_indexes_for_table], 
COUNT(1) OVER(PARTITION BY mid.[statement], mid.equality_columns) AS [similar_missing_indexes_for_table], 
mid.equality_columns, mid.inequality_columns, mid.included_columns, migs.user_seeks, 
CONVERT(decimal(18,2), migs.avg_total_user_cost) AS [avg_total_user_,cost], migs.avg_user_impact,
REPLACE(REPLACE(LEFT(st.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text]
FROM sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) 
INNER JOIN sys.dm_db_missing_index_group_stats_query AS migs WITH(NOLOCK) 
ON mig.index_group_handle = migs.group_handle 
CROSS APPLY sys.dm_exec_sql_text(migs.last_sql_handle) AS st 
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) 
ON mig.index_handle = mid.index_handle 
ORDER BY index_advantage DESC OPTION (RECOMPILE);
------

-- Getting missing index information for all of the databases on the instance is very useful
-- Look at last user seek time, number of user seeks to help determine source and importance
-- Also look at avg_user_impact and avg_total_user_cost to help determine importance
-- SQL Server is overly eager to add included columns, so beware
-- Do not just blindly add indexes that show up from this query!!!
-- H�kan Winther has given me some great suggestions for this query

-- SQL Server Index Design Guide
-- https://bit.ly/2qtZr4N



-- Get VLF Counts for all databases on the instance (Query 35) (VLF Counts)
SELECT db.[name] AS [Database Name], li.[VLF Count]
FROM sys.databases AS db WITH (NOLOCK)
CROSS APPLY (SELECT file_id, COUNT(*) AS [VLF Count]
		     FROM sys.dm_db_log_info (db.database_id)
			 GROUP BY file_id) AS li
ORDER BY li.[VLF Count] DESC OPTION (RECOMPILE);
------

-- High VLF counts can affect write performance to the log file
-- and they can make full database restores and crash recovery take much longer
-- Try to keep your VLF counts under 200 in most cases (depending on log file size)

-- sys.dm_db_log_info (Transact-SQL)
-- https://bit.ly/3jpmqsd

-- sys.databases (Transact-SQL)
-- https://bit.ly/2G5wqaX

-- SQL Server Transaction Log Architecture and Management Guide
-- https://bit.ly/2JjmQRZ

-- VLF Growth Formula (SQL Server 2014 and newer)
-- If the log growth increment is less than 1/8th the current size of the log
--		Then:            1 new VLF
-- Otherwise:
--		Up to 64MB:      4 new VLFs
--		64MB to 1GB:     8 new VLFs
--		More than 1GB:  16 new VLFs	



-- Get CPU utilization by database (Query 36) (CPU Usage by Database)
WITH DB_CPU_Stats
AS
(SELECT pa.DatabaseID, DB_Name(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]
 FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
              FROM sys.dm_exec_plan_attributes(qs.plan_handle)
              WHERE attribute = N'dbid') AS pa
 GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank],
       [Database Name], [CPU_Time_Ms] AS [CPU Time (ms)], 
       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
FROM DB_CPU_Stats
WHERE DatabaseID <> 32767 -- ResourceDB
ORDER BY [CPU Rank] OPTION (RECOMPILE);
------

-- Helps determine which database is using the most CPU resources on the instance
-- Note: This only reflects CPU usage from the currently cached query plans

-- sys.dm_exec_query_stats (Transact-SQL)
-- https://bit.ly/32tHCGH

-- sys.dm_exec_plan_attributes (Transact-SQL)
-- https://bit.ly/35iP2hV


-- Get I/O utilization by database (Query 37) (IO Usage By Database)
WITH Aggregate_IO_Statistics
AS (SELECT DB_NAME(database_id) AS [Database Name],
    CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576 AS DECIMAL(12, 2)) AS [ioTotalMB],
    CAST(SUM(num_of_bytes_read ) / 1048576 AS DECIMAL(12, 2)) AS [ioReadMB],
    CAST(SUM(num_of_bytes_written) / 1048576 AS DECIMAL(12, 2)) AS [ioWriteMB]
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
    GROUP BY database_id)
SELECT ROW_NUMBER() OVER (ORDER BY ioTotalMB DESC) AS [I/O Rank],
        [Database Name], ioTotalMB AS [Total I/O (MB)],
        CAST(ioTotalMB / SUM(ioTotalMB) OVER () * 100.0 AS DECIMAL(5, 2)) AS [Total I/O %],
        ioReadMB AS [Read I/O (MB)], 
		CAST(ioReadMB / SUM(ioReadMB) OVER () * 100.0 AS DECIMAL(5, 2)) AS [Read I/O %],
        ioWriteMB AS [Write I/O (MB)], 
		CAST(ioWriteMB / SUM(ioWriteMB) OVER () * 100.0 AS DECIMAL(5, 2)) AS [Write I/O %]
FROM Aggregate_IO_Statistics
ORDER BY [I/O Rank] OPTION (RECOMPILE);
------

-- Helps determine which database is using the most I/O resources on the instance
-- These numbers are cumulative since the last service restart
-- They include all I/O activity, not just the nominal I/O workload

-- sys.dm_io_virtual_file_stats (Transact-SQL)
-- https://bit.ly/3bRWUc0


-- Get total buffer usage by database for current instance  (Query 38) (Total Buffer Usage by Database)
-- This may take some time to run on a busy instance with lots of RAM
WITH AggregateBufferPoolUsage
AS
(SELECT DB_NAME(database_id) AS [Database Name],
CAST(COUNT_BIG(*) * 8/1024.0 AS DECIMAL (15,2)) AS [CachedSize],
COUNT(page_id) AS [Page Count],
AVG(read_microsec) AS [Avg Read Time (microseconds)]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
GROUP BY DB_NAME(database_id))
SELECT ROW_NUMBER() OVER(ORDER BY CachedSize DESC) AS [Buffer Pool Rank], [Database Name], 
       CAST(CachedSize / SUM(CachedSize) OVER() * 100.0 AS DECIMAL(5,2)) AS [Buffer Pool Percent],
       [Page Count], CachedSize AS [Cached Size (MB)], [Avg Read Time (microseconds)]
FROM AggregateBufferPoolUsage
ORDER BY [Buffer Pool Rank] OPTION (RECOMPILE);
------

-- Tells you how much memory (in the buffer pool) 
-- is being used by each database on the instance

-- sys.dm_os_buffer_descriptors (Transact-SQL)
-- https://bit.ly/36s7aFo


-- Get tempdb version store space usage by database (Query 39) (Version Store Space Usage)
SELECT DB_NAME(database_id) AS [Database Name],
       reserved_page_count AS [Version Store Reserved Page Count], 
	   reserved_space_kb/1024 AS [Version Store Reserved Space (MB)] 
FROM sys.dm_tran_version_store_space_usage WITH (NOLOCK) 
ORDER BY reserved_space_kb/1024 DESC OPTION (RECOMPILE);
------  

-- sys.dm_tran_version_store_space_usage (Transact-SQL)
-- https://bit.ly/2vh3Bmk




-- Clear Wait Stats with this command
-- DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

-- Isolate top waits for server instance since last restart or wait statistics clear  (Query 40) (Top Waits)
WITH [Waits] 
AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS],
          (wait_time_ms - signal_wait_time_ms) / 1000.0 AS [ResourceS],
           signal_wait_time_ms / 1000.0 AS [SignalS],
           waiting_tasks_count AS [WaitCount],
           100.0 *  wait_time_ms / SUM (wait_time_ms) OVER() AS [Percentage],
           ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats WITH (NOLOCK)
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',
		N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
        N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'CXCONSUMER',
        N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',
		N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', 
		N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', 
		N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE',
		N'PARALLEL_REDO_DRAIN_WORKER', N'PARALLEL_REDO_LOG_CACHE', N'PARALLEL_REDO_TRAN_LIST',
		N'PARALLEL_REDO_WORKER_SYNC', N'PARALLEL_REDO_WORKER_WAIT_WORK',
		N'PREEMPTIVE_COM_GETDATA', N'PREEMPTIVE_COM_QUERYINTERFACE',
		N'PREEMPTIVE_HADR_LEASE_MECHANISM', N'PREEMPTIVE_SP_SERVER_DIAGNOSTICS',
		N'PREEMPTIVE_OS_LIBRARYOPS', N'PREEMPTIVE_OS_COMOPS', N'PREEMPTIVE_OS_CRYPTOPS',
		N'PREEMPTIVE_OS_PIPEOPS', N'PREEMPTIVE_OS_AUTHENTICATIONOPS',
		N'PREEMPTIVE_OS_GENERICOPS', N'PREEMPTIVE_OS_VERIFYTRUST',
		N'PREEMPTIVE_OS_FILEOPS', N'PREEMPTIVE_OS_DEVICEOPS', N'PREEMPTIVE_OS_QUERYREGISTRY',
		N'PREEMPTIVE_OS_WRITEFILE', N'PREEMPTIVE_OS_WRITEFILEGATHER',
		N'PREEMPTIVE_XE_CALLBACKEXECUTE', N'PREEMPTIVE_XE_DISPATCHER',
		N'PREEMPTIVE_XE_GETTARGETSTATE', N'PREEMPTIVE_XE_SESSIONCOMMIT',
		N'PREEMPTIVE_XE_TARGETINIT', N'PREEMPTIVE_XE_TARGETFINALIZE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
		N'PWAIT_EXTENSIBILITY_CLEANUP_TASK',
		N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH',
		N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',
		N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SOS_WORK_DISPATCHER',
		N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SOS_WORKER_MIGRATION', N'VDI_CLIENT_OTHER',
		N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
		N'STARTUP_DEPENDENCY_MANAGER',
		N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',
		N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'WAIT_XTP_RECOVERY',
		N'XE_BUFFERMGR_ALLPROCESSED_EVENT', N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT', N'XE_LIVE_TARGET_TVF', N'XE_TIMER_EVENT')
    AND waiting_tasks_count > 0)
SELECT
    MAX (W1.wait_type) AS [WaitType],
	CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
	CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
    CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
    CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec], 
    CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],
    CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],
    CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],
    MAX (W1.WaitCount) AS [Wait Count],
	CAST (N'https://www.sqlskills.com/help/waits/' + W1.wait_type AS XML) AS [Help/Info URL]
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type
HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99 -- percentage threshold
OPTION (RECOMPILE);
------

-- Cumulative wait stats are not as useful on an idle instance that is not under load or performance pressure

-- SQL Server Wait Types Library
-- https://bit.ly/2ePzYO2

-- The SQL Server Wait Type Repository
-- https://bit.ly/1afzfjC

-- Wait statistics, or please tell me where it hurts
-- https://bit.ly/2wsQHQE

-- SQL Server 2005 Performance Tuning using the Waits and Queues
-- https://bit.ly/1o2NFoF

-- sys.dm_os_wait_stats (Transact-SQL)
-- https://bit.ly/2Hjq9Yl



-- Get a count of SQL connections by IP address (Query 41) (Connection Counts by IP Address)
SELECT ec.client_net_address, es.[program_name], es.[host_name], es.login_name, 
COUNT(ec.session_id) AS [connection count] 
FROM sys.dm_exec_sessions AS es WITH (NOLOCK) 
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK) 
ON es.session_id = ec.session_id 
GROUP BY ec.client_net_address, es.[program_name], es.[host_name], es.login_name  
ORDER BY ec.client_net_address, es.[program_name] OPTION (RECOMPILE);
------

-- This helps you figure where your database load is coming from
-- and verifies connectivity from other machines

-- Solving Connectivity errors to SQL Server
-- https://bit.ly/2EgzoD0



-- Get Average Task Counts (run multiple times)  (Query 42) (Avg Task Counts)
SELECT AVG(current_tasks_count) AS [Avg Task Count], 
AVG(work_queue_count) AS [Avg Work Queue Count],
AVG(runnable_tasks_count) AS [Avg Runnable Task Count],
AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count],
GETDATE() AS [System Time]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255 OPTION (RECOMPILE);
------

-- Sustained values above 10 suggest further investigation in that area
-- High Avg Task Counts are often caused by blocking/deadlocking or other resource contention

-- Sustained values above 1 suggest further investigation in that area
-- High Avg Runnable Task Counts are a good sign of CPU pressure
-- High Avg Pending DiskIO Counts are a sign of disk pressure

-- How to Do Some Very Basic SQL Server Monitoring
-- https://bit.ly/30IRla0



-- Detect blocking (run multiple times)  (Query 43) (Detect Blocking)
SELECT t1.resource_type AS [lock type], DB_NAME(resource_database_id) AS [database],
t1.resource_associated_entity_id AS [blk object],t1.request_mode AS [lock req],  -- lock requested
t1.request_session_id AS [waiter sid], t2.wait_duration_ms AS [wait time],       -- spid of waiter  
(SELECT [text] FROM sys.dm_exec_requests AS r WITH (NOLOCK)                      -- get sql for waiter
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) 
WHERE r.session_id = t1.request_session_id) AS [waiter_batch],
(SELECT SUBSTRING(qt.[text],r.statement_start_offset/2, 
    (CASE WHEN r.statement_end_offset = -1 
    THEN LEN(CONVERT(NVARCHAR(max), qt.[text])) * 2 
    ELSE r.statement_end_offset END - r.statement_start_offset)/2) 
FROM sys.dm_exec_requests AS r WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt
WHERE r.session_id = t1.request_session_id) AS [waiter_stmt],					-- statement blocked
t2.blocking_session_id AS [blocker sid],										-- spid of blocker
(SELECT [text] FROM sys.sysprocesses AS p										-- get sql for blocker
CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle]) 
WHERE p.spid = t2.blocking_session_id) AS [blocker_batch]
FROM sys.dm_tran_locks AS t1 WITH (NOLOCK)
INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK)
ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE);
------

-- Helps troubleshoot blocking and deadlocking issues
-- The results will change from second to second on a busy system
-- You should run this query multiple times when you see signs of blocking



-- Show page level contention (Query 44) (Page Contention)
SELECT er.session_id, er.wait_type, er.wait_resource, 
OBJECT_NAME(pinfo.[object_id], pinfo.database_id) AS [object_name], 
er.blocking_session_id, er.command,
          SUBSTRING(st.text, (er.statement_start_offset/2)+1,
          ((CASE er.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
              ELSE er.statement_end_offset
              END - er.statement_start_offset)/2) + 1) AS statement_text,
DB_NAME(pinfo.database_id) AS [Database Name], 
pinfo.[file_id], pinfo.page_id, pinfo.[object_id], pinfo.index_id, pinfo.page_type_desc
FROM sys.dm_exec_requests AS er WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
CROSS APPLY sys.fn_PageResCracker(er.page_resource) AS r
CROSS APPLY sys.dm_db_page_info(r.[db_id], r.[file_id], r.page_id, N'DETAILED') AS pinfo
WHERE  er.wait_type LIKE N'%page%' OPTION (RECOMPILE);
------

-- sys.fn_PageResCracker (Transact-SQL)
-- https://bit.ly/3sgwp9B



-- Get CPU Utilization History for last 256 minutes (in one minute intervals)  (Query 45) (CPU Utilization History)
DECLARE @ts_now bigint = (SELECT ms_ticks FROM sys.dm_os_sys_info WITH (NOLOCK)); 

SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization], 
               SystemIdle AS [System Idle Process], 
               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], 
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] 
FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 
              record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 
                      AS [SystemIdle], 
              record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') 
                      AS [SQLProcessUtilization], [timestamp] 
         FROM (SELECT [timestamp], CONVERT(xml, record) AS [record] 
                      FROM sys.dm_os_ring_buffers WITH (NOLOCK)
                      WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
                      AND record LIKE N'%<SystemHealth>%') AS x) AS y 
ORDER BY record_id DESC OPTION (RECOMPILE);
------

-- Look at the trend over the entire period 
-- Also look at high sustained 'Other Process' CPU Utilization values
-- Note: This query sometimes gives inaccurate results (negative values)
-- on high core count (> 64 cores) systems


-- Get top total worker time queries for entire instance (Query 46) (Top Worker Time Queries)
SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], 
REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text],  
qs.total_worker_time AS [Total Worker Time], qs.min_worker_time AS [Min Worker Time],
qs.total_worker_time/qs.execution_count AS [Avg Worker Time], 
qs.max_worker_time AS [Max Worker Time], 
qs.min_elapsed_time AS [Min Elapsed Time], 
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time], 
qs.max_elapsed_time AS [Max Elapsed Time],
qs.min_logical_reads AS [Min Logical Reads],
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
qs.max_logical_reads AS [Max Logical Reads], 
qs.execution_count AS [Execution Count],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
qs.creation_time AS [Creation Time]
--,t.[text] AS [Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);
------


-- Helps you find the most expensive queries from a CPU perspective across the entire instance
-- Can also help track down parameter sniffing issues



-- Page Life Expectancy (PLE) value for each NUMA node in current instance  (Query 47) (PLE by NUMA Node)
SELECT @@SERVERNAME AS [Server Name], RTRIM([object_name]) AS [Object Name], 
       instance_name, cntr_value AS [Page Life Expectancy], GETDATE() AS [System Time]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);
------

-- PLE is a good measurement of internal memory pressure
-- Higher PLE is better. Watch the trend over time, not the absolute value
-- This will only return one row for non-NUMA systems

-- Page Life Expectancy isn�t what you think�
-- https://bit.ly/2EgynLa


-- Memory Grants Pending value for current instance  (Query 48) (Memory Grants Pending)
SELECT @@SERVERNAME AS [Server Name], RTRIM([object_name]) AS [Object Name], cntr_value AS [Memory Grants Pending]                                                                                                       
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
AND counter_name = N'Memory Grants Pending' OPTION (RECOMPILE);
------

-- Run multiple times, and run periodically if you suspect you are under memory pressure
-- Memory Grants Pending above zero for a sustained period is a very strong indicator of internal memory pressure


-- Memory Clerk Usage for instance  (Query 49) (Memory Clerk Usage)
-- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans)
SELECT TOP(10) mc.[type] AS [Memory Clerk Type], 
       CAST((SUM(mc.pages_kb)/1024.0) AS DECIMAL (15,2)) AS [Memory Usage (MB)] 
FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
GROUP BY mc.[type]  
ORDER BY SUM(mc.pages_kb) DESC OPTION (RECOMPILE);
------

-- MEMORYCLERK_SQLBUFFERPOOL was new for SQL Server 2012. It should be your highest consumer of memory

-- CACHESTORE_SQLCP - SQL Plans         
-- These are cached SQL statements or batches that aren't in stored procedures, functions and triggers
-- Watch out for high values for CACHESTORE_SQLCP
-- Enabling 'optimize for ad hoc workloads' at the instance level can help reduce this
-- Running DBCC FREESYSTEMCACHE ('SQL Plans'); periodically may be required to better control this

-- CACHESTORE_OBJCP - Object Plans      
-- These are compiled plans for stored procedures, functions and triggers

-- If you see very high usage by MEMORYCLERK_SQLLOGPOOL
-- SQL Server 2019 CU9 added a new command, DBCC FREESYSTEMCACHE ('LogPool');

-- sys.dm_os_memory_clerks (Transact-SQL)
-- https://bit.ly/2H31xDR



-- Find single-use, ad-hoc and prepared queries that are bloating the plan cache  (Query 50) (Ad hoc Queries)
SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name],
REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text], 
cp.objtype AS [Object Type], cp.cacheobjtype AS [Cache Object Type],  
cp.size_in_bytes/1024 AS [Plan Size in KB],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index]
--,t.[text] AS [Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE cp.cacheobjtype = N'Compiled Plan' 
AND cp.objtype IN (N'Adhoc', N'Prepared') 
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC, DB_NAME(t.[dbid]) OPTION (RECOMPILE);
------

-- Gives you the text, type and size of single-use ad-hoc and prepared queries that waste space in the plan cache
-- Enabling 'optimize for ad hoc workloads' for the instance can help (SQL Server 2008 and above only)
-- Running DBCC FREESYSTEMCACHE ('SQL Plans') periodically may be required to better control this
-- Enabling forced parameterization for the database can help, but test first!

-- Plan cache, adhoc workloads and clearing the single-use plan cache bloat
-- https://bit.ly/2EfYOkl


-- Get top total logical reads queries for entire instance (Query 51) (Top Logical Reads Queries)
SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name],
REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text], 
qs.total_logical_reads AS [Total Logical Reads],
qs.min_logical_reads AS [Min Logical Reads],
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
qs.max_logical_reads AS [Max Logical Reads],   
qs.min_worker_time AS [Min Worker Time],
qs.total_worker_time/qs.execution_count AS [Avg Worker Time], 
qs.max_worker_time AS [Max Worker Time], 
qs.min_elapsed_time AS [Min Elapsed Time], 
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time], 
qs.max_elapsed_time AS [Max Elapsed Time],
qs.execution_count AS [Execution Count], 
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
qs.creation_time AS [Creation Time]
--,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
------


-- Helps you find the most expensive queries from a memory perspective across the entire instance
-- Can also help track down parameter sniffing issues


-- Get top average elapsed time queries for entire instance (Query 52) (Top Avg Elapsed Time Queries)
SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], 
REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text],  
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
qs.min_elapsed_time, qs.max_elapsed_time, qs.last_elapsed_time,
qs.execution_count AS [Execution Count],  
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads], 
qs.total_physical_reads/qs.execution_count AS [Avg Physical Reads], 
qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
qs.creation_time AS [Creation Time]
--,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
ORDER BY qs.total_elapsed_time/qs.execution_count DESC OPTION (RECOMPILE);
------

-- Helps you find the highest average elapsed time queries across the entire instance
-- Can also help track down parameter sniffing issues


-- Look at UDF execution statistics (Query 53) (UDF Stats by DB)
SELECT TOP (25) DB_NAME(database_id) AS [Database Name], 
		   OBJECT_NAME(object_id, database_id) AS [Function Name],
		   total_worker_time, execution_count, total_elapsed_time,  
           total_elapsed_time/execution_count AS [avg_elapsed_time],  
           last_elapsed_time, last_execution_time, cached_time, [type_desc] 
FROM sys.dm_exec_function_stats WITH (NOLOCK) 
ORDER BY total_worker_time DESC OPTION (RECOMPILE);
------

-- sys.dm_exec_function_stats (Transact-SQL)
-- https://bit.ly/2q1Q6BM

-- Showplan Enhancements for UDFs
-- https://bit.ly/2LVqiQ1


-- Look for long duration buffer pool scans (Query 54) (Long Buffer Pool Scans)
EXEC sys.xp_readerrorlog 0, 1, N'Buffer pool scan took';
------

-- Finds buffer pool scans that took more than 10 seconds in the current SQL Server Error log
-- Only in SQL Server 2019 CU9 and later

-- Operations that trigger buffer pool scan may run slowly on large-memory computers - SQL Server | Microsoft Docs
-- https://bit.ly/3QrFC81


-- Database specific queries *****************************************************************

-- **** Please switch to a user database that you are interested in! *****
--USE YourDatabaseName; -- make sure to change to an actual database on your instance, not the master system database
--GO

-- Individual File Sizes and space available for current database  (Query 55) (File Sizes and Space)
SELECT f.[name] AS [File Name] , f.physical_name AS [Physical Name], 
CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
CAST((f.size/128.0) AS DECIMAL(15,2)) - 
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) 
AS [Used Space in MB],
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) 
AS [Available Space In MB],
f.[file_id], fg.name AS [Filegroup Name],
f.is_percent_growth, f.growth, fg.is_default, fg.is_read_only, fg.is_autogrow_all_files
FROM sys.database_files AS f WITH (NOLOCK) 
LEFT OUTER JOIN sys.filegroups AS fg WITH (NOLOCK)
ON f.data_space_id = fg.data_space_id
ORDER BY f.[type], f.[file_id] OPTION (RECOMPILE);
------

-- Look at how large and how full the files are and where they are located
-- Make sure the transaction log is not full!!

-- is_autogrow_all_files was new for SQL Server 2016. Equivalent to TF 1117 for user databases

-- SQL Server 2016: Changes in default behavior for autogrow and allocations for tempdb and user databases
-- https://bit.ly/2evRZSR


-- Log space usage for current database  (Query 56) (Log Space Usage)
SELECT DB_NAME(lsu.database_id) AS [Database Name], db.recovery_model_desc AS [Recovery Model],
		CAST(lsu.total_log_size_in_bytes/1048576.0 AS DECIMAL(10, 2)) AS [Total Log Space (MB)],
		CAST(lsu.used_log_space_in_bytes/1048576.0 AS DECIMAL(10, 2)) AS [Used Log Space (MB)], 
		CAST(lsu.used_log_space_in_percent AS DECIMAL(10, 2)) AS [Used Log Space %],
		CAST(lsu.log_space_in_bytes_since_last_backup/1048576.0 AS DECIMAL(10, 2)) AS [Used Log Space Since Last Backup (MB)],
		db.log_reuse_wait_desc		 
FROM sys.dm_db_log_space_usage AS lsu WITH (NOLOCK)
INNER JOIN sys.databases AS db WITH (NOLOCK)
ON lsu.database_id = db.database_id
OPTION (RECOMPILE);
------

-- Look at log file size and usage, along with the log reuse wait description for the current database

-- sys.dm_db_log_space_usage (Transact-SQL)
-- https://bit.ly/2H4MQw9


-- Status of last VLF for current database  (Query 57) (Last VLF Status)
SELECT TOP(1) DB_NAME(li.database_id) AS [Database Name], li.[file_id],
              li.vlf_size_mb, li.vlf_sequence_number, li.vlf_active, li.vlf_status
FROM sys.dm_db_log_info(DB_ID()) AS li 
ORDER BY vlf_sequence_number DESC OPTION (RECOMPILE);
------

-- Determine whether you will be able to shrink the transaction log file

-- vlf_status Values
-- 0 is inactive 
-- 1 is initialized but unused 
-- 2 is active

-- sys.dm_db_log_info (Transact-SQL)
-- https://bit.ly/2EQUU1v



-- Get database scoped configuration values for current database (Query 58) (Database-scoped Configurations)
SELECT configuration_id, name, [value] AS [value_for_primary], value_for_secondary, is_value_default
FROM sys.database_scoped_configurations WITH (NOLOCK) OPTION (RECOMPILE);
------

-- This lets you see the value of these new properties for the current database

-- Clear plan cache for current database
-- ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

-- ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
-- https://bit.ly/2sOH7nb


-- I/O Statistics by file for the current database  (Query 59) (IO Stats By File)
SELECT DB_NAME(DB_ID()) AS [Database Name], df.name AS [Logical Name], vfs.[file_id], df.type_desc,
df.physical_name AS [Physical Name], CAST(vfs.size_on_disk_bytes/1048576.0 AS DECIMAL(15, 2)) AS [Size on Disk (MB)],
vfs.num_of_reads, vfs.num_of_writes, vfs.io_stall_read_ms, vfs.io_stall_write_ms,
CAST(100. * vfs.io_stall_read_ms/(vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS DECIMAL(10,1)) AS [IO Stall Reads Pct],
CAST(100. * vfs.io_stall_write_ms/(vfs.io_stall_write_ms + vfs.io_stall_read_ms) AS DECIMAL(10,1)) AS [IO Stall Writes Pct],
(vfs.num_of_reads + vfs.num_of_writes) AS [Writes + Reads], 
CAST(vfs.num_of_bytes_read/1048576.0 AS DECIMAL(15, 2)) AS [MB Read], 
CAST(vfs.num_of_bytes_written/1048576.0 AS DECIMAL(15, 2)) AS [MB Written],
CAST(100. * vfs.num_of_reads/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(15,1)) AS [# Reads Pct],
CAST(100. * vfs.num_of_writes/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(15,1)) AS [# Write Pct],
CAST(100. * vfs.num_of_bytes_read/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(15,1)) AS [Read Bytes Pct],
CAST(100. * vfs.num_of_bytes_written/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(15,1)) AS [Written Bytes Pct]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs
INNER JOIN sys.database_files AS df WITH (NOLOCK)
ON vfs.[file_id]= df.[file_id] OPTION (RECOMPILE);
------

-- This helps you characterize your workload better from an I/O perspective for this database
-- It helps you determine whether you have an OLTP or DW/DSS type of workload



-- Get most frequently executed queries for this database (Query 60) (Query Execution Counts)
SELECT TOP(50) LEFT(t.[text], 50) AS [Short Query Text], qs.execution_count AS [Execution Count],
qs.total_logical_reads AS [Total Logical Reads],
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
qs.total_worker_time AS [Total Worker Time],
qs.total_worker_time/qs.execution_count AS [Avg Worker Time], 
qs.total_elapsed_time AS [Total Elapsed Time],
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
CONVERT(nvarchar(25), qs.last_execution_time, 20) AS [Last Execution Time],
CONVERT(nvarchar(25), qs.creation_time, 20) AS [Plan Cached Time]
--,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
WHERE t.dbid = DB_ID()
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
------

-- Tells you which cached queries are called the most often
-- This helps you characterize and baseline your workload
-- It also helps you find possible caching opportunities


-- CREATE PROCEDURE (Transact-SQL)
-- https://bit.ly/3gxcuxG


-- Queries 61 through 67 are the "Bad Man List" for stored procedures

-- Top Cached SPs By Execution Count (Query 61) (SP Execution Counts)
SELECT TOP(100) p.name AS [SP Name], qs.execution_count AS [Execution Count],
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
qs.total_worker_time/qs.execution_count AS [Avg Worker Time],    
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
CONVERT(nvarchar(25), qs.last_execution_time, 20) AS [Last Execution Time],
CONVERT(nvarchar(25), qs.cached_time, 20) AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
------

-- Tells you which cached stored procedures are called the most often
-- This helps you characterize and baseline your workload
-- It also helps you find possible caching opportunities


-- Top Cached SPs By Avg Elapsed Time (Query 62) (SP Avg Elapsed Time)
SELECT TOP(25) p.name AS [SP Name], qs.min_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], 
qs.max_elapsed_time, qs.last_elapsed_time, qs.total_elapsed_time, qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute], 
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], 
qs.total_worker_time AS [TotalWorkerTime],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
CONVERT(nvarchar(25), qs.last_execution_time, 20) AS [Last Execution Time],
CONVERT(nvarchar(25), qs.cached_time, 20) AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY avg_elapsed_time DESC OPTION (RECOMPILE);
------

-- This helps you find high average elapsed time cached stored procedures that
-- may be easy to optimize with standard query tuning techniques



-- Top Cached SPs By Total Worker time. Worker time relates to CPU cost  (Query 63) (SP Worker Time)
SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], 
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
CONVERT(nvarchar(25), qs.last_execution_time, 20) AS [Last Execution Time],
CONVERT(nvarchar(25), qs.cached_time, 20) AS [Plan Cached Time]
--,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);
------

-- This helps you find the most expensive cached stored procedures from a CPU perspective
-- You should look at this if you see signs of CPU pressure


-- Top Cached SPs By Total Logical Reads. Logical reads relate to memory pressure  (Query 64) (SP Logical Reads)
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads], 
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute], 
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
CONVERT(nvarchar(25), qs.last_execution_time, 20) AS [Last Execution Time],
CONVERT(nvarchar(25), qs.cached_time, 20) AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);
------

-- This helps you find the most expensive cached stored procedures from a memory perspective
-- You should look at this if you see signs of memory pressure


-- Top Cached SPs By Total Physical Reads. Physical reads relate to disk read I/O pressure  (Query 65) (SP Physical Reads)
SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads], 
qs.total_physical_reads/qs.execution_count AS [AvgPhysicalReads], qs.execution_count, 
qs.total_logical_reads,qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index],
CONVERT(nvarchar(25), qs.last_execution_time, 20) AS [Last Execution Time],
CONVERT(nvarchar(25), qs.cached_time, 20) AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan 
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND qs.total_physical_reads > 0
ORDER BY qs.total_physical_reads DESC, qs.total_logical_reads DESC OPTION (RECOMPILE);
------

-- This helps you find the most expensive cached stored procedures from a read I/O perspective
-- You should look at this if you see signs of I/O pressure or of memory pressure
       


-- Top Cached SPs By Total Logical Writes (Query 66) (SP Logical Writes)
-- Logical writes relate to both memory and disk I/O pressure 
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS [TotalLogicalWrites], 
qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
CASE WHEN CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index], 
CONVERT(nvarchar(25), qs.last_execution_time, 20) AS [Last Execution Time],
CONVERT(nvarchar(25), qs.cached_time, 20) AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan 
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND qs.total_logical_writes > 0
AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY qs.total_logical_writes DESC OPTION (RECOMPILE);
------

-- This helps you find the most expensive cached stored procedures from a write I/O perspective
-- You should look at this if you see signs of I/O pressure or of memory pressure



-- Cached SPs Missing Indexes by Execution Count (Query 67) (SP Missing Index)
SELECT TOP(25) p.name AS [SP Name], qs.execution_count AS [Execution Count],
ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
qs.total_worker_time/qs.execution_count AS [Avg Worker Time],    
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
CONVERT(nvarchar(25), qs.last_execution_time, 20) AS [Last Execution Time],
CONVERT(nvarchar(25), qs.cached_time, 20) AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
AND CONVERT(nvarchar(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%'
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
------

-- This helps you find the most frequently executed cached stored procedures that have missing index warnings
-- This can often help you find index tuning candidates



-- Lists the top statements by average input/output usage for the current database  (Query 68) (Top IO Statements)
SELECT TOP(50) OBJECT_NAME(qt.objectid, dbid) AS [SP Name],
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO], qs.execution_count AS [Execution Count],
SUBSTRING(qt.[text],qs.statement_start_offset/2, 
	(CASE 
		WHEN qs.statement_end_offset = -1 
	 THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 
		ELSE qs.statement_end_offset 
	 END - qs.statement_start_offset)/2) AS [Query Text]	
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.[dbid] = DB_ID()
ORDER BY [Avg IO] DESC OPTION (RECOMPILE);
------

-- Helps you find the most expensive statements for I/O by SP



-- Possible Bad NC Indexes (writes > reads)  (Query 69) (Bad NC Indexes)
SELECT SCHEMA_NAME(o.[schema_id]) AS [Schema Name], 
OBJECT_NAME(s.[object_id]) AS [Table Name],
i.name AS [Index Name], i.index_id, 
i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,
s.user_updates AS [Total Writes], s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads],
s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
ON i.[object_id] = o.[object_id]
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups)
AND i.index_id > 1 AND i.[type_desc] = N'NONCLUSTERED'
AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND i.is_unique = 0
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);
------

-- Look for indexes with high numbers of writes and zero or very low numbers of reads
-- Consider your complete workload, and how long your instance has been running
-- Investigate further before dropping an index!


-- Missing Indexes for current database by Index Advantage  (Query 70) (Missing Indexes)
SELECT CONVERT(decimal(18,2), migs.user_seeks * migs.avg_total_user_cost * (migs.avg_user_impact * 0.01)) AS [index_advantage], 
CONVERT(nvarchar(25), migs.last_user_seek, 20) AS [last_user_seek],
mid.[statement] AS [Database.Schema.Table], 
COUNT(1) OVER(PARTITION BY mid.[statement]) AS [missing_indexes_for_table], 
COUNT(1) OVER(PARTITION BY mid.[statement], mid.equality_columns) AS [similar_missing_indexes_for_table], 
mid.equality_columns, mid.inequality_columns, mid.included_columns, migs.user_seeks, 
CONVERT(decimal(18,2), migs.avg_total_user_cost) AS [avg_total_user_,cost], migs.avg_user_impact,
REPLACE(REPLACE(LEFT(st.[text], 255), CHAR(10),''), CHAR(13),'') AS [Short Query Text],
OBJECT_NAME(mid.[object_id]) AS [Table Name], p.rows AS [Table Rows]
FROM sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) 
INNER JOIN sys.dm_db_missing_index_group_stats_query AS migs WITH(NOLOCK) 
ON mig.index_group_handle = migs.group_handle 
CROSS APPLY sys.dm_exec_sql_text(migs.last_sql_handle) AS st 
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) 
ON mig.index_handle = mid.index_handle
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON p.[object_id] = mid.[object_id]
WHERE mid.database_id = DB_ID()
AND p.index_id < 2 
ORDER BY index_advantage DESC OPTION (RECOMPILE);
------

-- Look at index advantage, last user seek time, number of user seeks to help determine source and importance
-- SQL Server is overly eager to add included columns, so beware
-- Do not just blindly add indexes that show up from this query!!!
-- H�kan Winther has given me some great suggestions for this query


-- Find missing index warnings for cached plans in the current database  (Query 71) (Missing Index Warnings)
-- Note: This query could take some time on a busy instance
SELECT TOP(25) OBJECT_NAME(objectid) AS [ObjectName], 
               cp.objtype, cp.usecounts, cp.size_in_bytes, qp.query_plan
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'
AND qp.dbid = DB_ID()
ORDER BY cp.usecounts DESC OPTION (RECOMPILE);
------

-- Helps you connect missing indexes to specific stored procedures or queries
-- This can help you decide whether to add them or not


-- Breaks down buffers used by current database by object (table, index) in the buffer cache  (Query 72) (Buffer Usage)
-- Note: This query could take some time on a busy instance
SELECT fg.name AS [Filegroup Name], SCHEMA_NAME(o.schema_id) AS [Schema Name],
OBJECT_NAME(p.[object_id]) AS [Object Name], p.index_id, 
CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],  
COUNT(*) AS [BufferCount], p.[rows] AS [Row Count],
p.data_compression_desc AS [Compression Type]
FROM sys.allocation_units AS a WITH (NOLOCK)
INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK)
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON a.container_id = p.hobt_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
ON p.object_id = o.object_id
INNER JOIN sys.database_files AS f WITH (NOLOCK)
ON b.file_id = f.file_id
INNER JOIN sys.filegroups AS fg WITH (NOLOCK)
ON f.data_space_id = fg.data_space_id
WHERE b.database_id = CONVERT(int, DB_ID())
AND p.[object_id] > 100
AND OBJECT_NAME(p.[object_id]) NOT LIKE N'plan_%'
AND OBJECT_NAME(p.[object_id]) NOT LIKE N'sys%'
AND OBJECT_NAME(p.[object_id]) NOT LIKE N'xml_index_nodes%'
GROUP BY fg.name, o.schema_id, p.[object_id], p.index_id, 
         p.data_compression_desc, p.[rows]
ORDER BY [BufferCount] DESC OPTION (RECOMPILE);
------

-- Tells you what tables and indexes are using the most memory in the buffer cache
-- It can help identify possible candidates for data compression


-- Get Schema names, Table names, object size, row counts, and compression status for clustered index or heap  (Query 73) (Table Sizes)
SELECT DB_NAME(DB_ID()) AS [Database Name], SCHEMA_NAME(o.schema_id) AS [Schema Name], 
OBJECT_NAME(p.object_id) AS [Table Name],
CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS DECIMAL(19,2)) AS [Object Size (MB)],
SUM(p.rows) AS [Row Count], 
p.data_compression_desc AS [Compression Type]
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON p.object_id = o.object_id
INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK)
ON p.object_id = ps.object_id
WHERE ps.index_id < 2 -- ignore the partitions from the non-clustered indexes if any
AND p.index_id < 2    -- ignore the partitions from the non-clustered indexes if any
AND o.type_desc = N'USER_TABLE'
GROUP BY  SCHEMA_NAME(o.schema_id), p.object_id, ps.reserved_page_count, p.data_compression_desc
ORDER BY SUM(ps.reserved_page_count) DESC, SUM(p.rows) DESC OPTION (RECOMPILE);
------

-- Gives you an idea of table sizes, and possible data compression opportunities



-- Get some key table properties (Query 74) (Table Properties)
SELECT OBJECT_NAME(t.[object_id]) AS [ObjectName], p.[rows] AS [Table Rows], p.index_id, 
       p.data_compression_desc AS [Index Data Compression],
       t.create_date, t.lock_on_bulk_load, t.is_replicated, t.has_replication_filter, 
       t.is_tracked_by_cdc, t.lock_escalation_desc, t.is_filetable, 
	   t.is_memory_optimized, t.durability_desc, 
	   t.temporal_type_desc, t.is_remote_data_archive_enabled, t.is_external 
FROM sys.tables AS t WITH (NOLOCK)
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON t.[object_id] = p.[object_id]
WHERE OBJECT_NAME(t.[object_id]) NOT LIKE N'sys%'
ORDER BY OBJECT_NAME(t.[object_id]), p.index_id OPTION (RECOMPILE);
------

-- Gives you some good information about your tables
-- is_memory_optimized and durability_desc were new in SQL Server 2014
-- temporal_type_desc, is_remote_data_archive_enabled, is_external were new in SQL Server 2016

-- sys.tables (Transact-SQL)
-- https://bit.ly/2Gk7998



-- When were Statistics last updated on all indexes?  (Query 75) (Statistics Update)
SELECT SCHEMA_NAME(o.schema_id) + N'.' + o.[name] AS [Object Name], o.[type_desc] AS [Object Type],
      i.[name] AS [Index Name], STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], 
      s.auto_created, s.no_recompute, s.user_created, s.is_incremental, s.is_temporary, 
	  s.has_persisted_sample, sp.persisted_sample_percent, 
	  (sp.rows_sampled * 100)/sp.rows AS [Actual Sample Percent], sp.modification_counter,
	  st.row_count, st.used_page_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id] 
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o.[object_id] = st.[object_id]
AND i.[index_id] = st.[index_id]
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE o.[type] IN ('U', 'V')
AND st.row_count > 0
ORDER BY STATS_DATE(i.[object_id], i.index_id) DESC OPTION (RECOMPILE);
------  

-- Helps discover possible problems with out-of-date statistics
-- Also gives you an idea which indexes are the most active

-- sys.stats (Transact-SQL)
-- https://bit.ly/2GyAxrn

-- UPDATEs to Statistics (Erin Stellato)
-- https://bit.ly/2vhrYQy




-- Look at most frequently modified indexes and statistics (Query 76) (Volatile Indexes)
SELECT o.[name] AS [Object Name], o.[object_id], o.[type_desc], s.[name] AS [Statistics Name], 
       s.stats_id, s.no_recompute, s.auto_created, s.is_incremental, s.is_temporary,
	   sp.modification_counter, sp.[rows], sp.rows_sampled, sp.last_updated
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON s.object_id = o.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE o.[type_desc] NOT IN (N'SYSTEM_TABLE', N'INTERNAL_TABLE')
AND sp.modification_counter > 0
ORDER BY sp.modification_counter DESC, o.name OPTION (RECOMPILE);
------

-- This helps you understand your workload and make better decisions about 
-- things like data compression and adding new indexes to a table



-- Get fragmentation info for all indexes above a certain size in the current database  (Query 77) (Index Fragmentation)
-- Note: This query could take some time on a very large database
SELECT DB_NAME(ps.database_id) AS [Database Name], SCHEMA_NAME(o.[schema_id]) AS [Schema Name],
OBJECT_NAME(ps.object_id) AS [Object Name], i.[name] AS [Index Name], ps.index_id, ps.index_type_desc, 
CAST(ps.avg_fragmentation_in_percent AS DECIMAL (15,3)) AS [Avg Fragmentation in Pct], 
ps.fragment_count, ps.page_count, i.fill_factor, i.has_filter, i.filter_definition, i.[allow_page_locks]
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , N'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps.[object_id] = i.[object_id] 
AND ps.index_id = i.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
ON i.[object_id] = o.[object_id]
WHERE ps.database_id = DB_ID()
AND ps.page_count > 2500
ORDER BY ps.avg_fragmentation_in_percent DESC OPTION (RECOMPILE);
------

-- Helps determine whether you have framentation in your relational indexes
-- and how effective your index maintenance strategy is


--- Index Read/Write stats (all tables in current DB) ordered by Reads  (Query 78) (Overall Index Usage - Reads)
SELECT SCHEMA_NAME(t.[schema_id]) AS [SchemaName], OBJECT_NAME(i.[object_id]) AS [ObjectName], 
       i.[name] AS [IndexName], i.index_id, i.[type_desc] AS [Index Type],
       s.user_seeks, s.user_scans, s.user_lookups,
	   s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads], 
	   s.user_updates AS [Writes],  
	   i.fill_factor AS [Fill Factor], i.has_filter, i.filter_definition, 
	   s.last_user_scan, s.last_user_lookup, s.last_user_seek, i.[allow_page_locks], i.[allow_row_locks],
	   i.[optimize_for_sequential_key]
FROM sys.indexes AS i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
LEFT OUTER JOIN sys.tables AS t WITH (NOLOCK)
ON t.[object_id] = i.[object_id]
WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1
ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC OPTION (RECOMPILE); -- Order by reads
------

-- Show which indexes in the current database are most active for Reads


--- Index Read/Write stats (all tables in current DB) ordered by Writes  (Query 79) (Overall Index Usage - Writes)
SELECT SCHEMA_NAME(t.[schema_id]) AS [SchemaName],OBJECT_NAME(i.[object_id]) AS [ObjectName], 
	   i.[name] AS [IndexName], i.index_id, i.[type_desc] AS [Index Type],
	   s.user_updates AS [Writes], s.user_seeks + s.user_scans + s.user_lookups AS [Total Reads], 
	   i.fill_factor AS [Fill Factor], i.has_filter, i.filter_definition,
	   s.last_system_update, s.last_user_update, i.[allow_page_locks], i.[allow_row_locks],
	   i.[optimize_for_sequential_key]
FROM sys.indexes AS i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
LEFT OUTER JOIN sys.tables AS t WITH (NOLOCK)
ON t.[object_id] = i.[object_id]
WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1
ORDER BY s.user_updates DESC OPTION (RECOMPILE);						 -- Order by writes
------

-- Show which indexes in the current database are most active for Writes



-- Get lock waits for current database (Query 80) (Lock Waits)
SELECT o.name AS [table_name], i.name AS [index_name], ios.index_id, ios.partition_number,
             SUM(ios.row_lock_wait_count) AS [total_row_lock_waits], 
             SUM(ios.row_lock_wait_in_ms) AS [total_row_lock_wait_in_ms],
			 SUM(ios.index_lock_promotion_attempt_count) AS [total index_lock_promotion_attempt_count],
             SUM(ios.index_lock_promotion_count) AS [ios.index_lock_promotion_count],
             SUM(ios.page_lock_wait_count) AS [total_page_lock_waits],
             SUM(ios.page_lock_wait_in_ms) AS [total_page_lock_wait_in_ms],
             SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) AS [total_lock_wait_in_ms]           
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
INNER JOIN sys.objects AS o WITH (NOLOCK)
ON ios.[object_id] = o.[object_id]
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ios.[object_id] = i.[object_id] 
AND ios.index_id = i.index_id
WHERE o.[object_id] > 100
GROUP BY o.name, i.name, ios.index_id, ios.partition_number
HAVING SUM(ios.page_lock_wait_in_ms)+ SUM(row_lock_wait_in_ms) > 0
ORDER BY total_lock_wait_in_ms DESC OPTION (RECOMPILE);
------

-- This query is helpful for troubleshooting blocking and deadlocking issues

-- sys.dm_db_index_operational_stats (Transact-SQL)
-- https://bit.ly/3l5rGEw


-- Look at UDF execution statistics (Query 81) (UDF Statistics)
SELECT OBJECT_NAME(object_id) AS [Function Name], execution_count,
	   total_worker_time, total_worker_time/execution_count AS [avg_worker_time],
	   total_logical_reads, total_physical_reads, total_elapsed_time, 
	   total_elapsed_time/execution_count AS [avg_elapsed_time],
	   CONVERT(nvarchar(25), last_execution_time, 20) AS [Last Execution Time],	
	   CONVERT(nvarchar(25), cached_time, 20) AS [Plan Cached Time]	   
FROM sys.dm_exec_function_stats WITH (NOLOCK) 
WHERE database_id = DB_ID()
ORDER BY total_worker_time DESC OPTION (RECOMPILE); 
------

-- New for SQL Server 2016
-- Helps you investigate scalar UDF performance issues
-- Does not return information for table valued functions

-- sys.dm_exec_function_stats (Transact-SQL)
-- https://bit.ly/2q1Q6BM


-- Determine which scalar UDFs are in-lineable (Query 82) (Inlineable UDFs)
SELECT OBJECT_NAME(m.object_id) AS [Function Name], is_inlineable, inline_type,
       efs.total_worker_time
FROM sys.sql_modules AS m WITH (NOLOCK) 
LEFT OUTER JOIN sys.dm_exec_function_stats AS efs WITH (NOLOCK)
ON  m.object_id = efs.object_id
WHERE efs.type_desc = N'SQL_SCALAR_FUNCTION'
ORDER BY efs.total_worker_time DESC
OPTION (RECOMPILE);
------

-- Scalar UDF Inlining
-- https://bit.ly/2JU971M

-- sys.sql_modules (Transact-SQL)
-- https://bit.ly/2Qt216S


-- Get Query Store Options for this database (Query 83) (Query Store Options)
SELECT actual_state_desc, desired_state_desc, [interval_length_minutes],
       current_storage_size_mb, [max_storage_size_mb], 
	   query_capture_mode_desc, size_based_cleanup_mode_desc, wait_stats_capture_mode_desc
FROM sys.database_query_store_options WITH (NOLOCK) OPTION (RECOMPILE);
------

-- New for SQL Server 2016
-- Requires that Query Store is enabled for this database

-- Make sure that the actual_state_desc is the same as desired_state_desc
-- Make sure that the current_storage_size_mb is less than the max_storage_size_mb

-- Tuning Workload Performance with Query Store
-- https://bit.ly/1kHSl7w

-- Emergency shutoff for Query Store (SQL Server 2019 CU6 or newer)
-- ALTER DATABASE [DatabaseName] SET QUERY_STORE = OFF(FORCED);


-- Get input buffer information for the current database (Query 84) (Input Buffer)
SELECT es.session_id, DB_NAME(es.database_id) AS [Database Name],
       es.[program_name], es.[host_name], es.login_name,
       es.login_time, es.cpu_time, es.logical_reads, es.memory_usage,
       es.[status], ib.event_info AS [Input Buffer]
FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) AS ib
WHERE es.database_id = DB_ID()
AND es.session_id > 50
AND es.session_id <> @@SPID OPTION (RECOMPILE);
------

-- Gives you input buffer information from all non-system sessions for the current database
-- Replaces DBCC INPUTBUFFER

-- New DMF for retrieving input buffer in SQL Server
-- https://bit.ly/2uHKMbz

-- sys.dm_exec_input_buffer (Transact-SQL)
-- https://bit.ly/2J5Hf9q



-- Get any resumable index rebuild operation information (Query 85) (Resumable Index Rebuild)
SELECT OBJECT_NAME(iro.object_id) AS [Object Name], iro.index_id, iro.name AS [Index Name],
       iro.sql_text, iro.last_max_dop_used, iro.partition_number, iro.state_desc, 
       iro.start_time, CONVERT(decimal(15,2),iro.percent_complete) AS [Percent Complete], 
	   iro.last_pause_time, iro.total_execution_time AS [Execution Min],
       CONVERT(decimal(15,2),iro.total_execution_time * (100.0 - iro.percent_complete)/iro.percent_complete) AS [Approx Execution Min Left] 
FROM  sys.index_resumable_operations AS iro WITH (NOLOCK)
OPTION (RECOMPILE);
------ 

-- index_resumable_operations (Transact-SQL)
-- https://bit.ly/2pYSWqq


-- Get database automatic tuning options (Query 86) (Automatic Tuning Options)
SELECT [name], desired_state_desc, actual_state_desc, reason_desc
FROM sys.database_automatic_tuning_options WITH (NOLOCK)
OPTION (RECOMPILE);
------ 

-- sys.database_automatic_tuning_options (Transact-SQL)
-- https://bit.ly/2FHhLkL



-- Look at recent Full backups for the current database (Query 87) (Recent Full Backups)
SELECT TOP (30) bs.machine_name, bs.server_name, bs.database_name AS [Database Name], bs.recovery_model,
CONVERT (BIGINT, bs.backup_size / 1048576 ) AS [Uncompressed Backup Size (MB)],
CONVERT (BIGINT, bs.compressed_backup_size / 1048576 ) AS [Compressed Backup Size (MB)],
CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, bs.backup_size) /
CONVERT (FLOAT, bs.compressed_backup_size))) AS [Compression Ratio], bs.has_backup_checksums, bs.is_copy_only, bs.encryptor_type,
DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)],
bs.backup_finish_date AS [Backup Finish Date], bmf.physical_device_name AS [Backup Location], bmf.physical_block_size
FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK)
ON bs.media_set_id = bmf.media_set_id  
WHERE bs.database_name = DB_NAME(DB_ID())
AND bs.[type] = 'D' -- Change to L if you want Log backups
ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);
------


-- Things to look at:
-- Are your backup sizes and times changing over time?
-- Are you using backup compression?
-- Are you using backup checksums?
-- Are you doing copy_only backups?
-- Are you doing encrypted backups?
-- Have you done any backup tuning with striped backups, or changing the parameters of the backup command?
-- Where are the backups going to?

-- In SQL Server 2016 and newer, native SQL Server backup compression actually works 
-- much better with databases that are using TDE than in previous versions
-- https://bit.ly/28Rpb2x


-- Microsoft Visual Studio Dev Essentials
-- https://bit.ly/2qjNRxi

-- Microsoft Azure Learn
-- https://bit.ly/2O0Hacc

AWS: Use the AWS CLI to delete snapshots from your account

The Amazon EC2 console allows you to delete up to 50 Amazon Elastic Block Store (Amazon EBS) snapshots at once. To delete more than 50 snapshots, use the AWS Command Line Interface (AWS CLI) or the AWS SDK.

To see all the snapshots that you own in a specific region, run the following. Note, replace af-south-1 with your region:

aws ec2 describe-snapshots --owner-ids self  --query 'Snapshots[]' --region af-south-1

Note: To run the code below, first make sure your in the correct account (or life will become difficult for you). Next replace BOTH instances “af-south-1” with your particular region. Finally, you can use a specific account number in place of –owner-ids=self (eg –owner-ids=1234567890).

for SnapshotID in $(aws ec2 --region af-south-1 describe-snapshots --owner-ids=self --query 'Snapshots[*].SnapshotId' --output=text); do
aws ec2 --region af-south-1 delete-snapshot --snapshot-id ${SnapshotID}
done

Sql Server: Query to View a list of missing indexes from your Sql Server database

Most companies will have a fair amount of SQL databases and its likely that most of those databases are performing sub-optimally due to missing indexes. We can debate (for a long time) the pros and cons of indexes, but the undeniable reality is that having missing indexes on large tables create a lot of issues in production environments (including, slowness, over spend on hardware and even outages). So how do you get a sense of how good or bad a database is? As luck would have it, Microsoft have a number of dynamic views that store the data you are looking for.

View missing index suggestions in DMVs

You can retrieve information about missing indexes by querying the dynamic management objects (DMVs). The following query uses the missing index DMVs to generate a series of CREATE INDEX statements. The index creation statements can be used to help you run the relevant DDL, once you have review all the output.

SELECT TOP 30
    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1), 
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) 
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' + 
        CONVERT (varchar, mig.index_group_handle) + '_' + 
        CONVERT (varchar, mid.index_handle) + ' ON ' + 
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') + 
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' + 
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON 
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON 
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;
GO

Macbook OSX: Using Touch ID / fingerprints to enable SUDO and permanently enabling this after Mac OSX updates

Each day that I wake up I try and figure out if I can do less work than yesterday. With this in mind I was playing around to see if there is a way to save me typing my password each time I SUDO. It turns out this is quite a simple change…

Open Terminal and run the following to edit sudos behaviour:

sudo nano /etc/pam.d/sudo

Next add the following to the top of the file:

auth       sufficient     pam_tid.so

The only issue with this is that /etc/pam.d/sudo is overwritten on every macOS update (major, minor or patch – it is always overwritten and reset back to its default state).

MacOS: Sonoma

In their “What’s new for enterprise in macOS Sonoma” document Apple listed the following in the “Bug fixes and other improvements” section:

Touch ID can be allowed for sudo with a configuration that persists across software updates using /etc/pam.d/sudo_local. See /etc/pam.d/sudo_local.template for details.

So lets create a template file in /etc/pam.d/sudo_local.template:

sudo nano /etc/pam.d/sudo_local.template

Next uncomment the auth line, as per:

# sudo_local: local config file which survives system update and is included fo$
# uncomment following line to enable Touch ID for sudo
auth       sufficient     pam_tid.so

This should mean that Touch ID now survive system updates!

Quick tests:

sudo ls
# exit sudo
sudo -k
sudo ls

To enable Touch ID access on Iterm2. You need to do the following. Go to Prefs -> Advanced -> Allow sessions to survive logging out and back in and set value to no . Restart Iterm2 and touch ID authentication will work on Iterm2.

Macbook OSX: Change the default image type of your screenshots from PNG to JPEG, GIF or PDF

There are a few things that I tweak when I get a new Macbook, one of which is the screenshot format (mainly because it doesnt natively render in Whatsapp). So I thought I would share the code snippet that you can run in Terminal to alter the default image type of your screenshots:

For JPEG use:

$ defaults write com.apple.screencapture type JPG

For GIF use:

$ defaults write com.apple.screencapture type GIF

For PDF use:

$ defaults write com.apple.screencapture type PDF

For PNG use:

$ defaults write com.apple.screencapture type PNG

How to make an offline copy of a static website using wget and hosting on AWS S3 with CloudFront

I have an old website that I want to avoid the hosting costs and so just wanted to download the website and run it from an AWS S3 bucket using Cloud Front to publish the content. Below are the steps I took to do this:

First download the website to your laptop

$ wget \
     --recursive \
     --no-clobber \
     --page-requisites \
     --html-extension \
     --convert-links \
     --no-check-certificate \
     --restrict-file-names=unix \
     --domains archive.andrewbaker.ninja \
     --no-parent \
         http://archive.andrewbaker.ninja/
$ cd archive.andrewbaker.ninja
$ ls

Below is a summary of the parameters (inc common alternatives):

–recursive: Wget is capable of traversing parts of the Web (or a single HTTP or FTP server), following links and directory structure. We refer to this as to recursive retrieval, or recursion.

–no-clobber: If a file is downloaded more than once in the same directory, Wget’s behavior depends on a few options, including `-nc’. In certain cases, the local file will be clobbered, or overwritten, upon repeated download. In other cases it will be preserved. When running Wget without `-N’`-nc’, or `-r’, downloading the same file in the same directory will result in the original copy of file being preserved and the second copy being named `file.1′. If that file is downloaded yet again, the third copy will be named `file.2′, and so on. When `-nc’ is specified, this behavior is suppressed, and Wget will refuse to download newer copies of `file. Therefore, “no-clobber” is actually a misnomer in this mode–it’s not clobbering that’s prevented (as the numeric suffixes were already preventing clobbering), but rather the multiple version saving that’s prevented. When running Wget with `-r’, but without `-N’ or `-nc’, re-downloading a file will result in the new copy simply overwriting the old. Adding `-nc’ will prevent this behavior, instead causing the original version to be preserved and any newer copies on the server to be ignored. When running Wget with `-N’, with or without `-r’, the decision as to whether or not to download a newer copy of a file depends on the local and remote timestamp and size of the file (see section Time-Stamping). `-nc’ may not be specified at the same time as `-N’. Note that when `-nc’ is specified, files with the suffixes `.html’ or (yuck) `.htm’ will be loaded from the local disk and parsed as if they had been retrieved from the Web.

–page-requisites: This causes wget to download all the files that are necessary to properly display a given HTML page which includes images, css, js, etc. –adjust-extension Preserves proper file extensions for . html, . css, and other assets

–html-extension: This adds .html after the downloaded filename, to make sure it plays nicely on whatever system you’re going to view the archive on

–convert-links: After the download is complete, convert the links in the document to make them suitable for local viewing. This affects not only the visible hyperlinks, but any part of the document that links to external content, such as embedded images, links to style sheets, hyperlinks to non-HTML content, etc.

–no-check-certificate: Don’t check the server certificate against the available certificate authorities. Also don’t require the URL host name to match the common name presented by the certificate.

–restrict-file-names: By default, Wget escapes the characters that are not valid or safe as part of file names on your operating system, as well as control characters that are typically unprintable. This option is useful for changing these defaults, perhaps because you are downloading to a non-native partition”. So unless you are not downloading to non-native partition you do not need to restrict file names by OS. its automatic. Additionally: “The values ‘unix’ and ‘windows’ are mutually exclusive (one will override the other)”

–domains: Limit spanning to specified domains

–no-parent: If you don’t want wget to descend down to the parent directory, use -np or –no-parent option. This instructs wget not to ascend to the parent directory when it hits references like ../ in href links.

Upload Files to S3 Bucket

Next upload the files to your S3 bucket. First move into the relevant bucket, then perform the recursive upload.

$ cd archive.andrewbaker.ninja
$ ls .
$ aws s3 cp . s3://vbusers.com/ --recursive

Create a CloudFront Distribution from an S3 Bucket

Finally go to CloudFront and create a distribution from the S3 Bucket you just created. You can pretty much use the default settings. Note: you will need to wait a few minutes before you browse to the distributions domain name: