Question :
I would like to ask you something about a query execution in SQL. in general, I know that when I run a SELECT query I mean a view, this is loaded in cache memory then is parsed, compiled and then the query optimizer applies improvements based on execution plans so for that we have also the support coming from buffer pool (storage engine and relation engine). So, all that happens in memory and finally the result set is sent to SQL client.
Otherwise, we have the log file in a DB and here is my doubt because I don’t know if the log file in SQL is also used in queries ‘SELECT’ executions. Or it’s just used in transactions like an update, delete or insert so I really would like to know the role of transactional log in SQL when I perform a query (view) and also his role in the other DML transactions.
Answer :
Yes, SELECT statements can in fact use space in the tempdb transaction log, as temporary working space to complete the query:
The Transaction Log (SQL Server):
Long-running transactions might also fill up tempdb’s transaction log. Tempdb is used implicitly by user transactions for internal objects such as work tables for sorting, work files for hashing, cursor work tables, and row versioning. Even if the user transaction includes only reading data (SELECT queries), internal objects may be created and used under user transactions. Then the tempdb transaction log can be filled.
Generally, the transaction log only interacts with data modification language statements such as insert, update and delete by recording the exact modifications made to the database so those changes can be guaranteed to either complete or be rolled-back. This is the primary mechanism by which SQL Server guarantees ACID compliance.
Select statements are not generally affected by the transaction log.
However, heavy transaction log activity resulting from a large number of concurrent DML statements may slow the system so much that select statements may appear to be affected.
If you have a specific question, please add the details to your question.