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

Monthly Archives: December 2008