Yep, you heard me right. I am going to automate everything I do. Or, at least, try to.

I’m not going to talk in detail about why I’m doing this, other than to say:

1. I have better things to do with my time than perform mundane, repeatable tasks.
2. I recently re-read John Sansom’s excellent blog post on the subject, and, as is usually the case with John’s work, found it rather inspiring. Bravo John!

Instead, I’m going to talk a little about two kinds of “automation”, and why I generally pick one that might not, at first glance, be the right choice.

Full versus Partial Automation

I will define something that is fully automated as a task or operation which requires absolutely zero manual effort on my part. That is, I don’t have to push a button, flip a switch, type a command, or even know that it is happening, unless something goes wrong. An example of something that is already fully automated in my world is the weekly and daily database maintenance that I have installed, using Ola Hallengren’s excellent (and freely available) scripts. These run every night and ensure that my databases are backed up, corruption free, and optimized. Unless something goes *bump* and the jobs fail, I don’t get so much as an e-mail upon completion.

By contrast, a process that is partially automated requires some manual effort, but is still optimized such as to require as little work on my part as possible. I may need to open a script, enter a few parameters, or run a Powershell command, but outside of that, all the logic and processing is done behind the scenes, with perhaps a pretty little progress bar showing that work is, well, progressing. An example of this might be setting up of a server side SQL trace; I have a series of scripts ready that, given a database name and a path at which to place the trace files, will setup a standardized trace, grant rights on some signed stored procedures to allow non-DBAs to read the trace data, and even generate a README file to be sent to the requesting team with a series of instructions.

In an ideal world, I suppose that all of the mundane daily work I have to take care of would be in the former (fully automated) category. But in reality, I find that there’s a bit of a diminishing return on putting more and more effort into fully automating tasks. Consider this scenario / requirement: the request and creation of new databases.

At a basic level, the process we follow is something like this:

1. A developer requests a new database via our in-house ticketing system.
2. We (my team) ensure that the request at least contains the following details:
1. The name of the database.
2. The server on which to create it (or at least a version of SQL Server).
3. The initial size of the database.
4. The domain users or group that requires access to the database.
3. Assuming we’ve got all the information required, we will go out to the database server where the databases will be placed, and determine a suitable location to place the data files. This is usually determined by simple the simple rule of “whichever volume has the most space”, with a few exceptions. If there is not enough space on the server, we’ll inform the requestor that there will be a delay, and start the process of ordering more storage.
4. Once we know where the data and log files will be placed, we will create the database, take a full backup (to initialize the differential backup chain), and grant the access requested.

Now, I could certainly envision writing some kind of application that would allow developers to submit requests for new databases and process everything straight through based on the rules outlines above, similar to what I see on my web host’s control panel for MySQL databases. But, at the same time, constructing such as system would take a lot of time and effort. Contrast that with a partial automation solution, such as:

• Provide an InfoPath form for the requests, forcing people to enter the necessary details in before submitting the request, thereby eliminating back-and-forth chatter.
• Use a Powershell script that, when given a server name, database, and log file size, will connect via WMI and determine a list of suitable locations for placing the database files, then prompt the user to select one. When selections are made, the database is created and a full backup taken. This eliminates logging into the computer (to look at drives) and stepping through a bunch of GUI screens to create the database.
• Have a second Powershell script that can accept a text file containing domain groups / users and role names, which will then grant the access required on a specified database. The file could be generated from the InfoPath form. Again, this eliminates some GUI clicks, and minimizes the chance of fat-fingering group / user names.

While this will not prevent someone from having to manually process the request, it will significantly cut down on the pain to do so, without a great deal of effort / complexity. Especially considering that these requests are fairly infrequent (perhaps one to three a month), I see no reason to spend additional time on providing a fully automated solution, when the partial one provides almost as much value.

Over the course of the year, I’ll be blogging about the various processes / means I use to accomplish this goal.

How do you automate your processes, and how do you determine a “break-even” point, if you will?

This post is the fourth in an ongoing series about how to survive as the only DBA in your organization. Since October of last year, I’ve been assigned to a team that is responsible for owning and maintaining the development infrastructure. It’s a great team of seasoned professionals, but not a single other DBA. As a result, I’ve had to think very carefully about how I go about my daily work, so as to give our customers consistently good service, while still allowing those without a lot of SQL Server related knowledge to pick up my work when I’m not available.

Automation Is A (Lone) DBA’s Best Friend

I subscribe wholly to the sentiment expressed in John Sansom’s excellent post, titled “The Best Database Administrators Automate Everything”. Time is something we cannot make more of for ourselves, and as such is highly precious. If you are spending time doing repetitive work that could be either partially or fully automated, that is time wasted.

But there’s another benefit as well to automating processes: it makes them easier to hand-off. Let’s consider the example of restoring a copy of a production database to a test server. The typical steps I would complete would be:

1. Determine the location that the backup file was placed at by the production DBA group (I do not have access to production backups, so they must copy them down to my server).
2. Determine where the database files should be placed on the server using this logic:
1. If the database already exists on the server, the files should be placed at the same location as they currently reside.
2. If the database does not exist, then the files should be placed at the default data and log location as specified in the server configuration.
3. If the database exists, set the database to single user mode using the “WITH ROLLBACK IMMEDIATE” option to effectively kill any open connections to the database.
4. Issue the restore command, using native commands or the Litespeed equivalent if the database came from a server using Litespeed.
5. Execute the databases specific post restore script from the source code repository. These scripts reset permissions for developers, synchronize users, etc.

While none of these steps are particularly hard, documenting them and teaching them to others would not be a small amount of work. Consider that step two involves querying system tables (or stepping through some screens in SSMS), and steps three and four require knowledge of T-SQL commands for restoring databases.

Instead, I chose to write a Powershell script which handles as much of the logic as possible. All it requires is a server name and the name of a backup file, and it handles the rest (except step five, but I’m working on that). This way, instead of teaching all that logic, all I need to do is show someone how to find the backup file (further simplified by standardizing paths across all the servers) and run the script.

I don’t know about you, but I’d much rather write code to do a job than write a document about how to do the job manually.

This post is the third in an ongoing series about how to survive as the only DBA in your organization. Since October of last year, I’ve been assigned to a team that is responsible for owning and maintaining the development infrastructure. It’s a great team of seasoned professionals, but not a single other DBA. As a result, I’ve had to think very carefully about how I go about my daily work, so as to give our customers consistently good service, while still allowing those without a lot of SQL Server related knowledge to pick up my work when I’m not available.

What Makes A Process Simple?

sim·ple   [sim-puhl] adjective

1. easy to understand, deal with, use, etc.: a simple matter; simple tools.
2. not elaborate or artificial; plain: a simple style
3. not ornate or luxurious; unadorned: a simple gown
4. unaffected; unassuming; modest: a simple manner
5. not complicated: a simple design

When I design a process for my team to follow, I always do my best to follow the definition above, especially the first and last lines. Processes should be easy to follow and only as complex as they need to be, no more. Programmers can sometimes get carried away with complexity, because a) it’s fun, b) it lets us stretch our muscles a little. That’s all fine and good in your lab, but when you’re trying to design something to be used in the real world by actual people (other than yourself), complexity can be a huge problem.

Take this example: your team needs a way to locate information about a particular server, including what it’s used for, make and model, etc. Here’s two choices for how you fulfill this requirement.

Option A – Command line / SQL scripts and a SQL Database

Sounds easy enough, right? And a relational database seems like the perfect place to store this kind of structured information.

Well, that’s great, for someone who is comfortable writing T-SQL code to interrogate a database. And who understands the structure of the database. And, oh by the way, can even find where the database is.

I’m guilty as charged on this one. When I started on my team there was no repository for server inventory, so I made one. I’d call it a pretty well designed database, normalization and structure wise, but there’s one glaring flaw: no interface. Every operation, such as adding a server, removing one, looking up information about one, etc, is done via T-SQL scripts (hand-written mostly). Fine for me, but my teammates are going to quickly get lost and just call me when they need information.

Let’s assume I’ve even saved scripts in our code library for common operations, such as looking up a server’s information by its name (not always a valid assumption, certainly). Even with that shortcut, so the team doesn’t have to write JOIN statements, consider the list of steps required to look up a server:

1. locate the correct script in the repository
2. Open the script in SSMS (do they have that installed?)
3. Connect to the server where the management database resides (do they know where that is?)
4. Look through the script and find the “find/replace” tags (I would at least use SSMS template parameters, though it’s debatable if that is easier)
5. Execute the script and interpret the resulting text output

Hmm… five steps and several questions. Not very simple.

Option B – A Small Web Application With A SQL Back End

With this choice, we still get the benefits of storing the data in relational format, but with a cleaner interface (even my crappy UI design skills can manage this one). Most competent admins can understand a web interface with fields and buttons, so they should have little problem using it once trained (and of course, its use would be documented).

Consider the steps required to perform the same operation here:

1. Open a web browser and browse to the web site (do they know where it is?)
2. Select the “Find Server By Name” option presented on the home page.
3. Enter the server name and click the “Find” button.

We’re down to three steps and only one question (do they know what the site address / URL is). Now we’re getting into the realm of simple.

One could certainly argue that there’s an even easier solution, along the lines of a folder on a searchable network drive with a set of Word documents, one per server, in standard format. I wouldn’t discount that argument, but I just really had folders full of Word documents, especially when you have to go through all of them one at a time to change some common field.

You could also argue that the process of writing a web application (and supporting it) would outweigh the simplicity gained by having the information presented in that manner. After all, can anyone else on your team understand C# code? What happens when it breaks and you’re on vacation? That may be true, but I honestly believe that if you write your code properly (and, drumroll please, simply) then the application should be pretty much self-sufficient. Good code hums along and doesn’t require babysitting.

So the next time you’re designing a process for your team, remember to keep things as simple as you can. Your teammates will thank you.

In the last two weeks, my team at work has taken on support of a new application that makes heavy use of SQL Server replication technologies. This is one are I’ve never played around with much, so it has been a steep learning curve to say the least.

One of the more common scenarios I’ve run into is that the distribution agent encounters an error applying replicated transactions, causing the flow to stop dead in its tracks. From some research, it seems like in general this symptom indicates that the publisher and subscriber are out of sync, and usually the best option is to re-initialize the subscription. For various reasons, however, that is not an option in this case, so I’m left with resolving the issue causing the replication to fail. That means finding the actual command causing the failure, which was a little tricky.

I’ve found that the Replication Monitor GUI is not very helpful in this regard. What I tend to see is that it reports the transaction sequence number just fine, but the command text is less reliable. Mostly I’ve seen the last command in the batch, which (with my application) usually ends up being a simple “IF @@TRANCOUNT > 0 COMMIT” statement. Not very useful.

So instead, I’ve taken to using some good old T-SQL commands to get the information I want. The first is the sp_helpsubscriptionerrors stored procedure. This is executed at the Distributor and will return a list of errors for a specified subscription. The columns error_text, xact_seqno, and command_id are very useful, as they tell you the specific error message being encountered, as well as the transaction and command that are causing the problem.

Once we have this information, we can use the sp_browsereplcmds stored procedure to actually view the command(s) causing the issue. While you could feed it both the transaction sequence number (usually the same for @xact_seqno_start and @xact_seqno_end) and the command ID (@command_id), I find that it’s most helpful leaving the command ID out. This way you can see the entire transaction’s worth of commands (including the offender), giving you some good context to evaluate how to resolve the problem.

As for actually resolving the problem, well that of course depends upon what the root cause of the issue is. In our case, it seems that most times the problem lies with some developer deleting or adding a row at the subscriber, causing data validation errors. It’s a painful process, which is why our response is usually “Hey Mr. Developer, you deleted this row and now it’s causing problems. Please let us know when you’ve recreated the row and we’ll restart replication.” Let people fix their own problems.

I’m sure I’ll blog more on the subject as I encounter other problems and learn more about supporting replication.

This post is the second in an ongoing series about how to survive as the only DBA in your organization. Since October of last year, I’ve been assigned to a team that is responsible for owning and maintaining the development infrastructure. It’s a great team of seasoned professionals, but not a single other DBA. As a result, I’ve had to think very carefully about how I go about my daily work, so as to give our customers consistently good service, while still allowing those without a lot of SQL Server related knowledge to pick up my work when I’m not available.

Design for next time, not this time

When you get a request to do something, say, restore a database and give a team some access, do you just click through the GUI quickly to get the job done, then move on? Or do you consider how you can design the process so that it can be repeated over and over again with the same result?

Before you answer that question, let’s consider why we’d want a process to be repeatable, especially as a lone DBA:

• It minimizes the work needed to take care of similar requests in the future. Instead of starting from scratch, you can re-use existing code or knowledge. You therefore have more time to work on other, more important (but perhaps less urgent) items.
• It results in consistent service to the client. The same input (“Please restore this database and grant me access.”) should always result in the same output (the database is restored to the right server, and the right permissions are given). The client always gets what they want, and it is delivered in the same manner.
• It allows us to delegate effectively.It’s much easier for a junior team member to walk through a well thought process than to have a request thrown at them and told to figure it out (which usually results in a stream of questions, followed by a gruff “Oh jeez I’ll just do it myself, it’ll be faster”). Now you can focus on the work that really requires a DBA toolset, instead of boring administrative tasks.

Still think the processes you put in place don’t need to be repeatable? Perhaps you should read the first post in this series, and remind yourself of the ramifications of blindly doing things your way.

Moving on, let’s examine the characteristics of a good, repeatable process:

• Simple – the process should accomplish what the intended result is, with a minimum of fuss and effort, with no unecessary complexity in terms of how the process works.
• Automated – the process should require as little manual intervention as possible. If there are no steps requiring human decision making, then it should only require our help when something goes wrong.
• Documented – the process should be clearly described, both in terms of how to complete it, as well as how it accomplishes our goals (from a technical perspective).
• Generic – the process should be designed such that it can be tweaked at run time (to use programming lingo) to meet varying needs, as opposed to requiring lots of manual intervention.

In the next few posts in this series we’ll examine each of these in more detail. Please let me know in the comments if you can think of any other required traits of a repeatable process!

This post is the first in an ongoing series about how to survive as the only DBA in your organization. Since October of last year, I’ve been assigned to a team that is responsible for owning and maintaining the development infrastructure. It’s a great team of seasoned professionals, but not a single other DBA. As a result, I’ve had to think very carefully about how I go about my daily work, so as to give our customers consistently good service, while still allowing those without a lot of SQL Server related knowledge to pick up my work when I’m not available.

We don’t need no stinkin’ team…

Sure, who needs teammates? I mean, we don’t need to take vacations, have sick days, or even take breaks to go workout at the gym or go for a run. We can just work all day, handling everything ourselves. And if we’re on vacation, we can always take our laptops with us, and tether our Crackberry to dial in to the network when things need to get done.

If you’re not laughing out loud, or at least cringing at what the last paragraph describes, I’d like to recommend a website for you to browse.

Hopefully we can all agree that we all need time off now and then. And perhaps the occasional unplanned absence will happen, despite our best attempts to be the Cal Ripken Jr. of our office and keep our no-sick-day streak alive. And when that time comes, would you rather have your customers needs smoothly handled without pause, or would you like them passed around from team to team like the proverbial hot potato? Or even better, given that wonderful “Well, can it wait until so-and-so is back in the office?” question. I don’t know about you, but I hate it when I hear that question.

You can be the man, without being the man all the time

We all like to feel important and desired; after all, it’s just a natural human need. Early in my career I craved the spotlight and guarded my knowledge carefully, lest someone else take my spot as “go to guy”. It felt great to always be the one to solve issues and get things done. Well, at least it felt great until I came home three hours late and faced a cold dinner plate, or spent the day after Christmas troubleshooting over a dialup connection, while my wife and relatives opened presents in the other room.

At some point in our careers, I believe strongly we have to choose one of two paths:

1. We can keep being the lone wolf who does things our way all the time, however we please, because we’re the only one who ever knows how to do them.
2. We can compromise our rigid principles, stop being stubborn, and learn that sometimes we have to consider if our teammates could do things how we’d do them.

Allow me to clarify that last option a bit: Just because you’re a whiz with T-SQL and could whip up a one-time script to update ten thousandmillion records for that cute girl / guy in Operations doesn’t mean that you should. Because like any reasonable customer, if it’s done once, they’ll expect it can be done again, whether you’re there or not. And if this person / group is an important consumer of your services, chances are you’ll be getting a phone call from your teammate (or your boss) when you’re at your kid’s Scout camp, asking how to modify your query (because you at least saved that and let the team know where it is, right?).

If your whole team is full of crack T-SQL programmers, good for you, go wild (and you are one lucky SOB); otherwise, don’t do something unless you’ve made sure your backup can do it just as well. Maybe you feel comfortable running a series of scripts to setup replication every time you restore a database, but how about the guy sitting next to you who spent most of his career as a Windows sysadmin? I’m not suggesting that you can’t do complex things that can’t be repeated with a few clicks, but do consider how to make the processes easier for those without your experience. Talk to your teammates and make sure you build a process they’ll be able to support. Maybe they’re not comfortable running scripts in Management Studio, but perhaps a wrapper Posh script that prompts them for the necessary information and takes care of things under the covers would be fine.

Believe me, you’ll still be appreciated when you return from that much-needed vacation, perhaps even more so because you made sure your customer would experience the same great service when you’re not there. And there will always be new challenges to tackle where you can flex your muscles to remind everyone just how good you are. Just remember to bring your teammates along too.

In my work there are things I greatly enjoy doing: performance tuning, design “think tanks”, occasional bouts of code writing, and looking at new technologies to name a few. I enjoy these because they allow me to flex my creative muscles a bit. I’ve always thought of myself as a fairly imaginative person, so I do like to let the ideas fly and see where we land.

On the other hand, there are things that, well, let’s just say I’d rather hammer nails through my fingernails than do. Building servers, for instance. I mean, does anyone really like clicking next a half a dozen times, typing in drive paths, selecting features, then watching pretty little progress bars inch their way across the screen? Only to have to re-do the same thing again when it fails 95% through (a story for another post perhaps). Or code reviews: I don’t think the English language is descriptive enough to accurately portray how awesomely boring it is scrolling through hundreds (in some cases thousands) of lines of code, looking for formatting errors, table hints, horribly named objects, and other big bad coding horrors. Every now and then I do have some interesting T-SQL to examine, but most of it is either garden variety, or so utterly incomprehensible as to make my brain twist into one giant Gordian knot. And let’s not get started on the ongoing (never-ending) work of cleaning up and remediating existing installs that haven’t been touched by a DBAs gentle hands in years.

But here’s the thing… those things I hate so much? They’re a very necessary part of being a good DBA. And they’re a major part of what my organization needs from me right now.

As much as I’d love to spend my days whiteboarding ideas for new products and playing the hero fixing bad SQL code with a flick of my wrist, right now the focus has to be on lower level chores. The environment’s still messy, with unpatched, un-backed-up servers floating about all over. There’s no consistentcy in how they’re all set up, from things like server configurations to agent jobs and storage layouts. I’ve got months of work ahead of me just to get above water.

I know what you’re thinking: all this being the case, why do I keep working like this, when surely there are positions that receive more praise, are more engaging, and do work more visibly of value to others. Yes, that’s very true, and if I wanted to, I’m sure I could get there in a short span of time. I think the same thing myself from time to time when work gets particularly frustrating or stressful. But then I remember a realization I came to some time ago: a good DBA knows that sometimes you have to be what your customer needs, not what you want to be.

The most value I can be to my employer is by ensuring everything is stable, patches, backed up, and there for them when they come in the next day. I can also ensure that the releases going out are properly done and won’t cause issues (which can in turn cost money) when rolled out. There are other people who can be heavily involved in database / application architecture and design, solve challenging T-SQL coding problems, and be the hero tuning badly performing queries. But right now, I’m the only one who can make sure that backups have run, databases aren’t corrupt, and there’s enough memory to answer queries. It’s not glitzy work, but it’s necessary. It’s the work I need to do, not the work I want to do. And I’m OK with that, because it’s what I signed up for when I took this job.

It’s that time again, when SQL bloggers from all over join forces to blog for a common theme. That’s right: T-SQL Tuesday!

Background: T-SQL Tuesday is the brainchild of Adam Machanic (Blog|Twitter) and is described as “the SQL Server blogosphere’s first recurring, revolving blog party. The idea is simple: Each month a blog will host the party, and about a week before the second Tuesday of the month a theme will be posted. Any blogger that wishes to participate is invited to write a post on the chosen topic.”

This month’s theme: “Beach Time”, hosted by Jason Brimhall (blog | twitter). Basically, we’re being asked to write about “What do you do prior to ‘Beach Time’ to ensure that the beach time will not involve work.”

This is actually a good topic for me, as last month I took the longest consecutive vacation of my working life at two full weeks. It was challenging to get everything in order before leaving, and I did get a phone call once (ironically on the first day off), but all in all my team did great work and kept all my projects in good order while I was gone (with one minor exception, but that was at least partially my fault as you’ll see).

So how did I pull this extended absence off?

I documented the status of all my projects, including what was outstanding from others and what needed to be done while I was gone.

We have a wonderful SharePoint site where we keep all our projects listed and have detailed status notes on all of them. This means that if any one of us ever had to suddenly be out for an extended period of time, someone else could just glance over their list and pick up where they left off.

This reminds me strongly of one of my GTD habits: keep things in lists and not in your head. By forcing us (and yes, I do sometimes neglect it a little, hence my first (but only) phone call) to keep this updated, we don’t have to rely on our memory to keep track of what’s on our plates.

I made sure my runbooks were up to date.

Just before I left, I closed out a major phase of a project to build a massive data warehouse containing PerfMon data for all the SQL servers we support. It’s a complex system with multiple inputs and moving pieces. Because of this, I made sure the write up a thorough document detailing the ins and outs of the system, including architecture and troubleshooting steps. No way was I being called when I was away because this thing broke.

Now as it turns out, the system did fail while I was gone, but was unnoticed! And while my teammates sheepishly admitted that they missed the alerts and should have at least dug into them a little further, I also took responsibility for not ensuring they (the alerts) were a) urgent enough to be noticed, b) clear enough to tell the reader where to get more information.

Once I was gone, I made damn sure I was very hard to get in touch with.

While I am the first to admit I have a pretty bad addiction to my Crackberry, when I’m on paid vacation I strictly enforce the “no e-mail rule”. I go so far as to actually turn off the mail synchronization on my phone and make it clear in my out of office that I will not be checking my mail at any time during my absence. Only my teammates know my phone number and they are told they can call me if absolutely necessary, but that I may not immediately respond. While I do tend to take my work laptop, that’s only because it’s also my playground for testing SQL related things, and I do not connect to the office.

Being selfish? Maybe a little, but hey, it’s my vacation time and I’m damn well going to enjoy it and not waste it working. That being said, on this last vacation I did end up working for about 3 hours, but that was wholly my fault for not properly documenting something before I left.

I have a great, strong team around me.

While this may not have much directly to do with me, I consider it a crucial factor in vacation success. If your team can’t hold down the fort, even given good documentation, then you’re pretty much sunk. I’ve been in those situations before, and let’s be honest: it sucks. There’s few things in the world more maddening than getting called on vacation because “Mr. So and So is here and he says you told him X, but that doesn’t make sense?”, especially when your conversations with Mr. So and So are explicitly detailed in writing, and readily accessible to your teammates. That’s a real example by the way, from my time as a retail store manager. Let’s just say that individual got politely redirected to where I kept a log of all my conversations, along with a gentle reminder that I was not to be disturbed. Thankfully, this is not the case where I am now, and I am eternally grateful for the hard work and dedication of my fellow DBAs.

So today I spent basically the entire day working on troubleshooting an issue that is related to a little SQL Server technology called MemToLeave. Now I’m not going to go into extreme technical detail on what exactly this is; instead I’ll direct you towards some other excellent resources:

http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-server-memtoleave-vas-and-64-bit.aspx

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/07/understanding-the-vas-reservation-aka-memtoleave-in-sql-server.aspx

http://blogs.msdn.com/khen1234/archive/2005/05/08/415501.aspx

Basically MemToLeave is an area of reserved memory outside of the buffer pool, which is commonly used for such operations as extended stored procedures, CLR, and linked servers. It only affects 32 bit systems, is limited to a maximum amount of 512MB, and can be set using the “-g” startup parameter.

In my short experience as a DBA, this is far and away the most complex and mind-bending piece of investigative work I’ve done by a landslide. After spending nearly three hours Googling  and digging into various DMVs, I had made very little headway. This is real down-the-rabbit-hole stuff; we’re talking about understanding and trying to determine what is happening deep within the internals of SQL Server. Even my senior DBA, who has a tremendous wealth of knowledge and more than a decade of experience, was largely at a loss to explain what was going on. I even went so far as to use the great Twitter #sqlhelp hashtag (and got some good information, thanks David Levy (twitter | blog)) to try my best to get some insight. As it turned out, the Twitter community helped to push us in what we believe to be the right direction.

While I’ll leave the technical details for another post, our basic conclusion after nearly a day’s worth of work is this: due to a tremendous amount of statements in the procedure cache (the application using this DB makes a maddening number of adhoc calls, in such a way that each is stored as its own plan, even with forced parameterization on), and the fact that a large number of these plans were stored in multi-page arrangements (see Jonathan’s blog post above), MTL became suddenly and rapidly depleted. Normally we’d be forced to stop and start SQL in order to clear this up, but in this case, all we ended up having to do is to a) wait for the application to cease its (adhoc) insert heavy behavior, then run a DBCC FREEPROCCACHE. Before running the command, the return result of xp_memory_size was around 3MB; after it immediately jumped to ~21MB. Also, we saw that the CACHESTORE_SQLCP memory clerk type went from consuming around 180MB to around 1/100th of that (we used the queries on Brent McCraken’s blog here, plus some home-brewed ones of various DMVs). The server stayed quiet the rest of the day.

While I’m far from convinced, the evidence is pretty strong that we’ve found at least a contributing factor. The next bulk series of imports into the system happens overnight, so in the morning we’ll be checking in to see how we are doing.

That’s all for now, but once we have more data I’ll probably be posting a followup with the exact queries and results. What a day!

Brent Ozar (blog | @BrentO) wrote a great post the other day that I just read that touched on the oft-seen differences between management’s priorities and our own. Even having only been a DBA for a mere month (damn, has it been that long?) I’ve already encountered situations like this all too frequently.

Troubleshooting and digging into data are two of my favorite things to do when it comes to DBA work. I love pulling numbers out and slicing-and-dicing them every which way until a pattern emerges. In this light, I’ve been really excited lately about the prospect of building a kind of DBA data warehouse: a place where aggregated performance statistics from all our servers are kept and can be analyzed in detail. It’s not a novel concept by any means, just one that’s never been implemented at work. With this in place we could engage in detailed benchmarking of our environment, and proactively find developing “hot spots” before they become real fires.

The trouble is, we can never seem to find time to work on it. There’s always other things to work on, whether it be dealing with issues or building more servers to add capacity. This is really tough to handle because as a team, we pride ourselves on keeping things running at peak efficiency, like a well-trained pit crew for a championship racing team. We can clearly see the value in this project and how much it would help us.

But all that being the case, I won’t be spending my free time working on that. If I do spend time outside of business hours working (something I try to avoid whenever possible), it’ll be on those other, more important items.

Yes, you read correctly: “more important”. “But Josh”, you say, “you just said that stability is your team’s top priority, and to boot that you find your biggest intiative in that area incredibly exciting. Why the heck are you calling the other things ‘more important’?”

The answer is simple: because that’s what my clients want from me. Like it or not, right now the word is that the primary focus must be on building for new initiatives and responding rapidly to the changing needs of our industry. Granted that stability was ranked right behind that, and I am certainly going to stake a case to management about the need for some time to be allocated to our bechmarking project. But as I commented on Brent’s post (modified slightly):

I think in this case you have two ways to go: you can continue to play the role of the a-hole that is always arguing about priorities and complaining that he has to work on weekends to get his “important” work done, or you can accept that your client’s priorities are what they are, and try and work within them. Well, I guess there’s a third choice: quit and start blogging about SQL Server full time, but most of us probably aren’t that brave in this economy.

So thanks for the reminder Brent; while I sure do love building warehouses and crunching numbers, I won’t be doing any of that outside of 8AM – 5PM Monday – Friday anytime soon.