Speeding up a Slow Website Part 2 – Microsoft SQL Server

Feb 07, 2019

Speeding up a Slow Website Part 2 – SQL Server

Following up on the previous article – speeding up a legacy website – this posting shows how we tackled the SQL database portion of the optimization.

Database Design

Any developer can create a database.Most developers can create a relational database and some developers can normalize and optimize a database.However very few developers can design a database that will perform effectively over years and as transactional volume increase Initially a poor design is not apparent due to the lack of traffic and companies marvel at how quick and easy the process was.As traffic increases,the database can become a bottleneck and site slowness and other problems start to appear.Companies typically respond to this by asking developers to optimize queries or by throwing in more powerful servers,memory and updating the operating systems and database software.This however only masks the problem for the short term.

ROI to Replace a Legacy System

In many cases,the ROI to rebuild the database just isn’t there.A brand new database design can be costly,require a much longer deployment and outages.The database is the central piece and will usually require changes in internal and other systems and processes,requiring changes for both consumers and users.

Client Challenges

Recently a large e-commerce company came to us with a challenge.The client had a 16 year old website running a classic ASP website on Microsoft SQL Server that was terribly slow.They asked us to identify quick and easy ways to make it faster.Their 80 person development teams had already tried optimizing the processes and identified the database as the culprit. They even built an extensive caching mechanism that generated HTML files multiple times per week.In spite of the efforts,the e-commerce processes were still extremely slow with average page load times of 30 or more seconds.With their busiest shopping season of the year approaching they needed results quickly.The client requested a complete review with a recommendations presentation within two weeks.

Vimware Approach

We approached the problem by looking at the code and the database.The fundamental issue was that the developers had tried to put as little stress as possible on the database and that resulted in a less than optimal design. In this post,I will highlight the suggestions we made for the database,and will also include a link to our detailed Recommendations-SQL-Server-Database-Optimization with all company information redacted.

Vimware Solutions

The short-term solutions focused on the following:

1.Incorrect indexing strategy
2.Incorrect or missing clustered indexes
3.Too many reads
4.Incorrect TempDB design
5.Irrelevant or unnecessary data being returned
6.Inefficient and poorly written SQL Server queries.
7.Incorrect SQL Server settings.

In every case, there were improvements that could have been made.
Medium to long-term solutions were to point out:     

    1. Re-design sections of the database
    2. Remove SQL Queries embedded in code
    3. Modify the process of database changes
    4. Add senior database engineer(s)










Based in Burbank,California since 2015,Vimware is dedicated to supporting small to midsize businesses and agencies with their behind-the-scenes IT needs.As a Managed Service Provider (MSP), we offer a range of services including cloud solutions,custom programming,mobile app development, marketing dashboards, and strategic IT consulting.Our goal is to ensure your technology infrastructure operates smoothly and efficiently, allowing you to focus on growing your business.Contact us to learn how we can assist in optimizing your IT operations.