question

sakuraime avatar image
0 Votes"
sakuraime asked AmeliaGu-msft commented

DMV query stats column : logcial_write

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 ?

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

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;

111647-image.png



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.



image.png (44.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered
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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered sakuraime commented

A parameterised query can certainly produce a different number of logical reads or writes for different parameter values.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

but are there any other similar option like statistics IO on to check the logical write immediately ?

0 Votes 0 ·

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.)

0 Votes 0 ·

please read my question. hope you understand

how to get logical write from : set statistics IO on ?

0 Votes 0 ·