03Oct 2016 by Grant Fritchey 1 Comment

Correlated Datetime Columns

SQL Server, SQL Server 2016, T-SQL
SQL Server is a deep and complex product. There's always more to learn. For example, I had never heard of Correlated Datetime Columns. They were evidently introduced as a database option in SQL Server 2005 to help support data warehousing style queries (frequently using dates and times as join criteria or filter criteria). You can read up on the concept here from this older article from 2008 on MSDN. However, doing a search online I didn't find much else explaining how this  stuff worked (one article here, that didn't break this down in a way I could easily understand). Time for me to get my learn on. The concept is simple, turning this on for your database means that dates which have a relationship, the example from MSDN uses OrderDate and…
Read More
30Sep 2016 by Grant Fritchey 2 Comments

Azure Data Platform Resources

Azure
A few months ago I created a GitHub repository for the purpose of building and maintaining a list of resources for Azure Data Platform training. You can see it here. My goal in putting this into GitHub instead of just running it on my blog is to make it a community resource. I want all of you to maintain it. If you're teaching a class (one hour or one week, I don't care), I'd like you to add yourself to the instructors list. If you have a blog where you post Azure content, please, add your blog. Are you a PowerBI monster? Get on the list. Please, help me create and grow this list so that people have a central, public, resource for this information. More and more of you are…
Read More
29Sep 2016 by Grant Fritchey No Comments

Volunteering With PASS

PASS
I was just asked how many times I've been to the PASS Summit. This year will be my 12th consecutive one. That made me start thinking. At my very first Summit, I met a couple of volunteers for PASS (Allen Kinsel was one of them, I'll blame him forever). They were having so much fun that I decided to volunteer. I've been volunteering now for eleven years. I couldn't stop. Here's what I've done over the years at PASS: Book Review Committee (my first PASS "job") Editorial Committee (I wrote stuff) Editor of the SQL Standard (I had other people write stuff) First-timer Mentor (I wasn't very good at this one) First-timer Event... Chair? (I got up in front of the room, this one might not count) Founder of SQL Kilt Day (and…
Read More
27Sep 2016 by Grant Fritchey 1 Comment

Networking and the PASS Summit

PASS
PASS Summit 2016 is fast approaching. If you're going, time to start making plans. If you're not going, sell your boss on the idea and get registered. It's only the largest Microsoft Data Platform event on the planet. There are over 200 sessions given by some of the most knowledgeable people you'll ever get the chance to learn from. The schedule is posted, just look it over. However, I want to drill down on another aspect of the event that it's way too easy to miss out on, networking. Lots of people miss this aspect of events like the PASS Summit. I know I used to. I went to all kinds of IT events over the years, but all I ever did was attend the sessions. I didn't spend any time…
Read More
26Sep 2016 by Grant Fritchey 2 Comments

Query Store and What Happened Earlier On Your Server

SQL Server 2016, T-SQL
Here's a great question I received: We had a problem at 9:02 AM this morning, but we're not sure what happened. Can Query Store tell us? My first blush response is, no. Not really. Query Store keeps aggregate performance metrics about the queries on the database where Query Store is enabled. Aggregation means that we can't tell you what happened with an individual call at 9:02 AM... Well, not entirely true. The aggregations that Query Store keeps are actually broken up into intervals that you can control. The default interval is 60 minutes. This means that the information stored in the DMV covers sets of intervals. This means that if, at 9:02AM, you had a query, or queries, that ran considerably longer than normal, you may be able to take a look…
Read More
19Sep 2016 by Grant Fritchey 9 Comments

Query Store and Plan Forcing: What Do You Use It For

SQL Server 2016, T-SQL
If you're working with Azure SQL Database or you've moved into SQL Server 2016, one of the biggest new tools is the Query Store. It provides a mechanism of capturing query performance over time and persisting it with the database. You also get the execution plans for those queries. Finally, you can choose to have the Query Store override execution plan selection by use of Plan Forcing. I've written about Query Store a few times: Query Store, Force Plan and "Better" Plans Query Store, Force Plan and Dropped Objects Precedence Goes to Query Store or Plan Guide Query Store, Forced Plans and New Plans Query Store and Optimize For Ad Hoc Query Store and Recompile Finding Your Query in Query Store Removing All Query Store Data Monitor Query Performance OK,…
Read More
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
07Sep 2016 by Grant Fritchey 3 Comments

Kilt Day! PASS Summit 2016, Thursday

PASS
Thursday at the PASS Summit is Kilt Day. This means you should wear a kilt. Whether you do it in support of Women in Technology, or you just like to swan about in a kilt, this is the day to sport your kilt. Whether you have a fully traditional tartan, you believe in utility, comfort, or you're prepared for the zombiepocalypse, there's a kilt for you. All are welcome. All are encouraged.
Read More
06Sep 2016 by Grant Fritchey 1 Comment

Query Store, Force Plan and “Better” Plans

SQL Server 2016, T-SQL
I am endlessly fascinated by how the Query Store works. I love teaching it at every opportunity too. Plus, almost every time I teach it, I get a new question about the behavior that makes me delve into the Query Store just a little bit more, enabling me to better understand how it works. I received just such a question at SQLSaturday Norway: If you are forcing a plan, and the physical structure changes such that a "better" plan is possible, what happens with plan forcing? Let's answer a different question first. What happens when the plan gets invalidated, when the index being used gets dropped or some other structural change occurs so that the plan is no longer valid? I answered that question in this blog post. The plan…
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