Sometimes for testing purposes (or an emergency) one might find it useful to remove query plan for a single stored procedure. This way you won't have to disrupt the entire instance (and possibly anger your fellow developers!). I often forget that DBCC FREEPROCCACHE accepts a plan handle as a parameter value. There are a few ways to identify the plan handle, usually it involves the sys.dm_exec_cached_plans with a cross apply on sys.dm_exec_sql_text and sys.dm_exec_query_plan.

I know, it sounds like it could be a desert. But it's poison! You ever have one of those Saturday morning emergencies and come face to face with a wait type that you're completely unfamiliar with? I recently got contacted that a highly transactional SQL Server was performing slowly. I noticed some memory pressure, but when I dove into wait types, I found that many of the transactions were waiting on the RESOURCE_SEMAPHORES wait. What is it? Basically it's when a query is wai

It is unbelievable how common it is to walk into a new organization only to find that they have no database backups. But when having a conversation with management, they expect zero data loss. Seems like a bit of a conundrum don't you think? It's important to bring about the importance of RPO and RTO and it's associated costs. What is it? RPO - Recovery Point Objective. Basically how much data is acceptable to lose. RTO - Recovery Time Objective. What is the maximal downtime

It's important to test your storage performance especially prior to installing or deploying a new SQL Server. Microsoft has provided us with a great tool called Diskspd, which was meant to replace SQLIO. Diskspd synthetically generates workloads to run against your server. It's pretty robust and has a lot of parameters so that you can customize your test. Ex. In the command below, I specified -b8k, which means the block size is going to run at 8k, which is the size that SQL

Happy Thanksgiving! If you're not familiar with DBAtools.io I suggest you head over to the site and see what you're missing. The amount of cmdlets is just staggering. These cmdlets can be used for so much more than migrations. But i'm going to share just a tiny fraction of what you can do. For me, this is like the "easy" button of migrations. I was looking to migrate a large database from one server to another. There are enough considerations to make when handling large amou

Today's post was prompted by SQL On Ice. He proposed that we take this week's TSQL Tuesday to show our appreciation of people who have helped us. I've said it before, in my recap for SQL Cruise, that one of the best things about working with SQL Server is the community. There's no way I'd be where I am today without it. When I first started attending user groups and SQL Saturdays, I felt like the "stupid" person in the class. But, as it turned out, there is no such thing as

There are several ways to monitor your Availability Groups using native SQL tools. In this particular article I'm going to cover a few SQL Agent Alerts that I have used in the past. Let's first identify some of the error numbers we will be referencing. Error 1480 - Indicates a server role change. Basically when the primary fails over to a secondary. Error 35264 - Let's us know if data movement has been suspended for whatever reason. Error 35265 - Let's us know if/when data mo

This post is meant to be a quick primer on Table Partitioning in SQL Server. What is it? Let's say you have a large table that is accepting a lot of transactions, partitioning allows you to scale this particular table out into different (physical partitions). Thus spreading out the I/O across multiple files and even disks. Why do I need it? While the integrity of the data or table remains intact, almost transparent to front end queries (still seen as a single table), but the

Build the cluster right so you don't run around frantically like this guy does for a living. So most DBA's know that AlwaysOn Availability groups were introduced in SQL 2012. I know it was the most exciting feature for me a few years ago and I couldn't wait to implement it. i'm going to share how I setup my home lab and thus used this as a template in a professional environment. To make this all readable I need to split it up into multiple parts. This first part will cover h

An elegant function for a more civilized age: Another way to detect deadlocks is to utilize the TRY CATCH block to raise errors in TSQL code. I found this to be a very elegant way of detecting deadlocks pragmatically. You can use the below code sample in a stored procedure. Essentially, you can make it so that if the error number is within 1204, 1205, 1222 it will return a clear message that a deadlock has occurred. What if we had an error that wasn't a deadlock? If another e