Query Performing Slowly
1) Identify the SPID of the query from
Sys.sysprocesses
2) Find any blockings exists or not and if
it exists find the LEAD Blocker.
3) Identify how many connections are there,
Benchmark if exceeds is an issue
4) CPU Utilization, Memory Utilization,
System Memory Information and how much SQL Server is consuming. If AWE is
enabled or not.
5) Any jobs are running both at OS level
and SQL Server level.
6) Affinity Mask for Processor and IO
should be checked.
7) Query observation, Coding standards have
been followed or not. Commit issued at regular intervals in the code.
8) Table statistics are outdated. Need to
update stats.
9) Indexes are created on correct columns
are not. Verify the query and check the columns listed under WHERE condition
and ensure that there are indexes created.
10) Even if indexes are created, they are
fragmented or not.
11) Run server side trace to track what
other activities are currently being rolled out to find the cause of the issue.
If approved Profiler also can be used.
12) Network Latency between Client and the
Server can cause performance issue.
13) MAXDOP feature can be utilized to
improve the parallel execution of the query.
14) If clustered instance and Active-Active
(N+1/N+M) Multiple Instance. If all nodes fail the last surviving node has all
instances running on it causing performance issue.
15 ) Check disk space availability for that database and also for system databases.
16 ) Verify if any Application or Database specific jobs are running or not.
17 ) Network bandwidth to be verified and also Storage SAN bandwidth when accessing data. Contact Network/Storage team.
18) Identify top 10 long running queries and see if they are causing the performance lag.
19) Ask requester for the Query. Verify the Estimated Execution Plan of the Query.
20) Check if any Table Scans are present in the Plan. Table Scans are very expensive from resource perspective.
Also verify the Load on the system, Example average load is 2500 but we could see 10,000 connections.
15 ) Check disk space availability for that database and also for system databases.
16 ) Verify if any Application or Database specific jobs are running or not.
17 ) Network bandwidth to be verified and also Storage SAN bandwidth when accessing data. Contact Network/Storage team.
18) Identify top 10 long running queries and see if they are causing the performance lag.
19) Ask requester for the Query. Verify the Estimated Execution Plan of the Query.
20) Check if any Table Scans are present in the Plan. Table Scans are very expensive from resource perspective.
Also verify the Load on the system, Example average load is 2500 but we could see 10,000 connections.
No comments:
Post a Comment