High lob logical reads on a query with just 2 simple tables

Question

Post reply

High lob logical reads on a query with just 2 simple tables

Santhoshkumar KB

SSCertifiable

Points: 5066
More actions
October 3, 2016 at 2:46 am

#330318

/*
Table1: NotificationType (NotificationTypeID INT PK Clustered, NotificationCriteria xml, TypeName Varchar(200), Deleted char(1))
Table1 has 25 rows
Table2: Subscription (SubscriptionID INT PK Clustered, NotificationTypeID FK, SubscriberID, SubscriptionCriteria xml, Status smallint, Deleted char(1))
Table2 has 3000 rows
*/
SELECT t1.SubscriptionID, t1.NotificationTypeID
, t2.NotificationCriteria--xml field (for notificationTypeID = 12, the length of xml field is 12000 characters)
, t1.SubscriberID
, t1.SubscriptionCriteria--xml field
, t1.Status, t1.FailedDate
FROM Subscription t1 WITH (NOLOCK)
INNER JOIN NotificationType t2 ON t2.NotificationTypeID = t1.NotificationTypeID
WHERE t2.NotificationTypeID = 12--only 1 row exists
AND t1.Status != 1 --Inactive
AND t1.Status != 2 --Failed
AND t1.Deleted <> 'N'
with statistics io set to on
(2045 row(s) affected)
Table 'Subscription'. Scan count 2, logical reads 498, physical reads 0, read-ahead reads 0, lob logical reads 491, lob physical reads 0, lob read-ahead reads 421.
Table 'NotificationType'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 14333, lob physical reads 0, lob read-ahead reads 16232.
Takes around 12-15 seconds to run
Could someone let me know why is the lob logical reads way too high for table NotificationType although it is returning only 1 row for NotificationTypeID = 12 !!
Thanks,
KB
Thanks,
Santhosh
Human Knowledge Belongs To The World !!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you post the execution plan please?

p.s. Are you sure you want the potentially missing and duplicate rows that nolock allows?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 2

p.s. Are you sure you want the potentially missing and duplicate rows that nolock allows?

Yes, at this time.

Execution plan is attached (note, it has more columns than I trimmed earlier)

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Hmm... Odd.

How large are the XML columns being returned by that query? (use the DataLength() function)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 4

Alternative 1:

Also, if I try to get the @NotificationCriteria xml part into a variable (it can only be 1 record per typeID)

This gets rid of lob logical reads. However, this takes same time to complete: 12-15 seconds

DECLARE @NotificationCriteria xml

SELECT @NotificationCriteria = NotificationCriteria FROM NotificationType WHERE NotificationTypeID = 12

SELECT t1.SubscriptionID, t1.NotificationTypeID

, @NotificationCriteria --adding the variable here

--, t2.NotificationCriteria

, t1.SubscriberID, t1.SubscriptionCriteria

, t1.CreateDate, t1.UpdateDate, t1.DeleteDate, t1.Status, t1.FailedDate

FROM Subscription t1 WITH (NOLOCK)

INNER JOIN NotificationType t2 ON t2.NotificationTypeID = t1.NotificationTypeID

WHERE t2.NotificationTypeID = 12

--AND t1.Status != 1 --Inactive

AND t1.Status != 2 --Failed

AND t1.Deleted <> 'N'

Table 'NS_NotificationType'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(2045 row(s) affected)

Table 'NS_Subscription'. Scan count 2, logical reads 498, physical reads 0, read-ahead reads 0, lob logical reads 507, lob physical reads 0, lob read-ahead reads 420.

Table 'NS_NotificationType'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 5

GilaMonster (10/3/2016)
Hmm... Odd.
How large are the XML columns being returned by that query? (use the DataLength() function)

NotificationCriteria is 16370 (constant for that 1 record) (this is the one having high lob in NotificationType table)

SubscriptionCriteria: max length is 11115 (this is another xml field in Subscription table that has low lob which is okay)

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 6

KBSK (10/3/2016)
SELECT t1.SubscriptionID, t1.NotificationTypeID , t2.NotificationCriteria--xml field (for notificationTypeID = 12, the length of xml field is 12000 characters) ... with statistics io set to on (2045 row(s) affected) Table 'Subscription'. Scan count 2, logical reads 498, physical reads 0, read-ahead reads 0, lob logical reads 491, lob physical reads 0, lob read-ahead reads 421. Table 'NotificationType'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 14333, lob physical reads 0, lob read-ahead reads 16232.
Takes around 12-15 seconds to run

Just cast'd the NotificationCriteria field to VARCHAR(MAX) and I got the result in less than a second with lob logical reads almost reduced to half

, CAST(nsnt.NotificationCriteria AS VARCHAR(MAX)) AS NotificationCriteria

(2045 row(s) affected)

Table 'NS_Subscription'. Scan count 2, logical reads 498, physical reads 0, read-ahead reads 0, lob logical reads 496, lob physical reads 0, lob read-ahead reads 420.

Table 'NS_NotificationType'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 8180, lob physical reads 0, lob read-ahead reads 8180.

Comparing the queries:

1. Both the xml and CAST'd to VARCHAR(MAX) returns same number of rows and same data size 16MB

2. The xml one takes almost 12-15 seconds to run with lob logical reads of 14333

3. The CAST'd one takes less than a second to run with lob logical reads of 8180

These results were just for 3K records in Subscription table.

However, production system has 160K records with a performance hit already !!

So, lob logical reads of 8180 means - it read 8180 pages from the data cache(index) right? And more lob reads could mean performance hit right?

Is there any way that I could tune the query by returning xml field only?

Any suggestions would be of great help. Thank you,

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

I'm sorry, I don't know the answer to your question - however, Googling revealed this interesting teaser written by Paul White. You may well find pointers (pun intended) to your problem either within the article or in links on the page. Good luck.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 8

ChrisM@Work (10/4/2016)
I'm sorry, I don't know the answer to your question - however, Googling revealed this interesting teaser written by Paul White. You may well find pointers (pun intended) to your problem either within the article or in links on the page. Good luck.

That was really a worth read by Paul White. Thanks for the link.

On the same lines, I did use the Variable assignment for tuning before I went through the link.

At that time I did not understand why there was no lob reads when I am getting the xml data in to variable

When the variable was used in the next immediate query, there were very few lob reads (like 15 from the variable for my query).

As per the link here is what Paul's explaination on Variable assignment.

"Variable Assignment:

The Clustered Index Scan assigns a LOB handle as before. At the root of the plan, the LOB handle is copied to the variable. The LOB data itself is never accessed (zero LOB reads), because the variable is never read. Even if it were, it would only be via the LOB handle last assigned."

This is what I did in my query

DECLARE @NotificationCriteria xml

--Get the xml into variable

SELECT @NotificationCriteria = NotificationCriteria FROM NS_NotificationType WHERE NotificationTypeID = 12

SELECT nss.SubscriptionID, nss.NotificationTypeID

, @NotificationCriteria --Use the xml variable here

--, nsnt.NotificationCriteria

, nss.SubscriberID

, nss.SubscriptionCriteria

, nss.CreateDate, nss.UpdateDate, nss.DeleteDate, nss.Status, nss.FailedDate

FROM NS_Subscription nss WITH (NOLOCK)

--remove join as it caused repeated lob reads for just 1 row from NotificationType table

--INNER JOIN (SELECT * FROM NS_NotificationType WHERE NotificationTypeID = 12) nsnt

--ON nsnt.NotificationTypeID = nss.NotificationTypeID

WHERE

--nsnt.NotificationTypeID = 12

nss.NotificationTypeID = 12

--AND nss.Status != 1 --Inactive

AND

nss.Status != 2 --Failed

AND nss.Deleted <> 'N'

--===Statistics with @NotificationCriteria commented

Table 'NS_NotificationType'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'NS_Subscription'. Scan count 2, logical reads 493, physical reads 0, read-ahead reads 0, lob logical reads 492, lob physical reads 0, lob read-ahead reads 420.

--===Statistics with @NotificationCriteria uncommented

Table 'NS_NotificationType'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'NS_Subscription'. Scan count 2, logical reads 493, physical reads 0, read-ahead reads 0, lob logical reads 507, lob physical reads 0, lob read-ahead reads 422.

Thanks for the link and now I understand this better !!

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!