Invoking Physical Reads in IO Statistics

• Lonely Rogue

  SSCrazy

  Points: 2578

  More actions

  September 18, 2014 at 11:53 pm

  #296570

  Hello folks -

  To see the Physical Reads in the IO STATISTICS am using CHECKPOINT and then DROPCLEANBUFFERS. But, it's not happening still.

  A small table -

  CREATE TABLE [dbo].[Tab1](

  [ID] [int] NULL,

  [IdentCol] [int] IDENTITY(1,1) NOT NULL,

  [CurrentDate] [datetime] NULL

  )

  I Inserted some rows.

  SET STATISTICS IO ON;

  GO

  CHECKPOINT;-- Flush the Dirty Pages

  GO

  DBCC DROPCLEANBUFFERS ;-- Empties the buffers

  GO

  --Buffer Descriptors

  SELECT OBJECT_NAME(P.Object_Id),*

  FROM Sys.dm_os_buffer_descriptors d

  JOIN Sys.allocation_units u ON d.allocation_unit_id=u.allocation_unit_id

  JOIN Sys.partitions p ON u.container_id=CASE WHEN u.type IN(1,3) THEN p.hobt_id ELSE p.partition_id END

  WHERE database_id=DB_ID()

  AND P.object_id=object_id('tab1')

  Above query retrieved no records implying there are no buffers for this table.

  Issued -

  SELECT * FROM tab1

  (108 row(s) affected)

  Table 'Tab1'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  The IO Stats still shows no Physical Reads. Am using SQL Server Dev 2008 R2. What else could be the reason?

  --In 'thoughts'...
  Lonely Rogue

• Lonely Rogue

  SSCrazy

  Points: 2578

  More actions

  September 21, 2014 at 4:36 am

  #1746912

  no one to help 🙁

  --In 'thoughts'...
  Lonely Rogue