Database Index Choices vs Throughput

Observations/Conclusions

There are some hard trade-offs here: Queue Performance, Delete Performance, Processing Performance, and Query Performance. According to the numbers, it appears a cluster key of ExternalDeviceId, CreatedDateTime, MessageId (Test 5) is the best choice, but not by a significant amount. Further testing will be required to see if this choice performs well enough when Querying for messages, since it has no other indexes and requires a clustered indexed range scan for every query.
Clustering by ExternalDeviceId, CreatedDateTime, MessageId results in the smallest database size of all the options because it has the fewest indexes.
We expected having a separate QueuedMessages table (to store new messages entering the system) would provided the best solution with respect to table locking during database maintenance (index rebuilds, etc), however; we did not see much improvement in this regard. Further testing on this solution should be investigated when we have more time.
The database log file was by far the hardest hit (by a factor of 3-4) from the 3 database files that make up the Queue database. We recommend separating the 3 database files on separate drives and IO channels with the LOG file being on the fastest disk. Additionally, backups should be performed to a separate disk so there is no disk contention during the backup.
There is a huge advantages from a scalability standpoint to having the database run on SSDs which is likely not possible running on vms in the data center. The machine running the database on SSDs exhibited none of the issues seen on the disk based solution with respect to timeouts during database maintenance.
The biggest outstanding risk is how the database performance over time with 100s of million records. On the disk based system we see dramatic performance degradation during database maintenance activities such as backup and index maintenance. The concern here is that if HL7 messages cannot be stored in a timely fashion that HL7 interfaces will timeout.

Test Scenario

Chicago Message set x 2 streams = 200K messages
ThroughputTest.cxn (attached) Database Index Choices vs Throughput
Pause all queues except the Branch Channel measure total time to Queue all messages (Queue Time)
Un-Pause all channels. Measure total time to Process all Messages (Processing Time)
Execute the delete command below, but WITHOUT the DROPCLEANBUFFERS call.
Query for Patient with ID = 105160 from UI
Execute the delete command below, but WITH the DROPCLEANBUFFERS call.
Query for Patient with ID = 105160 from UI
Clear Queues after each test using clear command below
Execute a database backup from the Client UI to clear log, etc

Test #	Rev	Computer	Clustered Index	Other Indexes	HL7Spy Send (secs)	Queue Time (secs)	Processing Time (secs)	Delete #5 (msecs)	Query #6 (msec)	Delete #7 (msecs)	Query #8 (msecs)	Notes
0	1849c28762f0	JR	MessageId	One for each fields in Queue worklist	138	362	411	340	88	13759	995	Baseline database
0	1849c28762f0	NJ	MessageId	One for each fields in Queue worklist	62	179	215	9	37	501	69	Baseline database
1	527c363e32dc	JR	ExternalDeviceId, CreatedDateTime	Filtered index on Queued messages	136	278	434	188	81	11280	1271	Use CreatedDateTime in queries instead of ModifiedDateTime
1	527c363e32dc	NJ	ExternalDeviceId, CreatedDateTime	Filtered index on Queued messages	60	146	215	93	28	287	53	Use CreatedDateTime in queries instead of ModifiedDateTime
2	08df4637d36e	JR	MessageId	Filtered index on Queued messages Fully covered index	132	424	488	188	78	3143	1799
2	08df4637d36e	NICK	MessageId	Filtered index on Queued messages Fully covered index	66	154	224	112	59	485	107
3	2854c3dfd94d	JR	NO Clustered index	Filtered index on Queued messages Fully covered index	119	304	395	338	322	10632	2111
4	937eae4ff5ac	JR	MessageId	Filtered index on Queued messages Fully covered index	118	283	380	217	71	8358	803
4	937eae4ff5ac	NJ	MessageId	Filtered index on Queued messages Fully covered index	60	144	247	237	64	273	131
5	3261f232cd21	JR	ExternaDeviceId, ModifiedDateTime, MesssageId	No CorrelationKey	120	230	403	80	75	1065	1665
5	3261f232cd21	NJ	ExternaDeviceId, ModifiedDateTime, MesssageId	No CorrelationKey	60	124	247	210	83	82	134
6	3261f232cd21	JR	MessageId	One fat index with everything	117	225	375	170	166	1457	2840
6	3261f232cd21	NJ	MessageId	One fat index with everything	60	122	226	75	62	307	116
7	4d923df7e6da	JR	ExternalDeviceId, CreatedDateTime, MessageId	No query indexes	115	219	401	125	147	988	1206
7	4d923df7e6da	NJ	ExternalDeviceId, CreatedDateTime, MessageId	No query indexes	61	126	228	74	147	134	46
8	8f4a708a9733		Separate QueuedMessage table	No query indexes	115	180	435	130	211	1652	2583
9	5124566bec79		Separate QueuedMessage table. MessageData and QueueData in same File Group	No query indexes	118	192	445	127	93	1582	2945

* Calculated from:

select DATEDIFF(ss,(select top 1 ModifiedDateTime from Message order by MessageId)
,(select top 1 ModifiedDateTime from Message order by MessageId desc))

** Calculated from the following where {0} = the ExternalDeviceId of one of the non-branch devices found in SELECT * FROM MessageStatistic

DECLARE @nonBranchDeviceId int
set @nonBranchDeviceId = {0}
select DATEDIFF(ss,(select top 1 ModifiedDateTime from Message where ExternalDeviceId = 2 order by MessageId)
,(select top 1 ModifiedDateTime from Message where ExternalDeviceId = 2 order by MessageId desc))

** Command for Delete:

DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
DECLARE @diff int,
        @startDate DateTime,
        @olderThan DateTime,
        @timer DateTime,
        @nonBranchDeviceId int
set @nonBranchDeviceId = {0}
select top 1 @startDate = CreatedDateTime from Message where ExternalDeviceId=@nonBranchDeviceId order by MessageId
select @diff = DATEDIFF(SECOND, @startDate, (select top 1 ModifiedDateTime from Message where ExternalDeviceId=@nonBranchDeviceId order by MessageId desc))
set @olderThan = DATEADD(SECOND, @diff/10, @startDate)
set @timer = SYSDATETIME()
delete TOP(5000) from Message where ExternalDeviceId = @nonBranchDeviceId AND QueueTypeCode != 'Q' AND ModifiedDateTime < @olderThan
PRINT 'Delete Time: ' + CAST(DATEDIFF(millisecond,@timer,SYSDATETIME()) as varchar(255))

*** Query command

DECLARE @timer DATETIME,
@nonBranchDeviceId int
set @timer = SYSDATETIME()
set @nonBranchDeviceId = {0}
SELECT TOP(100) * from Message
where ExternalDeviceId = @nonBranchDeviceId and PatientId = '105160' order by CreatedDateTime desc, MessageId desc
PRINT 'Query Time: ' + CAST(DATEDIFF(millisecond,@timer,SYSDATETIME()) as varchar(255))

*** Clear command

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Message_MessageData]') AND parent_object_id = OBJECT_ID(N'[dbo].[Message]'))
ALTER TABLE [dbo].[Message] DROP CONSTRAINT [FK_Message_MessageData]
TRUNCATE TABLE Message
TRUNCATE TABLE MessageData
TRUNCATE TABLE ProcessingHistory
TRUNCATE TABLE MessageStatistic
TRUNCATE TABLE MessageStatisticHistory
CHECKPOINT

Test number	Query type	Message Statistics
0	DELETE	Table 'Message'. Scan count 1, logical reads 144794, physical reads 164, read-ahead reads 2905, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 6, logical reads 10529, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
0	QUERY	Table 'Message'. Scan count 1, logical reads 1724, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
1	DELETE	Table 'Message'. Scan count 1, logical reads 81811, physical reads 3, read-ahead reads 2548, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 3, logical reads 10254, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
1	QUERY	Table 'Message'. Scan count 1, logical reads 629, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
1 NJ	DELETE	Table 'Message'. Scan count 1, logical reads 61359, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 3, logical reads 10254, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2	DELETE	Table 'Message'. Scan count 1, logical reads 46063, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 3, logical reads 10254, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2	QUERY	Table 'Message'. Scan count 1, logical reads 814, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2 NJ	DELETE	Table 'Message'. Scan count 9, logical reads 65335, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 3, logical reads 10254, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2 NJ	QUERY	Table 'Message'. Scan count 1, logical reads 5814, physical reads 2, read-ahead reads 3214, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
3	DELETE	Table 'Message'. Scan count 1, logical reads 67012, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 4, logical reads 10332, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
3	QUERY	Table 'Message'. Scan count 1, logical reads 900, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
4	DELETE	Table 'Message'. Scan count 1, logical reads 81452, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 5, logical reads 10370, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
4	QUERY	Table 'Message'. Scan count 1, logical reads 1724, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
4 NJ	DELETE	Table 'Message'. Scan count 1, logical reads 46286, physical reads 4, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 3, logical reads 10262, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
4 NJ	QUERY
5	DELETE	Table 'Message'. Scan count 1, logical reads 30780, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 2, logical reads 10151, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
5	QUERY	Table 'Message'. Scan count 1, logical reads 1399, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
5 NJ	DELETE	Table 'Message'. Scan count 1, logical reads 30778, physical reads 0, read-ahead reads 234, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 2, logical reads 10151, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
5 NJ	QUERY
6	DELETE	Table 'Message'. Scan count 1, logical reads 46783, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 2, logical reads 10151, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
6	QUERY	Table 'Message'. Scan count 1, logical reads 4881, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
6 NJ	DELETE	Table 'Message'. Scan count 1, logical reads 58852, physical reads 55, read-ahead reads 253, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 2, logical reads 10151, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
6 NJ	QUERY	Table 'Message'. Scan count 1, logical reads 5209, physical reads 0, read-ahead reads 585, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
7	DELETE	Table 'Message'. Scan count 1, logical reads 30848, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 2, logical reads 10172, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
7 NJ	DELETE	Table 'Message'. Scan count 1, logical reads 30748, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 2, logical reads 10165, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
7 NJ	QUERY	Table 'Message'. Scan count 1, logical reads 1146, physical reads 3, read-ahead reads 1495, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Connexion

Database Index Choices vs Throughput

Analytics

Observations/Conclusions

Test Scenario