21Jan 2011 by Grant Fritchey 2 Comments

Deprecation, Trace and Execution Plans

SQL Server, T-SQL
As I’m sure you know, Microsoft occasionally changes it’s mind. Or, it makes bad decisions and then rectifies them. Or, it even reinforces bad decisions. Regardless of the purpose, the means by which these changes are implemented when they involve taking things away is deprecation. Usually in SQL Server the deprecation process is supposed to be over three releases. So while seeing something on the deprecation list can be cause for concern if it’s something you like, you certainly don’t need to panic. I’ve finally had a chance to start working with Denali and the place that concerned me most was in the areas of deprecation. I want to make sure that when I suggest a particular approach, that the approach isn’t going to disappear in a version or two.Which…
Read More
18Jan 2011 by Grant Fritchey 19 Comments

DBA 101: Why is my log file full?

SQL Server
This question comes up constantly in different venues. I see it sometimes 2-3 times a day on SQL Server Central. I know it pops up at least once a week on Ask SSC. I’m sure I’ve seen it on Twitter #sqlhelp. “Why is my log file growing?” and “Why is my log file full” are heard regularly. Or, the variation, “I ran a full backup but the log file is still full/growing.” occasionally comes up. The people asking these questions are frequently, even appropriately, frantic. I’m writing this blog post for two reasons. First, to try to add a little bit of weight to what must surely be one of the most searched for phrases on the internet when it comes to SQL Server. Second, just to have a shorthand…
Read More
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