physical and logical IO activity
- Logical reads are always the same
- Physical reads have decreased to 0 since the 2nd run
| The amount of physical and logical IO activity | |
| 1st execution | Table 'SalesOrderDetail'. Scan count 1, logical reads 1247, physical reads 3, read-ahead reads 1285, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 3, read-ahead reads 685, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Store'. Scan count 1, logical reads 103, physical reads 1, read-ahead reads 101, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. |
| 2nd execution | Table 'SalesOrderDetail'. Scan count 1, logical reads 1247, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Store'. Scan count 1, logical reads 103, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. |
| 3rd execution | Table 'SalesOrderDetail'. Scan count 1, logical reads 1247, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Store'. Scan count 1, logical reads 103, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. |
| DBCC DROPCLEANBUFFERS | |
| 4th execution | Table 'SalesOrderDetail'. Scan count 1, logical reads 1247, physical reads 3, read-ahead reads 1285, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 3, read-ahead reads 685, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Store'. Scan count 1, logical reads 103, physical reads 1, read-ahead reads 101, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. |
- scenarios and details
------------------------------------------------
-- preparation
------------------------------------------------
--Download AdventureWorks backup files,
-- Restore DB
restore filelistonly from disk = 'C:\DB_file\AdventureWorks2016.bak'
restore database AdventureWorks
from disk = 'C:\DB_file\AdventureWorks2016.bak'
with move 'AdventureWorks2016_Data' to 'C:\DB_file\AdventureWorks.mdf',
move 'AdventureWorks2016_Log' to 'C:\DB_file\AdventureWorks_log.ldf'
use AdventureWorks
go
alter database AdventureWorks modify file(name ='AdventureWorks2016_Data',newname='AdventureWorks_Data')
alter database AdventureWorks modify file(name ='AdventureWorks2016_log',newname='AdventureWorks_log')
-- Check data pages that are currently in the SQL Server buffer pool.
USE AdventureWorks
go
SELECT OBJECT_NAME(p.object_id) [物件名稱],
p.index_id,
COUNT(*)/128. [耗用暫存區記憶體(MB)],
COUNT(*) [暫存區數量]
FROM sys.allocation_units au JOIN sys.dm_os_buffer_descriptors dobd
ON au.allocation_unit_id=dobd.allocation_unit_id
JOIN sys.partitions p on au.container_id=p.hobt_id
WHERE dobd.database_id=DB_ID() AND OBJECTPROPERTY(p.object_id,'ISSystemTable')=0
GROUP BY p.object_id,p.index_id
ORDER BY 4 DESC
-- Check execution plan usage
SELECT dest.text,deqp.query_plan,decp.objtype,decp.usecounts,deqp.dbid
FROM sys.dm_exec_cached_plans decp
OUTER APPLY sys.dm_exec_query_plan(decp.plan_handle) deqp
OUTER APPLY sys.dm_exec_sql_text(decp.plan_handle ) dest
-- The sql query that will be used for the IO test
set statistics io on
USE AdventureWorks
Select s.Name as Shop, SUM(SubTotal) AS SubTotal
from Sales.Store AS s left outer join Sales.SalesOrderHeader as soh on s.SalesPersonID = soh.SalesPersonID
left outer join Sales.SalesOrderDetail AS sod on soh.SalesOrderID = sod.SalesOrderID
group by s.Name
order by SUM(LineTotal) desc;
------------------------------------------------
-- 1st execution
------------------------------------------------
(699 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 1247, physical reads 3, read-ahead reads 1285, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 3, read-ahead reads 685, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Store'. Scan count 1, logical reads 103, physical reads 1, read-ahead reads 101, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
------------------------------------------------
-- 2nd execution
------------------------------------------------
(699 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 1247, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Store'. Scan count 1, logical reads 103, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
------------------------------------------------
-- 3rd execution
------------------------------------------------
(699 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 1247, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Store'. Scan count 1, logical reads 103, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
------------------------------------------------
-- Removes all clean buffers from the buffer pool
------------------------------------------------
DBCC DROPCLEANBUFFERS
------------------------------------------------
-- 4th execution
------------------------------------------------
(699 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 1247, physical reads 3, read-ahead reads 1285, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 3, read-ahead reads 685, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Store'. Scan count 1, logical reads 103, physical reads 1, read-ahead reads 101, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
