Case Scenario: Optimizing Query Performance

 

In this scenario, you are a database developer in your organization. You have been assigned the task of optimizing the database used by the popular community Web site that your organization owns. Because of the high user activity on the community Web site, there are two performance problems. First, the user response time for the community chat forums is very poor. The Web site developers have narrowed this problem down to slow response times from the forum stored procedures in the SQL Server instance hosting the forums. You need to find the poorly performing stored procedures and optimize them. Second, employees in the finance department use database queries from Microsoft Office Excel to retreive usage statistics for the community Web site. These queries currently take up to 30 minutes to execute depending on what statistics are being retreived. The CFO has stated that the execution time for these queries must be reduced to a couple of seconds.

Answers

 

To find out which stored procedures are performing poorly, you should examine the output of a SQL Trace (through Profiler or the trace stored procedures). Queries with value in the Duration column that is too high need to be optimized. When optimizing these queries, execute them in SQL Server Management Studio, examine their graphical execution, and consider rewriting the queries as well as redesigning the indexes on the queried tables. To reduce the query execution times from up to 30 minutes down to a couple of seconds, you will most likely have to do something other than design appropriate indexes. First, examine if these long response times are due to the finance queries being blocked by other users. If this is the issue, one solution may be to replicate the data to another SQL Server instance and execute the finance queries against this instance instead of the production server. If blocking is not the problem, either consider de-normalizing the tables to allow for shorter response times or using Analysis Services Cubes as a source for the finance department's queries.