14Feb 2011 by Grant Fritchey 6 Comments

DBA 101: Why Don’t People Run Backups

SQL Server, T-SQL
It happened multiple times this week. It happens multiple times every week. Some poor soul is posting on a message board, usually with the heading “URGENT” (why that one word so frequently, I just don’t know), that they deleted production data/dropped a production table/updated production data/dropped a database/received a data corruption error/whatever. Now, they need to get the data back. “URGENT, What do I do now?” And so you ask, as you should, what kind of backups do you have? Over and over the answer is: “Backups? What’s a backup” or “Oh, the system guys backup of the MDF files every night” or “We don’t really need those” or “We don’t have room to back up our databases” or some other excuse that simple comes down to, we didn’t set…
Read More
08Feb 2011 by Grant Fritchey 8 Comments

TSQL Tuesday #15–Automation in SQL Server

SQL Server, T-SQL
Automation is the separation point for the professional DBA from the amateur. That makes this a very important topic. Thanks to Pat Wright (blog|twitter) for coming up with something great to write about and hosting the event. I recently wrote an article for the SQL Spackle series over on SQL Server Central on how to “Set Up and Schedule a Server Side Trace.” That covers well what to do to set up a trace on your system so that you can automate it. But I think I left out a few details that I think are worth pointing out here. The basics on creating the Server Side Trace using Profiler and scheduling it using SQL Agent are well covered in the article. The extra areas I want to address are…
Read More
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