Home / server / Questions / 18712
Accepted
Mike Messina
Asked: 2009-06-04 05:20:29 +0800 CST

SQL/IIS (Classic ASP) Web Application Performance Optimization

  • 772

We have a Classic ASP web-application that we are hosting with a 3rd party hosting provider (Rackspace). We have ~ 100 databases on a SQL server and around the same number of websites on a Web-Server (IIS). Does anyone have any tips for optimizing performance. We occasionally see memory and CPU spikes throughout the day. We are setup with a simple SQL server and Web-server pair.

sql-server iis web-server web-hosting

3 Answers

  1. On the SQL side of things, it might be a good idea to use SQL Profiler to identify any of your queries or stored procedures that are taking a long time to run. If you can identify any real problems here it can help speed up your application.

    Keep in mind the Profiler tool often requires a lot of filtering to get the correct information. It is nice in the fact that you can record the start times of the queries being run and try to match them up with your CPU spike times.

    • 2
  2. Best Answer

    That's not a simple question and doesn't have a simple answer. Entire books have been written on the subject of performance optimisation.

    Maybe try starting with these articles which go into (in the first part) finding the performance problems. http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    I can also suggest a book. "SQL 2008 Performance tuning distilled" by Grant Fritchey and Sajal Dam.

    If you can find the worst performing of the queries, post them here or on a good SQL forum and someone will definitely help you. As it is, the question's way too broad to have a simple answer.

    • 2

  3. I suspect you're going to need to do some profiling. At a gross level, you need to know where the bottlenecks are, so typical Windows performance monitoring guidelines apply. Chart gross performance of CPU (% utilization, % user mode vs kernel mode, context switches), disk (queue length, % disk time), and memory (page faults / sec, working set sizes). You'll want to start narrowing it down to which processes (IIS WAM, SQL Server, etc) are giving you grief.

    Like the other posters said-- if it's the SQL queries you're running, then it's probably a database optimization problem. Don't overlook the possibility that your scripts, however, are executing silly queries (SELECT * and filtering the results in the script, etc) that are causing anomalous database load.

    If do you get down into individual scripts (i.e. you find that the bottleneck points to IIS / WAM processes), then have a look at this Stackoverflow question: https://stackoverflow.com/questions/398028/performance-testing-for-classic-asp-pages There's a code snippet in there that is a basic execution timer. At the risk of impacting performance further, but in an effort to get more visibility, you could also consider logging the parameters passed into and execution times of your scripts into the database. Looking for outliers in that data might help you find edge conditions in your scripts. Ideally, you want to design with profiling in mind from the start, and retrofitting can be tough.

    • 1