13Dec 2010 by Grant Fritchey 133 Comments

Life/Work Balance

SQL Server, Tools
Technology, especially information technology, is the greatest thing to ever happen to mankind, freeing us from toil and drudgery. Technology, especially information technology, is a pernicious evil taking over our lives forcing us to work harder and longer. Depending on the time of day, the day of the week, my mood, my wife’s mood, or the direction the wind is blowing, either of these statements could be true. The fact is, I love technology and I do have to wrestle with keeping it from taking over my life, but only because I have so much fun with the toys that technology brings. You want to know how much I love toys, ask me about my Droid sometime. Pull up a chair. We’re going to be here a while. The trick…
Read More
06Dec 2010 by Grant Fritchey 12 Comments

Review: Idera SQL Doctor

SQL Server, T-SQL, Tools
Recently, a co-worker practically slammed me up against the wall, exclaiming “You have to check out this new tool, right now!” The piece of software he was so excited about was Idera’s SQL Doctor. Based on this assaultrecommendation, I decided to take a little time & look the software over. SQL Doctor, as the name implies, is a diagnostic tool. It runs a set of best practice rules against your server, your databases and your code. As the rules are executed, your system’s compliance with these best practices is evaluated and an interactive report is generated. With the report you can drill down on various aspects of your system to see where you may have gone wrong. All that sounds very clinical, just laid out like that. But the fact…
Read More
02Dec 2010 by Grant Fritchey No Comments

SQL Spackle

SQL Server, T-SQL, Tools
I previously mentioned how SQL Server Central was listing ideas for articles, primarily for short, quick, pointed articles that they were terming SQL Spackle. Spackle is a term in the US that represents the filler you put into dry-wall to smooth it out or fix small holes, so SQL Spackle is meant to fix small gaps in knowledge or documentation. My first SQL Spackle article was published today. I kind of forgot it was coming. I wrote a quick and pointed outline of how to set up and schedule a server-side trace. This is something I've always advocated, and now I can point to a bit of spackle to fill in the gaps for those who don't know how to get it done.
Read More
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
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
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
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
18Oct 2010 by Grant Fritchey 13 Comments

Powershell is Really Easy… If you know what you’re doing

PowerShell, SQL Server
Sigh... I just spent almost a full work day trying to come up with, what turned out to be a single line script. The requirements were simple. After patching of servers is completed, and everything is rebooted, sometimes, not often, but sometimes, SQL Server Agent doesn't restart correctly. I was asked to put together a script that will check a list of servers to see if Agent is running. Any PowerShell guru's reading this are either laughing or crying, already. So, I started exploring how to connect to the server using using some type of PS-Drive, but I quickly figured out that I'd have to loop through the servers that way. Plus, I don't think there's a way to check Agent status through the SQL drive. So that went out…
Read More
04Oct 2010 by Grant Fritchey 1 Comment

How to Tell if Execution Plans are Reused

SQL Server, T-SQL
I try to watch the search phrases that point people to the blog because sometimes, you get a sense of what problems people are running into. The latest question or phrase I've seen a lot lately is along the lines of "how do you know if an execution plan is being reused." Since compiling an execution plan can be an extremely expensive operation, it's worth your time to understand how well a given plan is getting reused. If you've seen me present, I'll frequently talk about the application that had a query with an 86 table join. Recompiles on that thing were frequent and extremely costly. The only good news was, they were recompiles. If we weren't getting plan reuse it would have been an even worse system than it was. There are…
Read More
27Sep 2010 by Grant Fritchey 3 Comments

Parameter Name Size And Performance

SQL Server, T-SQL
I saw an odd statement the other day, "The size of the name of the parameter does not affect performance." My first thought was, "Well, duh!" But then, I had one of those, "Ah, but are you sure" thoughts. And you know what, I wasn't sure. If size of the parameter name did affect performance, I figured, the one sure place where that would be evident is in the size of the execution plan. Right? I mean, if there was an impact on memory, and hence on performance, that's probably where you'd see evidence of it. I wrote two queries: DECLARE @ThisIsAVeryVeryLongParameterNameThatIsTrulyRidiculousButItIllustratesThePointThatParameterLengthDoesNotAffectPerformance int SET @ThisIsAVeryVeryLongParameterNameThatIsTrulyRidiculousButItIllustratesThePointThatParameterLengthDoesNotAffectPerformance = 572 SELECT soh.SalesOrderID ,sod.SalesOrderDetailID FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID WHERE soh.SalesOrderID = @ThisIsAVeryVeryLongParameterNameThatIsTrulyRidiculousButItIllustratesThePointThatParameterLengthDoesNotAffectPerformance DECLARE @v int SET…
Read More