What are the Necessary Ingredients for Writing a Successful Book for New SQL Server Developers?

Posted on January 30, 2009 by Brad

A number of book publishers regularly send me books, hoping that I will write a good review for them. Most of the time, I don’t have the time to read them, so they end up on my bookshelf collecting dust, or maybe I will give them away to friends.

Recently, though, I decided to read one of the books that was sent to me with the goal of writing a review. The book was designed to get non-SQL Server developers up to speed on how to develop SQL Server 2008 applications using Transact-SQL. This is a very worthy goal, because based on my experience, it is bad application design and code that is the root of most SQL Server scalability and performance problems. So I had great expectations when I began reading the book.

Before I go any further, I want to give you an example that I think summarizes this book, then I will go into more specifics. For example, let’s say that a company has hired a new developer right out of college. The developer is bright and knows Visual BASIC, C#, .NET, and so on. In other words, he knows application coding inside and out. For his first project, he is assigned to create a new application that will use SQL Server as the backend database. Unfortunately, other than taking a couple of database theory classes in college, he has never written any Transact-SQL code. No problem, this is a bright developer. He picks up a copy of the book (I was reviewing), reads it, and because he is bright, masters the fundamentals of Transact-SQL development quickly, and he is ready to begin coding his first SQL Server-based application. If I was the DBA for this organization, I would be scared, very scared. The problem is that you can’t learn how to develop SQL Server-based applications from a single book, especially if that book focuses strictly on how to write Transact-SQL, and little else.

Reading this book got me thinking. What would the ideal book that teaches developers how to create SQL Server-based applications include? After some thought, I came up with the following suggestions (not in any special order):

Introduction to DBMS: Assuming the reader has no knowledge of DBMS, the book should begin with strong overview of how DBMS systems work.
Database Design: While entire books have been written on this topic, it is important that developers learn the basics earlier than later.
Teach Transact-SQL: Transact-SQL must be taught in an appropriate order, starting with the basics, building a strong foundation, and then moving onto more complex coding.
Teach How to Resolve Real World Problems: While learning the basics of Transact-SQL is fine and dandy, what is more important is that developers understand how to use it to resolve specific business problems that they will face day-in and day-out. This is one of the biggest mistakes I think most books make. They leave out the messy, real world stuff, instead, focusing on simple examples that are rarely realistic.
Content Must be Current: While this seems obvious, many books include old information that is no longer true. For example, don’t include recommendations based on SQL Server 6.5 if the book focuses on SQL Server 2008.
Must be Accurate: Another obvious point, but some books include obvious mistakes and provide misinformation.
Cover Available Tools: SQL Server has many tools built-in that can make development faster and easier. New developers need to learn how to best take advantage of them.
Best Practices: Transact-SQL allows you to write bad and great code. As a beginner, this is not always obvious. A great book should include recommended best practices so new developers won’t make obvious mistakes.
Holistic: SQL Server-based applications aren’t written in isolation. Because of this, the book should include information on SQL Server internals, indexing, scalability, high availability, how SQL Server best interacts with client-side application languages, the operating system, networking, and much more.

After writing the above list, I realize that it is virtually impossible to include everything I have included to fit into a single book. Instead, perhaps a series of book, each building upon the other, might be a good approach. This way, a person new to SQL Server development would properly get up to speed, putting them in the position so that they could write competent database applications, even though they may not have a lot of experience. Of course, experience is the key to writing great applications, but everybody has to start somewhere, and hopefully is not from reading a single, poorly-written book on SQL Server development.

So what do you think? What are the biggest problems you have found with books you have read that “supposedly” teach SQL Server development? Do you agree with my list, and do you have any items you would like to add to it?

Also, if you have read some books that come close to the above ideals, what are they?

Play the T-SQL Alphabet Game

Posted on January 21, 2009 by Brad

I recently had the opportunity to play the T-SQL Alphabet Game, which was introduced to me by SQL Server MVP, Rodney Landrum, the author of Pro SQL Server 2008 Reporting Services.

While I am not much of a game player myself, I can see how it can be a fun game for DBAs to play when they need a diversion from their generally stressful job duties.

On the surface, the game seems very simple to play, but looks can be deceiving. Here’s how you play. First, you need two or more players. The more players, the more fun the game can be. Second, pick someone to start the game, it doesn’t matter who it is. The first player must come up with a valid T-SQL command that begins with the letter “A”. The second player then must come up with a T-SQL command that beings with the letter “B”. The third player must come up with a T-SQL command that begins with the letter “C”, and so on, rotating through each of the letters of the alphabet. Everybody gets a turn in a round-robin fashion.

For example, a valid T-SQL command for the letter “A” might be “ABS”, for the letter “B” might be “BACKUP DATABASE”, and for the letter “C” might be “CAST”, and so on.

Each person gets 5 seconds to come up with the answer, and if they can’t come up with a valid answer, then they are out of the game. This continues until there is only one person left—the winner. If you have a smart group playing the game, you may have to cycle through the alphabet more than once until you have a winner. Of course, if you have to recycle the alphabet, you can’t recycle previous answers. All responses have to be different.

As I said earlier, this game sounds easy, but under the pressure of time, and with some letter’s that have few T-SQL commands that begin with that letter, the game can become very challenging.

If you want, you can change the rules of the game to add variety. For example, you can give 10 seconds to each player to come up with an answer, you can choose the winner by who comes up with the most correct answers within the designated time frame, or make up any variation of the game you want, assuming you set the rules before you begin the game.

So the next time you and a few of your DBA friends get together, and you want to do something really fun (geeky), then play this game, and see who really is the most technically savvy (geekest) DBA of the group.

Decoding Micro-speak

Posted on January 1, 2009 by Brad

I spend a lot of time at conferences where Microsoft employees, such as product managers and developers, make presentations. In addition, I also spend a lot of time talking with them, one-on-one. Over the years, I have noticed that Microsoft has its own set of internal buzz words. Many of these terms are used generically in the IT industry, but I don’t know if they started with Microsoft and then transitioned to the IT industry, or if Microsoft picked up this terms from the IT industry and made them their own.

However it happened, here are some of the buzz words that I hear over and over again from Microsoft employees, along with what they mean.

Ubiquitous: Commonly and widely used or available. While the use of “ubiquitous” at Microsoft is not as ubiquitous as it once was, it was one of the first terms I noticed that Microsoft used over, and over, and over.

Story: An explanation of the benefits of a particular feature, product, or service. This now appears to be the most common buzz word at Microsoft. Everything has to have a story, from a new feature in SQL Server, to the latest marketing campaign. Another variation of “story” is the buzz phrase “value proposition,” although it is less in fashion than it used to be.

Space: A market niche. “Space” has become popular throughout the IT industry, and is still used a lot at Microsoft, but its use seems to be dying out over time.

Adoption Blocker: Something that prevents a customer from buying a Microsoft product or service. Microsoft often assumes that everything they design and market should sell well. If it doesn’t sell well, then some “adoption blocker” has prevented the sales from taking place. Sometimes, I think Microsoft forgets that not everything they design and sell is necessarily what their customers really want.

Ecosystem: All of the parts that make up a larger system. For example, SQL Server has its own ecosystem, and when Microsoft evaluates a new feature to be added to SQL Server, it has to fit appropriately within the “ecosystem.”

Baked In: A feature or functionality that is included with a product. This term is becoming more popular, and I have often heard Microsoft people say that a particular feature has been baked in the product, which often implies that the customer is getting it for free (as part of a larger purchase).

Dog Fooding: We use our own products for internal use at Microsoft. In most cases, “dog fooding” refers to Microsoft using a beta or CTP version of a product internally before it is released to the public. Think of it as internal beta testing.

Conversation: A discussion. I think a “conversation” often implies that someone is asking for input and feedback, but that it doesn’t necessarily mean that they are really listening, or will take action based on the “conversation.”

Long Pole: Critical path. For example, “If we hire another two engineers, we can do tasks A and B in parallel, but C and D have to be done sequentially, so that’s our long pole.” Thanks to a former Microsoft employee for providing this definition for me.

There a lot more of these buzz words, and I don’t have the space to cover them all. What I want to hear from you is what buzz words do you commonly run across in your work, especially those buzz words that sound amusing or are way overused.

Check Out the Free Performance Analysis of Logs (PAL) Tool

Posted on December 23, 2008 by Brad

Traditionally, analyzing Performance Monitor data has not been an easy task. It often involved viewing Performance Monitor logs in graph view, seeing what was happening, and hoping that your knowledge and background in SQL Server and the Windows OS provided you with enough information in order to help you to be able to pinpoint specific performance-related issues.

Thanks to SQL Server MVP Jonathan Kehayias, I recently learned about a tool that can greatly benefit DBAs who want to automatically analyze Performance Monitor logs. The tool is called “Performance Analysis of Logs”, or “PAL” for short. It was developed by Clint Huffman of Microsoft, and can be downloaded free from Codeplex at www.codeplex.com/PAL. The tool is free and is Open Source code.

In brief, the tool takes a Performance Monitor log file (.blg), analyzes it, and provides you with an HTML report that presents a graphical analysis of the data, including alerts for those counters that exceed a specified threshold. The thresholds for the counters are based on those recommended by various Microsoft product teams and Microsoft support.

The tool includes many features, and is customizable, allowing you to configure it to meet your specific needs. While it won’t help resolve all of your performance problems, it provides a great start, and I highly recommend that all DBAs download it and give it a try.

Below is a very brief overview of the tool.

Using PAL

The first step is to create a Performance Monitor log file, as this will be the source of the data that will be analyzed by PAL. In the example below, I am using the Reliability and Performance Monitor, and I have created a data collector called the “PAL Data Collector.” You can include as many counters as you want, but PAL won’t analyze all Performance Counters, only those that it knows about. More on this later. In addition, you will want to collect data over a representative period of time, such as one hour, one day, and so on. Keep in mind that collecting Performance Monitor logs can become resource intensive if you collect a lot of counters and collect the data for long periods of time. Once you have collected data, copy that log file onto a local desktop computer, this way, when PAL is running, it won’t negatively affect the performance of the server you are analyzing.

Now, it’s time to start PAL. The tool is wizard driven, so once the Welcome screen appears, click “Next.”

The next step is to tell PAL the location of the Performance Monitor log file you want to analyze. Notice (below) that you can analyze an entire log file or a partial log file (based on time and data).

The “Threshold File” screen is probably the most important screen to complete correctly. Notice at near the top of the screen below, “Microsoft SQL Server 2005” listed next to “Threshold File Title.” This is actually a drop-down box where you can select different analysis templates.

Below is example of the analysis templates that are available. As you can see, PAL is a multipurpose tool and can be used for analyzing the performance of many different Microsoft products. Currently, there is both a Microsoft SQL Server 2000 and 2005 analysis template. Currently, there isn’t a SQL Server 2008 template, although you can use some of the templates with SQL Server 2008.

Once you have selected an analysis template, you can optionally click on the “Edit” button, and a screen similar to the one below appears. This screen lists every Performance Counter that is analyzed by this analysis template, and in addition, you have the ability to edit it. What I really find useful about this screen is that it lists all of the threshold figures used by Microsoft to determine if a particular counter is exceeding its recommended threshold or not. When you create your Performance Monitor logs, I highly suggest you collect all of the counters that are listed in the analysis template that you want to use, so you can take full advantage of the analysis ability of PAL.

Once you have selected an analysis template (SQL Server 2005 in my case), the next step is to answer some questions about your server. The questions that are presented depend on which analysis template you have selected. Below, notice that there are five questions that you need to answer. This data is used when the analysis report is created later.

Next, the wizard presents us with the Analysis Interval screen. Here, we tell PAL how we want to slice and dice the data. For example, if we want to see Performance Monitor counter analysis for every 60 second time period, or for every hour time period, we can. This way, you can see what was happening during the time interval you specify. If you choose “AUTO,” PAL will automatically slice all of your data into 30 time slices. As this is not a great choice in most cases, you will probably want to pick your own time period.

The Output Options screen (below) allows you to specify where the reports will be outputted to. You can use the default values, or change them as you desire.

The Queue screen shows you (see below) the script file that it will run in order to analyze your data. In most cases, you will just click on “Next” to continue.

Now we are ready to analyze the Performance Monitor log file. In most cases, you will want to use the default “Execute: Execute what currently is in the queue” option, and the analysis begins. If you have collected a lot of data, it might take some time for the analysis to complete.

Once the analysis is over, a single-page (and very long) HTML report is produced. The top part of the report (shown below) is a table of contents and allows you to quickly drill down into the area(s) you want to focus on. It also shows you those areas where alerts have been produced. As you might imagine, an alert is not a good thing.

If you scroll past the table of contents screen (assuming you don’t want to drill down right away), you come to the “Alert” section of the report (see below). This is one of the most powerful parts of the report. For whatever Analysis Interval you have selected, the Performance Monitor counters are summarized. For example, I set my Analysis Interval to 5 seconds, so in the figure below, you can see three 5-second intervals (the last interval is cut off). During each of these intervals you can see the alerts that were created. For example, in the interval starting at 6:26:35 PM, there were three alerts. In the next interval, there were 2 alerts, and in the last interval, 7 alerts. In other words, what PAL has done is to summarize the counters for the Analysis Interval, and based on the thresholds set inside of PAL, it has produced various Performance Monitor counter warnings, in addition to providing us with various data for each counter. If a performance counter was fine during this time period (as defined by the predefined counter thresholds, then it won’t appear in this report.

Blue alerts are informational, yellow alerts are warnings, and red alerts are critical. So if you see a lot of yellow or red alerts, this is where you want to focus your time, as these are strong indications where your server may be having a problem. In addition, you can drill down into this information by clicking on the alert (it is a hyperlink) and it will move you to the location on the page where there is detailed data about the alert that provides additional information you can use to help you identify and resolve the specific problem.

For example, let’s say that I want to find out more about the red alert for the first interval for “Page Life Expectancy < 300 Seconds,” which is shown in the above screen. To find out more about this alert, I click on the hyperlink and the following detail screen appears.

In the above detail section of the report, I can see a graph of this counter over time (in this case, just a few seconds), along with the minimum, average, maximum, hourly trend (mine is negative because I only captured a few minutes of data), and other statistical data.

If you don’t know what this counter means to you as a DBA, there is an explanation at the top of the screen that tells you that if this value is below 300 (which it is), then adding more memory to your SQL Server instance would most likely boost performance. Each alert in the report provides similar information, providing you with a “short course” in Performance Monitor counter analysis.

Summary

PAL can take a Performance Monitor log file, analyze it, and highlight those counters that exceed commonly accepted thresholds, helping you to determine what, if any, problems your server is experiencing. This information, along with other information you gather, can be used to help you determine what is happening with your server, and hopefully, providing you with enough information to correct it.

This short blog entry just shows you a very small part of what PAL can do for you. Hopefully, you see the potential power this tool has, and you will download it and give it a try.

Is Your Internet Activity Hurting Your DBA Career?

Posted on December 17, 2008 by Brad

I have been active on the Internet for 15 years, and during that time I have left a long trail behind me. If you Google my name, there are over 5,000 hits. If you check my name in Live Search, there are over 8,000 hits. If you wanted to, you could trace much of my online activity over the years with simple searches. And if you were really clever, you could visit the Wayback Machine and find all about me on archived web pages that don’t currently exist on the Internet. My life is an open book.

Do you know what? More and more employers are using Internet search to find out all about you before they hire you. So if a prospective employer enters your name into a search engine, what will they find?

Will they find information that strongly points to your professionalism, providing a strong indication that you are they type of person they can trust once they hire you? Or will they find information that will strongly discourage them from hiring you?

According to a recent Careerbuilder.Com survey, 22% of hiring managers say that they actively use the Internet to research prospective job candidates. Based on my experience, I think that number is low. I think it is closer to 40-50%. In fact, I talked to a hiring manager just this past week, and he does regular searches on job applicants. And what was even more interesting was just that same day he had decided not to hire someone because of posts he read on an applicant’s Facebook page.

So what does this mean to your career? It means that if you aren’t careful how you portray yourself on the Internet, your career could be negatively impacted.

If you are serious about being a professional DBA, and want to continue to pursue this profession as a career, I suggest the following:

–Conduct your own Internet searches (using several search engines) on your name to see what pops up. If you have a common name, then you may have to add additional search criteria to ensure that the search hits you get back are directly related to you. Another way to help restrict your searches is to use quote marks around your full name. For example, in my case, I would search on “Brad McGehee” (including the quote marks as you see here).

–Check out all of the web pages where you are mentioned and evaluate the content. Does it reflect you in a positive light, or negatively? In other words, if a hiring manager saw these same web pages, is there any content that could portray you in anything less than a positive way? While those photos of you partying with your buddies on Flickr might seem funny to you, they won’t impress most hiring managers. Or if you participate in unfriendly flame wars in forums or newsgroups, the same applies.

–If you find content that is negative, you have three major options:

In many cases, you are probably the source of the negative content (such as Facebook, MySpace, or Flickr pages), and because of this, you have control over the content and can clean it up.
Second, if you don’t have control over the content (for example, a friend has posted a photo of you drinking at a party), and you know the person who controls the content, ask them to remove it.
Third, if you can’t get the negative content removed, one option is to “remake” yourself on the Internet and hope that the negative content goes away. By this, I mean you want the negative content to sink so low in Internet search results that nobody will ever be able to find it. How do you do this? Start by creating new content, such as joining a professional social networking group, such as Plaxo or LinkedIn (assuming you haven’t done so already), contributing to professional forums (and using a signature tag so people can find the posts), writing a professional blog, writing articles for professional websites, and so on. In other words, add so much positive content to the Internet that it overwhelms any negative content that you don’t control.

In my next blog posting, I will offer some specific dos and don’ts when it comes to “acting professional” on the Internet.

PS: Ah, you maybe thinking, nobody can find out about my Internet antics because I use an “alias,” so nobody can find out who I really am. Forget that. Unless you are an expert at hiding your identify, using an “alias” doesn’t offer much protection. In fact, for the fun of it, I checked out the “aliases” of some of my acquaintances, and it wasn’t very difficult for me to find out who they really were. Or, you may tell me, “only my friends have access to my personal content, because I don’t make my profile on social networking sites public.” While this may help, remember that anything you put on the Internet is not protected. It is very easy for one of your friends to copy your “amusing” photo and publish on a non-protected web page. So don’t assume that the “superficial privacy” offered by most social network sites will be as effective as you might think. Once something is published on the Internet, privacy goes out the door.

Dos and Don'ts for Projecting a Professional Image on the Internet

Posted on December 17, 2008 by Brad

In the past, I have suggested that less than professional content, that you or others post on the Internet about yourself, could have a negative effect on your career as a DBA. In this post, I offer suggestions on how to project a professional image on the Internet.

- Do separate your professional life and personal life from one another on the Internet. For example, if you have a blog, don’t include both professional and personal posts. Instead, create two separate blogs. If you want to share personal content with friends and family, only do so on websites that offer some degree of security, and only allow friends and family to access it. But keep in mind that there is no such thing as absolute security and privacy on the Internet, so still don’t include content that you wouldn’t want your mother or a potential employer to ever see.
- Do use your full name whenever you post professional content (forums, newsgroups, blogs, articles, etc.) on the Internet. You want people to be able to easily find you, and the best way to do this is to use your full name as your “personal brand.”
- Don’t use a “cute, clever, or funny” alias (user name) to refer to yourself professionally. “SQLSanta,” or similar aliases, no matter how amusing, do not make a good impression.
- Don’t post pictures or videos of yourself (or others) that depict anything that could be construed as anything less than professional.
- Do post professionally-taken portraits so that others can see what you look like.
- Don’t share links to URLs that include potentially offensive content.
- Do create profiles at professional social networking websites, such as Plaxo.com, LinkedIn.com, SQLPass.org, Google Profiles, and similar websites, and keep them up-to-date. You want people to know about your knowledge and experience, and to be able to easily find you. You don’t want to miss out on a potentially lucrative job opportunity.
- Do keep an updated resume on a job website, website, or professional blog if you are looking for a job.
- Don’t keep a resume on a job website, website, or professional blog if you aren’t looking for a new job (otherwise you might be).
- Don’t embellish the truth when completing professional profiles, biographies, or when creating resumes. The truth will always catch up with you.
- Do help others by participating in professional forums, newsgroups, blogs, and other websites where you have the opportunity share your knowledge.
- Do keep all forum or newsgroup postings professional, culturally sensitive, considerate, and helpful.
- Don’t engage in forum or newsgroup flame wars.
- Don’t use “adult” language anytime, anywhere.
- Don’t disparage others. If you don’t like someone, ignore them instead.
- Don’t write anything on the Internet that you don’t want your current employer to see. For example, don’t criticize or ridicule them, or share confidential information about your organization.
- Do assume that anything you post on the Internet will be seen by everyone, and for a long time to come. Once you post content on the Internet, you loose control over it.

Yes, this list may be very conservative, but so are many hiring managers. If you want to maximize your future career opportunities, you want to project your best possible image.

If you have any suggestions for additions to this list, please offer them.

Brad M McGehee

SQL Server DBA

Author Archives: Brad