12Sep 2016 by Grant Fritchey 6 Comments

The Clustered Index Is Vital To Your Database Design

Azure, SQL Server, SQL Server 2016
This is post #9 supporting  Tim Ford’s (b|t) initiative on #iwanttohelp, #entrylevel. Read about it here. You get one clustered index per table. That bears repeating, you get one clustered index per table. Choosing a clustered index is an extremely important and fundamental aspect of all your SQL Server work. The one clustered index that you get determines how the data in your table is stored. Because the clustered index determines how your data is stored, it also determines how your data is retrieved. Much of SQL Server is engineered around the clustered index because it is such a foundational object for the rest of all behavior. Without a clustered index, the data in your table is stored in what is called a heap. It is essentially a pile, a heap, of data,…
Read More
29Aug 2016 by Grant Fritchey 7 Comments

Azure SQL Data Warehouse Execution Plans

Azure
Azure SQL Data Warehouse can sometimes feel like it's completely different from SQL Server, but under the covers, it is still (mostly) SQL Server and it does have execution plans. Let's take a look at one. I've created a new SQL Data Warehouse using the sample database available on the portal, AdventureWorksDW. Here's a query against that database: SELECT dd.FullDateAlternateKey AS OrderDate, dc.LastName, SUM(fis.SalesAmount) AS SumSalesAmount FROM dbo.FactInternetSales AS fis JOIN dbo.DimDate AS dd ON fis.OrderDateKey = dd.DateKey JOIN dbo.DimCustomer AS dc ON dc.CustomerKey = fis.CustomerKey GROUP BY dd.FullDateAlternateKey, dc.LastName HAVING SUM(fis.SalesAmount) > 5000.0 ORDER BY OrderDate DESC; If I attempt to capture an execution plan using the SQL Server Management Studio GUI, nothing happens. If I try to use T-SQL commands, I get an error that those commands are…
Read More
15Aug 2016 by Grant Fritchey 5 Comments

Query Store, Force Plan and Dropped Objects

Azure, SQL Server 2016
I love the Query Store. Seriously. It’s a huge leap forward in the capabilities of Azure SQL Database and SQL Server in support of performance monitoring and query optimization. One of my favorite aspects of the Query Store is the ability to force plans. Frankly though, it’s also the scariest part of the Query Store. I do believe that plan forcing will be one of the most ill-used functions in SQL Server since the multi-statement table-valued user-defined function (don’t get me started). However, unlike the UDF, this ill-use will be because of poor understanding on the part of the user, not a fundamental design issue. No, plan forcing and the Query Store are very well constructed. Let me give you an example of just how well constructed they are. Let’s…
Read More
25Jul 2016 by Grant Fritchey 2 Comments

Monitor Query Performance

Azure, SQL Server, SQL Server 2016
Blog post #7 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel. Read about it here. Sooner or later when you're working with SQL Server, someone is going to complain that the server is slow. I already pointed out the first place you should look when this comes up. But what if they're more precise? What if, you know, or at least suspect, you have a problem with a query? How do you get information about how queries are behaving in SQL Server? Choices For Query Metrics It's not enough to know that you have a slow query or queries. You need to know exactly how slow they are. You must measure. You need to know how long they take to run and you need to know how many resources are…
Read More
18Jul 2016 by Grant Fritchey 14 Comments

Common Table Expression, Just a Name

SQL Server, SQL Server 2016, T-SQL
The Common Table Expression (CTE) is a great tool in T-SQL. The CTE provides a mechanism to define a query that can be easily reused over and over within another query. The CTE also provides a mechanism for recursion which, though a little dangerous and overused, is extremely handy for certain types of queries. However, the CTE has a very unfortunate name. Over and over I've had to walk people back from the "Table" in Common Table Expression. The CTE is just a query. It's not a table. It's not providing a temporary storage space like a table variable or a temporary table. It's just a query. Think of it more like a temporary view, which is also just a query. Every time I explain this, there are people who don't…
Read More
11Jul 2016 by Grant Fritchey 26 Comments

Same Query, Different Servers, Different Performance. Now What?

SQL Server, SQL Server 2016, T-SQL
Based on the number of times I see this question on forums, it must be occurring all the time. You have two different servers that, as far as you know, are identical in terms of their options and setup (although not necessarily in terms of power, think a test or pre-production system versus production). On these servers you have a database on each that, as far as you know, is the same as the other in terms of options, objects, maybe even data (although, this does mean that you have unmasked production information in your QA environment, which potentially means you're going to jail, might want to address this, especially now that I've told you about it, mens rea, you're welcome). On each database you run, as far as you know, the exact same query (whether…
Read More
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
13Jun 2016 by Grant Fritchey 9 Comments

Choosing the Right SQL Server Edition

SQL Server 2016
Post #6 of #entrylevel #iwanttohelp in support of Tim Ford's (b|t) beginner's initiative. If you're just getting started with SQL Server, the choices you have in front of you are legion. Which drive do you install your instance on? Which drives hold the databases? How many files do you need for a database? What do the tables look like? Which column or columns should be the primary key? Clustered index? Stored procedures? In-Memory? MAXDOP? Et, as they say, cetera. Ad, as they also say, nauseum. Before any of that though, you need to pick the correct type of SQL Server to install. That's right, just saying "SQL Server" is not enough. You must pick between: SQL Server Developer's Edition SQL Server Express Azure SQL Database SQL Server Standard SQL Server…
Read More
01Jun 2016 by Grant Fritchey 1 Comment

Independent Azure Data Platform Instructors

Azure
The Azure Data Platform is taking off. I'm seeing more and more interest on the forums, at conferences and in my personal interactions. I've been teaching the data platform for six years. Almost as soon as it was available, I started working with it, putting up blog posts and setting up sessions. I've had stuff in production on the platform for almost that long too. I'm an advocate and, I hope, an independent voice on the topic. By independent in this case, I mean non-Microsoft. Don't get me wrong, most of the people I learn from work for Microsoft. They are excellent instructors and more knowledgeable on the topic than I'll ever be. I'm not questioning the ability of Microsoft people to deliver the very best Data Platform content. I…
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