Total amount withdrawn by a customer query on highly active endpoint

Transact-SQL
1

We have this query to work out total amount withdrawn in the last 24 hours by a customer.

SELECT
SUM(Amount) SumWithdrawalAmount
FROM dbo.PaymentRequest pr WITH (READUNCOMMITTED)
WHERE
pr.PaymentRequestTypeId = 0 --Withdrawal
AND pr.CreateDate >= GETDATE()-1 --Last 24h
AND pr.PaymentAccountId = 'xxxxxxx-FD54-ED11-80F6-xxxxxxxxxxx'

SELECT
SUM(Amount) SumWithdrawalAmount
FROM dbo.PaymentRequest pr WITH (READUNCOMMITTED)
WHERE
pr.PaymentRequestTypeId = 0 --Withdrawal
AND pr.CreateDate >= GETDATE()-1 --Last 24h
AND pr.PaymentAccountId = 'xcxcxcx-FD54-ED11-80F6-xxxxxxxx'
AND pr.PaymentMethodExternalSystemId IN (1, 2, 3)

Suggestions on improving it ?
Query plan is attached.

image (8)
image (8)1881×770 209 KB

2

You can create a composite filtered index on the where conditions

pr.PaymentRequestTypeId = 0 --Withdrawal
pr.CreateDate >= GETDATE()-1 --Last 24h
pr.PaymentAccountId = 'xcxcxcx-FD54-ED11-80F6-xxxxxxxx'
pr.PaymentMethodExternalSystemId IN (1, 2, 3)

SELECT
    SUM(Amount) SumWithdrawalAmount
FROM 
    dbo.PaymentRequest pr WITH (READUNCOMMITTED)
WHERE
    pr.PaymentRequestTypeId = 0 --Withdrawal
AND 
    pr.CreateDate >= GETDATE()-1 --Last 24h
AND 
    pr.PaymentAccountId = 'xcxcxcx-FD54-ED11-80F6-xxxxxxxx'
AND 
    pr.PaymentMethodExternalSystemId IN (1, 2, 3)
3 
CREATE NONCLUSTERED INDEX [PaymentRequest__IX_CreateDate] ON dbo.PaymentRequest 
    ( CreateDate, PaymentAccountId, PaymentRequestTypeId, PaymentMethodExternalSystemId ) 
    INCLUDE ( Amount )
    WITH ( /*DATA_COMPRESSION = ROW,*/ FILLFACTOR = 90, /*ONLINE = ON,*/ SORT_IN_TEMPDB = ON );

Adjust the WITH to match your normal specifications for indexes.

4

Thanks for suggestions i should have mentioned that building a index on the table will be not possible.
The table is 439 million rows and over 2TB in size and causes too much contention as its highly available DB and table so cannot afford the downtime.

I was hoping that we could improve in some other way.

5

Also i dont believe filtered index is possible as cant build an index on non deterministic GUID column PaymentAccountID

6

You need to adjust an index one way or another to get better performance. You need a "covering index" for this query.

You could also create a new version of the existing index with an INCLUDE of the column(s) it's looking up, and once the new index is complete drop the old one.

7

leave that column

but on all the others

8
  1. Try everything out in a test system first.
  2. I am assuming the GUID is created by something like NEWID().
  3. With enterprise edition you might be able to use ONLINE indexing but as I have only every used standard edition I am renaming indexes.

Try running something like the following just after midnight every day so the filtered index never has more than two days worth of data.

SET ANSI_NULLS,  QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE Maintain_PaymentRequest_PaymentAccountId_IX_Recent
AS

SET NOCOUNT, XACT_ABORT ON;

IF EXISTS
(
	SELECT 1
	FROM sys.indexes 
	WHERE name='PaymentRequest_PaymentAccountId_IX_Recent_Recent_New' AND object_id = OBJECT_ID('dbo.PaymentRequest')
)
	DROP INDEX PaymentRequest_PaymentAccountId_IX_Recent_Recent_New ON dbo.PaymentRequest;

DECLARE @SQL varchar(8000) =
'CREATE NONCLUSTERED INDEX PaymentRequest_PaymentAccountId_IX_Recent_Recent_New' + CHAR(13) + CHAR(10)
+ 'ON dbo.PaymentRequest (PaymentAccountId, CreateDate,  PaymentMethodExternalSystemId, PaymentRequestTypeId)' + CHAR(13) + CHAR(10)
+ 'INCLUDE (Amount)' + CHAR(13) + CHAR(10)
+ 'WHERE CreateDate >= ''' + CONVERT(char(8), CURRENT_TIMESTAMP - 1, 112) + '''' + CHAR(13) + CHAR(10)
+ 'AND PaymentRequestTypeId = 0' + CHAR(13) + CHAR(10)
+ 'WITH (FILLFACTOR = 50);'

--print @SQL

EXEC (@SQL);

IF EXISTS
(
	SELECT 1
	FROM sys.indexes 
	WHERE name='PaymentRequest_PaymentAccountId_IX_Recent' AND object_id = OBJECT_ID('dbo.PaymentRequest')
)
	EXEC sp_rename N'dbo.PaymentRequest_PaymentAccountId_IX_Recent', N'PaymentRequest_PaymentAccountId_IX_Recent_Old', N'INDEX'; 

EXEC sp_rename N'dbo.PaymentRequest_PaymentAccountId_IX_Recent_New', N'dbo.PaymentRequest_PaymentAccountId_IX_Recent', N'INDEX';  

IF EXISTS
(
	SELECT 1
	FROM sys.indexes 
	WHERE name='dbo.PaymentRequest_PaymentAccountId_IX_Recent_Old' AND object_id = OBJECT_ID('HumanResources.EmployeePayHistory')
)
	DROP INDEX PaymentRequest_PaymentAccountId_IX_Recent ON dbo.PaymentRequest;
GO

You will also need to keep track of the fragmentation of the index. You may have to adjust the FILLFACTOR if it ever goes above 1%.