Using Extended Events to Capture Implicit Conversions

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL
Using the appropriate data type to avoid conversions or implicit conversions is a fundamental approach to good T-SQL coding practices. Implicit conversions can lead to poor performance. However, other than looking at every execution plan in your system, is there a way to see if you are experiencing implicit conversions? Actually, yeah, it's right there in Extended Events. plan_affecting_convert Built right into the Extended Events is an event that captures conversions that would affect execution plans, plan_affecting_convert. This event will show both CONVERT and CONVERT_IMPLICIT warnings that you would normally only see within an execution plan. You can capture this event with others. Capturing events together along with causality tracking makes it very easy to track queries that have the issue. Here's one example of how you might capture implicit…
Read More

Database Fundamentals #19: JOINS

Database Fundamentals
The last Database Fundamentals post introduced the SELECT and FROM commands. We're going to start using JOIN operations shortly, but first, let's explore the idea behind joins. The very concept of relational storage that is the foundation of SQL Server requires you to related one table to another.  You do this through a operation called JOIN. There three basic types of JOINS, INNER, OUTER, and CROSS. Think of them like this. It’s all about relationships. The relationships are only ever between two sets of data. Yes, you can combine lots of tables together through a query, but each JOIN relationship will be between two sets of data. Types of Joins If you take two sets of data and represent them as two circles, they might look like this. An INNER…
Read More

Adaptive Joins and Join Hints

SQL Server 2017
At a recent all-day seminar on query performance tuning I was asked a question that I didn't know the answer to: "How do join hints affect adaptive joins?" I don't know. Let's find out together. Adaptive Joins Here's a query that we can run against AdventureWorks: [crayon-5bc7890d0d7f9209735550/] Without a columnstore index in SQL Server 2017, the execution plan looks like this: Let's introduce a columnstore index: [crayon-5bc7890d0d808466612505/] Now, if we run the same query, the execution plan changes to use an adaptive join like this: You can read more on adaptive joins here if this is new behavior. Now, what if we decide that we just want to see a hash join all the time? Adding a Join Hint I'm not a giant fan of any of the query hints.…
Read More

Introducing Azure Data Studio

Azure
If you're watching Microsoft Ignite or tracking the information coming out of it on social media, then you know that Azure SQL Studio has been changed to Azure Data Studio. I've got an early release on some of the bits. Let's explore what's going on. Azure Data Studio The core concept here is to have a development tool that gives you a common framework for working with data, not just SQL data, but CosmosDB and others. Further, a tool that you can run where you work. Do you have a Mac? Cool. Use Azure Data Studio. Running Linux? Cool. Use Azure Data Studio. Still on Windows with me? We also get Azure Data Studio. The first thing I'm excited about is the load time. It's fast. Really fast. Out of…
Read More

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-5bc7890d0fe95847913185/] For reasons I'll explain in a bit, I'm going to free the procedure cache: [crayon-5bc7890d0fea3183374907/] Then, if I capture an estimated plan for two different values: [crayon-5bc7890d0fea9535253238/] 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