28Jun 2016 by Grant Fritchey 6 Comments

CASE Statement in GROUP BY

SQL Server, SQL Server 2016
Set based operations means you should put everything into a single statement, right? Well, not really. People seem to think that having two queries is really bad, so when faced with logical gaps, they just cram them into the query they have. This is partly because SQL Server and T-SQL supports letting you do this, and it's partly because it looks like a logical extension of code reuse to arrive at a query structure that supports multiple logic chains. However, let's explore what happens when you do this on particular situation, a CASE statement in a GROUP BY clause. You see this a lot because a given set of data may be needed in slightly different context by different groups within the company. Like many of my example queries, this…
Read More
31May 2016 by Grant Fritchey 3 Comments

Precedence Goes to Query Store or Plan Guide?

SQL Server 2016
While presenting at SQLDay in Wroclaw, Poland, on the Query Store, I was asked a pretty simple question, which takes precedence, the Query Store or a Plan Guide? One of my favorite answers to questions is "I don't know" because it gives me the opportunity to learn. Let's figure this one out together. I'll post the code to recreate this experiment within AdventureWorks at the end of the article. I'm doing this because the code for forcing execution plans using Plan Guides can be pretty doggone long (you may need to generate your own XML from a plan on your own system, fair warning). I have a stored procedure that I use frequently to demonstrate parameter sniffing and bad parameter sniffing, AddressByCity (listed below). This query when passed the value…
Read More
12Apr 2016 by Grant Fritchey 8 Comments

A View Is Not A Table

Azure, SQL Server, SQL Server 2016, T-SQL
Blog post #4 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel In SQL Server, in the T-SQL you use to query it, a view looks just like a table (I'm using the AdventureWorks2014 database for all these examples): SELECT * FROM Production.vProductAndDescription AS vpad;   SELECT vpad.Name, vpad.Description, vpmi.Instructions FROM Production.vProductAndDescription AS vpad JOIN Production.Product AS p ON p.ProductID = vpad.ProductID JOIN Production.vProductModelInstructions AS vpmi ON vpmi.ProductModelID = p.ProductModelID WHERE vpad.ProductID = 891 AND vpad.CultureID = 'fr'; The above query actually combines two views and a table. This is what is commonly referred to as a "code smell". A code smell is a coding practice that works, but that can lead to problems. In this case, we're talking about performance problems. The performance problems when using views to join to…
Read More
29Feb 2016 by Grant Fritchey 4 Comments

Wait Statistics in Azure SQL Database

Azure, T-SQL
You need to be aware that you're going to see differences when you're working with Azure SQL Database when it comes to wait statistics. If you're running a v12 Azure SQL Database (and if you're not, go fix it), you can run a query against sys.dm_os_wait_stats. Here's the output for the TOP 10 waits ordered by wait time on one of my databases: Everything you're used to seeing, right? Well... not quite. This is Azure SQL Database. So, let's use sys.dm_db_wait_stats, a DMO that is only available within Azure. This lists waits by database. The TOP 10 here are: You'll notice that these results are wildly different from those above. What we're looking is largely a server versus a database, but not completely. I mean that sys.dm_os_wait_stats is showing the waits for the…
Read More
22Feb 2016 by Grant Fritchey 3 Comments

Why Is The Server Slow?

SQL Server, SQL Server 2016, T-SQL
This is blog post #2 in support of Tim Ford's (b|t) #iwanttohelp, #entrylevel. If you haven't been working in SQL Server for very long, you may not have got this phone call yet, but you will: Hi, yeah, the server is slow. Thanks. Bye. Let's pretend for a moment that you know which server they're referring to (because just finding out that piece of information can be a challenge). Now what? The list of tools and mechanisms within SQL Server for gathering metrics is extremely long: Performance Monitor Dynamic Management Views & Functions System Views Extended Events Trace Events Activity Monitor Data Collector Execution Plans 3rd Party Tools I'm leaving out lots of stuff in that list. So where do you start when you get this phone call? Where is the server slow?…
Read More
25Jan 2016 by Grant Fritchey 7 Comments

Finding Your Query in Query Store

Azure, SQL Server 2016, T-SQL
Query Store is pretty amazing. I'm loving working with it. I think it's likely to change how query tuning will be done in the future. Lots of people are probably going to just use the reports and tools in SQL Server Management Studio. However, a pretty healthy chunk of us will start using the system views in order to programmatically access the information stored in Query Store. One of the first things you're going to want to do is track down your query. The primary views you'll want are sys.query_store_query and sys.query_store_query_text. They join together based on the query_text_id. Let's take four scenarios and see if we can retrieve the correct query. First up, an ad hoc query: SELECT e.NationalIDNumber, p.LastName, p.FirstName, a.City, bea.AddressTypeID FROM HumanResources.Employee AS e JOIN Person.BusinessEntityAddress AS…
Read More
09Sep 2015 by Grant Fritchey 16 Comments

Yes, Foreign Keys Help Performance

SQL Server
I created this example several years ago that illustrates how foreign key constraints can help performance. It's a contrived example. Granted. I feel like it illustrates the point. However, over the years, people have questioned one aspect of it. The optimizer uses the foreign keys to figure out which tables can be eliminated from the query, making for a more efficient plan and making the query run faster. The pushback has always been, "Yeah, Grant, but nobody writes T-SQL where they include extra tables that they don't need." My initial response, after I stop laughing, is to point out any number of ORM tools. But, you know what, let's assume that's correct. No one would ever create a giant catch-all view that has all their JOINs in one place so they don't have…
Read More
11May 2015 by Grant Fritchey 13 Comments

Is Performance Better With LEFT JOIN or RIGHT JOIN?

T-SQL
I tend to write my queries using LEFT JOIN. Why? Because logically I see it in my head like this: Give me all the rows from this table and only those rows that match from the other table. But, wouldn't this logic work just as well: Give me only the rows in this table that match the rows from this other table where I'm selecting all of them. I know. If I worked on it some more I could make that a better sentence, but I'm pretty sure the logic is still sound. Only matching rows from one data set, all the rows from another data set. In short, RIGHT JOIN. I read recently that we ought to be making everything into a LEFT JOIN because it performs better. I suspect…
Read More
05May 2015 by Grant Fritchey 15 Comments

Effects of Persisted Columns on Performance

T-SQL
I live for questions. And my favorite questions are the ones where I'm not completely sure of the answer. Those are the questions that make me stop presenting in order to take a note so I can try to answer the question later, usually in a blog post. Guess where we are today? I was asked at SQL Bits in London about the direct impact of the PERSISTED operator on calculated columns, both inserts and selects. I didn't have a specific answer, so I wrote it down for later (and asked the, self-described, persisting Dane, to email me to remind me. He did, so I put together a few tests to try to answer his question. First, I created three tables: CREATE TABLE dbo.PersistTest ( PersistTestID INT IDENTITY(1,1) NOT NULL PRIMARY…
Read More
06Apr 2015 by Grant Fritchey 8 Comments

Constraints and SELECT Statements

Azure, SQL Server, T-SQL
I've posted previously about how a foreign key constraint can change how a SELECT query behaves. Logically that just makes sense. But other types of constraints don't affect execution plans do they? Yes. Let's take this constraint as an example: ALTER TABLE Sales.SalesOrderDetail WITH CHECK ADD  CONSTRAINT CK_SalesOrderDetail_UnitPrice CHECK  ((UnitPrice>=(0.00))) That will ensure that no values less than zero can slip in there. We can even validate it: INSERT Sales.SalesOrderDetail (SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate ) VALUES (60176, -- SalesOrderID - int N'XYZ123', -- CarrierTrackingNumber - nvarchar(25) 1, -- OrderQty - smallint 873, -- ProductID - int 1, -- SpecialOfferID - int -22, -- UnitPrice - money 0.0, -- UnitPriceDiscount - money NEWID(), -- rowguid - uniqueidentifier GETDATE() -- ModifiedDate - datetime ); Will give me…
Read More