Jeremy Carter

SQL Server guru Paul Randal is hosting this month’s TSQL Tuesday and he has asked a great question that all good companies should be asking themselves. Why are DBA skills necessary?

I would answer that in today’s market being mediocre does not cut it. Companies need to stay ahead of the curve if they want to make it. They have a responsibility to themselves and to their customers. I think that it used to be a badge of honor when sites went down because of some new product or a great deal of attention was thrown their way. That is no longer the case. Web sites are expected to be up and running 24×7, no questions asked. And what is sitting behind most web sites? You can bet there is some sort of database server(s) setting behind it, feeding everything from content, to user preferences, to processing orders.

Yes it is true that you can install SQL Server, create a database and the thing will just run. The question though is how long and how well will it run? Is it all going to come tumbling down on your biggest sales day or during your latest promotion? In my experience if you do not have the dba skills and have been proactively taking care of your server, these are the very times you are going to experience failures.

So when should a dba start looking after the server? In my opinion there should be someone with some dba skills involved before SQL Server is even installed. Questions for server specs such as cpu, memory, disk, all need to be answered from the onset of the project. Now if you have a little 5 or 10GB db do you need to have a full time DBA? Most likely not, but in my experience it is usually somewhere around 10GB that you’ll start to see issues if no one is looking out for the server.

Someone with dba skills should be involved, not only insuring the hardware is up to snuff, but they should also be looking ahead to insure that best practices are being followed and all of the options are configured correctly. Basic things like recovery, max server memory, index maintenance plans, etc all need to be addressed before the lack of attention sneaks up and bites you.

The question of when do you need a full time dba is a bit tougher question to answer. In a nod to Paul, I have to say “It depends.” Most people would probably say that it depends on the size of the database; the larger the database, the more time needs to be spent on it. While this is generally true, it certainly is not a hard and fast rule. I have seen some customers with very small 10GB databases that seem to need constant tuning while others with multi-terabyte databases get by with little tweaking.

How can that be so you ask? The difference is in the forethought put into the server’s physical hardware, the database schema, and the queries being ran against it. If you have good hardware, a good schema, and well written queries you’ll be able to get more bang for your buck.

As an example the other day I had a client that was asking for help because their db server was just getting hammered. He already knew that corners had been cut in the past and the code was bad and that he needed more hardware to compensate. I was able to do some tuning to get them by but it would not be enough for the upcoming holiday season. Now because of negligence in the past, he was having to double the size of the hardware required. And not only was he going to be out money for the hardware but he still has to do something about the code or the site will just never scale for their planned future growth.

I can’t help but think that had a good dba been involved sooner that much of his pain could have been prevented and more money kept in their pocket. Poor planning costs money to redo code, purchase additional hardware, lost sales, and lost customer satisfaction. All too often people get caught up in what is good for the moment and forget to look ahead to the future.

A good dba with good skills can help anticipate and prevent those fires from ever starting, let alone growing out of control. A little TLC can go along way to ensure happy customers, coworkers, bosses, and spouses.

The folks over on the SQL Server support team have done it again. They saw a need for a tool to aid in troubleshooting, they wrote it, and have released it on Codeplex. This latest offering helps you troubleshoot issues with SQL Server backups and restores using 3rd party utilities.

I have to say I am pretty excited about this one. At work we are very compartmentalized and I have zero access to the backup software/servers. Whenever SQL backups fail, it can sometimes lead to finger pointing and confusion in deciding if it is SQL server or the backup application at fault. Luckily our 3rd party backup app does write a pretty verbose log to the local file system so I can sometimes look through that and get a good idea of where the issue occurred and why. However sometimes I look at the log and it is just complete gibberish and I have no choice but to throw up the white flag.

Enter SQL Server Backup Simulator. With this new tool I will be able to simulate taking backups and performing restores using sqlvdi.dll just like those 3rd party applications do. This tool was just released so I can’t say just how helpful it will be but I am hopeful that this will be another great tool to add to my toolbox. It will be great if I can use this to eliminate any issues on the SQL Server side so less time is wasted going back and forth. If the issue turns out to be SQL Server it looks like the tool will provide some good information about the sqlvdi.dll versions and errors.

The SQL Server Backup Simulator can be downloaded from Codeplex. If you have used to tool please let me know. I would love to hear how it worked for you.

I had an interesting issue the other day where a customer’s maintenance plan was failing. The maintenance plan had 5 steps to it. The first three were all executing Agent Jobs using the Execute SQL Server Agent Job Task. The last two steps were a History Cleanup and Maintenance Cleanup tasks. Each step used a connector that specified to go to the next step after completion of the last.

Sometimes one or two of the Agent Jobs steps would fail stating that they were chosen as the deadlock victim. Looking further in the steps they were pretty simple, just executing some backups and restores.

I enabled the deadlock trace flags to capture the relevant info and waited for it to fail again. Once it failed again I saw that sometimes when the backup or restore was trying to update the history tables in MSDB it was deadlocking with the queries executed in the History Cleanup. At first I thought this was weird as how could multiple steps possibly be executing at the same time.

It turns out that the Execute SQL Server Agent Job Task uses the stored procedure sp_start_job. This stored procedure starts the job and immediately reports if the job started successfully or not. It does not report if the job completed successfully. Therefore the first three steps plus the History Cleanup were all being executed at the same time.

Luckily the fix in this case was quite easy. I just removed the History Cleanup task to its own maintenance plan and we scheduled it to run at a different time. In other situations you may need to write a loop that checks the status of the job executed so that you don’t move on until the job has completed.

The fact that the Execute SQL Server Agent Job Task can run multiple steps asynchronously does not seem to be well documented anywhere, so I thought this would be a good reminder for myself and anyone else that runs into similar issues.