Grant Fritchey - Page 3 of 110 - Intimidating Databases and Code

08Oct 2018 by Grant Fritchey 3 Comments

Database Fundamentals #19: JOINS

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…

01Oct 2018 by Grant Fritchey 3 Comments

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-5c6d78dc02998427681619/] Without a columnstore index in SQL Server 2017, the execution plan looks like this: Let's introduce a columnstore index: [crayon-5c6d78dc029ab913676750/] 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.…

24Sep 2018 by Grant Fritchey 10 Comments

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…

24Sep 2018 by Grant Fritchey 7 Comments

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…

19Sep 2018 by Grant Fritchey 5 Comments

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…

18Sep 2018 by Grant Fritchey 9 Comments

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…

17Sep 2018 by Grant Fritchey 3 Comments

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…

12Sep 2018 by Grant Fritchey 29 Comments

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…

10Sep 2018 by Grant Fritchey No Comments

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

03Sep 2018 by Grant Fritchey 1 Comment

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…