pnp.gif

How To Use IIS Log Files In Performance Testing

Edmund Wong

Applies to

  • Visual Studio Team System
  • IIS 6.0
  • Performance Testing

Summary

This How-To will introduce you to the concepts and processes to use IIS 6.0 log files to identify performance issues in performance testing. This information can also be compared to production data for verification. Also, this article assumes specific technology involved. But in reality, the same technique can be applied to different technologies such as another type of web server.

First, this article talks about how to set up the IIS log files to collect the performance data.
Second, it goes onto how to parse and process the IIS log files.
Third, it shows how to do analysis based on the information.
Fourth, the focus is shifted on using Visual Studio Team System to do the same analysis without IIS log files.

This article will also demonstrate how to use the following technologies to aid performance testing: IIS 6.0 logs, Log Parser, Excel, SQL 2005 and Visual Studio Team System.

Contents

  • Objectives
  • Overview
  • Summary of Steps

Objectives

The purpose of this how-to is to educate project teams on how to use Internet Information Services (IIS) log file to gain valuable information in performance testing.

At the end of this how-to document, you should be able to do the followings:

1. Setup and collect IIS log files.
1. Analyze IIS log files information and focus on areas of improvement in a systematic manner.
1. Make business and technical decisions based on IIS log files information.
1. How to gain the same level of information using Visual Studio Team System in performance testing.

There are some steps that you can follow to practice. Given all required software are available at the time, it will take a beginner around 2 to 4 hours to experiment all the steps.

Overview

Internet Information Services 6.0 (IIS) is one of the most popular web servers of our time. It has built in a very fast and useful logging mechanism to enable project teams to address performance issues in production and in performance testing.

There are many benefits in analyzing IIS log files. First, it can be used in production as well as in performance testing. It enables a production comparison to verify performance testing results. Second, it can be easily understood by business managers and performance testers. It bridges a gap of communication. The performance tester can easily identify performance bottleneck and correlate them to business value using data derived from IIS log files. Third, it provides many technical insights into the system based on how you slice and dice the raw data. These technical insights are tremendously useful in solving performance bottlenecks.

However, many people lack a systematic approach to collect and analyze IIS log files. In this article, we will address how to systematically collect and analyze IIS log files to identify some common performance problems and how to use Visual Studio Team System to analyze the same information.

Sumary of Steps

Step 1: Setting up IIS Log File
Step 1a: How to set up IIS Log File
Step 1b: When to set up IIS Log File
Step 2: Importing or Parsing IIS Log File
Step 2a: Using LogParser 2.2 to parse IIS log file
Step 2b: Using Excel to parse IIS log file
Step 2c: Using SQL 2005 Management Studio to parse IIS log file
Step 3: Analyzing IIS Log Files
Step 3a: Simple Network Analysis
Step 3b: Simple Response Time Analysis
Step 4: Using Visual Studio Team Studio to analyze the same information

'''Note''': This article talks about IIS 6.0 log files. However, the same methodology can be applied to other web servers that support the “W3C Extended Log File Format”. Other log file formats can be used with some adaptations and other tools.

!!!Step 1: Setting up IIS Log File
This section explains how and when to set up IIS Log Files. This is a common topic and many people should have some background in it. However, even if you have knowledge of this topic, it is recommended that you should browse through this section quickly to check out the focus on setting up IIS Log Files for performance testing purposes.

!!!!Step 1a: How to set up IIS Log File
The following instruction is based on Windows 2003.

First, you have to start the Internet Information Service (IIS) Manager. On Windows Server 2003, it is Start -> All Programs -> Administrative Tools -> Internet Information Service.

Second, it is per web site setting. You can select the web site and right click Properties. The corresponding web site’s property page will pop up:

http://static.flickr.com/127/3173668852aa68bed2do.jpg
  • Enable logging – It should be checked to enable logging. You must be a member of the Administrators group on the local computer to enable logging.
  • Active log format – The default is “W3C Extended Log File Format”. We will focus on this using this format. We need to use “W3C Extended Log File Format” because it has advanced options for tracking performance issues. The other supported formats are: Microsoft IIS Log File Format, NCSA Common Log File Format and ODBC Logging.
  • Properties – Select this “Properties” button will enable us to continue and set additional performance tracking options.
http://static.flickr.com/144/317366886aa0c0ae10fo.jpg
  • New log schedule – The default is Daily. There are other settings based on time interval and file size. It is your decision to choose one that is manageable in your situation. Typically, the default of Daily is sufficient.
  • Use local time for file naming and rollover – The default is checked. For performance testing, please use local time to simplify. Only in rare occasions when you need to correlate web server logs in different time zones, this setting should be unchecked.
  • Log file directory – This is where the log file would be written. Please note that the actual log file would be written to a sub-directory. E.g. C:\windows\system32\LogFiles\W3SVC1. The web site ID at the end is used to identify the sub-directory. For each performance testing iteration, a separate directory can be used for logging to organize results. E.g. d:\iislogs\test20061001, d:\iislogs\test20061008. Use naming conventions based on your project team’s habit.

http://static.flickr.com/123/317366887ca9c50c58co.jpg
  • Advanced – The advanced option is only available when the “W3C Extended Log File Format” is selected. The 3 options for tracking performance issues are “Bytes Sent (sc-bytes)”, “Bytes Received (cs-bytes)” and “Time Taken (time-taken)”. sc-bytes is the number of bytes going from server to client. cs-bytes is the number of bytes going from client to server. Time-taken is the number of milli-seconds of execution. To simplify, select all options for performance testing.

!!!!Step 1b: When to set up IIS Log File

There are 3 situations when you need to set up the IIS Log File

1. In single user performance analysis – You should set up the IIS Log File and then click through the user interface for a given scenario and then archive the single user baseline. This article will not discuss how to do single user performance analysis. But if the application is slow in single user, there is no need to run the stress test. Fix the single user performance problem first before proceeding in stress test.
1. In stress test – You should set up the IIS Log File to assist in performance testing using Visual Studio Team System. The analysis of the IIS Log File is based on the system under stress. In ideal situation, it is best to keep a separate IIS Log File.
1. In production – You should set up the IIS Log File. Use the same analysis of the IIS Log File and compare your production results with your performance testing results. First, you can verify whether your presumption of your stress test scenarios is correct. If you see 20% of users hitting URL A and 80% of users hitting URL B, you should use similar ratios in your stress test. There is no perfect match. Just try to be reasonable.

!!!Step 2: Importing or Parsing IIS Log File
3 tools are discussed in this article: Log Parser 2.2, Microsoft Excel and SQL Server 2005 Enterprise Edition. These are the poor man’s tools for importing or parsing IIS log files. Within Microsoft, there are enough developers to have written many various tools and systems to automate IIS Log File processing for analysis and data warehouse purpose. However, these 3 tools are widely available publicly and they get the job done.


!!!!Step 2a: Using LogParser 2.2 to parse IIS log file
LogParser 2.2 is a powerful. You can download it from
http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en

You can find valuable support information from this site: http://www.logparser.com/

This tool is written by a great Microsoft developer. It is super fast and convenient. It is the preferred way to analyze IIS log file. Microsoft.com uses it to troubleshoot problems when lead engineers needs to parse through millions of requests in a hurry.

This is a sample log parser response time query that ACE team uses.

Logparser: "SELECT cs-method AS Method, sc-status AS Status, To_Lowercase(cs-
uri-stem) AS Page, cs-uri-query AS Querystring, AVG(time-taken) AS Avgtimems,
MIN(time-taken) AS Mintimems, MAX(time-taken) AS Maxtimems, SUM(time-taken)
AS Sumtimems, COUNT(*) AS Hits INTO Results.csv FROM MyIISLogFile.log GROUP
BY cs-uri-stem, cs-uri-query, cs-method, sc-status ORDER BY AVG(time-taken)
DESC" - i:IISW3C -o:CSV


This is a sample log parser network analysis query that ACE team uses.

Logparser: "SELECT cs-method AS Method, sc-status AS Status, To_Lowercase(cs-
uri-stem) AS Page, cs-uri-query AS Querystring, Avg(sc-bytes) as ByteSent, AVG
(cs-bytes) as ByteReceived, COUNT(*) AS Hits INTO Results.csv FROM
MyIISLogFile.log GROUP BY cs-uri-stem, cs-uri-query, cs-method, sc-status ORDER
by ByteSent DESC" -i:IISW3C -o:CSV


These log parser queries save the result to a file called Result.csv. It is a comma delimited file that can easily be opened in Excel for additional analysis. For further information, please read this KB article: http://support.microsoft.com/kb/910447

This article has many best practices. E.g. Finding the 20 slowest pages in your Web site.


!!!!Step 2b: Using Excel to parse IIS log file
Here are the steps to import IIS log file to Excel for analysis. The following instruction is based on Excel 2003:
  • Launch Excel and select an empty worksheet
  • Select from the menu “Data” -> ”Import External Data” -> “Import Data”
  • Use “All Files” in Files of Type to view all files. Open the IIS Log File.
  • Select “Delimited”. Click Next.

http://static.flickr.com/125/3173668901056b1fa97o.jpg
  • Check “Space”. Click Next

http://static.flickr.com/131/317366891be4e87949bo.jpg
  • Use default and click Finish.

http://static.flickr.com/141/31736689221187afa67o.jpg
  • Click OK

http://static.flickr.com/139/317373414e7fbb3f24fo.jpg
  • Delete the extra information except headers. Delete cell A4 and shift cells left. If there are additional headers within the content, you can sort column A and delete excess information.

http://static.flickr.com/127/317373439_5382e9d4df.jpg
  • You can then from the menu “Data” -> “Fitler” -> “Auto Filter”. Then, you can sort the 3 important columns (bytes sent/bytes received/time taken), hide other columns and use Excel functions to parse the data.

http://static.flickr.com/144/317373463_7d8ad66d59.jpg

Excel is a powerful tool for parsing IIS log file. However, there is a limitation of 65536 rows. You can use “Start Import at Row” option to skip rows and do the import multiple times. However, Excel is mostly used in looking at small IIS Log File and to parse IIS Log File quickly.


!!!!Step 2c: Using SQL 2005 Management Studio to parse IIS log file
Here are the steps to import IIS log file to SQL 2005 for analysis:
  • Create database for IIS Log analysis. (See SQL 2005 Book Online for details) The sample has used MyIISLog as the database name.
  • Generate a table with standard schema. The sample has used PerfTestTable as the table name. Below is the template used to store IIS log data:

Create Database MyIISLog
GO
USE MyIISLog
GO
/** Object: Table dbo.PerfTestTable Script Date: 10/23/2006 02:51:24 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.PerfTestTable(
MyID int IDENTITY(1,1) NOT NULL,
date datetime NULL,
time datetime NULL,
s-sitename nvarchar(255) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
s-computername nvarchar(255) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
s-ip nvarchar(255) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
cs-method nvarchar(255) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
cs-uri-stem nvarchar(512) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
cs-uri-query nvarchar(512) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
s-port int NULL,
cs-username nvarchar(255) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
c-ip nvarchar(255) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
cs-version nvarchar(255) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
cs(User-Agent) nvarchar(255) COLLATE SQLLatin1GeneralCP1CI_AS
NULL,
cs(Cookie) nvarchar(255) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
cs(Referer) nvarchar(255) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
cs-host nvarchar(255) COLLATE SQLLatin1GeneralCP1CI_AS NULL,
sc-status int NULL,
sc-substatus int NULL,
sc-win32-status int NULL,
sc-bytes int NULL,
cs-bytes int NULL,
time-taken int NULL
) ON PRIMARY

  • Select Database. Right Click “Task” -> Select “Import Data” to launch SQL Server Import and Export Wizard.
  • Choose a Data Source: Flat File Source. “Browse” for IIS Log File. Also, change the setting “Header rows to skip” at 4. Then select “Columns”.

http://static.flickr.com/141/31737328524554b4800o.jpg
  • Enter exactly 1 space in “Column delimiter” and click “Refresh”. Then select “Advanced”.

http://static.flickr.com/136/3173732978b42f1438co.jpg
  • By this time, the advanced tab should have been populated with columns 0 to 21. Hold down shift and select columns 2 to 21. Update the following fields:

Data Type Unicode string DT_WSTR
OutputColumnWidth 4000


Note that we are using 4000 in the sample. It may truncate your data if your string is too long. In most cases, 4000 should be enough.


http://static.flickr.com/134/317373311372a697807o.jpg
  • After clicking next from previous screen. Select the destination database.

http://static.flickr.com/132/317373320aa395d3230o.jpg

  • Select destination table that you created in step 2c item 2. Click “Edit Mapping”. Select destination mapping one by one. It should be in sequence. The only non matching field is the first one which is the identity column. For the remaining of fields is a one by one matching field

http://static.flickr.com/130/317373334_492daea2b4.jpg
  • Then execute to process the data. It is possible to save an SSIS package to automate this process. This sample does not delve into SSIS package detail. SSIS information can be found in SQL 2005 Book Online.

http://static.flickr.com/132/31737334962d206ce44o.jpg
  • Continue and check the result is done. There is no error and check for the number of rows processed.

http://static.flickr.com/131/3173733549e98fa1226o.jpg

'''Note:''' You can save this process into an SSIS package and just re-run it. You can overwrite the source table, truncate the destination table and then automate this process. SSIS information can be found in SQL 2005 Book Online.


!!!Step 3: Analyzing IIS Log Files
The final step is to make sense of the IIS Log File data in terms of performance testing. Let’s say that you have run a stress test and you need to analyze the data. You can look at the IIS Log File captured during the stress test. We have discussed 3 tools for parsing IIS Log File. To simplify the discussion, we will choose to use SQL 2005 in the following discussion. Similar results can be achieved by logparser and Excel. Some results are presented in Excel format. Those results are queried from SQL 2005 and they are copied and pasted into a better presentation format in Excel.


!!!!Step 3a: Simple Network Analysis
SQL 2005 Query for simple network analysis

-- Network Analysis based on URL and parameter just for ASPX
select top 20 cs-uri-stem,cs-uri-query, avg(sc-bytes) as BytesSent, avg
(cs-bytes) as BytesReceived from PerfTestTable group by cs-uri-stem, cs-
uri-query
order by BytesSent desc

http://static.flickr.com/138/317373361a62d7c0c28o.jpg

The action is to query the largest data transfer. Identify common large data transfer performance problems
1. Large ViewState in ASP.NET. For example, put a report in Web Form Control. It will generate a huge view state.
1. Large graphics. Use tools to reduce the graphics size.
1. Large amount of data displayed in table. Use paging to reduce the data transfer.

Also, most web pages have high bytes sent from the server to the browser and we seldom see problem the other way around.

Please note that in this analysis, we do not see latency impact. The rule of thumb is to reduce round trips. IIS Log file is not the ideal tool to check for network latency impact.


!!!!Step 3b: Simple Response Time Analysis
SQL Query for simple response time analysis for time taken.

-- Count frequency, time taken based on URL just for ASPX. Group by URL and
Group by parameter
select cs-uri-stem, cs-uri-query, count(cs-uri-stem)as
Hits, avg(time-taken) as AVGTimeTaken, max(time-taken)as MAXTimeTaken from
PerfTestTable where cs-uri-stem like '%aspx' group by cs-uri-stem, cs-uri-
query
order by AVGTimeTaken Desc SQL Query for simple response time analysis
over a threshold.

-- Count frequency based on URL for ASPX over a threshold. Group by URL and
Group by parameter
select cs-uri-stem, cs-uri-query, count(cs-uri-stem)as Hits from
PerfTestTable where cs-uri-stem like '%aspx' and time-taken > 3000 group by
cs-uri-stem, cs-uri-query order by cs-uri-stem

Results at one load level. At this point, ASP.NET has 0 queuing.

http://static.flickr.com/133/317431965dfbd4f52c1o.jpg

When the same application is stressed at a much higher load level, the response time begins to degrade. At this point, ASP.NET has around 50 items in queuing. The CPU utilization is more. Almost ¼ to ½ of the hits are exceeding 3 seconds, which is the threshold of requirement. The response time analysis can be done couple times to find out what the business users think are acceptable load level and the acceptable response time.

http://static.flickr.com/138/317373378baa01d4819o.jpg

The discussion of a full stress test is out of scope for this article. The central idea is to use IIS Log File to judge how many requests have exceeded threshold. It gives the business user an understanding. Also, remember that even though there are some guidelines on response time acceptability, it is a subjective issue.

In additional, you can do this in production and verify the results against performance testing done using Visual Studio Team System.

!!!Step 4: Using Visual Studio Team Studio to analyze the same information
Analyzing IIS log file gives a lot of information for network analysis and response time acceptability. However, if you are running stress test with Visual Studio Team System, you can take advantage of the default reporting capabilities to do the same analysis quickly.

After you have run a stress test with Visual Studio Team System, you will get a default report. You can get a list view of network analysis. You select -> Tables. Then, you select -> Requests view.Then, you can sort the content length column in descending order. You can identify the largest data transfer similar to parsing the IIS log file sc-bytes and cs-bytes fields.

http://static.flickr.com/130/317373394_abcb7b282c.jpg

You can get a list view of response times of each page. You select -> Tables. Then, you select -> Pages.

http://static.flickr.com/134/317373410_09558a9247.jpg

This list view of response time gives you many information. The “Total” column is the number of hits for that particular URL. The “Ave” column is the average response time in seconds. The “Max” column is the maximum response time in seconds. However, the maximum column can be skewed if there is 1 extreme sample. Thus, the 90% and 95% columns are provided to focus on the majority of the samples.

In additional, Visual Studio Team System has an extra feature to define the response goal in each request. If such a goal is defined, the “Goal” column will show the goal in seconds and “% Meeting Goal” column will show the percentile of samples meeting the performance goal.

Using Visual Studio Team System can save you time to retrieve some basic analysis information. This saves you from parsing the web log file every time. However, there will still be times when you want to parse the web log file. For example, you may want to parse the web log file in production to verify the response time result matches the performance testing prediction.

!!!Additional Resources
  • Log Parser 2.2 and ASP.NET http://support.microsoft.com/kb/910447
  • ACE Team Blog http://blogs.msdn.com/ace%5Fteam/

Additional Resources

Last edited Feb 7, 2007 at 3:12 AM by mycodeplexuser, version 3

Comments

rriehle Jun 9, 2011 at 5:29 PM 
Some links on this pages are no longer working. Also, other graphics are pretty small (like http://farm1.static.flickr.com/134/317373410_09558a9247.jpg).