SQL Server Archives - Page 9 of 33

20Jan 2020 by Grant Fritchey 10 Comments

Extended Events Misperceptions: Profiler is Easier

I know, I know, I'm on #teamexevents so I think that Extended Events can do no wrong, but let's address this thought that Profiler is easier. Now, if we're strictly talking knowledge, sure, if you've got a lot of experience with Profiler/Trace and very little with Extended Events, of course Profiler is easer. However, what I'm told is that Profiler doesn't require very much set up, while Extended Events does. That's just wrong, but let's put it to the test. The Test For the comparison, we're not going to do anything special with either tool. I'm just going to start collecting query data with the fewest possible clicks and/or key strokes. I'm going to use both tools short cuts to make this as fast as possible. The goal is, click,…

13Jan 2020 by Grant Fritchey 4 Comments

Causality Tracking in Extended Events

SQL Server

If you go through all the stuff I've written about Extended Events, you'll find that I use causality tracking quite a bit. However, I've never just talked about what causality tracking is and why I use it so frequently. Let's fix that issue now. Causality Tracking Causality tracking is quite simple to understand. It's property that you set for a given session. A session, of course, is defined by one or more events and a target. You can define things about a session, like it's name, when you define the session itself. Turning on, or enabling, causality tracking is just a matter of defining that the session will have causality tracking. It looks like this in the GUI: It looks like this in the T-SQL code: CREATE EVENT SESSION QueryBehavior…

06Jan 2020 by Grant Fritchey 8 Comments

Execution Plans: First Operator

SQL Server, T-SQL

The first time you see a new execution plan that you're examining to fix a performance problem, something broken, whatever, you should always start by looking at the first operator. First Operator The first operator is easily discerned (with an exception). It's the very first thing you see in a graphical execution plan, at the top, on the left. It says SELECT in this case: This is regardless of how you capture the execution plan (with an exception). Whether you're looking at an execution plan from the plan cache, Query Store, or through SSMS, the execution plan, regardless of complexity, has this first operator. In this case, it says UPDATE: If you get an execution plan plus runtime metrics (previously referred to as an "actual" execution plan), you'll still see…

16Dec 2019 by Grant Fritchey 53 Comments

Why Don’t People Use Columnstore Indexes?

Professional Development, SQL Server

I saw this question on SQL Server Central the other day and had an immediate, visceral reaction. I know why. Now, before I explain my answer, please, let me reassure you. I get it. You're busy. What I'm about to suggest is not meant as a direct critique of you. It's just an observation of the human condition. Heck, maybe I'm wrong. So, before you write the angry screed about how busy you are and why you can't possibly do what I'm about to suggest, believe me, I already understand. I'm still going to suggest something that's going to make some of you angry. Common Knowledge If you'll permit me, I want to talk about Extended Events before we talk about Columnstore. SIDE NOTE: Standing invitation, any time I'm at…

09Dec 2019 by Grant Fritchey 24 Comments

Distributing Jupyter Notebooks

Professional Development, SQL Server

If you're working with the Microsoft Data Platform, you should be, at the least, exploring Azure Data Studio as a new tool in your toolbox. One of the big reasons for this is the inclusion of Jupyter Notebooks. For those who don't know, Jupyter Notebooks are an open source documentation tool that lets you combine text and pictures with live code. From this we can talk about runbooks that you can share with people, lessons in combination with videos, presentations, interactive software documentation and lots more. I'm myopically focused at the moment on Azure Data Studio, but there are a lot of other places and ways to create or consume notebooks. However, I'm going to keep my focus. The issue I'm running into, is distributing the notebooks. Where to go…

02Dec 2019 by Grant Fritchey 5 Comments

Tracking CPU Use Over Time

SQL Server

A question that I've seen come up frequently just recently is, how to track CPU use over time. Further, like a disk filling up, people want to know how to predict their CPU usage, so that they can easily decide "now is when I upgrade the hardware". Well, the bad news is, that ain't easy. CPU Use Over Time There are a bunch of ways to look at processor usage. The simplest, and probably most common, is to use the Performance Monitor counters such as '% Processor Time'. Query this, you can get an average of the processor usage at a moment in time. Ta-da! Fixed it. I thought you said this was hard Grant. Well, hang on. Are you running on a single processor machine? If so, cool, maybe…

22Oct 2019 by Grant Fritchey 2 Comments

Every Execution Plan Is An Estimated Plan

SQL Server

I consider myself to be the most responsible for making such a huge deal about the differences between what is labeled as an Estimated Plan and an Actual Plan. I walked it back in the second edition of the Execution Plans book. Hugo and I completely debunked the issue in the third edition of the Execution Plans book. That is the one you should all be referencing now. As I like to joke, the guy who wrote the first two editions of the book was an idiot (and lest anyone take offense, let's be clear, I'm the idiot). Now, I'm trying my best to make this whole issue more clear. Let's talk about the "different" plans you can capture in SQL Server. Estimated Plan This is where you have a…

14Oct 2019 by Grant Fritchey 1 Comment

Database Fundamentals #23: Filtering Data

Database Fundamentals, SQL Server

If you've been reading these Database Fundamentals posts, you've already seen the WHERE clause because of your use of it when manipulating data with DELETE and UPDATE statements. It's also been used several times earlier in this series to limit the values returned from a SELECT statement. The primary places where people run into trouble with T-SQL is in the JOIN criteria and the WHERE clause criteria. This occurs because they donâ€™t understand well enough what the filters and operators theyâ€™re using will do. They end up returning too much data because they didnâ€™t us the WHERE clause or misapplied it. They also filter too much data out. Just remember, there are even more functions than we go over here in this series. While these basic operators answer most needs,…

07Oct 2019 by Grant Fritchey 5 Comments

SQL Injection Mitigation in SQL Server 2019

SQL Server

I've been teaching a lot more about SQL Injection lately (including blog posts). I've been doing this because, despite this being a 21 year-old problem with well defined solutions, we're still dealing with it. Recently, while sitting in the speaker room at Techorama Netherlands (fantastic event, strongly recommended), I had the opportunity to spend a little time with Niko Neugebauer. I was freaking out because my demos were failing (fixed 'em finally). Niko was talking to me about the new Feature Restrictions and their effect on SQL Injection in SQL Server 2019. I didn't know what he was talking about, so I had to look it up. Of course, top resource, Niko's blog. Feature Restrictions in SQL Server 2019 The Feature Restrictions in SQL Server 2019 are actually being added…

09Sep 2019 by Grant Fritchey 9 Comments

Get the Last Actual Plan With sys.dm_exec_query_plan_stats

SQL Server, T-SQL

I've always felt responsible for making such a big deal about the differences between estimated and actual plans. I implied in the first edition of the execution plans book (get the new, vastly improved, 3rd edition in digital form for free here, or you can pay for the print version) that these things were so radically different that the estimated plan was useless. This is false. All plans are estimated plans. However, actual plans have some added runtime metrics. It's not that we're going to get a completely different execution plan when we look at an actual plan, it's just going to have those very valuable runtime metrics. The problem with getting those metrics is, you have to execute the query. However, this is no longer true in SQL Server…

Category: SQL Server