| Six months ago, you were part of a development team that deployed a large and complex ASP.NET application into production. The Web application is used by customers of a large insurance company to get rate quotes for insurance. Built with multiple tiers, the Web application accesses a SQL Server 2005 database that has been partitioned across multiple servers using Data-Dependent Routing. For the past six months, the application has been performing well and no slowdowns have been reported. All of the sudden, the application started experiencing sporadic but dramatic slowdowns. When the application was moved into production, key performance counters were identified and performance baselines established for all database servers. You have been asked to quickly determine what is causing the problem and make a recommendation for resolving the issue. What steps do you take to analyze the cause of the problem? |
|
Answers
| Because the problem has occurred suddenly and appears sporadically, it is not likely to be a memory issue. However, nothing should be overruled at this point. The first thing you should do is to execute Windows System Monitor using the key counters included in the baseline. Save the log results to the database or a log file. You will need to continue monitoring until the performance problem has been observed. Note the time when the issue occurred and when the monitoring stopped. At the same time, run a trace using the events from the baseline using SQL Server Profiler and log it to the database or a log file. Then, compare the resulting counter and trace logs to the log files created for the performance baseline. Note outliers and investigate to determine if they are indicative of the problem. It might be necessary to execute queries using various dynamic management views to investigate an outlier. It might also be necessary to repeat the monitoring until you determine a root cause. Once you identify a suspected cause, make the appropriate changes and repeat the monitoring to determine if the outliers are still present. Continue this process until you are confident that the problem has been identified and resolved. You should then create a new performance baseline for all database servers. This will be used for any issues that might arise in the future. |