1

Concerning execution model of SQLOS (RUNNING state, RUNNABLE queue, WAITER list), what will be the state of a task when there is a logical read of pages in RAM currently going on?

If it's a WAITER list, what would be the most prevalent wait type?

Can I somehow measure the time that such operation takes?

I know lots of logical reads slow down your queries, lots of table / index scans (resides already in buffer pool) slow down your queries - I just want to know how do they appear in the statistics/dmv's or how to distinguish it from other "classic" wait types.

  • 1
    I'm confused - Are you asking if SQL Server keeps a record of the amount of time it takes the cpu to fetch contents of ram? – Sean says Remove Sara Chipps Aug 15 '18 at 21:28
6

A worker goes on a waiter list when it needs to wait for some kind of resource. There is no resource to wait on for logical reads. Assuming that your buffer pool hasn't paged out the data is already in memory managed by SQL Server. Queries that do lots of logical reads are slow because they spend a lot of CPU doing those logical reads.

If you want to get an idea of what internal operations SQL Server is doing when performing logical reads then you could do ETW tracing using perfview or some other tool while running a query which is bottlenecked by logical reads. ETW tracing might be a good step if you believe that your workload has issues with memory throughput. Please note that this is probably not the case and the following demo is almost certainly the wrong way to approach a query performance issue.

First I'll hack together a query which I expect to spend most of its time doing logical reads:

DROP TABLE IF EXISTS #215016;

SELECT 1 ID, REPLICATE('Z', 1000) FILLER INTO #215016
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

SELECT t1.ID, t2.ID
FROM #215016 t1
INNER JOIN #215016 t2 ON t1.ID < t2.ID
OPTION (MAXDOP 1, QueryRuleOff BuildSpool);

SQL Server used a full core of CPU throughout the query's execution. Here are perfview results taken from a ten second sample:

ETW

Perhaps the circled method name is what you're interested in. For this query SQL Server spends about 5% of its CPU time on sqlmin!BPool::Get, which I assume has to do with actually performing the logical read. In defense of that idea, that method is what calls sqlmin!BUF::AcquireLatch most of the time:

enter image description here

and the number of shared latches taken during the query is about the same as the number of logical reads. But this is just a guess because method names aren't documented by Microsoft and I don't know the full scope of a "logical read".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.