Is high logical read terrible for performance? AFAIK this is reading from memory, which is fast
1
vote
2
answers
951
views
I have database query that seems to have abnormally high logical read (refer below).
I am not a DBA, but from the plan it seems pretty optimized to me? There are 2 index scans but the cost isn't that bad IMHO, just **14%** and **4%** respectively.
I am **more interested in addressing the seemingly abnormally high logical reads** (relative to the other queries).
**The execution plan for this query**, pasted into Paste The Plan: https://www.brentozar.com/pastetheplan/?id=BJj8s6L1a
This is the query generated by Entity Framework.

-sql
/*
This query text was retrieved from showplan XML, and may be truncated.
*/
SELECT [a].[Id], [a].[UserName], [a].[Email], [a].[PhoneNumber], [a].[FullName], [a].[StaffNo], [a].[Division], [a].[Department], [a].[Section], [a].[ModifiedDate], [a].[UserType], [a].[UserRegistrationStatus], [a].[Company], [a].[Remark]
FROM [AspNetUsers] AS [a]
WHERE ((([a].[IsActive] = CAST(1 AS bit)) AND ([a].[IsDeleted] = CAST(0 AS bit))) AND EXISTS (
SELECT 1
FROM [AspNetUserClaims] AS [a0]
WHERE ([a].[Id] = [a0].[UserId]) AND (([a0].[ClaimType] = 'tenantId') AND ([a0].[ClaimValue] = @__currentTenantId_0)))) AND EXISTS (
SELECT 1
FROM [UserAuthorizedArea] AS [u]
INNER JOIN [AspNetRoles] AS [a1] ON [u].[RoleId] = [a1].[Id]
WHERE ((([u].[IsDeleted] = CAST(0 AS bit)) AND ([u].[TenantId] = @__ef_filter__CurrentTenantId_0)) AND ([a].[Id] = [u].[UserId])) AND ([a1].[RoleType] = @__request_SearchUserModel_RoleName_1))
ORDER BY [a].[Id]
OFFSET @__p_2 ROWS FETCH NEXT @__p_3 ROWS ONLY
Any idea **how this query can be optimized further** to reduce the logical read?
**Number of executions** : 1,883
**Total durations (ms)** : 809,714 ms
**Average query duration** : 809,714 / 1,883 = 430 ms

Asked by Rosdi Kasim
(111 rep)
Sep 19, 2023, 03:12 AM
Last activity: Sep 19, 2023, 01:43 PM
Last activity: Sep 19, 2023, 01:43 PM