I currently have a SQL Server 2016 Standard instance running in a Windows Server 2016 VM which has 4 x 2 core processors and 12 GB of ram.
The SQL Server install is pretty standard with no extra options configured etc.
I have a table called
MessageStack which receives incoming messages from an ASP.NET MVC web application and a .NET TCP Server application.
I have another application that processes these messages. All applications are local to the SQL Server.
MessageStack table receives on average about 70-100 messages per second although these can burst to upwards of 500 messages per second. This table has various columns, but the main ones are:
[ms_id] [int] IDENTITY(1,1) NOT NULL [ms_encodedalert] [nvarchar](2000) NOT NULL [ms_receivedtime] [datetime] NOT NULL [ms_processing] [bit] NOT NULL CONSTRAINT [DF_MessageStack_ms_processing] DEFAULT ((0)) [ms_thread] [nvarchar](50) NULL
On this table I have a clustered index on ms_id as this is the only column I use to identify a row in this table.
ALTER TABLE [dbo].[MessageStack] ADD CONSTRAINT [PK_MessageStack] PRIMARY KEY CLUSTERED ([ms_id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
Now the application that processes these messages will pull the top 1000 messages in that table, setting ms_processing to 1 and putting a name in ms_thread and then begin processing them. It's a multi threaded application and can manage around 100 messages a second. It passes each message to a thread which does the processing and then eventually deletes the message once done using
delete from messagestack where ms_id = xxxxx
This has all been good for a while but very recently our incoming messages has been increasing slightly and I've found that the bigger the
MessageStack table the longer queries are taking. Looking into this a bit more using things like
sp_BlitzIndex I can see that the clustered index is causing lots of page locks and waits which explains why my message processing time is going crazy with single row deletes sometimes taking 1.4 seconds!
Running Query : delete from messagestack where ms_id=58550 Query stats[1403.08100 ms][1 rows].
I have tried to remove the clustered index and instead use a UDF to randomly generate the
ms_id using the
SEQUENCE feature so that the
ms_id values weren't close together hoping to avoid page locks. However this just moved the problem else where causing the inserts to take longer. I got this idea from reading this page https://blogs.msdn.microsoft.com/blogdoezequiel/2013/05/23/pagelatch_ex-waits-and-heavy-inserts/
The next option I tried was creating a new table called
IncomingStack and not having a
ms_id column. Instead a process moved all messages from this table to the
messagestack table (which causes a table lock while it's being done as I wrapped it in a transaction) in an attempt to limit the times the clustered index was being updated. At this point I realised that it's not so much my inserts causing the problem but the overall transactions/second on just the
messagestack table which at peak can be around 700-800.
The next step I tried was to turn the message processing app into a distributed application running on 2 servers at once. My theory being that if a single server can do approx 100 messages/sec then 2 should be able to do more than that! All that happens now is the general queries run a bit slower so both servers settle at around 60 messages/sec which is better but still not what I had hoped for.
The final options I haven't tried yet are
ms_idto be a
GUIDcolumn that uses
NEWID()as a default value. I would still require an index on
ms_idbut maybe the randomness would would better than using
SEQUENCEand a custom UDF. This however would require updating a load of code and stored procedures.
Set the current index to not allow page or row locks. I don't know what the impact of this would be though.
In short I don't know if there is a better design hence the question. I'm pretty sure in any option I need an index on ms_id otherwise my code will resort to table scans to find rows.
Does anyone know what the implications of my 2 options are? Is 1 worth testing or is it basically pointless and 2 what if I change the page/row locks setting.
Or maybe there are other options I'm unaware of!