Author Archives: Brad

Do You Need an IT or CS Degree to be a Successful DBA?

Posted on by

When I am making small talk with DBAs I know, one of the things I like to ask them is what their college degree is in. While Information Technology (IT) or Computer Science (CS) degrees are common responses, most DBAs I know don’t have a degree in IT or CS. In fact, the types of degrees they hold range wildly. Some of the responses I have gotten include:

  • PhD in Clinical Psychology
  • BA in Creative Writing
  • BS in Geology
  • Masters in Engineering
  • PhD in Organizational Psychology
  • Pharmacy
  • BA in Philosophy
  • BS in Hydrology
  • BA in Linguistics
  • Masters in Business Administration

Personally, I have a BA in Economics and a Masters in Business Administration, and I only took one computer-related course during my undergraduate and graduate studies.

Once I find out what degree a DBA friend has, I often follow up, asking them how they became a DBA. This provides an even more diverse set of stories, most of which are rooted in a deep interest in technology, and through some unplanned and convoluted set of events, they are now successful DBAs.

So my question to you is, what is your college degree in? And to follow up, how did you get from where you stated to where you are today? And last of all, does your college degree really make all that much difference in your success as a DBA?

SQL Server DBA Certifications: How Many People Have Which Certifications?

Posted on by

According to Microsoft, as of April 2009:

153,130 people have received the MCDBA: SQL Server 2000 certification

51,445 people have received the MCTS: SQL Server 2005 certification

854 people have received the MCTS: SQL Server 2008 Implementation and Maintenance Certification

3,577 people have received the MCTS: SQL Server 2005 Business Intelligence certification

333 people have received the MCTS: SQL Server 2008 Business Intelligence Development and Maintenance certification

456 people have received the MCTS: SQL Server 2008 Database Development certification

7,928 people have received the MCITP: SQL Server 2005 Database Administrator certification

358 people have received the MCITP: SQL Server 2008 Database Administrator certification

3,377 people have received the MCITP: SQL Server 2005 Database Developer certification

213 people have received the MCITP: SQL Server 2008 Database Developer certification

1,385 people have received the MCITP: SQL Server 2005 Business Intelligence Developer certification

137 people have received the MCITP: SQL Server 2008 Business Intelligence Developer certification

18 people have received the MCA: Database certification

39 people have received the MCM: SQL Server 2005 certification

2 people have received the MCM: SQL Server 2008 certification

From the numbers above, it appears that DBA certifications are becoming less popular among DBAs. What do you think?

For more information on Microsoft DBA certifications, visit here.

Physical File Defragmentation

Posted on by

Do You Include Physical File Defragmentation as Part of Your SQL Server Maintenance?

Ever since I can remember, beginning with MS-DOS 1.0, physical file fragmentation has often been a problem on many systems I have used, hurting I/O performance as the disk heads have to thrash about to find all the many file fragments that make up a single physical file. Generally, using either the built-in OS defragmentation tools, or third-party tools, I have kept my physical files defragmented, so they are contiguous, helping to optimize my system’s I/O.

As technology has changed (SANs or SSD drives) physical file fragmentation has become less important. See Linchi Shea’s blog series on this topic. On the other hand, there are still a lot of servers with local storage that can be still be negatively affected by physical file fragmentation.

When I build a new physical box to run SQL Server, the new box has little or no physical file fragmentation to start with. Then, when I create my MDF and LDF files, I pre-size them to as close as I can to their final size (or at least as large as I expect them to grow in the next year or so). This way, when the MDF and LDF files are created on a new server, they are created in a contiguous manner, and there is no physical file fragmentation. Of course, if I do need to grow the MDF or LDF files, I do so in a controlled manner to minimize fragmentation. I avoid using autogrowth to grow my databases, as this can greatly contribute to file fragmentation as the MDF and LDF files grow over time.

In other cases, I have to deal with SQL Servers that have been around for a long time and have not been properly maintained. In those cases, I check for how bad fragmentation is, and if it is bad, I fix it before I create any new pre-sized MDF or LDF files. As a DBA, I prefer to be proactive and prevent physical file fragmentation from occurring in the first place.

What I would like to know is what has been your experience with physical file fragmentation on your SQL Servers? Have you experienced it? How has it affected performance? How do you fix it if you have it? What defragmentation tools do you use, and why? How do you prevent it from happening in the first place? In other words, how do you deal with physical file fragmentation on your SQL Servers?

The Maintenance Plan Wizard Can Lead You Astray

Posted on by

In theory, the SQL Server Maintenance Plan Wizard is supposed to make it easier for non-DBAs or novice DBAs to create database maintenance plans that help to optimize the performance of their SQL Server databases. The problem is that unless you really know what you are doing, you can hurt the performance of your SQL Server if you make poor choices. Unfortunately, the Wizard does not tell you if you make poor choices.

For example, check out the following screen, which is part of the SQL Server 2008 Maintenance Plan Wizard.

image

If you are an experienced DBA, your first thought should be, “Why are all the maintenance tasks checked?” This is because experienced DBAs know that you only need to perform some of these tasks, not all of them, and in fact, if you choose all of them, you can cause your server to waste a lot of valuable resources. For example, performing all three of the following tasks: reorganizing, rebuilding, and updating statistics is redundant, as rebuilding your indexes essentially performs the same tasks as reorganizing and updating statistics. In other words, if you rebuild your indexes, reorganizing and updating statistics is a duplication of effort. There are also some additional problems that can occur when selecting all of the options, but I don’t have time to write about them all now, but I think you get the point.

Now, let’s look at the above screen from the perspective of the non-DBA, or novice DBA who isn’t familiar with what maintenance plans do, or how they work. From their point of view, don’t you think that the more options you select, the better off your SQL Server will be? Isn’t more always better than less? What has prompted me to write about this topic is because I just reviewed a SQL Server instance that was installed and set up by a non-DBA, and as you have already probably guessed, they selected every maintenance task on the menu, thinking that more is better than less. Because the non-DBA made this choice (which is not all than uncommon), the maintenance plan took more than twice the time it really needed to run, wasting a lot of the server’s resources. There were some additional problems as well, but that is another story.

The point I want to make is that the Maintenance Plan Wizard can sometimes be useful in the hands of an experienced DBA, but it can be very dangerous in the hands of a non-DBA or novice-DBA. In fact, most experienced DBAs I know write their own maintenance plans because using the Wizard just has too many limitations and problems. If you are an experienced DBA, you know what I mean. If you are a non-DBA or novice DBA reading this, please take heed of my message, more is not better. If you decide to use the Wizard instead of writing your own custom maintenance plans, please do your research before you begin, learning what should, and what should not be done, within a maintenance plan.

PS: I have decided to write a book on how to use the Maintenance Plan Wizard properly. Look for it during the Fall of 2009.

Don’t Make Dumb Mistakes by Duplicating Indexes

Posted on by

Just today, I was reviewing the indexing of a database used by a third-party application. As part of my review, I ran a script against it that is used to identify duplicate indexes. The script I used was written Paul Nielsen, the author of the SQL Server Bible; and Itzik Ben-Gan. The script is available here.

After running Paul’s script against the database to find duplicate indexes, I discovered that almost every table had a duplicate index. As I delved into this a little closer, I discovered what the designer of this database had done.

First, it looked like he created primary keys using non-clustered indexes on an identity column for most of the tables in his database. Second, he then added a clustered index on the same identity column for each of these same tables. By doing so, he ended up having two identical indexes on the same identity column, one clustered and one non-clustered. Instead, the designer should have created his primary keys using clustered indexes instead of using non-clustered indexes. This would give him the same end effect, but requiring only a single index, not two. Obviously, by having duplicate indexes on most of his tables, he was causing SQL Server a lot of additional work to maintain extra indexes that were not needed.

If you have never checked your databases for duplicate indexes before, you should do so, as you might find duplicate indexes that can be removed, helping to boost the performance of your existing databases.