05Nov 2018 by Grant Fritchey No Comments

What Is Happening, Right Now, At the PASS Summit?

PASS, Redgate Software
Honestly, that's a very hard question to answer. I mean, first of all, you can look at the schedule. There will be all day pre-conference seminars going on today (when this is published) and tomorrow. The rest of the week has all the breakout sessions. During the event this week, if you're not there, you can take part in some of the event by visiting the PASSTV web site. Other than that, you can follow me on Twitter (@gfritchey). It really is one of those "you need to be there" kind of events. If you're not there this year, start planning for next year. However, Redgate is here to help. What We Learned at PASS Summit Redgate is going to hold a webinar on Tuesday, November 20th. The title of…
Read More
29Oct 2018 by Grant Fritchey 14 Comments

Explicitly Drop Temporary Tables Or Wait For Cleanup?

SQL Server, SQL Server 2016, SQL Server 2017, T-SQL
I was recently asked if we are going to see performance differences if we explicitly drop temporary tables. I couldn't remember the specifics, but I said it actually didn't matter. However, that answer has bugged me, so I set up a quick test. Explicitly Drop Temporary Tables We could make this a crazy set of tests, but I wanted to keep things relatively simple. I created two procedures that create identical temporary tables. One drops the tables, the other doesn't: CREATE PROC dbo.BOMDrop AS CREATE TABLE #BOMData (BOMLevel SMALLINT, PerAssemblyQty DECIMAL(8, 2), ComponentName VARCHAR(50), ProductAssemblyName VARCHAR(50), UnitMeasure VARCHAR(50)); INSERT #BOMData (BOMLevel, PerAssemblyQty, ComponentName, ProductAssemblyName, UnitMeasure) SELECT bom.BOMLevel, bom.PerAssemblyQty, c.Name, pa.Name, um.Name FROM Production.BillOfMaterials AS bom JOIN Production.Product AS pa ON pa.ProductID = bom.ProductAssemblyID JOIN Production.Product AS c ON c.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
24Sep 2018 by Grant Fritchey 14 Comments

Extended Events and Stored Procedure Parameter Values

SQL Server, SQL Server 2016, SQL Server 2017
One complaint I've received frequently is that you can't see stored procedure parameter values in Extended Events. That is patently not true. However, it does depend on where and how you capture the events and which stored procedure parameter values you're going for. I think this is a holdover from 2008 when Extended Events... well, let's be kind and say... didn't work well. Now, they do. Let's explore this a little. Capturing Stored Procedure Executions As with most things, there's more than one way to capture stored procedure execution in Extended Events. First up, it depends entirely on how they're called and on your intentions when you capture the information. Here are the three methods I know to capture just the completion metrics on stored procedure calls: rpc_completed sql_batch_completed module_end…
Read More
19Sep 2018 by Grant Fritchey 5 Comments

Databases and DevOps, It’s Not Just Me

Redgate Software
Anyone who subscribes to my blog or my YouTube channel as well as anyone following me on social media knows that I talk about DevOps and databases a lot. I can't help it. I have a real passion for the topic. I sincerely believe we should all be taking advantage of the ways we can improve what we do offered by DevOps. It's not just me. Gene Kim on DevOps If you haven't read book The Phoenix Project, you should. Go get that done. I'll wait. You back? Good. Loved it right? One of the authors of the book will be presenting a webinar with Redgate Software (my employer) on Tuesday, September 25th on 2018 at 17:00BST (that's 11:00 CDT here in the US). He'll be joined by Steve Jones…
Read More
18Sep 2018 by Grant Fritchey 9 Comments

Announcing the 5th Edition of SQL Server 2017 Query Performance Tuning

SQL Server, SQL Server 2016, SQL Server 2017
I am quite excited to announce that the latest, most up to date, and by far the largest, copy of my book on query tuning is now available. 900 pages of information on how to improve the performance on your Azure SQL Database and SQL Server instances has been published. You can get your hands on the digital copies now and the print copies will be available shortly. Follow this link to Amazon to get your copy. Special, public, and heart-felt thanks to my technical editor, my hero, Joseph Sack. He's worked with me through four editions of this monster and the book wouldn't be what it is without him. If you'd like to learn some of the material in the book, I'll be presenting an all-day seminar two more…
Read More