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

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

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

Before we begin: While the gui/wizard for setting up Availability groups is super easy, I'm going to go through utilizing T-SQL. This gives us an "Under the hood" look at the way these are setup. I will provide the full script at the end of the post. As an FYI, I will be using SQL 2017 Developer edition running on Windows Server 2016 in my virtual lab. (Running on Hyper-V). Installing SQL Server: The installation of SQL Server on each node is a basic Standalone installation.

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

What is it? A trigger is almost like a command/function that executes automatically in response to another transaction that has executed. Think of it as kind of a stored procedure that executes automatically when an event occurs. Types: DML Triggers - After and Instead AFTER triggers can run after an insert, update delete or merge. INSTEAD OF trigger can override the actions of a particular DML statement. They can provide some sort of error or value checking. Virtual Tables:

You might be turning the gun on yourself if you ever have to implement this. Disclaimer: I would never suggest running this in a production environment without good reason. I was asked to come up with something that will automatically kill blocked processes until the developers could figure out how to fix it (despite my suggestions for performance tuning). I wrote a simple stored procedure and it was placed into a job that ran continually. You can, of course, modify the quer

Not always optimal... What is it? Let’s say you have a parameterized proc, and it creates and caches an execution plan based on certain values for said parameters. It “sniffs” the parameters and uses that for cardinality estimation. Then you run this stored proc again, but with different values for the parameters. SQL Server is going to use the execution plan that has been cached even though a different execution plan would be more optimal due to different values (and uneven