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…
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…
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…
In April, I said I was going to start learning Jupyter Notebooks. It's November. Let's get going with your first Jupyter Notebook. A quick aside before we start. I think one of the huge strengths that is going to come out of these things is as a runbook. You can share a notebook with someone, they can run the queries on it against their own systems and return the book, with the results to you. That's going to be extremely useful as a troubleshooting tool, but has all sorts of other functionality as well. I strongly suggest you start learning these things, as I am. Azure Data Studio There are a number of ways to create and consume Jupyter Notebooks, but I want to focus on the functionality around data…
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…
In order to take advantage of R and Python (and Java in SQL Server 2019) directly from your SQL Server scripts, you'll be using the function sp_execute_external_script. When you see this code in use for the first time, it's going to remind you of sp_execute_sql. The very first thing I thought about was, "Oh no. Another SQL Injection vector." I have a little good news and a little bad news. It's Not SQL The first and most important thing to understand is, we're not talking about SQL. Let's start with looking at some code. This is straight from the examples in the Microsoft documentation linked above: DROP PROC IF EXISTS generate_iris_model; GO CREATE PROC generate_iris_model AS BEGIN EXEC sp_execute_external_script @language = N'R' , @script = N' library(e1071); irismodel <-naiveBayes(iris_data[,1:4], iris_data[,5]);…
Not really, but sort of. The beauty of containers, at least in a dev/test environment, is the ability to spin them up while you need them and then throw them away when you're done. Containers give you a bunch of functionality not otherwise available through a VM. However, once you've spun up a container, they're so dull. Why Are Containers Boring Grant? I'm so glad you asked. Last week I was presenting at SQLIntersection (great show, you should consider attending). I was talking about Query Store in SQL Server 2019. One person in the audience asked, "Can Query Store run inside a container?" I responded, "Great question, let's check." I then switched over to VS code to show this: docker run ` --name DemoSharedVol ` -p 1460:1433 ` -e "ACCEPT_EULA=Y"…
In my last post I showed how you can create a volume with your container. I then showed a few things you can with a container using a volume. I want to explore volumes just a little bit more. Locate Your Volume To have a little more fun with volumes, first, let's share a drive. You do this in the Settings in Docker Desktop (assuming that's what you're using): While this should just work, it didn't for me until I restarted Docker. So you may need to do that. Go to the drive and create a directory. I'm putting one in at C:\Docker\SQL. Once I've done that, let's create a new container: docker run ` --name SQL19 ` -p 1433:1433 ` -e "ACCEPT_EULA=Y" ` -e 'SA_PASSWORD=$cthulhu1988' ` -v C:\Docker\SQL:/sql `…
In yesterday's blog post we pulled SQL Server images in preparation for today's blog post where we create containers from those images. If you haven't already, get Docker installed and follow the instructions here to get at least one image on your machine. If you're interested in why I'm talking about containers all week, read this. I'm using all PowerShell commands to control Docker. Docker Run You can use 'docker create' to create an image and then start it up. However, we can just get started running a container from one of the images we downloaded yesterday. We can just simultaneously create and start the container using 'docker run': docker run -e 'ACCEPT_EULA=Y' ` -e 'SA_PASSWORD=$cthulhu1988' ` -p 1433:1433 ` --name dockerdemo ` -d mcr.microsoft.com/mssql/server:2017-latest Let's break this down a…
The system_health Extended Events session is incredibly useful. Further, it's running, by default, in every server you have under management that is 2008 or greater. Things are not the same in Azure though. system_health in Azure SQL Database If you look at the documentation for system_health, it shows that it's applicable to Azure SQL Database. However, if you try to run the example query, it won't work. This is because the implementation of Extended Events inside Azure SQL Database is a little different. Instead, you need to use the Azure SQL Database equivalent system views to create the same query like this: SELECT CAST(dxdst.target_data AS XML) FROM sys.dm_xe_database_session_targets AS dxdst JOIN sys.dm_xe_database_sessions AS dxds ON dxds.address = dxdst.event_session_address WHERE dxds.name = 'system_health'; Now, running this in Azure, prepare to be…