Are all of your servers set up to clean out their backup history periodically?

Are you sure?

If you looked back at the monitor with “WTF?” written all over your face, I’ll bring you up to speed.  Here’s the problem: SQL Server stores information about completed backups in the MSDB database.  In older versions of SQL Server, that database isn’t indexed very well, and it can become a performance bottleneck during backups and restores.  I’ve written about one particular case where I cut the company’s backup times by 2/3 just by cleaning out their backup history.

To make sure your servers are keeping their MSDB clean, you can use SQL Server Management Studio 2008′s new multi-server-query-execute feature to quickly query all of your servers.  Go read my article on how to query multiple servers at once in SSMS, and then run this query against your SQL Servers:

SELECT TOP 1 backup_start_date
  FROM msdb.dbo.backupset WITH (NOLOCK)
  ORDER BY backup_set_id ASC

The query runs almost instantaneously since it’s sorted by the primary key of the table.  The results of the query are shown here:

Consolidated MSDB Backup History

In that screenshot, I can see right away that three of the servers in my lab have problems.  They don’t have cleanup tasks properly configured.

If you see results like this in your own environment, drop what you’re doing and go add MSDB cleanup tasks to your maintenance plans or add sp_delete_backuphistory to your T-SQL maintenance scripts.  The first time it runs, you may run into some horribly long runtimes and locks, unfortunately.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

Want to play around with the StackOverflow database export?  Here’s how to import the XML files into SQL Server, and some notes about the tables and data schema.

Script to Import StackOverflow XML to SQL Server

This T-SQL script will create six stored procedures:

• usp_ETL_Load_Badges
• usp_ETL_Load_Comments
• usp_ETL_Load_Posts
• usp_ETL_Load_Users
• usp_ETL_Load_Votes
• usp_ETL_Load_PostsTags (which isn’t one of the StackOverflow tables – more on that in a minute)

The XML import code is from an excellent XML tutorial by Denny Cherry.  The scripts create a table (named Badges, Comments, Posts, Users, Votes) for each XML file.  The schema matches the XML file with one exception – I added an identity field to the Badges table.  The rest already had Id fields.  The tables don’t have any indexes to speed querying. I would highly recommend that you not change the schema of any of these tables, because I’ll be giving out more scripts over the coming days and weeks that rely on the base tables.  If you want to add more data, add additional tables.  Plus this will keep your importing clean anyway – you can dump and reload the StackOverflow data repeatedly as long as you keep that data separate.

After importing, the database is about 2gb of data.  Be aware that depending on your database’s recovery model and how you run these stored procs, your log file may be 2gb as well. None of the sentences in this paragraph blend together well, which bothers me but not quite enough to stop publishing the blog entry. Anyway, on we go.

If the table already exists when the stored proc runs, the table contents are deleted using the TRUNCATE TABLE command, which requires hefty permissions.  If you don’t have admin rights on the box, substitute DELETE for the five TRUNCATE TABLE commands.  Using DELETE will take significantly longer to run.  For reference, with TRUNCATE TABLE, the stored procs take around 10 minutes on my faster machines, and around half an hour on my slower virtual machines.

These stored procs only work for the new database dump released on Monday morning, not the one released last week.  If you get invalid XML errors while importing, you’ve got the older database dump.  Go get the fresh hotness.

Now for some schema notes, and I’m going to go out of alphabetical order because everything links back to the Users table.  I’m only going to cover the fields that aren’t immediately obvious:

Users Table

• Id – primary key, identity field from the original StackOverflow database.  Id 1 is “Community”, which is a special user that denotes community ownership, like wiki questions and answers.
• LastAccessDate – this is useful because it tells you when the data export was last updated.  If you’re doing queries for things like the last 30 days, check the most recent date here.
• Age – the user enters this manually, so it’s not terribly reliable as I discovered earlier.
• AboutMe – I’m using an nvarchar(max) field here, but you can go with a shorter field like nvarchar(2000).
• UpVotes and DownVotes – the number of votes this user has cast.

Posts Table

In StackOverflow, questions and answers are both considered posts.  If a record has a null ParentId field, then it’s a question.  Otherwise, it’s an answer, and to find the matching question, join the ParentId field up to Posts.Id.

• Id – primary key, identity field from the original StackOverflow database.
• Title – the title of the question.  Answer titles will be null.
• OwnerUserId – joins back to Users.Id.  If OwnerUserId = 1, that’s the community user, meaning it’s a wiki question or answer.
• AcceptedAnswerId – for questions, this points to the Post.Id of the officially accepted answer.  This isn’t necessarily the highest-voted answer, but the one the questioner accepted.
• Tags – okay, time to blow out of the bullet points for a second.

StackOverflow limits you to five tags per question (answers aren’t tagged), and all five are stored in this field.  For example, for question 305223, the Tags field is “<offtopic><fun><not-programming-related><jon-skeet>”.  It’s up to you to normalize these.  If you’d like to normalize them out into a child table, check out the usp_ETL_Load_PostsTags stored proc, which creates a PostsTags table with PostId and Tag fields.  Each Posts record (questions only) will then have several child records in PostsTags.

Next, check the contents of the Tag field carefully.  StackOverflow allows periods in the tag, like the .NET tag and ASP.NET tag.  However, in the database, these are stored as “aspûnet”.  Just something to be aware of.

Comments Table

• Id – primary key, identity field from the original StackOverflow database.
• PostId – the post parent for this comment.  Joins to the Post.Id field.
• UserId – who left the comment.  Joins to the User.Id field.

Badges Table

• Id – an identity field for a primary key.  This number is meaningless – I just added it for some referential integrity.
• UserId – joins back to Users.Id to show whose badge it is.
• Name – the name of the Badge, like Teacher or Nice Answer.
• CreationDate – when the user achieved the badge.

Votes Table

This stores the votes cast on posts, but the key field is VoteTypeId.  The VoteType table wasn’t included in the export, so this table isn’t too useful yet, but if the guys give me the OK I’ll post the contents of that table here.  The Votes table doesn’t include *who* cast the votes, and I’ve got my hands full analyzing the other tables anyway, so I haven’t been interested in the VoteTypes yet.

All of the Id fields except for Badges.Id are from StackOverflow’s original database.  In theory, these numbers will not change, which means if you build your own child table structures like UserBaconPreferences, and you join via User.Id, you should be able to blow away and reload the Users table with every new StackOverflow database dump.  That’s the theory, but in reality, you shouldn’t rely on anybody else’s ID fields, because there’s no reason to believe these won’t completely change down the road.  Who knows – Jeff might switch over to GUIDs as primary keys.

Sample Questions Query

Once you’ve got it all together, you can do some fun stuff. Let’s look at some overall statistics about questions (not answers):

SELECT COALESCE(COUNT(DISTINCT p.ID),0)           AS Questions
       ,COALESCE(AVG(p.Score * 1.00),0)           AS AvgScore
       ,COALESCE(AVG(p.ViewCount * 1.00),0)       AS AvgViewCount
       ,COALESCE(COUNT(DISTINCT p.OwnerUserId),0) AS DistinctQuestioners
       ,COALESCE(AVG(p.AnswerCount * 1.00),0)     AS AvgAnswerCount
       ,COALESCE(AVG(p.CommentCount * 1.00),0)    AS AvgCommentCount
       ,COALESCE(AVG(p.FavoriteCount * 1.00),0)   AS AvgFavoriteCount
       ,COALESCE(COUNT(ClosedDate),0)             AS ClosedQuestions
       ,COALESCE(AVG(u.Reputation * 1.00),0)      AS AvgQuestionerReputation
       ,COALESCE(AVG(u.Age * 1.00),0)             AS AvgQuestionerAge
       ,COALESCE(AVG(u.UpVotes * 1.00),0)         AS AvgQuestionerUpVotes
       ,COALESCE(AVG(u.DownVotes * 1.00),0)       AS AvgQuestionerDownVotes
FROM   dbo.Posts p
       INNER JOIN dbo.Users u
         ON p.OwnerUserId = u.Id
WHERE p.Tags IS NOT NULL

And some of the results are:

• Questions – 176,137
• Average Score – 1.89
• Average View Count – 311
• Distinct Questioners – 39,795 (meaning anyone who has asked a single question has asked an average of 4.4 questions – there may be some odd stuff in here around anonymous questions though, haven’t looked at that yet)
• Average Answer Count – 4
• Average Comment Count – 2.3
• Closed Questions – 3,656 (or 2% of all questions)
• Average Questioner Reputation – 1,506
• Average Questioner Age – 30 (but remember, that’s unreliable)

I’m just getting started playing with it, and I’ll have a fun new StackOverflow statistics toy available for everybody to play with in a couple of days.  In the meantime, you can download the StackOverflow database dump via BitTorrent and download my ETL stored procs.

Update: Sample StackOverflow Queries in the SQLServerPedia Wiki

Jon Skeet had an excellent idea: we need a wiki to store interesting queries.  Wouldn’t you know, I happen to run one!  I added a section in SQLServerPedia for sample StackOverflow database queries.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

Sounds simple, right?  Just grab the max.  But what if you want to use a single T-SQL operation to fetch the most recent record and some of its attributes?

StorminSpank asked:

@BrentO SQL Question. 4 columns, ID, Date, Info1, Info2. ID has multiple entries. Want Latest Date for Distinct ID, but also info1 and 2.

Let’s say our table schema is:

CREATE TABLE [dbo].[TestTable](
	[id] [int] NOT NULL,
	[create_date] [date] NOT NULL,
	[info1] [varchar](50) NOT NULL,
	[info2] [varchar](50) NOT NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
	[id] ASC, [create_date] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

And let’s populate it with some data:

INSERT INTO dbo.TestTable (id, create_date, info1, info2)
VALUES (1, '1/1/09', 'Blue', 'Green')
INSERT INTO dbo.TestTable (id, create_date, info1, info2)
VALUES (1, '1/2/09', 'Red', 'Yellow')
INSERT INTO dbo.TestTable (id, create_date, info1, info2)
VALUES (1, '1/3/09', 'Orange', 'Purple')
INSERT INTO dbo.TestTable (id, create_date, info1, info2)
VALUES (2, '1/1/09', 'Yellow', 'Blue')
INSERT INTO dbo.TestTable (id, create_date, info1, info2)
VALUES (2, '1/5/09', 'Blue', 'Orange')
INSERT INTO dbo.TestTable (id, create_date, info1, info2)
VALUES (3, '1/2/09', 'Green', 'Purple')
INSERT INTO dbo.TestTable (id, create_date, info1, info2)
VALUES (3, '1/8/09', 'Red', 'Blue')

Now let’s get out the most recent record for each ID:

SELECT tt.*
  FROM dbo.TestTable tt
    LEFT OUTER JOIN dbo.TestTable ttNewer
    ON tt.id = ttNewer.id AND tt.create_date < ttNewer.create_date
  WHERE ttNewer.id IS NULL

That left outer join is looking for any newer TestTable records, but the where clause makes sure there aren’t any. If you switch it around to “IS NOT NULL”, you’d get the exact opposite – you’d get all of the older records.

And remember, ladies and gentlemen, do as I say and not as I do – never SELECT *.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

Tim Ford, aka SQLAgentman, posed a question: when you think you know why something’s not working, when do you modify somebody else’s code, and when do you refuse?  I know, I know!  I’ve got the perfect answer – “It Depends.”

Vendor Apps That Are Under Paid Support

I never touch vendor applications when I’m paying maintenance contracts.  It’s not that I want them to do their job – although I do.  It’s not because I’m lazy – although I am.  The reason is simple: I don’t want to be thrown under the bus later.

If you’re working with an application and you think the database schema was designed by monkeys banging on typewriters, don’t even think about changing the database schema yourself.  If you do, guess what happens when the next version or service pack arrives?  The guys who designed the schema are probably the same guys who designed the installer.  I’ve seen installers that run T-SQL scripts that absolutely depend on the schema being completely identical to the way it was last time.  If there’s any variations, the installer throws an error, and you’d better believe they’re not handling errors correctly either.  It leaves your database in a completely messy state, totally unsupported by the vendor, and then the blame game starts.  You’ll get the shaft, because you tweaked their schema.

Nobody will remember how you made the database faster.  Everybody will remember the DBA who couldn’t keep his hands out of somebody else’s code, and borked it.  Doesn’t matter if you’re right: you’re only as good as your customers think you are.

Vendor Apps That Aren’t Under Maintenance Contracts

Maybe the vendor’s gone belly-up, and you’re just trying to keep the product running.  In that case, sure, modify the schema.  You’re going to own the support yourself anyway. Just make a rollback script right then and there before you do it, and save the rollback script in case something else goes wrong later.

Maybe your company’s about to go belly-up, and you’re not paying maintenance on anything.  In that case, sure, modify the schema, because you gotta do whatever it takes to keep the trains running, or you’ll be laid off for not playing along.

In-House Applications

If the development team consists of people on your payroll, then this is your chance to show value within the company.  Quickly document:

• What needs to be done
• Why it will improve performance
• What you’d do if this DOESN’T improve performance
• How long it’ll take to do
• When it can be done (right now, after hours, only at month end, etc)
• Who can do it (but not who SHOULD do it)

Lay these points out as quickly as possible, in as short of an email as possible, and send it to the product managers.  For that last bullet point, I usually say, “I’m totally fine with doing this myself, but I wanted to communicate it to the team in case someone else would like to do it or wants to sit with me while I do it in order to learn more.”

You want to be as friendly and upbeat as possible, because you’re basically pointing the finger at somebody’s baby and calling it ugly.  Be a part of the solution, not a part of the precipitate.

Open Source Applications

This process is almost guaranteed to fail long term, but it works short term:

1. Modify open source code
2. Put it into production in your environment
3. Try to get it into the trunk branch (the public repository of modifications)

This process is almost guaranteed to work long term, but it can take a while:

1. Modify open source code
2. Try to get it into the trunk branch
3. After it’s released, put it into production in your environment

Try the first route, and you might fork the code and be forever patching your stuff.  I’m a fan of set-it-and-forget-it.  Mostly the forget-it part – I’m a really big fan of that.  So I’ve been burned by tweaking open source code, forgetting about my changes, and then upgrading to the latest version.  Surprise, my stuff is borked, and there’s nobody to blame but me.

Who I’m Tagging

I’m curious to hear what StatisticsIO and Kevin3NF think, especially Jason since he’s done open source work.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts