Normally performance
problems are reported by application developers or Clients.
First, we have to gather
information about the performance issue like
·
Check
whether the issue is for entire application or any specific feature in the
application.
·
Check
whether the issue is after recent upgrades to application or
sqlserver or OS or any hardware changes.
·
Check
whether any changes to the data or increase in number of users on the SQL
Server recently.
·
Collect
the baseline metrics on the application or query i.e. how much time it used to
take before and how much time it taking now?
Troubleshooting approach
will differ for general performance problems affecting whole application or
more specific problem like slowness with specific Stored Procedure or Query.
General Performance
Troubleshooting:
·
Check the
overall hardware resource usage like, CPU usage on the server, Memory usage on
the server, I/O usage on the server and Network usage.
·
If yes,
then drill further down in that direction, if everything looks normal, then
will proceed with checking at SQL Server level.
·
Check SQL Server error logs and event logs for
any errors.
·
Check for
any blocking or deadlocks.
·
Check
waits stats to see the top waits.
·
Check if there are regular maintenance on the SQL Server like rebuilding indexes and
update of statistics. If not, then implement those which will significantly
improve the performance.
·
Run DMV’s
to identify Top Duration, Top CPU, and Top Read or Write intensive queries and
try to tune them by creating appropriate indexes or report them to developer
suggesting to re-write those queries.
· Checking for SQL configuration settings like,
MaxDoP, SQL Max Server Memory, Lock Pages in Memory, Instant File Initialization,
Auto-Growth settings, etc.
Above steps should help in
understanding the performance problem and in fixing the same.
0 comments:
Post a Comment