Whenever you execute any query….
Everything will be on Memory unless data is not available from disk(file).
The command will go to relational engine and create a query plan for the same. –compile plan (memory) and execute plan( memory). Once the plan is generated… it will pass it to storage engine… storage engine will retrieve the required data pages from Disk (File/Filegroup) and retrieve the data pages to memory using Latches (retrieving synchronization)….. and on memory pages usage locks for consistency.
Once data is in memory it will transferred to Output –user.
Deletion of data means just unallocated the data pages. When we run any deletion command. It will fetch the required page into memory (latches). and then de allocate the row(s) from that page(s) using (locks). once that is done it will log the records in ldf file (using latch – writelog)and send a confirmation message to output. And after the lazy writer or checkpoint process (usage Latches)it will flush the dirty pages back to disk. Delete record means job de-allocate space and mark them as ghost, ghost cleanup task will clean that pages. Ghost cleanup starts every 5minutes (10minutes for SS2008)
Inserted will perform similar stuff as delete but just reverse the transaction, instead of deletion it will insert the rows into page(s).
Index : Both deletion and insert cause fragmentation. Insertion also responsible for page split .
Update is a combination of Delete and insert.
So – latches plays very important role in any transaction, we don’t have direct control on latches. Whenever we require any pages require latchs.
Locks are on memory – when we retrieve data pages of objects.