I have a web application, backed by a SQL Server database, which was working fine till yesterday. Now I have performance issues with that application. How do I know whether it is an application issue or database issue or hardware issue?
Can anyone help guide me through a step by step procedure of basic troubleshooting to find out whether the performance issue is related to the application, database, or hardware?
This question is going to require additional questions before you can get close to diagnosing the problem:
Do you have the source code? Do you know what SQL statements are being executed during the time that the app is having performance problems? If so, you can run the SQL statements against the DB directly from a SQL console window and see if the performance issue is purely in the DB.
Do you have access to the DB logs? I'm not familiar with SQL server logs but I know Oracle has a bunch of them and they're full of good stuff.
Assuming the DB appears to be responding satisfactorily, is there a network involved? Is this a web application? Do you have access to the Web logs of the web server?
Is the problem limited to a certain set of users? Do some users experience the problem, and other don't?
Run Profiler. Filter by duration where value > 50 ms and you are very likely to find the worst offenders. If the statements are SELECT's, copy them to the query analyzer and run displaying the actual execution plan and tune accordingly (create indexes, etc).
First things first... define a list of all changes, no change is too minor.
Once you have that list of changes start backing them out one by one.
Step by step details ehhh... that's a tough one. I am always looking for something obvious. If I see something that looks suspicous I will stop what I am doing and investigate further or I will put it on a white board as a possible problem.
1) Again I always start by creating a list of everything that has changed with every piece of the infrastructure (Firewall, Switch, DB, HotFix, Web Server, ...) If something has changed then I always request that have more information about that change. My guess is you do not have any such information, not that I'm trying to put you down it takes organizations a while before the reach the proper maturity level with their operations to start capturing all changes.
2) Start looking at logs. Since all of my applications are on Windows Server I start by looking at the Application Event Log. I'm looking for application errors. Next I go to the System Event log, again I'm looking for errors. Next I may do an analysis on my IIS logs.... I usualy have the time-taken field enabled in these logs so I focus on long running requests.
3) Next I will look at the DB server. I will ask my DBA to run SQL Profiler to see what queries are taking a long time. I will also ask that they collect information about database locks. I also ask them to check the health of DB (are the indexes up to date, are the tables/indexes fragmented).
4) Next I have Windows Server Admins collect performance counter statistics on Web Server and SQL Server. I want to look for memory leaks, IO queuing, CPU utilization.
The first suspect is a change in the code. If something happened in the code and the performance issue correlates with that, then that is the prime suspect.
If nothing changed, then the database is a good suspect. Assuming from your tag you are using MS-SQL, there are two likely scenarios:
The data hit a certain level where the engine changed the algorithm used to execute the queries, and this new algorithm requires different indexing.
The indexes simply need to be rebuilt. Rebuilding indexes is quite trivial, as you can see from this link.
user will say: it is running slow
boss will say: fix it
network guy will say: it is a database problem
database guy will say: it is an application problem
application guy will say: it is a network problem