Grant Fritchey, Author at Grant Fritchey

06Aug 2018 by Grant Fritchey 2 Comments

Analyze Actual Execution Plan

One of the many new sets of functionality introduced in SQL Server Management Studio 17 is the new option "Analyze Actual Execution Plan." If Microsoft continues down this path, there will be a lot of useful functionality at some point. If you haven't yet looked at Analyze Actual Execution Plan, well, read on. Analyze Actual Execution Plan To get to the new functionality, you have to have an Actual Execution Plan open within SSMS 17. After that, it's just a matter of right clicking to bring up the context menu: If you select the menu choice, then a new window opens at the bottom of the execution plan, showing each of the batch statements in one tab, and some interesting stuff in the next tab: You can click that to…

03Aug 2018 by Grant Fritchey No Comments

Youtube Channel Update: 3 August 2018

Misc

I've posted a number of new videos to the Youtube channel that might be of interest if you're a data professional. First up, I've started migrating my Database Fundamentals posts over to Youtube as SQL Server Fundamentals. The first one is available: [embedyt] https://www.youtube.com/watch?v=NHkzj9ZRhbk[/embedyt] Speaking of DBAs, the job is not going away, but it is changing. Find out how to survive the change: [embedyt] https://www.youtube.com/watch?v=loUwmKQ_Eg4[/embedyt] Would you like to know how the Query Store works? Here's a getting started video: [embedyt] https://www.youtube.com/watch?v=XK8rBO9R43c[/embedyt] I also have a good video on how to combine capturing query metrics along with wait statistics using Extended Events: [embedyt]https://www.youtube.com/watch?v=nYyTSxry03A&t=97s[/embedyt] Wait until you see what Trace Flag 7412 can do for you. It's very cool. [embedyt]https://www.youtube.com/watch?v=ei2zJwZyRic&t=79s[/embedyt] Finally, if you are interested in attending one of my…

30Jul 2018 by Grant Fritchey No Comments

Identifying HIPAA, PCI & SOX Data for Masking

Redgate Software

Working for a company based in the UK (still currently a part of the EU) I had a lot of motivation to learn about the GDPR and what it means for data professionals. Further, the understanding that, through treaties and court precedent, the GDPR can apply to companies around the world also motivated me to learn about the privacy and protection mechanisms that it required. However, there is privacy and protection much closer to home from the data and security definitions in HIPAA, PCI and SOX. I've been doing a bunch of research on all these to better understand how they, along with the GDPR, and a whole slew of new legislation coming from around the world, will impact the database. More specifically, I've been trying to understand how best…

23Jul 2018 by Grant Fritchey 5 Comments

Database Fundamentals #18: The SELECT Statement

Database Fundamentals

In my previous Database Fundamentals post, I showed you how to use the Query Designer to build a query. That was a SELECT statement. The basic construct of all your SELECT statements will be the same. Youâ€™re going to define a list of columns, the table or tables youâ€™re interested in, and some sort of filter criteria. Thatâ€™s the bare bones basics of how it works. But, as we all know, the devil is in the details. There are lots and lots of details. This section will introduce the T-SQL SELECT statement and start explaining some of those details. Column List Youâ€™ve been introduced to the basic concepts of the column list in the SELECT statement. It represents the information that is going to be available for display by whatever…

16Jul 2018 by Grant Fritchey 5 Comments

Databases and DevOps, Tools are the Easy Part

DevOps

Moving your database development, deployment and management into a DevOps methodology does involve choosing and implementing tools and tooling. Tools are a necessary aspect of DevOps because, one of the fundamentals of implementing a DevOps approach is automation. To automate, you need the right tools. However, tools and automation, while they represent a lot of work, are actually the easy part of the process of moving into DevOps. What's the hard part? Changing how you do things. Change is Hard One of the fundamental questions you need to learn when you start to implement a DevOps approach consists of a single word: Why. "We always manually run a script in staging prior to running it in production." Well, why? Why can't that be automated? Is there a reason that the…

09Jul 2018 by Grant Fritchey 1 Comment

What Is The Preferred Join Operator in SQL Server?

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL

I schedule many of my blog posts at least 2-3 weeks out, so I have time to adjust them, change, them, insert new things into the schedule, what have you. So, as you read this, I'll have written it at least two weeks ago. As I write this, I'm recovering from a very mild, but irritating, cold. I needed to write a blog post, but my stuffed head wasn't coming up with ideas. So, I half-jokingly asked Twitter for any execution plan questions so I could write something. First response, also a joke, was the question at the title of this post: What is the preferred operator when joining tables: Hash Match, Nested Loops or Merge? While my immediate response to this question is, yes. Meaning, they're all preferred, situationally.…

05Jul 2018 by Grant Fritchey 60 Comments

Why Aren’t You Using SSMS 17?

SQL Server, SQL Server 2016, SQL Server 2017

Last fall, Microsoft split the coding and release of SQL Server Management Studio away from any dependency on the server code. With that, they began an aggressive and exciting series of releases with exciting new functionality in each release. However, you don't care. How do I know? I've seen the telemetry that shows that most of you are still using old versions of SSMS. I want to know why. Please, post comments below so I can understand. Why You Should Move to SSMS 17 There are a ton of reasons for you to make the move. The least of which is bug fixes are coming hot and fast. The more exciting things are the ability to compare execution plans or the new Extended Events sessions that look just like those…

25Jun 2018 by Grant Fritchey 4 Comments

My Speaking Schedule

Misc

I have quite a few speaking engagements coming up. Rather than an intermittent blog post, I've decided to have a permanent home for this information. If you want to know where to get in touch with me, go to this page of my blog. I'll keep a rolling update of all the events I have coming up. Also, if you want me to speak at your event, it might be a good idea to look to see if I'm already scheduled, or, if I'm going to be right in your area already (I'm happy to go to local user groups if I'm free). I hope this page is helpful. I'll still occasionally promote a particular event with a blog post or links at the bottom of other posts. Anything I…

18Jun 2018 by Grant Fritchey 11 Comments

Database Fundamentals #17: Learning T-SQL

Database Fundamentals

While SQL Server Management Studio (SSMS) provides a robust graphical user interface (GUI), the commands you're going to use the most to control databases and the data within them in SQL Server are going to be done through T-SQL. Therefore, you really need to spend time learning how to write, read, and edit T-SQL. Previous posts in the Fundamentals series have showed how to INSERT, UPDATE and DELETE data using T-SQL. Next, we're going to learn SELECT. However, I want to show you a crutch you can use as you get started learning how to write T-SQL, the Query Designer window. The instruction on this topic is only meant to provide a mechanism to focus on the more important topic, learning T-SQL. However, this may be an easier path for…

11Jun 2018 by Grant Fritchey 6 Comments

Plan Metrics Without the Plan: Trace Flag 7412

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL

I place a lot of emphasis on capturing actual execution plans because of the runtime metrics, but with Trace Flag 7412, we don't need the plan. This is great news, because capturing execution plans, even using extended events, is an expensive proposition. However, using either the query_thread_profile event, or, Trace Flag 7412, we can get the runtime metrics without the plan. Trace Flag 7412 Here's how it works.You can either be running the extended event, query_thread_profile (a debug event, but one documented and supported by Microsoft) or, enable the Trace Flag 7412. I like to use the extended event in a targeted fashion to easily see behaviors on a query without having to capture the plan. You can even capture the information and then combine it with an estimated plan…

Author: Grant Fritchey