1

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.

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
1
  • Thanks so much @Brad, I was thinking about this whole weekend lol. so in this way i just have one last doubt but is related the executions plans so, I will create a new post for my second question thanks,
    – Andres Angel
    Commented May 8, 2017 at 13:31
1

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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.