23Jan 2019 by Grant Fritchey 2 Comments

All Day, Training Day at SQLBits

Azure, SQL Server 2016, SQL Server 2017, T-SQL
It's a somewhat late addition, but I have an all-day Training Day at SQLBits. It takes place on Thursday, February 28th. You can read all about it on the SQLBits web site. I want to take a moment here to expand on the information that we're going to cover. I think the abstract does a good job of conveying what we'll be doing all day, but I figured a little more detail won't hurt. Query Tuning is Hard This is the very first thing I talk about. Query tuning is hard. I've got a nearly 1,000 page book on the topic, which should give you an idea of just how much material there is to cover. With the training day I've decided to focus on the tools that Microsoft gives…
Read More
21Jan 2019 by Grant Fritchey 1 Comment

Get Your Learn On

PASS, SQL Server
There is one truth that I can say about technology with an absolute certainty: It's going to change. Get your learn on! Technology is going to be shifting under your feet, constantly. Even if all you ever do is work with SQL Server, on premises, on hardware, without VMs, containers, clouds or any of that foofaraw, things are going to change. Dealing With Change What's the best way to deal with change? Get your learn on! Learning all the new stuff is absolutely necessary. It's unavoidable. Even if you're not running SQL Server 2019 in production today (and very few of you should be as I write this because it's still in preview) because you're still on SQL Server 2005 (and, by the way, support ended in April of 2016,…
Read More
03Dec 2018 by Grant Fritchey 9 Comments

Database Fundamentals #20: Using the JOIN Operator, Inner Join

Database Fundamentals
It is entirely possible to try to JOIN two tables on almost any field, as long as the two data types can, in some way, be made to reconcile to each other, you can try to join the tables. But, most database designs assume a much more directly relationship and provide a column or columns in one table that match the identifying column or columns in the other table. INNER JOIN The INNER JOIN will return the parts of both data sets that match. Frequently, what you'll see when joining two tables is the same column name in each table. With that, you have to be sure to identify the owner of each column. I've introduced what is called an alias to make it so I don't have to type…
Read More
26Nov 2018 by Grant Fritchey 5 Comments

Forcing a Plan That Has a Plan Guide

SQL Server 2016, SQL Server 2017, T-SQL
The question that came up during a recent class I was teaching was: What if you have a plan guide to get the plan you want, but then decide, instead of using the plan guide, you'll just force the plan? Ummmm…. No idea. Let's test it. First, Create a Plan Guide I have a couple of queries I use to teach about how statistics affects plan choice, so we'll use that here. I'm going to also define and create a plan guide that makes this plan use a small row count for all queries against it: CREATE OR ALTER PROC dbo.AddressByCity @City NVARCHAR(30) AS SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode FROM Person.Address AS a JOIN Person.StateProvince AS sp ON a.StateProvinceID = sp.StateProvinceID WHERE a.City = @City; GO…
Read More
19Nov 2018 by Grant Fritchey 1 Comment

Query Store and Plan Cache Plans Compared

SQL Server 2016, SQL Server 2017
Query Store plans and the plans in cache are identical, right? There won't be differences because the plan that is in cache is the plan that was used to execute the query. Similarly, the plan that is in the Query Store is the plan that was used to execute the query as well. Therefore, they will be the same. However, some small differences actually can show up. Differences Between Plans In order to compare the two plans, first, we need a query. Here's a stored procedure that I'm going to use to generate a plan that will be in cache and in the query store: CREATE PROC dbo.ProductTransactionHistoryByReference ( @ReferenceOrderID int ) AS BEGIN SELECT p.Name, p.ProductNumber, th.ReferenceOrderID FROM Production.Product AS p JOIN Production.TransactionHistory AS th ON th.ProductID = p.ProductID…
Read More
22Oct 2018 by Grant Fritchey 6 Comments

Why Did a Plan Get Removed From Cache?

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL
I was recently asked if we could tell why a plan was removed from cache. If you read this blog, you know what I'm going to say next. I checked the extended events and there are actually two different events that will tell us information about a plan removed from cache; sp_cache_remove and query_cache_removal_statistics. Let's talk about how these work. Removed From Cache Just so we can see ALL the activity, I'm creating an Extended Events session that captures a little more than just the two events: CREATE EVENT SESSION PlanCacheRemoval ON SERVER ADD EVENT sqlserver.query_cache_removal_statistics (WHERE (sqlserver.database_name = N'AdventureWorks2017')), ADD EVENT sqlserver.rpc_completed (WHERE (sqlserver.database_name = N'AdventureWorks2017')), ADD EVENT sqlserver.rpc_starting (WHERE (sqlserver.database_name = N'AdventureWorks2017')), ADD EVENT sqlserver.sp_cache_hit (WHERE (sqlserver.database_name = N'AdventureWorks2017')), ADD EVENT sqlserver.sp_cache_insert (WHERE (sqlserver.database_name = N'AdventureWorks2017')), ADD EVENT…
Read More
15Oct 2018 by Grant Fritchey 9 Comments

Using Extended Events to Capture Implicit Conversions

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL
Using the appropriate data type to avoid conversions or implicit conversions is a fundamental approach to good T-SQL coding practices. Implicit conversions can lead to poor performance. However, other than looking at every execution plan in your system, is there a way to see if you are experiencing implicit conversions? Actually, yeah, it's right there in Extended Events. plan_affecting_convert Built right into the Extended Events is an event that captures conversions that would affect execution plans, plan_affecting_convert. This event will show both CONVERT and CONVERT_IMPLICIT warnings that you would normally only see within an execution plan. You can capture this event with others. Capturing events together along with causality tracking makes it very easy to track queries that have the issue. Here's one example of how you might capture implicit…
Read More
08Oct 2018 by Grant Fritchey 3 Comments

Database Fundamentals #19: JOINS

Database Fundamentals
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…
Read More
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: SELECT p.Name, COUNT(th.ProductID) AS CountProductID, SUM(th.Quantity) AS SumQuantity, AVG(th.ActualCost) AS AvgActualCost FROM Production.TransactionHistory AS th JOIN Production.Product AS p ON p.ProductID = th.ProductID GROUP BY th.ProductID, p.Name; Without a columnstore index in SQL Server 2017, the execution plan looks like this: Let's introduce a columnstore index: CREATE NONCLUSTERED COLUMNSTORE INDEX ix_csTest ON Production.TransactionHistory ( ProductID, Quantity, ActualCost ); 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…
Read More
24Sep 2018 by Grant Fritchey 12 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…
Read More