30Nov 2010 by Grant Fritchey 6 Comments

Regressions

SQL Server
[caption id="" align="alignleft" width="244" caption="Hannah Dustin, Upset about Regression"][/caption] One of the most important take-aways from David Dewitt's presentation at the PASS Summit was the level of fear within the Query Processing team at Microsoft caused by regressions. If you missed Dr. Dewitt's presentation, I tried to capture as much of it as I could here, and it will be available within the DVDs from PASS. Regression is when something moves backwards to a less perfect state. When talking about the optimizer in SQL Server, a regression is when you see a query that used to run fast in SQL Server 2000 or 2005 and suddenly after upgrading to 2005 or 2008, the exact same query now generates a different execution plan and runs slowly. Now do you know why…
Read More
24Nov 2010 by Grant Fritchey 4 Comments

FreeCon

Misc
I got my FreeCon. What's more, I did it in public and didn't get in trouble. FreeCon is the brain child of Brent Ozar (blog|twitter). Basically Brent gathered together a few bloggers & writers from the SQL Server community, some extremely well known, like Tom LaRock (blog|twitter), some in the middle, like me, and others that are clearly up & coming like David Stein (blog|twitter).  He jammed us all in a room and made us talk to one another. OK, that's a lie. He invited us out to this lovely little poetry space in Seattle the day before the PASS Summit was due to start, where we imbibed good food & coffee and had the opportunity to share a lot of great information with each other. The basic concept was…
Read More
22Nov 2010 by Grant Fritchey 48 Comments

Do Foreign Key Constraints Help Performance?

SQL Server, T-SQL
Most people are very aware of the fact that having a foreign key constraint in place on your tables adds overhead to inserts and deletes. And many people believe that there is no benefit to foreign keys beyond referential integrity (which, the application can handle perfectly well, right?). But is that an accurate statement? Here's the basis for our investigation, a query in AdventureWorks2008R2: SELECT p.LastName + ', ' + p.FirstName AS 'PersonName' FROM Person.Address AS a JOIN Person.BusinessEntityAddress AS bea ON a.AddressID = bea.AddressID JOIN Person.BusinessEntity AS be ON bea.BusinessEntityID = be.BusinessEntityID JOIN Person.Person AS p ON be.BusinessEntityID = p.BusinessEntityID; This query results in the following execution plan: I know that is an ugly query and an ugly query plan, but bear with me for a moment. Do you…
Read More
19Nov 2010 by Grant Fritchey 5 Comments

Un-SQL Friday: Branding?

Misc
Following in Midnight DBA's more interesting half new blog fest topic. My branding? I don't have a clue. There, I said it. Up until very, very recently, I've just been doing stuff. I haven't really even tried to think about what the plan was. Write a book? Sure, sounds like a cool challenge. Write another one? Well, the last one didn't kill me, OK. Start a blog. Jump on Twitter. Pick a Name. Could I pretty please present at PASS? I've been very focused on building my name, but I honestly never thought about my brand. It's just not something that occurred to me. Yes, I am that slow. Now recently, stuff has been happening and I'm suddenly concerned about it. I too went through FreeCon with Brent Ozar (blog|twitter).…
Read More
18Nov 2010 by Grant Fritchey 18 Comments

Reason for Early Termination of Statement

SQL Server, T-SQL
Wouldn't you like to know why the optimizer stopped tuning the execution plan you're looking at? It's actually possible and simple to get this information. I talked about this a little more than a year ago, but I left out some information that might be useful. Let's take a very simple query: SELECT * FROM Person.Address AS a; This generates a simple execution plan, in fact a trivial plan: I know that this is a trivial plan one of two ways. The hard way is to look at the XML (BTW, this is the type of thing you can't see it in STATISTICS PROFILE, which I understand a lot of people are using). Right at the top is the SELECT element. You can see the value for StatementOptmLevel property is…
Read More
16Nov 2010 by Grant Fritchey No Comments

SQL Server Team-Based Development

Misc
The new book is available in a free download from Red Gate. I had a lot of fun working on the three chapters that I did for this book. The topics I received are near & dear to me because of all the time I've spent working on getting databases into source control and automating (as much as possible) the deployment of databases out of source control. Everything I go over in the book might not be 100% applicable to your situation, but I'm pretty sure almost anyone involved in database development and deployment can find some useful information there. For those who are interested, I don't just cover Red Gate tools in the book either. There's quite a bit of time spent describing how to automate deployments using Visual…
Read More
15Nov 2010 by Grant Fritchey 4 Comments

Switching Off Parameter Sniffing

Uncategorized
Or, another way to put it, in most cases, shooting yourself in the foot. I was not aware that the cumulative update for SQL Server 2008 back in June included a switch that allows you to turn parameter sniffing off within SQL Server. Thanks to Kendra Little (blog|twitter) for letting me know about it (although she let me know by "stumping the chump" during my lightening talk at the Summit, thanks Kendra!). When I first saw the switch, I thought about the places where switching off parameter sniffing could be helpful. But, as I thought about it, the more I realized that this was an extremely dangerous switch. Why? Because, most people only ever hear about parameter sniffing when they run into a problem. Someone says "Parameter sniffing" and you see people cringe.…
Read More
15Nov 2010 by Grant Fritchey 2 Comments

ScaryDBA Blog Reboot

Misc
After all the conversations at FreeCon last week (more on that later, when I've assimilated it better), I finally decided to make some changes. The list of things I'm hitting is extremely long, but I'm going to be going through them a few at a time. First, and easiest, was getting moved off the WordPress domain to my own, hosted, domain. You're seeing the results. This is the new ScaryDBA blog. I suspect the theme may change once or twice over the next week, but basically, all the functionality that has been added, better feeds, conversation tracking, other stuff, will remain. Thank you for visiting in the past. I intend to provide you with plent of reasons to visit in the future.
Read More
12Nov 2010 by Grant Fritchey 2 Comments

The New Path To MCM

SQL Server
Microsoft has announced changes to the MCM program. This page shows all the ways that you can become an MCM without having to spend a month at Microsoft. I'm interested in this for two reasons. First, I'd love to have the opportunity to try to become an MCM. Making it easier to make the attempt, hopefully without dumbing down the curriculum, is a great idea. Second, scroll down to the books section. That's right, my book is one of the suggested books for learning enough so that you can pass the MCM test. I'm gob-smacked and honored and jazzed and I'll stop gushing now.
Read More
11Nov 2010 by Grant Fritchey 6 Comments

PASS Summit 2010, Day 3 Key Note

PASS
Today is Dr. Dewitt. The ballroom, where the keynotes are held, is filled with extra chairs. The Summit organizers expect extra attendance today, and well they should. Dr. Dewitt was amazing last year. I suspect this year will be more of the same. Rick Heiges is introducing the day (waiting for Dr. Dewitt). Lynda Rab is leaving the board. Sad. I started volunteering for the PASS organization working for Lynda. She's great. The new board members are Douglas McDowell, Andy Warren and Allen Kinsel. The spring SQL Rally event was announced. I'll be presenting a full day session on query performance, Query Performance Tuning, Start to Finish. Look for (a lot) more blog posts on this. The Summit next year has been moved to mid-October. WHOOP! This is great because…
Read More