Improve an application performance by adding index

问题:

I've got an old web application with a lot of legacy code and I need to improve performance for some reports. I want to avoid reports rewrite, because it's standard legacy application which is hard to maintain, test and develop. So I'm trying to speed these reports by adding indexes for reports queries in MSSQL (2008). Reports execute about 3-4k queries.

Below is my improvement plan:
1. Clean MSSQL buffers by running

DBCC DROPCLEANBUFFERS  
DBCC FREEPROCCACHE  
DBCC DROPCLEANBUFFERS

2. Run MSSQL profiler and then execute report.
3. Collect all report's queries and then export them to MSSQL.
4. Calculate sum of duration.
5. Group queries by textdata and order them by sum(duration) desc.
6. Execute queries from profiler's export and add indexes which are recommended by MSSQL Management Studio.
7. Repeat steps from 1 to 6 and compare results.

I've got interesting results. When I improve report #1 performance, report #2 performance are decreased. Also sometimes after adding indexes I've got performance decreasing tenfold.

My questions:
1. Is it a good idea to improve application performance by improving SQL queries performance?
2. Is it possible to improve performace of reports which execute 3-4k SQL queries by adding indexes?

回答1:

For a poor performing database, inappropriate/missing indexes can be ONE of the many reasons.

If you think not having enough indexes is what causing the poor performance then there are tool to check if the missing indexes is the actual problem, but sometimes problem can be the query itself.

If your query is written poorly no matter how many indexes you add it will perform poor.

Sometimes query is written fine and there are enough indexes to support the query but there isn’t enough resources (Memory RAM, processor horse power) to get the job done quickly.

I would suggest you to investigate your database a bit more before you start adding indexes and introduce more problems which never existed before.

Some useful links

1. Are you using SQL's Missing Index DMVs?

   This will allow you to see if there are any missing indexes and queries may gain performance by adding indexes.

2. Wait statistics, or please tell me where it hurts

   This blog written by Paul Randal will help you to see exactly what is causing slow performance in your sql server. Whether it be your hardware or indexes or queries.

3. Most Expensive Queries

   This will allow you to see the most expensive queries in your database, I would go query by query and try to improve the code (if possible) and also check if any index can help the query to reduce its cost.

Import Note

Performance tunning in sql server is no simple task and there is no silver bullet for it either, you will need to investigate more to find the actual bottlenecks in your database and target them, and with each change do a comparison that how that change has impacted your database performance. Good luck with that :)

回答2:

1. It's a good idea to improve application performance by improving SQL queries performance if DB is a bottleneck. From information provided above I can say that in your case DB performance is important and worth to improve.

2. It's possible to significantly improve performance of reports which execute a lot of queries by adding indexes. Especially if tables itself contain tons of records (10 000 and more). But, indexes have their own drawbacks:

   • they slowdown insertions/updates/deletions
   • they influence query execution plan. As a result in some cases DB manager chooses to use index when it would be more appropriate to perform full table scan. Which in turn causes performance degradation.