10Oct 2016 by Grant Fritchey 2 Comments

Statistics Are Vital For Query Performance

SQL Server, SQL Server 2016
This is post #10 supporting  Tim Ford’s (b|t) initiative on #iwanttohelp, #entrylevel. Read about it here. When you send a query to your SQL Server database (and this applies to Azure SQL Database, APS, and Azure SQL Data Warehouse), that query is going to go through a process known as query optimization. The query optimization process figures out if you can use indexes to assist the query, whether or not it can seek against those indexes or has to use a scan, and a whole bunch of other stuff. The primary driving force in making these decisions are the statistics available on the indexes and on your tables. What Are Statistics Statistics are a mathematical construct to represent the data in your tables. Instead of scanning through the data each and every…
Read More
05Oct 2016 by Grant Fritchey 20 Comments

Where Do We Go To Share?

Misc
No one reads blogs any more. Twitter is dying. Facebook is broken. LinkedIn? Please. G+. Is that even on any more? Where do we go to share? I'm seeing it here on this blog. Traffic is down. Not just day-to-day traffic, but the search hits. That could just be that I'm producing crap content or stuff that no one is interested in. However, Twitter isn't growing like it once was and there are many reports that it's shrinking. Facebook is running into problems. So... Where the heck are people going? How do we continue to share without a relatively common communication tool? I know there's some push for Yammer. However, lots of people hate it. Slack and slack channels get a little traction, but to a degree this is just another type of…
Read More
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