14Aug 2014 by Grant Fritchey No Comments

A Full Day of Query Tuning

SQL Server, T-SQL
I'm excited to able to say that I've been given the opportunity to put on a full day workshop at SQL Connections on Friday, September 19th, 2014. The title is "Query Performance Tuning in SQL Server 2014", but I assure you we're going to cover things that are applicable if you're still working on SQL Server 2005. We'll start the day covering the different mechanisms you have to capture query metrics. We'll go over dynamic management objects and extended events that are incredibly important to you in understanding which queries you need to tune. We'll get an introduction into how the optimizer works and the importance that statistics, indexes and constraints play in helping the optimizer make the choices it makes. I promise, execution plans will be covered throughout the…
Read More
06Aug 2014 by Grant Fritchey 25 Comments

Say Thank You

Misc, Professional Development
I was approached by a man at SQL Bits who only wanted to thank me for blogging. He said, "I know you probably never hear from anyone, but you should. Thanks for what you do." I'm not mentioning his name because he promised me he'd write an editorial for publication, so I'm going to wait on him to get that done so he gets full credit then, not on my silly blog post. But, the idea stuck with me. I realized, that I don't do it. Oh yeah, I have an "Interesting Reading" set of links that I post to Twitter, Facebook and Linkedin. Sharing is "thanking" in our modern vernacular, right? No, of course not. It's not the same. I really do appreciate the help that I get on…
Read More
01Aug 2014 by Grant Fritchey 6 Comments

Speaker of the Month: August 2014

Professional Development
Speaker of the Month is now officially one year old. I went back and reread my first post. The goals were for a relentlessly positive experience. Reading back through the twelve posts, I feel like I hit that mark pretty well. Yes, I've always pointed out places where improvements can be made, but I think I've done it in a constructive and positive manner. Plus, I'm picking your session (if you get picked), as the best session that I saw that month, which is pretty darned positive in and of itself. I also promised it would be random and arbitrary. Mission accomplished. I've considered wrapping this up. I did it for a year, just to see what the response would be. I didn't have much more of a goal in…
Read More
23Jul 2014 by Grant Fritchey 6 Comments

Challenge Accepted

PASS
There seemed to be some question whether my comfort level with my own masculinity would prevent me from wearing these: Oh please! Couldn't we be a little more challenging? Anyway, here's the deal, you donate to a good cause, Doctors Without Borders. We hit 10K and I'll sport those lovely rainbow whatever-they-are at the PASS Summit 2014. Sound good? I'll go one better. You double the goal, make it hit 20K, and I'll present my session while wearing the rainbow whosimawatchits. BOOM!
Read More
21Jul 2014 by Grant Fritchey 5 Comments

Victims of Success

PASS, Professional Development
I took part in the PASS Summit 2014 selection committee this year because I was really curious about seeing how the sausage gets made. I've seen how actual sausage gets made and I still eat sausage.  Despite a few hiccups and communication issues, internal and external, I think the selection process for the Summit went really well this year. But, there was still some controversy. Being a naturally pushy person, I got involved in the controversy, for good or ill, and subsequently have had conversations with many people about the selection process (which, reiterating, I think went extremely well overall). But, the one thing that kept coming up over and over was a simple question: How come I/PersonX didn't get picked? The easy answer is because you/PersonX had a horrible abstract. But you…
Read More
15Jul 2014 by Grant Fritchey 2 Comments

Execution Plan Details

PASS, SQL Server, T-SQL
I wouldn't say it's common knowledge that you should look at execution plans when tuning queries, but it's not exactly uncommon knowledge either. But, people tend to get focused on just looking at the graphical part of the plan and there's just not enough information there. Let's take a look at a query: SELECT pc.Name, ps.Name, v.Name, pr.ReviewerName, p.Name, v.ModifiedDate, p.Color FROM Production.Product AS p LEFT JOIN Production.ProductReview AS pr ON pr.ProductID = p.ProductID JOIN Production.ProductSubcategory AS ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID JOIN Production.ProductCategory AS pc ON pc.ProductCategoryID = ps.ProductCategoryID JOIN Purchasing.ProductVendor AS pv JOIN Purchasing.Vendor AS v ON v.BusinessEntityID = pv.BusinessEntityID ON pv.ProductID = p.ProductID WHERE v.ModifiedDate = '2006-02-17 00:00:00.000' AND p.Color LIKE 'Y%'; This generates an execution plan that looks like this: Neither v.ModifiedDate nor p.Color have indexes.…
Read More
11Jul 2014 by Grant Fritchey No Comments

Speaker of the Month, July 2014

Professional Development
Another month another bunch of great presentations. I almost don't want to do this any more. It's hard. I sit through a presentation and I think, "Well, here's the winner this month." Then I go to another presentation and I think, "Well, fudge, now one of these people loses." Then I go to a third and I'm simply blown away. And now I have to pick. Well, it's hard. So let me do this, I'm going to declare two winners this month, but only review one of them. Hey, my blog, my rules. First, I want to award speaker of the month for July 2014 to Wayne Sheffield(b|t) and his presentation Table Variables and Temp Tables that I saw at SQL Saturday 294. What's my measure? That I learned stuff…
Read More
08Jul 2014 by Grant Fritchey 5 Comments

Worst Day of a DBAs Life

Professional Development, Redgate Software
Red Gate Software is running a campaign around coping with the worst day of a DBAs life. We've been posting some really fun stories with, I hope, a kernel of useful information inside each. Chances are, if your DBA career has been like mine, your worst days don't involve explosions and car chases. But now they're asking for people to write up stories, what was the worst day in your life as a DBA. I know, I know, first world problems, right? Regardless, I have yet to put a company out of business or kill anyone with any errors I've made, although I've worked at places where either was possible. But the one day that just stands out, well it started about three weeks ahead of the bad day. I…
Read More
03Jul 2014 by Grant Fritchey 9 Comments

Reflections on the 2014 PASS Summit Selection Process

PASS, Professional Development
Oh we are a bunch of high school kids at heart. Maybe high school never ends (and there's a nightmare, god I hated high school). But, there's been drama about the 2014 PASS Summit sessions and the Selection Committee's work. I was on the committee. I worked as a part of the team responsible for rating sessions for the Azure track (said track is gone, more on that later). As self-serving a statement as this is, I think we did a good job. Further, I think the process worked. You can read the official explanation of the process here. Amy did great work and deserves your thanks. All the volunteers who reviewed over 900 submissions from more than 300 people, ON THEIR OWN TIME, FOR FREE, also deserve your thanks. The vitriol directed at…
Read More
02Jul 2014 by Grant Fritchey 5 Comments

Statistics and Natively Compiled Procedures

T-SQL
Statistics are one of the single most important driving factors for the behavior of the query optimizer. The cardinality estimates stored within the statistics drive costing and costing drives the decision making of the optimizer. So, how does this work with the new SQL Server 2014 natively compiled procedures? Differently. In-memory tables do not maintain their statistics automatically. Further, you can't run DBCC SHOW_STATISTICS to get information about those statistics, so you can't tell if they're out of date or not or what the distribution of the data is within them. So, if I create some memory optimized tables, skip loading any data into them and then run this standard query: SELECT a.AddressLine1, a.City, a.PostalCode, sp.Name AS StateProvinceName, cr.Name AS CountryName FROM dbo.Address AS a JOIN dbo.StateProvince AS sp ON sp.StateProvinceID =…
Read More