Identifying application problem, database problem, or hardware problem?
I have a web application backed by a SQL Server database that was running until yesterday. Now I'm having performance issues with this app. How do I know if there is a problem or a problem with the database or a hardware problem?
Can anyone help me with a basic troubleshooting step-by-step procedure to figure out if the performance issue is application, database, or hardware related?
a source to share
This question will require additional questions before you can get any closer to diagnosing the problem:
Do you have the source code? Do you know what SQL statements are being executed at a time when an application is experiencing performance problems? If so, you can run SQL statements directly from the DB directly from the SQL console window and see if the performance issue is solely 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 are full of good stuff.
Assuming the DB responds satisfactorily, does the network exist? Is this a web application? Do you have access to the web server web logs?
Is the problem limited to a specific set of users? Some users have a problem and others don't?
a source to share
First first ... define a list of all changes, no changes are too few.
Once you have a list of changes, start putting them off one by one.
Step by step details ehhh ... that's tough. I am always looking for something obvious. If I see something suspicious, I will stop at 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 making a list of everything that has changed with each part of the infrastructure (Firewall, Switch, DB, HotFix, Web Server, ...). If something has changed, I always ask for more information about this change. I assume you don't have that information, not that I'm trying to let you go, it takes an organization to start committing all the changes until you reach a level of maturity with their operations.
2) Start looking for magazines. Since all my applications are on Windows Server, I start by looking at the application event log. I am looking for application errors. Then I go to the system event log looking for errors again. Then I can analyze my IIS logs .... Usually these logs include a time field, so I focus on long running queries.
3) Next, I'll take a look at the database server. I will ask my DBA to run SQL Profiler to see which queries are taking a long time. I will also ask them to collect database lock information. I also ask them to check the health of the database (current indexes, table fragments / indexes).
4) Next, I have Windows Server Administrators collecting performance counter statistics on the web server and SQL Server. I want to look for memory leaks, I / O queues, cpu load.
a source to share
The first suspect is a code change. If something happened in the code and a performance issue correlates with it, then this is the prime suspect.
If nothing has changed, the database is a good suspect. Assuming you are using MS-SQL from your tag, two scenarios are possible:
-
The data goes to a certain level where the engine changed the algorithm used to execute queries, and this new algorithm requires different indexing.
-
The indexes just need to be rebuilt. Rebuilding the indexes is pretty trivial as you can see from this link .
a source to share