Tuning IIS5 Performance – Tables Without Proper Index in MS Access Databases Can Cause Performance to Degrade

By Oved Blass, May 2002

Summery

We host web sites and we are specializing with Windows 2000, ASP, .NET based hosting. Recently we had a performance problem with one of our servers that hosts few hundreds web site. Just before buying a new server and upgrading our hardware (which would have solve the problem), we found the solution that is described here. This document can give you one very important IIS5 performance tip and a tool that sets a property for many web sites on a server in few seconds. Contact me if you need a custom built tool to automate IIS or Windows management (for instance adding web hosting accounts, DNS records, etc.)

The Problem’s Symptoms

Customers were complaining about poor performance. Pages that use MS Access database were responding very slow, some times so slow that the visitors received a script time out message (the default for this is after 90 seconds). The slow responses from pages that use MS Access were seen at all web sites on the server, no matter the size or the complexity of the database and the queries. There was no noticeable performance degrade with HTML pages and a slight degrade with the performance of web site that used SQL Server.

 

Using Windows 2000 performance tool we noticed that when these symptoms appeared, processor utilization and network activity dropped, while disk usage was slightly increasing. It was clear that there was a bottleneck that prevented the CPU and Internet connection to reach their full capacity. Memory usage was also high.

 

Below you can see the main system counters during the problem. Note that network traffic and CPU utilization dropped. Disk usage was heavy during these times. This snapshot was taken when the problem was severe. At other times the fluctuations with CPU, network and memory were not that clear.

 

 

We tried to improve the performance with these following steps:

 

These steps only improved the performance slightly. We were still observing times in which some pages as far as the web sites visitors are concerned did not respond at all.

 

We looked at articles on the Internet about IIS performance. These documents gave us many ideas how to pin point the problem, looking at the memory and CPU usage of IIS processes and SQL Server processes (which was running on the machine as well IIS). Many performance documents were written for IIS4, and do not have updated information for IIS5. IIS5 manages threads differently from IIS4, so much of the threads tuning chapters in these documents is irrelevant. See Tuning Internet Information Server Performance

 

Nevertheless, although we saw disturbing patterns in the performance counters that indicate that something was wrong, we could find the cause of the problem.

 

Note that Windows 2000 has a bug that does not allow the ASP counters to be viewed through a terminal server session. All ASP counters show zero when watching them with performance monitor through a terminal server session. We spent hours being sure that there is no problem with the ASP counters, until we accidentally found the reason at Microsoft document Q286433

 

Tuning Data Access

Since the problem was affecting pages that used MS Access databases, we tried to look for ways to tune these components. A month earlier we installed on this Windows 2000 Server, a new version of the data access components: MDAC 2.7. We wanted to test whether this newer version had a flaw that slowed the machine. We did not find any documents on the Internet confirming that. We installed the latest refresh for MDAC 2.7, but it did not yield any noticeable improvement. Note also that the Jet database driver that could also be a source of the problem is not included in MDAC 2.7. Also the data access components do not provide counters that can be viewed with performance monitor or a tool to tune these components. Some documents on the web advise to improve performance of data access by following few guidelines while writing the application. We have no control of how our customers are writing their applications, we can only recommend them to follow these guidelines. The best guidelines:

We decided to review the designs of databases of the major web sites on the server that use MS Access. We started with reviewing the largest databases.

One of the databases, 50MB in size, was used to implement a forum. The main table contained about 50000 records of answers for the forum. We noticed that there was no index defined for the field that was used to retrieve the records from this table. This web site had about 1000 user sessions per day and one page in the web site was making few queries to this answers table. We suspected that these queries, without a proper index in the database were bringing the server to its knees. We created an index at the proper field of the table with MS Access, and Walla, the server return to itself! The performance improved immediately and dramatically.

CPU, memory and disk utilization dropped and the server was responding quickly to all pages, including pages that used MS Access.

 

Here is a snapshot of the main counters after the problem had been solved:

 

 

Conclusions

Required

Tools to pin point over used MS Access database in hosting environments

Tools to find out, which web site is causing a problem

References

Tuning Internet Information Server Performance

HOWTO: Improve Performance of Applications Using Jet 4.0 (Q240434)

IIS5 Documentation, chapter about performance tuning