performance Tunning continue…

We have discussed performance tunning part1, continue on that…

Resource is the base for performance, if you don’t have sufficient space the optimum query/system may also not work efficiently.


Latch is for physical synchronization, like while

>> accessing pages from IO to memory and vice versa,

>> writing data to log

>> backup


>>Service broker

>> Full text

>> Versioning

>> Query Optimization

>> CLR


                Locks are for data consistence, it’s over query to access/work on pages, Lock occurs on memory objects/pages.

Lock escalation as Page/row -> partition/table -> Database



                Waits are of two types

    1. System session waits :  
  • Sessions are idle and wake up scheduled or whenever resources are free (e.g. Lazy writer/log writer/checkpoint Sleep…)
  • Due to incompatible locks on the pages related session has to wait for earlier session to complete or release the lock.
  • Due to heavy load on the resource, the session is waiting for resource to response
  • Due to blocking/deadlock.
    1. Waiting task impacted by user query


  • When a session/query access the memory pages it will require related locks on that particular page/resource and if other query/session require to access same page but that page was exclusively locked, then blocking occurs and first session blocks second session.
  • As stated earlier if first session might be waiting for other resource then also other session depends on first sessions than also second session blocking first one.
  • Blocking can be due to any reason (lock/Waits/Latches) the first session is holding /blocking the current session. Means – Blocking depends upon lock/waits/latch/other holding of stuff.
  • Blocking could also occur due to other third party or linked server.
  • Deadlock is sequence of first session is reverse to second one and the chain would never complete, in that situation one session has to be terminated due to deadlock victim.


Performance impact due to :

Blocking /Waits

Resource bottleneck

Next blog will be having related queries and process…


This entry was posted in Performance Tuning and tagged . Bookmark the permalink.

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.