Extended Events and Stored Procedure Parameter Values

SQL Server, SQL Server 2016, SQL Server 2017
One complaint I've received frequently is that you can't see stored procedure parameter values in Extended Events. That is patently not true. However, it does depend on where and how you capture the events and which stored procedure parameter values you're going for. I think this is a holdover from 2008 when Extended Events... well, let's be kind and say... didn't work well. Now, they do. Let's explore this a little. Capturing Stored Procedure Executions As with most things, there's more than one way to capture stored procedure execution in Extended Events. First up, it depends entirely on how they're called and on your intentions when you capture the information. Here are the three methods I know to capture just the completion metrics on stored procedure calls: rpc_completed sql_batch_completed module_end…
Read More

Databases and DevOps, It’s Not Just Me

Redgate Software
Anyone who subscribes to my blog or my YouTube channel as well as anyone following me on social media knows that I talk about DevOps and databases a lot. I can't help it. I have a real passion for the topic. I sincerely believe we should all be taking advantage of the ways we can improve what we do offered by DevOps. It's not just me. Gene Kim on DevOps If you haven't read book The Phoenix Project, you should. Go get that done. I'll wait. You back? Good. Loved it right? One of the authors of the book will be presenting a webinar with Redgate Software (my employer) on Tuesday, September 25th on 2018 at 17:00BST (that's 11:00 CDT here in the US). He'll be joined by Steve Jones…
Read More

Announcing the 5th Edition of SQL Server 2017 Query Performance Tuning

SQL Server, SQL Server 2016, SQL Server 2017
I am quite excited to announce that the latest, most up to date, and by far the largest, copy of my book on query tuning is now available. 900 pages of information on how to improve the performance on your Azure SQL Database and SQL Server instances has been published. You can get your hands on the digital copies now and the print copies will be available shortly. Follow this link to Amazon to get your copy. Special, public, and heart-felt thanks to my technical editor, my hero, Joseph Sack. He's worked with me through four editions of this monster and the book wouldn't be what it is without him. If you'd like to learn some of the material in the book, I'll be presenting an all-day seminar two more…
Read More

Query Store and Log Backups

T-SQL
A question that came up recently around Query Store is what happens when there are log backups in use on the database. Let's talk about it. Query Store and Log Backups The core of the answer is very simple. Query Store, like any other data written to a database, whether a system table or a user table, is a logged operation. So, when you backup the database, you're backing up Query Store data. When you backup the logs, you're also backing up Query Store data. A point in time will include all the data written to the Query Store at that point. However, that's the kicker. At what point was the Query Store information written to disk? By default, there's a fifteen minute cycle before the Query Store moves the…
Read More

3rd Edition, SQL Server Execution Plans, a Story

SQL Server, SQL Server 2016, SQL Server 2017
Four years ago, after a bunch of dithering and some negotiations with Tony Davis, my editor, I started to update my book, SQL Server Execution Plans. We managed to convince Hugo Kornelis to be the tech editor. I started to do the real writing in early 2015. I was most of the way through a first draft and no one liked it. Tony was unhappy. Hugo was unhappy. I was unhappy. I was just trying to update the existing book, SQL Server Execution Plans. It wasn't working. We all came to the conclusion that the old book was wrong. Not simply in a technical sense, although there was a lot of that, but in a structural sense. So we started rearranging things. SQL Server 2014 came out, but I was…
Read More

Estimated Plans and Forced Plans from Query Store

SQL Server 2016, SQL Server 2017, T-SQL
While all plans are estimated plans, there is still a difference between capturing an estimated plan and looking at a plan from the cache or from query store. Or is there? A question came up during a recent presentation; what happens to capturing an estimated plan when you're forcing plans? Let's find out. The answer is interesting. Estimated Plans Here's my stored procedure that I'll be using with AdventureWorks2017: [crayon-5ba8f0363cc13420786364/] For reasons I'll explain in a bit, I'm going to free the procedure cache: [crayon-5ba8f0363cc1d276524520/] Then, if I capture an estimated plan for two different values: [crayon-5ba8f0363cc22430504326/] I end up with two different execution plans: Click to embiggen This is because the different values have different data distribution within my statistics and parameter sniffing leads to difference in the plans.…
Read More

Back to University, Redgate University

Redgate Software
Let's be really clear, Redgate makes ingeniously simple tools. That's a fact. Nothing has changed. However, if you really want to get the full range of capability out of the tools, you need to understand how best to put them to work. Enter Redgate University. Redgate University The concept is very simple, one might even say ingeniously so; have a course on how to get more out of the tool. So, for example, I can demo SQL Clone and SQL Data Masker in about 3 minutes each. You'll understand what they are, how they work and how they'll help you in your company. Done. You can even install them and have them working within your environment within about 15 minutes or so on each. Cool. However, you won't be taking…
Read More

Why I Love Extended Events, Reason 394: Customize Live Data

Azure, SQL Server, SQL Server 2016, SQL Server 2017
There are so many reasons to love Extended Events that it can really be overwhelming, but, one more reason that I love them are the ability to customize Live Data. More importantly, you can share that customization with others or move it between machines. Let's explore this. Customize Live Data If you open up an Extended Events file, or connect to a running Session, you're looking at the Live Data window and it looks something like this: I won't lie, that's a pretty ugly UI. At the top are all the events you've captured. At the bottom are the event fields. If you want to look at the information in different events, you click on the event, then down at the fields... blech! However, I have some options. Let's right…
Read More

SQL in the City Summit

Redgate Software
This fall, in October, Redgate Software will be hosting three, live, in-person events. These events will take place in New York, London and Chicago. They are on the 12th, 18th, and 26th of October, respectively. You can follow this link to read all the details. I'll be speaking there, along with other Redgaters like Steve Jones and Tom Austin. There will also be other, quite amazing, speakers; Bob Ward of Microsoft, Brian Randell from MCW Technologies, Ronit Reger of Microsoft, Bob Pusateri consultant and Bob Walker of Octopus. We're going to be providing excellent educational content done in the way that only Redgate does it. We'll cover a number of topics, all related to our central theme of Compliant Database DevOps. I want to tell you a little about my…
Read More

Actual Execution Plan Costs

T-SQL
Why don't "actual execution plans" have "actual execution plan costs"? This is a question and a myth I have to fight against all the time. It's so hard to convince people that all execution plans are estimated plans in the first place (by the way, all execution plans are estimated plans). If we execute a query at the same time we capture a plan, we have enabled SQL Server to also capture run-time metrics with that plan. So we end up with what is known as an actual plan, but it's still just an estimated plan plus those run-time metrics. Execution Plan Costs When you look at a given operator within an estimated plan, it's going to show you four numbers related to cost: Estimated CPU Cost Estimated I/O Cost…
Read More