May I know in what cases , for the same query , the logical write, from sys.dm_exec_query_stats will be higher than the other execution ?
how to get logical write from : set statistics IO on ?
May I know in what cases , for the same query , the logical write, from sys.dm_exec_query_stats will be higher than the other execution ?
how to get logical write from : set statistics IO on ?
Hi sakuraime,
How are things going on?
Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.
Best Regards,
Amelia
A parameterised query can certainly produce a different number of logical reads or writes for different parameter values.
but are there any other similar option like statistics IO on to check the logical write immediately ?
So what's wrong with SET STATISTICS IO? Why do you want a second command?
(As long as you only ask short questions, you will only get short answers. If you detail what your real concern is, we might be able to give you a better answer.)
please read my question. hope you understand
how to get logical write from : set statistics IO on ?
SET STATISTICS IO ON
CREATE TABLE nisse(a int NOT NULL)
INSERT nisse(a)
SELECT object_id FROM sys.objects
SET STATISTICS IO OFF
The output on my machine:
Table 'nisse'. Scan count 0, logical reads 102, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(102 rows affected)
Those "reads" for nisse cannot rows read, since the table is empty, so I guess we will have to assume that they are writes.
But obviously for an UPDATE or DELETE operation, where there also may have to be reads to locate the rows, I guess the numbers are a mix or reads and writes.
Hi sakuraime,
In addition, to get logical write from sys.dm_exec_query_stats, please refer to this query, it shows us the number of Logical Writes incurred by an SQL statement, which might be helpful:
SELECT
max_logical_writes, total_logical_writes, last_logical_writes,
execution_count, QueryString
FROM sys.dm_exec_query_stats
CROSS APPLY (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2 + 1) AS QueryString
FROM sys.dm_exec_sql_text(sql_handle)
) AS query_text
WHERE QueryString LIKE '%test%'
ORDER BY total_logical_writes DESC;

Best Regards,
Amelia
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
14 people are following this question.