Analyze IIS Web Site performance with LogParser and SQL Server

In this post I'll go through the steps you have to take in order to import IIS logs into SQL Server and provide some queries to get you started on identifying performance problems.

1. Localize your IIS logs

By default, IIS log files are located in the following directories:

• IIS 7 and later: %SystemDrive%\inetpub\logs\LogFiles
• IIS 6 and earlier: %WinDir%\System32\LogFiles

Open the IIS Manager and select Sites, as shown bellow. This will show you the ID of each website hosted on your server. You will need this ID to determine which W3SVC* directory to analyze.

Open Windows Explorer and navigate to the directory that contains the IIS log files of your website.

Locate your log file based on the naming and copy it somewhere else. (ie: c:\temp\logs).

Read more about IIS Log File Naming Syntax

2. Use LogParser to import data into a SQL database

You can find LogParser at the following location: Log Parser 2.2
I have used the following command:

C:\Program Files\Log Parser 2.2>logparser "SELECT * INTO iisLogs FROM c:\temp\logs\*.log" -i:iisw3c -o:SQL -server:localhost -database:webLogs -username:sa -password:yourpass -createTable: ON

You will have to fill in your own SQL Server location and credentials.
Running this command will take some time depending on the size of your log file. After it finishes you can see the created table on your DB, with a structure identical to the IIS log file.
Read more about W3C Extended Log File Format (IIS 6.0)

3. Run scripts to identify performance problems

Status codes

SELECT
    scstatus + scsubstatus As Status,  
    COUNT(*) AS Hits  
    FROM [dbo].[iisLogs]
GROUP BY scstatus + scsubstatus  
ORDER BY Hits DESC 

This query will allow you to compare 200-Success status code responses to:

• cached content: 304 - Not Modified
• error codes: 404-Not Found, 500- Internal Server Error

In my case I see a lot of redirects, almost as much as 200 status codes. Something might be wrong with the friendly URLs redirect feature and needs more investigating. The errors though are nothing to worry about.

Slowest 20 pages

SELECT TOP 20
  csuristem AS URL,
  MAX(timetaken) AS Max,
  MIN(timetaken) AS Min,
  AVG(timetaken) AS Average,
  COUNT(*) AS Hits
FROM [dbo].[iisLogs]
GROUP BY csuristem
ORDER BY Average DESC 

This query will give you an indicator on where to focus your performance improvement effort.

Requests per hour

SELECT
  DATEADD(HOUR, DATEDIFF(HOUR, 0, time), 0) AS Hour,
  COUNT(*) AS TotalRequests
FROM [dbo].[iisLogs]
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, time), 0)
ORDER BY DATEADD(Hour, DATEDIFF(Hour, 0, time), 0)

This query will display the number of requests per hour intervals during a day. This way you know at what time your website is mostly used.

Average response time per hour

SELECT DATEADD(Hour, DATEDIFF(Hour, 0, time), 0)  AS Hour, 
 AVG([timeTaken]) AS AverageResponseTime
FROM [dbo].[iisLogs]
GROUP BY DATEADD(Hour, DATEDIFF(Hour, 0, time), 0)
ORDER BY DATEADD(Hour, DATEDIFF(Hour, 0, time), 0)

This query will return the average response time per hour intervals during a day.

4. Compare data sets

If you need to go in front of managers to explain what happened you can create Excel charts with the data you get from your queries. This will give you a visual representation that is easier to interpret.

For example I have compared the average response time with the user load, the results in my case were a bit surprising.