reads for DMVs
-
Hi All,
I have created a test table with rowsize ~8K.
I have inserted 2 rows and when I read data from the table, then it essentially has to show me 2 logical reads but it showing 6. why is it so? Am I missing anything?
Second question is why sp_whoisactive is showing this spid information.
/*
spid =55
==============
*/
create table test
(
id int primary key, --4 bytes
ename varchar(7000) --7000 chars
)
go
insert into test
select 101,REPLICATE('A' , 7000)
union all
select 102,REPLICATE('B' , 7000)
go
/*
spid =56
==============
*/
select * from test;
--monitor queries
use master
go
select session_id as spid,status,reads,logical_reads from sys.dm_exec_sessions
where session_id = 56
and is_user_process = 1
go
exec master..sp_WhoIsActive
@Output_Column_List = '[session_id][status][reads][physical%][sql_%]'
,@show_sleeping_spids= 1
,@filter_type = 'session', @filter = '56'
GoEnvironment:
using Microsoft SQL Server 2017 (RTM-CU31).
Thanks,
Sam
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
Reads are not a measure of rows. They're a measure of pages. That includes disk and memory. For a tiny table, it's probably on mixed extents, so while, in theory, your tiny two rows would be stored in a single page, more than one is involved, in this case, 6.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
You are correct. Each 7000 byte + the ID row occupies 1 page.
On my box, I'm always getting 2 reads more than the number of leaf level pages regardless of the number of rows. I suspect that's from SQL Server checking to see if it already has a plan for the code or not but I don't know for sure.
I don't know if it helps or not but the 2 row version of the table contains 3 pages... 1 page with two rows on it as the "Root" of the B-Tree and 2 pages in the leaf level with the data. That's on SQL Server 2022.
On SQL Server 2017 on the first run, I 6 reads and then it dropped down to just 2 extra reads + the 2 actual reads on subsequent runs.
So, the answer to your question is "Because SQL Server is doing other stuff than just reading your data". 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Created a table in such a way, each rowsize is one 8K page.
Now when I execute this
SELECT
T.name as 'Table',
ix.index_id,
ix.[name] AS IndexName,
ix.type_desc AS IndexType,
[ips].[index_level],
ips.page_count AS [PageCount]
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.tables T on T.object_id = ips.object_id
INNER JOIN sys.indexes ix ON ips.[object_id] = ix.[object_id] AND ips.index_id = ix.index_id
INNER JOIN sys.objects ob ON ix.[object_id] = ob.[object_id]
WHERE ob.[type] IN('U','V')
AND ob.is_ms_shipped = 0
and T.name ='test'
I can see 3 pages . one Root page and two leaf level pages.
From where is the 4th page coming from?
SET STATISTICS IO ON
select * from test;
(2 rows affected) Table 'test'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Thanks,
Sam
-
Remember, it's not just pages on disk. It's also pages in memory, and every page on disk has to be moved into memory.
Different question. Why does it matter? I mean a query reading 2 pages or 3? 3 or 4? 4 or 400 matters to me. 4 or 4,000,000,000 matters even more.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - vsamantha35 wrote:
Created a table in such a way, each rowsize is one 8K page.
Now when I execute this
SELECT T.name as 'Table', ix.index_id, ix.[name] AS IndexName, ix.type_desc AS IndexType, [ips].[index_level], ips.page_count AS [PageCount] FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') ips INNER JOIN sys.tables T on T.object_id = ips.object_id INNER JOIN sys.indexes ix ON ips.[object_id] = ix.[object_id] AND ips.index_id = ix.index_id INNER JOIN sys.objects ob ON ix.[object_id] = ob.[object_id] WHERE ob.[type] IN('U','V') AND ob.is_ms_shipped = 0 and T.name ='test'
I can see 3 pages . one Root page and two leaf level pages.
From where is the 4th page coming from?
SET STATISTICS IO ON
select * from test; slope game
(2 rows affected) Table 'test'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Thanks,
Sam
That will not be effective in solving the issue since it is the same as before: If there are (sleeping)sessions (any user connected to that database, for example, just open a query window using context of that database), they have S-lock on the database, and the session trying to set the database offline or in single user will be blocked because it needs X-lock on the database; what you should do instead is to make use of ALTER DATABASE.
- Stephanie226 wrote:vsamantha35 wrote:
Created a table in such a way, each rowsize is one 8K page.
Now when I execute this
SELECT T.name as 'Table', ix.index_id, ix.[name] AS IndexName, ix.type_desc AS IndexType, [ips].[index_level], ips.page_count AS [PageCount] FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') ips INNER JOIN sys.tables T on T.object_id = ips.object_id INNER JOIN sys.indexes ix ON ips.[object_id] = ix.[object_id] AND ips.index_id = ix.index_id INNER JOIN sys.objects ob ON ix.[object_id] = ob.[object_id] WHERE ob.[type] IN('U','V') AND ob.is_ms_shipped = 0 and T.name ='test'
I can see 3 pages . one Root page and two leaf level pages.
From where is the 4th page coming from?
SET STATISTICS IO ON
select * from test; slope game
(2 rows affected) Table 'test'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Thanks,
Sam
That will not be effective in solving the issue since it is the same as before: If there are (sleeping)sessions (any user connected to that database, for example, just open a query window using context of that database), they have S-lock on the database, and the session trying to set the database offline or in single user will be blocked because it needs X-lock on the database; what you should do instead is to make use of ALTER DATABASE.
Hi Stephanie226,
It would appear that you've misread/mistaken what "the issue" is. The OP isn't trying to do anything with the database itself. The OP simply wants to know why a query is using more reads than it appears that it should.
(Note to self... PP2S).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This was removed by the editor as SPAM
-
The discrepancy between the expected logical reads and the actual number of logical reads could be due to several factors. Here are a few possibilities to consider:
Data Page Structure: Each row in a table is stored on a data page, and if the row size is larger than the page size (8K in your case), it may require additional pages to accommodate the row. This is known as row overflow. When reading the rows, SQL Server may need to access multiple pages from geekydane resulting in more logical reads than expected.
Indexing: If your table has indexes, reading data may require accessing both the data pages and index pages. Depending on the structure and configuration of your indexes, additional logical reads may occur.
Caching: SQL Server uses a buffer cache to store frequently accessed data pages in memory. If the required data pages are already in the cache, they can be read from memory directly, resulting in fewer logical reads. However, if the pages are not in the cache, they must be fetched from disk, leading to additional logical reads.
Regarding your second question about sp_whoisactive showing SPID information, SPID (Server Process ID) is a unique identifier assigned to each connection or session in SQL Server. sp_whoisactive is a widely used stored procedure for monitoring and troubleshooting SQL Server activity. When you execute sp_whoisactive, it provides detailed information about the active sessions on the server, including the SPID associated with each session. This information is useful for identifying and analyzing active processes or queries.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply