Mike McIntyre's Blog

Troubleshooting MOSS/WSS Performance Issues

One of the things that I find myself doing more and more of is troubleshooting performance related issues for SharePoint. We’re at the point in the lifecycle of the product where people have it installed, it’s working (for the most part) and they want it to crawl/search/render faster.

Performance issues, by their very nature, are not (typically) resolved quickly. The process usually involves the gathering of large amounts of disparate data, correlating it, and looking for patterns and anomalies. Strangely, one of our biggest challenges in support is collecting the data in a consistent manner for analysis.

Over the last year, or so, I’ve come up with a set of instructions that I feel will help streamline not only the collection of the data for analysis by Microsoft but should also serve as a best practice for customers who wish to monitor and tune their own performance. I’ve broken this out as follows:

• Web Front Ends/Query Servers
• Index Server(s)
• SQL Server(s)
• IIS Log Data
• ULS Log Data
• Event Log Data
• Naming conventions to use

Web Front Ends/Query Servers

Frequency: Daily

Detail:

1. Please ensure that the instructions in http://support.microsoft.com/kb/281884 are followed. This will allow for the Process ID value to be included as part of the process monitor data that is being captured.

2. The following counters need to be collected on all web front end and query servers on a daily basis:

• Web Front End/Query Servers (WFECounterList_MOSS2007.txt)
  • \ASP.NET(*)\*
  • \ASP.NET v2.0.50727\*
  • \ASP.NET Apps v2.0.50727(*)\*
  • \.NET CLR Networking(*)\*
  • \.NET CLR Memory(*)\*
  • \.NET CLR Exception(*)\*
  • \.NET CLR Loading(*)\*
  • \.NET Data Provider for SqlServer(*)\*
  • \Processor(*)\*
  • \Process(*)\*
  • \LogicalDisk(*)\*
  • \Memory\*
  • \PhysicalDisk(*)\*
  • \SharePoint Publishing Cache(*)\*
  • \System\*
  • \Web Service(*)\*
  • \Web Service Cache\*
• Index Servers (IDXCounterList_MOSS2007.txt)
  • \ASP.NET(*)\*
  • \ASP.NET v2.0.50727\*
  • \ASP.NET Apps v2.0.50727(*)\*
  • \.NET CLR Networking(*)\*
  • \.NET CLR Memory(*)\*
  • \.NET CLR Exception(*)\*
  • \.NET CLR Loading(*)\*
  • \.NET Data Provider for SqlServer(*)\*
  • \Processor(*)\*
  • \Process(*)\*
  • \LogicalDisk(*)\*
  • \Memory\*
  • \PhysicalDisk(*)\*
  • \SharePoint Publishing Cache(*)\*
  • \Web Service(*)\*
  • \Web Service Cache\*
  • \SharePoint Search Archival Plugin(*)\*
  • \SharePoint Search Gatherer\*
  • \SharePoint Search Gatherer Project(*)\*
  • \SharePoint Search Indexer Catalogs(*)\*
  • \SharePoint Search Schema Plugin(*)\*
  • \System\*
• SQL Servers (SQLCounterList_MOSS2007.txt)
  • \.NET Data Provider for SqlServer(*)\*
  • \Processor(*)\*
  • \Process(*)\*
  • \LogicalDisk(*)\*
  • \Memory\*
  • \PhysicalDisk(*)\*
  • \Network Interface(*)\*
  • \NBT Connection(*)\*
  • \Server Work Queues(*)\*
  • \Server\*
  • \SQLServer:Access Methods\*
  • \SQLServer:Catalog Metadata(*)\*
  • \SQLServer:Exec Statistics(*)\*
  • \SQLServer:Wait Statistics(*)\*
  • \SQLServer:Broker Activation(*)\*
  • \SQLServer:Broker/DBM Transport\*
  • \SQLServer:Broker Statistics\*
  • \SQLServer:Transactions\*
  • \SQLAgent:JobSteps(*)\*
  • \SQLServer:Memory Manager\*
  • \SQLServer:Cursor Manager By Type(*)\*
  • \SQLServer:Plan Cache(*)\*
  • \SQLServer:SQL Statistics\*
  • \SQLServer:SQL Errors(*)\*
  • \SQLServer:Databases(*)\*
  • \SQLServer:Locks(*)\*
  • \SQLServer:General Statistics\*
  • \SQLServer:Latches\*
  • \System\*

3. Next, you will want to create text file that contains your counters. I suggest the following:

• Web Front Ends Server(s)/Query Server - WFECounterList_MOSS2007.txt
• Index Server(s) – IDXCounterList_MOSS2007.txt
• SQL Server(s) – SQLCounterList_MOSS2007.txt

4. From a command line, issue the following command on every web front end in your farm. You will need to change the drive letters & locations, as well as the counter list being used, so that they are appropriate for your environment. Please use an account that has administrative access to the server in order to ensure that we have access to the appropriate counters. Substitute the domain\userid in the appropriate section of the command below. Also, you will be prompted for the password; so, do not enter it on the command line.

The command should resemble the following:

Baseline counter	logman create counter Baseline -s %COMPUTERNAME% -o C:\PerfLogs\Baseline_%COMPUTERNAME%.blg -f bin -v mmddhhmm -cf [WFE|IDX|SQL]CounterList_MOSS2007.txt -si 00:01:00 -cnf 12:00:00 -b mm/dd/yyyy hh:mm AM -u "domain\userid" *
Incident counter	logman create counter Incident -s %COMPUTERNAME% -o C:\PerfLogs\Incident_%COMPUTERNAME%.blg -f bin -v mmddhhmm –cf [WFE|IDX|SQL]CounterList_MOSS2007.txt -si 00:00:05 -max 500 -u "domain\userid" *

5.  We have defined two (2) sets of counters to be used: Baseline & Incident. The baseline counter should be running 24x7 in the environment. This will allow us to capture baseline performance data so that we have something against which to evaluate any changes. For the purposes of illustration, here is the command to begin collecting data on 08/18/2009 @ 6:00 am in the morning. We will capture 12-hours of data, and then rotate the logs.

logman create counter Baseline -s %COMPUTERNAME% -o C:\PerfLogs\Baseline_%COMPUTERNAME%.blg -f bin -v mmddhhmm -cf WFECounterList_MOSS2007.txt -si 00:01:00 -cnf 12:00:00 -b 8/18/2009 6:00 AM -u "domain\userid" *

When an incident occurs, we will need to capture more fine-grained data. For that purpose, we have defined an Incident-based counter to be used. This is identical to the baseline counter except that it samples more frequently. Due to the increased sample rate, it will collect more data faster than the baseline counter. You need to be diligent with respect to maintaining sufficient disk space to accommodate incident-based captures. This counter set should only be used during an incident/outage and only for the minimal time necessary. Once the incident-based data has been collected, you may turn off the incident counters. However, the baseline counters should continue to be run 24x7 until further notice.

In our example above, the sample rate for the Incident counter has been set to every 5 seconds. This is by no means an absolute value and may need to be adjusted depending upon the particular type of performance problem that you are troubleshooting.

To start the incident based counter, issue the following command from a command line prompt:

logman start Incident -s %COMPUTERNAME%.

If you are starting the counter local to the machine in question, %COMPUTERNAME% will expand to the appropriate value; otherwise, you will need to specify the NetBIOS name of the computer.

6. Assuming a data upload time of 6:00pm (or later), you may have up to three (3) files containing performance data:

• Baseline_%COMPUTERNAME%_mmddhhmm_001.blg (Yesterday: 6p-6a)
• Baseline_%COMPUTERNAME%_mmddhhmm_002.blg (Today: 6a-6p)
• Baseline_%COMPUTERNAME%_mmddhhmm_003.blg (Today[new]: 6p-6a)

IIS Log Data Collection

Frequency: Daily

Detail:

On each of the web front ends/query servers, we will want to capture IIS traffic so that we can do a thorough traffic analysis. Our intent is to correlate the IIS traffic with what we’re seeing in the performance data.

Windows 2003

1. Start the Internet Information Services (IIS) Manager on each machine. The easiest way to accomplish this via the applet in the a Administrative Tools section of the Start menu

2. Open the tree up (on the left hand side) until Web SItes is available. Select and right-click to bring up the Properties pane. This should apply the settings for all of the web sites defined underneath

3. Verify that Logging is enabled as well as the w3c Extended Log File Format is specified

4. Click on the Properties... button to get to the detail

5. Click on the Advanced tab and make sure that ALL properties are selected. If you would like to see what each of these fields means, check out the following: W3C Extended Log File Format (IIS 6.0)

6. Once all have been selected, Apply and exit out of the IIS Configuration.

7. At this point, perform an IISReset on each machine. This will force the new log settings to be picked up. I would suggest you visually verify (examine the newest logfile) to make sure that all fields are now being tracked.

Windows 2008

1. Start the Internet Information Services (IIS) manager on each machine. The easiest way to accomplish this is via the applet in the Administrative Tools section of the Start menu

2. Click on the Machine node in the tree (left hand side). This will bring up the area view in the right hand side. Your display should similar to the following:

3. You will want to double click on the Logging icon to in the IIS section (see diagram above). This will open up the logging options screen

4. Click on the Select Fields button for the format. Make sure that the Format is set to the default value of W3C. Select all fields to be logged and then click on the OK button at the bottom of the dialog box

5. Exit out of the IIS Manager at this point

6. I would recommend an IISReset on each machine where this change was made. This will force the new log settings to be picked up. I would suggest you visually verify (examine the newest logfile) to make sure that all fields are now being tracked.

ULS Log Data Collection

Frequency: Daily

Detail:

1. From central administration server (TDCUKMWS01), the following series of commands will need to be executed from a DOS command prompt window. It is assumed that MOSS/WSS has been installed in the default location.

• cd /d %commonprogramfiles%\microsoft shared\web server extensions\12\bin
• stsadm -o setlogginglevel -default

2. You should see the following messages appear in the command prompt window

• Updating DiagnosticsService
• Updating SPDiagnosticsService
• Operation completed successfully

3. You will need to verify that the Tracing Service is active and collecting valid data. From the ULS Log folder, scan the directory and ensure the newest 5 files are >10 kilobytes. If they are less, open the newest file and ensure that the only message(s) are not, “Tracing Service list trace events. Current value –n.” If this message appears, restart the tracing service by launching a command prompt on the server and typing ‘net stop sptrace’, ensuring the following messages appear:

• The Windows SharePoint Services Tracing service is stopping.
• The Windows SharePoint Services Tracing service was stopped successfully.

Once the service is successfully stopped, at the prompt, type ‘net start sptrace’, ensuring the following messages appear:

• The Windows SharePoint Services Tracing service is starting.
• The Windows SharePoint Services Tracing service was started successfully.

4. Review the newest ULS log, ensuring tracing events are successfully written to the log.

Event Log Data Collection

Frequency: Daily

Detail:

1. On a daily basis, we will need the application & system event logs for the past 24 hours. The simplest way to accomplish this is to use the eventquery.vbs applet that is part of the Operating System on Windows 2003. It is located in %windir%\system32\eventquery.vbs

2. Assuming that the date in question is 04/20/2009, the following command will produce the output that is desired:

• %windir%/system32/cscript.exe /nologo %windir%\system32\eventquery.vbs /L Application /V /FI "Datetime eq 04/20/09,12:00:00AM-04/20/09,11:59:59PM" /FO CSV > 042009_Application_%COMPUTERNAME%.csv
• %windir%/system32/cscript.exe /nologo %windir%\system32\eventquery.vbs /L System /V /FI "Datetime eq 04/20/09,12:00:00AM-04/20/09,11:59:59PM" /FO CSV > 042009_System_%COMPUTERNAME%.csv

If the eventquery.vbs is not an option for you, the same thing can be accomplished via the UI as follows:

1) Open the Event View applet and select the appropriate log from the list on the left hand side. In the example show, we will be selecting the Application log; however, the steps are generic enough that they can be applied to any event log.

2) From the menu across the top, select the Filter option

3) We will be selecting the Events On for both the From: and To: portion of the filter. Please modify the dates and time to include the previous 24-hour time period. For example, if today is 04/23/2009, we would see the values as seen below. This will result in all data from the previous day being captured

4) Once the filter criteria has been established, click on the OK button. This will return us to the Event Viewer application. From here, make certain that the Application Log is selected. Right-click and select the Export List... option

5) Export the file in a CSV format using the naming convention: ApplicationLog_%COMPUTERNAME%_MMDDYY.csv

Naming Conventions to Use

This data should be compressed (either winrar or winzip) and conform to the following naming standards:

• IIS Logs: IIS_%COMPUTERNAME%_MMDDYY.[ZIP|CAB|RAR]
• Application logs: EventLogs_%COMPUTERNAME%_MMDDYY.[ZIP|CAB|RAR]
• ULS Logs: ULSLogs_%COMPUTERNAME%_MMDDYY. [ZIP|CAB|RAR]
• Performance Logs: Perf_%COMPUTERNAME%_MMDDYY.[ZIP|CAB|RAR]
• Misc Logs: Misc_%COMPUTERNAME%_MMDDYY.[ZIP|CAB|RAR]

DO NOT attempt to compress the files on the same machine that is responsible for gathering the data. This places an undue burden on the machine and only serves to skew the performance data that is being gathered. We suggest that you perform your compressions & uploads from a machine that is not being actively monitored.

Summary

Is this the only way to collect this data? Absolutely not! What I have tried to do here is bring some consistency and uniformity of process to our problem domain. As I said, troubleshooting performance issues is arduous work. Wherever you can simplify tasks, go for it. Hopefully, this will simplify the data collection portion. The fun part is still left to you!

Enjoy!

Analyzing IIS Log files using Log Parser – Part 1

Working with SharePoint, I often find myself in situation where I need to analyze IIS log files in a variety of ways to understand what kind of load is being placed on the system. In an effort to make this easier for me, and others, I have tried to automate, script, etc., wherever possible.

The first pass that I typically run on the IIS data is to slice the IIS traffic by hour. IIS Logs files record their time in UTC; however, most people live/operate in a different time zone. In order to make the results more reflective of the actual user environment, you will need to adjust the times, either forward or backwards relative to UTC, to compensate for your particular environment.

The Scripts

For ease of use, I typically put my Log Parser scripts into a file and then reference them via the command line. This has the added advantage of allowing me to pass in arguments from the command line. As I mentioned before, I have two scripts – one for negative offset to UTC and the other for positive offset to UTC.

HitsByHourMinusGMT.sql

  1: SELECT 
  2:     SUB(QUANTIZE(TO_TIMESTAMP(date, time), 3600), TIMESTAMP(‘%tzoffset%’,’hh:mm’)) AS Hour, 
  3:     COUNT(*) AS Hits 
  4: INTO 
  5:     %destination% FROM %source% 
  6: GROUP BY 
  7:     Hour 
  8: ORDER BY 
  9:     Hour ASC

HitsByHourPlusGMT.sql

  1: SELECT 
  2:     ADD(QUANTIZE(TO_TIMESTAMP(date, time), 3600), TIMESTAMP(‘%tzoffset%’,’hh:mm’)) AS Hour, 
  3:     COUNT(*) AS Hits 
  4: INTO 
  5:     %destination% FROM %source% 
  6: GROUP BY 
  7:     Hour 
  8: ORDER BY 
  9:     Hour ASC

Traffic.cmd

  1: @if /i "%DEBUG%" neq "yes" echo off 
  2: REM 
  3: REM Generate data for Traffic analysis 
  4: REM 
  5: @SET FileList=090715,090716
  6: 
  7: @SET ServerList=1,2,3,4 
  8: 
  9: @ECHO Initializing... 
 10: FOR %%i IN (%ServerList%) DO @IF EXIST %%i.csv DEL %%i.csv /F /Q 
 11: 
 12: @ECHO Generating Traffic Analysis For Servers 
 13: FOR %%i IN (%ServerList%) DO @IF EXIST data\*%%i*.log LOGPARSER file:HitsByHourMinusGMT.sql?source=data\*%%i*.log+destination=%%i.csv+tzoffset="07:00" -i:iisw3c -o:csv 
 14: 
 15: @SET FileList= 
 16: @SET ServerList=
 17: 

The data

In order for all of this to work properly, we have to assume a minimum amount of IIS log fields have been captured. Typically, I will recommend that they all be captured. That gives you the most flexibility when it comes to parsing. Also, when I do this, I’m usually analyzing multiple machine simultaneously. In order to do that efficiently, there has to be some type of naming scheme in use to differentiate the various log files from one another. It may be as simple as adding the server name/number to the beginning/end of the filename. In our example, we have the following:

• ex0907151.log
• ex0907152.log
• ex0907153.log
• ex0907154.log

Let’s break this name down just a bit.

• 090715 – yymmdd that the logfile as captured
• 1,2,3,4 – indicates server1, server2, server3, and server4. It could have just as easily been the server name

The command line

For the purposes of this example, I’m going to assume that the servers we’re getting data from are located in Redmond, WA (where else :-)). That is an offset of UTC-7 hours. Also, to make things a bit easier, I want to generate a file per machine. So, the command would resemble the following:

logparser file:HitsByHourMinusGMT.sql?source=ex0907151.log+destination=1.csv+tzoffset=”07:00” –i:IISW3C –o:csv

logparser file:HitsByHourMinusGMT.sql?source=ex0907152.log+destination=2.csv+tzoffset=”07:00” –i:IISW3C –o:csv

logparser file:HitsByHourMinusGMT.sql?source=ex0907153.log+destination=3.csv+tzoffset=”07:00” –i:IISW3C –o:csv

logparser file:HitsByHourMinusGMT.sql?source=ex0907154.log+destination=4.csv+tzoffset=”07:00” –i:IISW3C –o:csv

This will create 4 csv files that contain data for each server. What if we had a lot of days and multiple servers? This approach would get a bit tiresome. Let’s leverage the command line a little bit more to do the heavy lifting.

I have multiple days worth of data in a folder called, appropriately enough, data.

By modifying the FileList variable in the Traffic.cmd script, I can account for all them. Now, if I simply run the Traffic.cmd script from the command line, it generates a csv file per server. The output should look something similar to the following:

If we look at the contents of one of these files, we see the following:

This is a breakdown, by hour, of the number of hits that this server received. A hit is defined as anything that generates a line in the IIS Log file.

Now what?

Well, we’ve got all these raw numbers that we need to do something with. Let’s pull them into Excel and graph them to see if we can see anything that interests us.

Let’s create a simple spreadsheet that contains a summary of all of the data. I like to break it out by server, by date, etc.

As you can see, down the first column are the times (localized to the server timezone), and then the hits/hour for each day for each server. So now what? This is good information (I think) but what do I do with it? Well, you use Excel to do what Excel does best – you create some graphs so that you can see patterns in the data. In this case, the graphing could not be simpler. We are simply going to create line graphs per server/per day.

Let’s talk about some of things that these graphs are telling us:

• On 8-Jul-09, Server 01 was bearing most of the load peaking at close to 900K hits/hour. This load appears to be fairly well distributed across the normal business hours of the day and represents what you would expect to see.
• Assuming that the farm in question is well balanced, you would expect to see similar traffic across each node throughout the course of the day. That is not what you are seeing. Clearly, on 8-Jul-09, Server 01 & Server 02 are shouldering most of the traffic. On 15-Jul-09, Server 04 is getting a large spike in traffic in the early morning hours before leveling out for the remainder of the day. The farm appears to be better balanced; however, we still have some unexplained spiking that bears further investigation

Another view that I find useful is to look at the aggregate traffic per day per hour across the farm to get an idea of what daily traffic patterns look like. All things being equal, I would expect to see roughly the same type of pattern during weekdays, a decline during the weekend, etc.

 

 

 

 

 

There are several notable areas of interest that warrant further investigation

1. Why do we see such a massive peak here? It’s not only uncharacteristic but it is also, presumably, in the early, pre-business hours. What is generating such a heavy load on the farm?
2. Again, we have another uncharacteristic peak during the middle of the day. Normally, this wouldn’t cause too much concern; however, when you look at compared against the other days, it really stands out. Why? Did the server experience larger than expected traffic? Did it go live on that day?
3. If we look at these days together, the pattern looks normal. A gentle rise, levels off for a bit, and then declines. This is the pattern I would expect to see during a normal business day. However, this traffic begins to increase around 3:30am. levels off about 6:30am and then starts to decline around 1:00pm. This is an atypical traffic pattern as compared to the other days

Something to keep in mind while you’re analyzing this type of data is patterns. Patterns are very difficult to discern from a small dataset; so, the more data you have to analyze/graph, the better.

In the next installment, I will take a look at dissecting the IIS logs by unique users/IP addresses to see who is doing what where.

Enjoy!

Mine the ULS logs for query latency - Redux

I was reading a blog posting from Dan Blood, Sr. Test Engineer at Microsoft, on how to mine the ULS logs for query latency. If you haven’t read the post yet, I encourage you to do so. It has a wealth of information in it.

I was recently working a similar issue where I needed to do this exact thing and I discovered a couple of improvements that I’d like to share on the process that Dan has put together. I think it will help simplify.

When running Dan’s script against a sizeable amount of ULS data, I found that it produced a tremendous amount of data. Later in the analysis portion, that data will be grouped by hour; so, I decided to be a bit proactive and do it up front. Here’s the modified logparser script that I used.

ULSQueryLatency.sql

  1: -- 
  2: -- logparser file:ULSQueryLatency.sql?source=*.log+destination=Latency.csv -i:TSV -oCodepage:-1 -iTsFormat:"MM/dd/yyyy hh:mm:ss.ll" -fixedSep:ON -o:csv 
  3: -- 
  4: -- To restrict to start/end hours of the day, uncomment the 
  5: -- QUANTIZE statements in the WHERE clause and 
  6: -- pass in the startofday and endofday value. For example, 
  7: -- 
  8: -- logparser file:ULSQueryLatency.sql?source=*.log+destination=Latency.csv+startofday="08:00:00"+endofday="18:00:00" -i:TSV -oCodepage:-1 -iTsFormat:"MM/dd/yyyy hh:mm:ss.ll" -fixedSep:ON -o:csv 
  9: -- 
 10: -- 
 11: SELECT 
 12:     QUANTIZE(Timestamp, 3600) AS Hour, 
 13:     AVG(TO_REAL(Extract_token(Message, 7, ' '))) AS TotalQPTime, 
 14:     AVG(TO_REAL(Extract_token(Message, 8, ' '))) AS v2, 
 15:     AVG(TO_REAL(Extract_token(Message, 9, ' '))) AS v3, 
 16:     AVG(TO_REAL(Extract_token(Message,10, ' '))) AS v4, 
 17:     AVG(TO_REAL(Extract_token(Message,11, ' '))) AS TimeSpentInIndex, 
 18:     SUM(TO_REAL(Extract_token(Message,12, ' '))) AS v6, 
 19:     SUB(v4, TimeSpentInIndex) AS JoinTime, 
 20:     SUB(v3, v4) AS SecurityTrimmingTime, 
 21:     CASE v2 
 22:         WHEN 0.0 
 23:             THEN 0.0 
 24:         ELSE SUB(v2, v3) 
 25:    END AS DuplicateDetectionTime, 
 26:     SUB(TotalQPTime, v2) AS FetchTime 
 27: FROM 
 28:     %source% TO %destination% 
 29: WHERE 
 30:     Category = 'MS Search Query Processor' AND 
 31:     Message LIKE '%Completed query execution with timings:%' 
 32: --    AND 
 33: --    ( 
 34: --        QUANTIZE(TO_TIME(Timestamp), 3600) >= TIMESTAMP('%startofday%', 'hh:mm:ss') AND 
 35: --        QUANTIZE(TO_TIME(Timestamp), 3600) <= TIMESTAMP('%endofday%',   'hh:mm:ss') 
 36: --    ) 
 37: GROUP BY Hour

 

Now, in my particular situation I needed to run this against several servers and report on their query latency individually. If this isn’t a requirement for you, then no need to spend the additional time.

Being the lazy sort that I am, I put together a little command script to handle the heavy lifting. The following assumptions are made:

• ULS logs are located in a folder and are in the standard named form of %COMPUTERNAME%-yyyymmdd-hhmm.log
• I have placed my logparser scripts in a folder that is referenced via the %LPQ% environment variable

SearchLatency.cmd

  1: @if /i "%DEBUG%" neq "yes" echo off 
  2: REM 
  3: REM Generate data for Search Latency analysis 
  4: REM 
  5: @SET ServerList=SERVER01,SERVER03,SERVER04,SERVER05 
  6: 
  7: @ECHO Initializing... 
  8: FOR %%i IN (%ServerList%) DO @IF EXIST %%i-SearchLatency.csv DEL %%i-SearchLatency.csv /F /Q 
  9: @IF EXIST SearchResultsDetail.csv DEL SearchResultsDetail.csv /F /Q 
 10: 
 11: @IF EXIST data\*.LOG LOGPARSER file:%lpq%\SearchResultsDetail.sql?source=data\*.log+destination=SearchResultsDetail.csv -i:iisw3c -o:csv 
 12: 
 13: FOR %%i IN (%ServerList%) DO @IF EXIST ULSLogs\%%i*.log LOGPARSER file:%lpq%\ULSQueryLatency.sql?source=ULSLogs\%%i*.log+destination=%%i-SearchLatency.csv -i:TSV -iCodepage:-1 -iTsFormat:"MM/dd/yyyy hh:mm:ss.ll" -fixedSep:ON -o:csv 
 14: 
 15: @SET FileList= 
 16: @SET NumberOfSteps= 
 17: 
 18: @ECHO Complete!

This will produce a series of csv files, one per server, which can be easily imported into Excel. They should look something like the following:

You will note that it is grouped by hour instead of by data point as Dan’s script does. This allows me to handle larger data sets and generate a consistent amount of output data. Since we were going to end up grouping by hour in Excel, I decided to do it here instead :-).

How do I generate the pretty graph?

So, we’ve got the csv file…what do we do now? This is where the fun comes. We’re going to construct a pivot chart like the one Dan does. Since I had to figure out how to do this, I thought I’d share the process.

1. After importing the csv file into Excel, I typically will convert it to a table so that I can reference it by name as well as making it easier to read.

2. Next, let’s convert the Hour column into a more pleasing format. We do this by selecting the column and picking a custom format:
        

3. Next, we’re going to create a PivotChart

 

Let’s go ahead and let it go to a new worksheet. This will give us room to work.

4. We’re going to drag the following fields to the Pivot

• Values
  • TimeSpentInIndex
  • JoinTime
  • SecurityTrimmingTime
  • DuplicateDetectionTime
  • FetchTime
• Axis Fields (Categories)
  • Hour

When you’re done, it should look something like this:

 

It should have also created a graph that looks like this (not quite what we’re after… yet)

5. Next, we want to change up the grouping just a bit so that we can see by day/by hour detail. So, from the Pivot Table data, we’re going to select the Hour data and right-click to group the data.

6. Next, we’re going to group by Days and Hours. This will change our our display and our graph slightly

7. Next, let’s change the chart type from line to stacked bar. This should give us the following

8. Next, let’s modify the legends to Avg instead of Sum. Also, we’re going to move the legends below the graph for easier viewing, and fix it up with some titles. What we’re left with is a lot more readable :-)

How to quickly compare file version information across your MOSS farm

6/1/2009, 5:10 PM

I often find myself in a situation where I need to quickly compare multiple servers to determine if the same version of the product has been installed on all machines. The easiest way to do this, at least for me, involves:

1. SPSReport (http://www.codeplex.com/spsreport)
2. Log Parser (http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en)
3. Excel pivot table

First, you want to obtain an SPSReport (LITE) from all of the servers in question. You are primarily concerned with the following files:

• %COMPUTERNAME%_WSEVer.txt (WSS)
• %COMPUTERNAME%_MOSSVer.txt (MOSS)
• %COMPUTERNAME%_GacVer.txt (GAC)

Using the following Log Parser script and command, I am able to quickly generate version data for each:

  1: --
  2: -- Logparser file:SPSReportFilever.sql?source=Server1_WSEVer.txt,Server2_WSEVer.txt+destination=WSEVer.csv -i:TEXTLINE -o:CSV
  3: --
  4: SELECT
  5: 	SUBSTR(TEXT, 22, 14) AS FileVersion,
  6: 	LTRIM(SUBSTR(TEXT, 41, 10)) AS FileSize,
  7: 	SUBSTR(TEXT, 52, 10) AS FileDate,
  8: 	SUBSTR(TEXT, 63) AS FileName,
  9: 	EXTRACT_TOKEN(EXTRACT_TOKEN(EXTRACT_FILENAME(LogFilename),0,'.'),0,'_') AS ComputerName
 10: FROM
 11: 	%source% TO %destination%
 12: WHERE
 13: 	INDEX_OF(TEXT,'-') = 0

Save the above script in a file. Let's call it SPSReportFilever.sql. You will want to execute the following command from a DOS prompt for each grouping: WSS, MOSS, and GAC:

Logparser file:SPSReportFilever.sql?source=Server1_WSEVer.txt,Server2_WSEVer.txt+destination=WSEVer.txt -i:TEXTLINE -o:CSV

This, in turn, will produce a csv file with the following content:



This will allow us to easily import this into an Excel spreadsheet and then perform a simple pivot operation.

1. Import the csv into excel 

   

2. Now, let's Format as Table to make it easier to view, sort, etc.

   

3. Now, we're going to create a pivot table to arrange the data into an even easier format to see what versions are installed where
   1. Create PivotTable

   

   1. Next drag the fields according to the following
   2. This gives us a pivot table that looks like the following. However, we still have some slight adjustments to make to it
   3. Next, we want to add some additional detail. Double-click on the one of the FileNames on the right. A dialog will appear asking what detail you would like to show. Select FileVersion and hit OK
   4. This results in a change on the left side. Now, all of the FileNames have a version number under each of them. Now, let's make a couple of adjustments to make things easier to view
      1. Remove the Grand Total column
   5. Freeze Panes to allow us to see the server name and file name
   6. Now, let's expand one of the File Names. In our example, we choose microsoft.office.excel.server.webservices.dll. We see the following:

   This tells us that the same version of this dll is installed on all of servers in our farm. What if it had not been? What would that look like?

   1. As can be seen from the same procedure on the MOSS file versions, we have a situation where one of the servers did not receive the appropriate updates

   As we see here, SERVER2 is running a version of mssrch.dll that is significantly behind the version that is running on all of the other machines in the farm. Further investigation reveals that the SERVER2 did not have the MOSS April 2009 CU applied to it while other machines in the farm DID have the April 2009 CU applied. We applied the MOSS April 2009 CU to SERVER2 and our issues were resolved

Enumerating items counts in lists

Recently, we found ourselves in a situation where we suspected that the customer had exceeded our capacity planning guidelines for the number of items in a document library; however, how do we go about verifying that? Well, the steps below will walk you through how to enumerate the counts for the different folders. You have the ability to set a threshold value and report on everything that exceeds that threshold.

Enjoy!

1. Connect to the SQL server via Microsoft SQL Server Management Studio (SQL 2005) or Query Analyzer (SQL 2000)

   

2. Once connected, open a query window

   

3. Once you have a query window open, make sure the master database is selected:

   

4. Once you have a query window open, paste the following SQL query:

     1: create table ##Largelists
     2: (
     3:     [listID] uniqueidentifier NULL,
     4:     [siteID] uniqueidentifier null,
     5:     [webid] uniqueidentifier null,
     6:     [counts] bigint NULL,
     7:     [fullurl] nvarchar(255) NULL,
     8:     [dirname] nvarchar(255) null,
     9:     [tp_title] nvarchar(255) NULL,
    10:     [servername] nvarchar(100) null,
    11:     [content_DB] nvarchar(100) null
    12: )
    13: 
    14: go
    15: CREATE TABLE #DBNamesLL
    16: (
    17:     DatabaseName VARCHAR(800),
    18:     RecStatus INT Default 0
    19: )
    20: 
    21: DECLARE @cmdStr NVARCHAR(2000)
    22: DECLARE @dbName VARCHAR(500)
    23: INSERT INTO #DBNamesll (DatabaseName)
    24: SELECT 
    25:     [Name] 
    26: FROM 
    27:     sys.databases 
    28: WHERE 
    29:     state_desc = 'online'
    30: ORDER BY 
    31:     [Name] ASC
    32: 
    33: WHILE EXISTS 
    34:     (SELECT 
    35:         * 
    36:      FROM 
    37:         #DBNamesLL 
    38:      WHERE 
    39:         RecStatus=0
    40:     )
    41: 
    42: BEGIN
    43:     SELECT TOP 1 
    44:         @DbName = DatabaseName
    45:     FROM 
    46:         #DBNamesLL
    47:     WHERE 
    48:         RecStatus = 0
    49:     
    50:     SELECT @cmdStr = N'USE ' + quotename(@dbName, '[') + N';'
    51:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES with (NOLOCK) WHERE TABLE_NAME = ''namevaluepair'')'
    52:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'BEGIN'
    53:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'INSERT INTO ##largelists'
    54:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'select a.siteid, a.webid, a.listid, count(a.id) [counts], c.fullurl, a.dirname, b.tp_title,'
    55:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + '(@@servername) [servername], (select db_name(dbid) from master..sysprocesses with (NOLOCK) where spid=@@SPID) [Content_DB]'
    56:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'from alldocs as a with (nolock) inner join'
    57:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'alllists as b with (nolock) on a.listid=b.tp_id inner join'
    58:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'webs as c with (nolock) on b.tp_webid=c.id'
    59:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'group by a.listid, c.fullurl, b.tp_title, a.siteid, a.webid, a.dirname'
    60:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'HAVING count(a.id) > ''1500'''
    61:     SELECT @cmdStr = @cmdStr + CHAR(13)+CHAR(10) + 'END'
    62:     EXEC sp_executesql @Cmdstr
    63: 
    64:     UPDATE 
    65:         #DBNamesLL
    66:     SET 
    67:         RecStatus = 1
    68:     WHERE 
    69:         RecStatus = 0 AND 
    70:         DatabaseName = @DbName
    71: END
    72: 
    73: SELECT 
    74:     WebID, 
    75:     SiteID, 
    76:     ListID, 
    77:     tp_Title, 
    78:     DirName, 
    79:     Counts, 
    80:     content_DB, 
    81:     servername 
    82: FROM 
    83:     ##largelists 
    84: ORDER BY 
    85:     counts DESC
    86: 
    87: DROP TABLE #DBNamesLL
    88: DROP TABLE ##largelists

5. In my OOB configuration, I changed the 1500 value to 300 in order to get some results. Here is what I get:

   

   This indicates that I have 831 list items in the HelpFold/MetaData/1033/MS_OSS folder. It has an associated WebID, SiteID, and ListID shown. There is additional information to the right that identifies the server and the content database where the list is located.

6. After we have verified that data will actually be returned, we need to save the results to a file in order to get them back to Microsoft. The easiest way to do that is to send the query results to a file.

   

7. Now, execute the query again by clicking on the Execute button

   

8. When you execute the query, a Save Results dialog will pop up requesting a location for you to save the query results. Specify one that is easy for you to remember:

   

9. Now, send the results file (SingleServerLargestFileQuery.rpt) to Microsoft for analysis.

Hotfix for Advanced Search issue in MOSS (950437)

Apparently, some detail was accidentally omitted from the KB article - Expensive Transact-SQL queries are generated in the back-end instance of SQL Server when you perform a search in SharePoint Server 2007 (http://support.microsoft.com/?kbid=950437)

Advanced search queries issue very expensive SQL queries like “filename LIKE ‘%health%’” when we try to query with contains restrictions against individual properties. Depending upon the complexity of the query and the size of the corpus, the resulting query could possibly timeout. This hotfix allows for the removal of this capability.

For users wanting to re-enable the change, here are the steps to add “Contains” and “Does not contain” queries back to the drop-down list:

1. From the home page, click on the Advanced Search link

2. Next, you will want to select the Edit Page option from the Site Actions drop-down

3. On the Advanced Search Box web part, click on the edit drop-down and select Modify Shared Web Part

4. Expand the Properties section under the Advanced Search Box

5. In the properties textbox, find the following
<Option Name=”AllowOpContains” Value=”False”/> under the root node. If it does not exist, create it.

6. Change the value from False to True (case sensitivity applies)

7. Click OK

8. Click Publish to publish the page

9. Now you should see the “Contains” and “Does not contain” options again

What was I thinking?

Have you ever written an email and clicked the send button only to wish you had given it just another minute or two of thought? Of course, you can always attempt to do a message recall; however, if your recipient has rules turned on to route your messages, they're going to get it anyway. Also, if you're sending to clients who do not honor that request, you're going to be out of luck. So, how can I configure Outlook to save me from myself?

1. From the Outlook menu bar, choose Tools | Options. You should see the following:

   

2. Click on the Mail Setup tab

   

3. By default, the Send immediately when connected should be checked. You will want to un-check this option box and then click on the Send/Receive… button to take us to the next part

   

4. You should see an All Accounts group already defined. This, by default, will contain everything that you are currently syncing with. This includes things like:
   • Email accounts
   • SharePoint sites
   • RSS Feeds
   • Published calendars
5. Now, make sure that the Schedule an automatic send/receive every checkbox is checked. Instead of 30 minutes, you may want to adjust down to 2 minutes. This should be enough time for you to reconsider whether or not you really wanted to send that last email or not.
6. Once you are done setting these options, click on the Close button
7. Let's send a test message and see what happens.

   

   You will notice that the message is queued for delivery in your Outbox. In approximately 2 minutes (or whatever setting you chose), the email will be delivered and cleared from your Outbox. However, if during that time you decide you want to retract that email, it's a lot easier (and safer) to simply delete it from the Outbox than it is to rely upon everything between you and intended recipient to clean it up for you.

How can you download a hotfix without contacting Microsoft?

A customer can get the fix they want without calling in to Microsoft, assuming they know the KB number of the hotfix they want and can remember the URL format for a self-service hotfix request:

http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=KBNumber&kbln=KBLanguage

Let's use our August Cumulative Update (CU) for MOSS as an example.

1. Navigate to the to the update. In this instance, the August CU for MOSS 2007 Global is KB 956056. This yields the following URL: http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=956056&kbln=en-us. If this is the first time (in this browser session) that you have visited this site, you will be prompted to acknowledge a EULA for the site. Click on the I Accept button to proceed

   

2. Next, you will see the screen for requesting the hotfix. There are a couple of areas that we need to talk about on this screen:

   

   1. By default, this screen will show the x86 hotfix. By clicking on the Only show hotfixes for my language and platform you will see all of the fixes (x86 & x64) that are available under this particular KB article. You should check those boxes that are applicable for you.
   2. Next, we need to know where to send the information about where to download the hotfix. Fill in your email address in this area. Make sure that your spam filter is set to allow email from "hotfix@microsoft.com".
   3. Finally, we need to make sure you're not a bot :-). So, we request that you enter the string that you see in the picture at the bottom of the page.
   4. Once you have done that, click on the Request hotfix to complete the process
3. Next, you will see the Hotfix Confirmation screen with some additional instructions. It should look like this:

   

4. In a few minutes, you should receive an email with detailed instruction on how to obtain the hotfix. It will look something like the following:

   IMPORTANT INFORMATION

   For your convenience, we put the hotfix that you requested on an HTTP site. You can download the hotfix from this site without us filling up your e-mail inbox.

   WARNING This hotfix has not undergone full testing. Therefore, it is intended only for systems or computers that are experiencing the exact problem that is described in the one or more Microsoft Knowledge Base articles that are listed in "KB Article Numbers" field in the table at the end of this e-mail message. If you are not sure whether any special compatibility or installation issues are associated with this hotfix, we encourage you to wait for the next service pack release. The service pack will include a fully tested version of this fix. We understand that it can be difficult to determine whether any compatibility or installation issues are associated with a hotfix. If you want confirmation that this hotfix addresses your specific problem, or if you want to confirm whether any special compatibility or installation issues are associated with this hotfix, support professionals in Customer Support Services can help you with that. For information about how to contact support, copy the following link and then past it into your Web browser:

   http://support.microsoft.com/contactus/

   For additional support options, please copy the following link and then paste it into your Web browser:

   http://support.microsoft.com/

   Before you install this hotfix

   ------------------------------

   If you decide to install this hotfix, please note the following items:

   Do not deploy a hotfix in a production environment without first testing the hotfix.

   Back up the system or the computer that will receive the hotfix before you install the hotfix.

   Additional hotfix information

   -----------------------------

   This hotfix package uses a password. Therefore, you must enter for each package the password that we included in this e-mail message. To make sure that you enter the correct password, we recommend that you highlight, copy, and then paste the password from this e-mail message when you are prompted. If you do not enter the correct password, you cannot install the hotfix.

   NOTE Passwords are set to expire every seven days. Download the package within the next seven days to make sure that you can extract the files. If there are fewer than seven days left in the password change cycle when you receive this e-mail message, you receive two passwords. If this is the case, use the first password if you download the hotfix package before the date in the "Password Changes On" field that is listed in the table at the end of this e-mail message. Use the second password if you download the hotfix package after the date in the "Password Changes On" field.

   NOTE For your convenience, we send the hotfix location to you in a hyperlink. To connect to this hotfix location, you can click the hyperlink in the "Location" field that is listed in the table at the end of this e-mail message to have your Web browser open that location. However, sometimes e-mail program settings disable hyperlinks. If the hyperlink in this e-mail message is disabled, please copy the hyperlink in the "Location" field and then past it into the address box of your Web browser. Make sure that you include the exact text (without spaces) between the parentheses in the http:// address.

   Package:
   -----------------------------------------------------------
   KB Article Number(s): 956056
   Language: All (Global)
   Platform: x64
   Location: (http://hotfixv4.microsoft.com/Microsoft%20Office%20SharePoint%20Server%202007/sp2/officekb956056fullfilex64glb/12.0000.6327.5000/free/358323_intl_x64_zip.exe)
   Password: ^!$1re^S6

   NOTE Make sure that you include all the text between "(" and ")" when you visit this hotfix location.
   

 

 

Microsoft releases Best Practice Analyzer (BPA) for WSS 3.0 and MOSS 2007!

Well, Microsoft has finally released their first pass at a Best Practice Analyzer (BPA) for WSS 3.0 and MOSS 2007! The BPA is based upon the same engine that Exchange has been using for a couple of years. Here is a small overview.

Overview

The Microsoft Best Practices Analyzer for Windows SharePoint Services 3.0 and the 2007 Microsoft Office System Best Practices Analyzer programmatically collects settings and values from data repositories such as MS SQL, registry, metabase and performance monitor. Once collected, a set of comprehensive 'best practice' rules are applied to the topology. Administrators running this tool will get a detailed report listing the recommendations that can be made to the environment to achieve greater performance, scalability and uptime.

Instructions

To install this download:

1. Download the file by clicking the Download button (above) and saving the file to your hard disk.
2. Double-click the BestPracticeAnalyzer.exe program file on your hard disk to start the Setup program.
3. Follow the instructions on the screen to complete the installation.

Instructions for use:

1. On the Start menu, click Run. In the Open box, type cmd, and then click OK.
2. Type the following command, then press ENTER:
   
   cd "C:\BPA"
   
   where C:\BPA is the location that contains the Microsoft Best Practices Analyzer for Microsoft Windows SharePoint Services 3.0 and the 2007 Microsoft Office System. (If the Microsoft Best Practices Analyzer for Microsoft Windows SharePoint Services 3.0 and the 2007 Microsoft Office System is in a different location, replace C:\BPA with that location.)
3. If you execute the sharepointbpa.exe from the command line, you will see a sample usage as shown below:
   
4. Type the following command, and then press ENTER:
   
   sharepointbpa.exe -cmd analyze -substitutions SERVER_NAME CentralAdministrationServer
   
   where "CentralAdministrationServer" is the server name for your SharePoint Central Administration.
   
   It is important that SERVER_NAME is included in the parameters capitalized and that CentralAdministratorServer is replaced with the server name that is running SharePoint Central Administration.
5. After the analysis is complete, open sharepointbpa.report.htm in a web browser. You should see something similar to the following:
   
   
   
   Sharepointbpa.report.htm will be in the same location that the Microsoft Best Practices Analyzer for Microsoft Windows SharePoint Services 3.0 and the 2007 Microsoft Office System was installed.
6. Type EXIT and then press ENTER to quit the command prompt.

Additional Information:

1. Unlike the Exchange BPA, this tool must be run on the actual server from which data is being collected. You cannot run this tool remotely from a client machine.
2. You can also produce an output in a raw xml format by using the following command:
   
   sharepointbpa.exe -cmd analyze -substitutions SERVER_NAME %COMPUTERNAME% -reportformat xml
   
3. If you would like to generate the output file into an altername location, simply specify the outputdirectory option:
   
   sharepointbpa.exe -cmd analyze -substitutions SERVER_NAME %COMPUTERNAME% -reportformat xml -outputdirectory rpts
   
4. Expect more rules to follow as it gains momentum!
   
   

SPSReport has moved (yet again)

Well, SPSReport has moved yet again! It is now, hopefully, in its final resting place. You can find the current version at http://www.codeplex.com/spsreport.