Troubleshooting Performance -1

Whenever you get a call from user that they are facing any performance issue what would you do.

As I blog earlier on this, we should follow the top down approach where we need to see the scenario from bigger picture first, and have to ask some general questions:

  1. From how long are you facing this issue?
  2. Are you the only one who is having issue?
  3. Is this issue happen at particular time?
  4. Why do you this is this database related(optional)?

First thing to check:

  1. Get the bigger picture first – Is server accessible?
  2. Login on to the server and see if you can able get in to sql server ?
  3. If you have a benchmark compare the resources against it?

Lock/Blocking/Waits/Latch/ Deadlock:

Locks:

            For any operations requires lock on the resources to maintain the consistence on transactions. Prevent resources.

  1. Lock: Guarantee consistency
    1. Shared (S)
    2. Update (U)
    3. eXclusive (X)

Intent Shared(IS) /Intent eXclusive (IX) /Shared Intent eXclusive (SIX). 

Level locks –LOCK escalation

  1. 1.      Page/Row
  2. 2.      Partition/Table
  3. 3.      Database

Locks

IS

S

U

IX

SIX

X

IS

Y

Y

Y

Y

Y

N

S

Y

Y

Y

N

N

N

U

Y

Y

N

N

N

N

IX

Y

N

N

Y

N

N

SIX

Y

N

N

N

N

N

X

N

N

N

N

N

N

 

X : is not compatible with any other locks, means when you have an eXclusive lock on particular object(row/page/table/database) that cannot be accessible by anyone until eXlcusive lock released.

When you want update a particular row(s).

  1. Shared (s) Lock on complete Database.
  2. An intent eXclusive(IX) lock will be held on to that Table Lock
  3. An intent eXclusive(IX) lock will be held on to that Page Lock
  4. An eXclusive lock will be held on to that Row Lock.

Latch:

  1.             Latch : is a physical synchronization. Used by the system.
    1. KP
    2. NL
    3. SH
    4. UP
    5. EX
    6. DT

Latches are also required for physical consistence internally by the system.

Latch Wait time is reset every 5minutes

But due to Locks and latches we may have to get “Blocking” which interns require other process to wait for the resource to be available. These Blocking will automatically released once that resource is available. If the wait time of the process goes high result to performance problem.

 Now a day’s no one wants to wait for our results to be slow, so minimum waits/blocking is a goal these days.

Blocking/Waits : Live Lock

Blocking: When two/more processes are dependent and one resource is in “Running” state the other process is blocked by “running” process. –Live locking

Waits : Blocking happens and blocked process is waiting… for what resource waiting is happening is wait types.

Blocking is the general issue with the performance/speed of any query- data access. But blocking what is also important. Blocking – means on process/thread* is processing which is holding that resource(s) and during that if other process is trying to access the same could cause blocking.

Blockage are generally

>>if some resources are being accesses Exclusively and other tries to access.(programming issue).

>>Access usage of some resource and due to that the resource is not able to response per the request. (heavy load).

>>Accidently major changes to existing system(unplanned workload )

>>Recourse failure- accidently (breakdown)

>> Make small transactions.

You can get so much documentation on net about the theoretical information. But here I am writing specifically about the commands – for performance tuning – a consolidated place where I could just logon and work my daily activity.

* “process“ and “thread” are same

There are several ways to do the single stuff. I will try to make maximum troubleshoot techniques.

Here is the brief definition of each

There are three states of the process:

Running: Process is having all the required resources and its in running state –active

Runnable : Waiting for resource to be available –ready to run.

Suspended: after Running if required resource is not available. Process goes to suspended during the time until resources are available.

Deadlock: Due to the sequence of the programming, if  the lock dependencies are interdependent and cause –deadlock.

Due to all these, we would get performance issue.  There are several ways to resolve these issues.

So how to make our system work in such a way that we get minimum blocking/waits.

>> Less locks

>> Less latches

>> High speed resources –memory/IO/CPU (more….)

>> Optimum use of the resources , Check the bottleneck  if the Resources is properly configured –like IO/Tempdb/Memory (min/max)CPU –using perfmon/…

>> Proper configuration to get optimum result.

>> Overall system check : Any external pressure.

>> Check the existing query – tune the query to run efficiently

>> Long Running queries (time/Resources)

Versioning:

Isolation Level:

Resources:

IO

Memory

Processor

Network

Tools:

Data Collector

Perfmon:

Profiler/user Trace:

XEvent:

Other Tools:

 

Will blog on each in detail later in this series of blogs. in this series I will detail about perfromance tunning.

Rerernece: MCM_Readiness -videos

 

About these ads
This entry was posted in Performance Tuning, Webcast and tagged . Bookmark the permalink.

One Response to Troubleshooting Performance -1

  1. deepak says:

    Hi Vinay, Cannot is this happen due to Fragmentation? If this is please provide the steps to check for slow running query. How to check Fragmentation like through sys.dm_db_index_physical_stats, sys.dm_db_index_operational_stats, sys.dm_db_index_usage_stats.

    Thanks
    Deepak

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s