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
>> Full text
>> Query Optimization
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
- 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.
- 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 :
Next blog will be having related queries and process…