Case Scenario: Diagnosing a Performance Problem


 

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?

image from book

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.




MCITP Self-Paced Training Kit Exam 70-442  .Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
MCITP Self-Paced Training Kit Exam 70-442 .Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
ISBN: 073562383X
EAN: N/A
Year: 2007
Pages: 162

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net