Grant Fritchey

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…

07Jun 2018 by Grant Fritchey No Comments

SQL in the City Streamed: June 2018

DevOps, Redgate Software

Next week, Redgate Software, will be putting on SQL in the City Streamed. These events are a great way to learn about SQL Server, the Microsoft Data Platform, privacy and protection, the GDPR, and, of course, DevOps. All of that, with some great information on Redgate tools and how they can help tossed in there. Agenda Scroll down at the link and check out the agenda. We're covering a pretty wide range of topics this time. I have the keynote, and I'm not going to tell you what it's about yet. OK. I'll tell you a little. Twist my arm. I have a message about DevOps that I think is very important. When it comes to the database, we frequently think about development and deployment as nothing but change, and…

04Jun 2018 by Grant Fritchey 5 Comments

Extended Events, the system_health Session, and Waits

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

I advocate for, use, document, teach, and just downright love, Extended Events. They are so much better than the old Trace Events (aka, Profiler) that it's sometimes difficult to keep from just gushing. Let's talk about a common situation that you're going to run into on your servers all the time and how you can put Extended Events to work to help you, without actually doing any work at all. What's that? Be lazy and get rewards? Yes. The Extended Events system_health Session On your servers, any of them that are SQL Server 2008 or newer, right now, unless you've performed actions to prevent this, you're running the Extended Events system_health session. It's just happening, currently, on all your servers. Nothing you need to do about it at all. I'll…

29May 2018 by Grant Fritchey 2 Comments

Query To Retrieve Statistics Data: dm_db_stats_histogram

SQL Server 2017, T-SQL

Starting with SQL Server 2016 Sp1 CU2, a new way of directly querying statistics, specifically the histogram, has been introduced: dm_db_stats_histogram. We've always been able to query the statistics using DBCC SHOW_STATISTICS. However, the output, three different result sets with three different structures, made automating access to statistics information a pain. Now, we have more capability through dm_db_stats_histogram. dm_db_stats_histogram To access the information in dm_db_stats_histogram, you just have to pass in the object_id and the statistics_id values for the statistics you're interested in like this: SELECT * FROM sys.dm_db_stats_histogram(OBJECT_ID('HumanResources.Employee'), 1) AS ddsh; It's very straight forward to use. The results look like this: Handy right? Now you can query the histogram directly. Yeah, I hear a few of you saying... and this helps me... how? Here's an example. This query…

21May 2018 by Grant Fritchey No Comments

Why PASS?

PASS

In case this is your first time hearing of it, PASS is the single largest community of Microsoft Data Platform professionals on the planet. It consists of local groups, virtual groups, multiple online events, SQLSaturday, and, to pay for it all, PASS Summit. However, that's not true. It consists of a whole bunch of our peers, people, data pros and developers, trying to do better and be better. PASS I'm currently serving, as an unpaid volunteer, on the Board of Directors of the PASS organization. Actually, truth be told, I'm sitting as president of the board. That means that I'm responsible for the whole shooting match. Our goals are really simple. We want to create as many possible ways for you, me, and all our peers to connect, share and…

14May 2018 by Grant Fritchey 11 Comments

A View Will Not Make Your Query Faster

SQL Server 2017

Twice recently, one on a blog post, and one in a forum post, I've seen people state, unequivocally, without reservation or hint of a caveat, that, "Oh, just put that query into a view. It will run faster." To quote the kids these days... Time for a rant. But First... Frequently when I post something that says, "Query Y runs faster than Query Red", I get responses from people saying, "Yeah, but if you run Query Red more than once..." or "Query Red was experiencing blocking..." or "You can't say Query Y is ALWAYS faster..." So, before we go down that road, a quick note on methodology. First, I'll be using Adventureworks because, reasons. Second, I won't run any of the following queries once. When doing something like this, I'll…

11May 2018 by Grant Fritchey 2 Comments

YouTube Channel Update: 11 May 2018

Misc

I've been busy getting the videos up on YouTube. If they're helpful to you, please subscribe. Some point soon I'll start doing some livestreams, maybe from different events, or during a presentation. If you're subscribed, you'll know about it when it happens. Videos We're getting closer and closer to when implementation of enforcement of the GDPR starts on the 25th of this month. Here's something else to think about in regards to data mapping: [embedyt] https://www.youtube.com/watch?v=GWIhg7uV1IQ[/embedyt] I find over and over that people really don't understand what a deadlock is. I make an attempt at explaining it so that everyone can understand: [embedyt] https://www.youtube.com/watch?v=pmxQGfasm54[/embedyt] Have I mentioned the GDPR yet? Yeah, well, you're also going to have to deal with data portability when dealing with the GDPR. Get your JSON…

07May 2018 by Grant Fritchey 5 Comments

When Simple Parameterization…Isn’t

SQL Server 2017

I'm desperately working to finish up a new version of my book on Execution Plans. We're close, so close. However, you do hit snags. Here's one. My editor decided to change one of my queries. I used a local variable so that I got one set of behaviors. He used a hard-coded value to get a different set. However, the really interesting thing was that his query, at least according to the execution plan, went to simple parameterization. Or did it? Simple Parameterization The core concept of simple parameterization is easy enough to understand. You have a trivial query using a hard-coded value like this: SELECT * FROM Person.Person AS p WHERE p.BusinessEntityID = 42; The resulting execution plan looks like this: The initial, graphical, pointer that we're seeing parameterization…