How Query execute

Whenever you execute any query….

Everything will be on Memory unless data is not available from disk(file).

Select :

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.

Delete:

            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)

Insert:

            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:

            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.

About these ads
This entry was posted in Basic, Internal and tagged . Bookmark the permalink.

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