Do Alpacas Dream of farming DBAs?

Comments 36

Share to social media

422-alpaca1.jpg

M

ost days I truly love being a SQL DBA. Then there are the days when I seriously consider making the jump to Alpaca farming.

You see, my company recently purchased an application that the Human Resources department uses in order to monitor the Internet usage of employees.

Like most turnkey applications it was not vetted properly by anyone in the Technology department. Unfortunately, I.T., to some, does not stand for Information Technology, but rather Implement This. So, contracts were signed and we were handed the specification sheet from the vendor.

According to this specification, we should be able to keep a running 90 days of data to report against, data older than that is archived and purged from the database. Under this plan, the database should consume no more than 50 GB of space.

We set things up according to this specification and after the initial eight months we were topping out at 150 GB of consumed disk space for the database; even whilst purging the data that was more than three months old. Currently, ten months after implementation, we hit crisis point when the database is 270 GB in size, there is a mere 1 GB free on the disk devoted to the data file, and we are purging all data older than 30 days rather than the planned 90 days. Clearly, something was very wrong

A series of calls to the vendor establishes the fact that the purge process is not working correctly. There are two tables that still hold orphaned records from archived data. These records are overlooked in the purge process controlled via the application because of the lack of referential integrity between these tables and any others in the database which would have fired an error, or which could have been modified for cascading deletes.

The vendor’s level-two support provided our analyst with two scripts, which they rather optimistically believed would successfully run to clear out unnecessary data from each table. It looks something like this:

DELETE FROM tblOrphans 
    WHERE FieldX NOT IN (SELECT FieldX FROM tblParent1)
    AND FieldX NOT IN (SELECT FieldX FROM tblParent2)

This is one of those situations that every DBA encounters occasionally. We all know from experience that it takes time and space to make space. Balancing log and tempdb growth against reclaimed space, i/o, locking, and many other variables are not taken into consideration by most application vendors when they provide you with scripts like this one. Understanding the root cause of a problem and formulating the correct plan for its resolution is arguably one of the most important traits of a good DBA.

In order that you should share our feelings of dread at this point, I’ll sketch in a few details. The schema of tblOrphans is thus:

CREATE TABLE dbo.tblOrphans 
    (FieldX INT NOT NULL, FieldY VARCHAR(750) NOT NULL) 
ON PRIMARY

There are two indexes on tblOrphans:

CREATE UNIQUE NONCLUSTERED INDEX idx1 ON dbo.tblOrphans   
   (FieldX ASC, FieldY ASC)
WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
      IGNORE_DUP_KEY = OFF, ONLINE = OFF) 
ON PRIMARY

And…

CREATE UNIQUE NONCLUSTERED INDEX idx2 ON dbo.tblOrphans   
   (FieldY ASC, FieldX ASC)
WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
      IGNORE_DUP_KEY = OFF, ONLINE = OFF) 
ON PRIMARY

Other items of note:

  • None of the three tables in question have foreign key constraints
  • Neither parent table has a relevant index
  • tblOrphans consists of 205 million records, consuming 50GB of space
  • 191 million records would be deleted as a result of the delete query
  • Customers are accommodating of downtime. 24×7 access is not required
  • The server consists of two logical drives
    • C: 136 GB RAID 5 with 50 GB available space
      • Application files
      • all user database log files
    • D: 273 GB RAID 5 with 1 GB available space
      • system databases’ data and log files
      • user database data files
      • browser logs

The problem is this. We can see that the script as it was given us is most unlikely to run successfully. We need to release enough space to the O/S to allow the nightly 2GB worth of browsing logs to be input into the database until the next monthly purge; retaining enough data file space allowing for growth from the nightly input from these logs. The bright side is that we will be replacing this product instead of upgrading to a new version of their software. This means that modifying schemas, adding/removing indexes, and implementing referential integrity is acceptable so long as reporting and data entry is not adversely affected.

I know the steps (and failures) I encountered along the way to resolving this matter and I’ll outline the solution I ended up utilizing in a future article. In the meantime, I’d be very curious to know what you would have done with the information that has been presented.

Simple-Talk offer an iPod shuffle to the best proposed solution, submitted by 1st September 2007 as a comment to this article, and there will be three runners-up prizes of a Simple-Talk gift bag, which includes the highly desirable Red-Gate USB memory drive, and higly-collectable Simple-Talk pen..

I must be going, the Alpacas are getting restless!

(as you see below in the comments, the Simple Talk readers did not spare their energies in giving Timothy their sage advice. We continue the story, and Timothy’s award of the prizes, in the sequel…. Greetings from the Alpaca Mill)

422-alpaca2.jpg

About the author

Timothy Ford

See Profile

Tim is a SQL Server MVP, and has been working with SQL Server for over ten years. He is the co-founder of SQL Cruise, LLC, a training company for SQL Server specializing in deep-dive sessions for small groups, hosted in exotic and alternative locations throughout the world. He is also a Senior SQL Server Professional for Next Wave Logistics, Inc. and is the owner and Lead Consultant for B-Side Consulting, LLC.
He's been writing about technology since 2007 for a variety of websites and maintains his own blog at http://thesqlagentman.com/ covering SQL as well as telecommuting and professional development topics. Tim is an established SQL Server Community Speaker and long-term volunteer in the technical community, having held positions in the Professional Association for SQL Server (PASS) since 2002. He has also been leading the West Michigan SQL Server User Group (WMSSUG) since 2008.
When not neck-deep in technology, Tim spends his time travelling with his wife, Amy and sons, Austen and Trevor, be it to Alaska or simply to one of Trevor’s tennis practices or Austen’s Boy Scout meetings. Tim is passionate about photography, cooking, music, gaming, and exercise, and either experiences or writes about them often.

Timothy Ford's contributions