I currently have an MSa SQL Server 2016 standard serverStandard instance running in a 2016Windows Server 2016 VM which has 4 x 2 core processors and 12 GB of ram.
The SQL serverServer install is pretty standard with no extra options configured etc.
I have a table called MessageStack
MessageStack which receives incoming messages from a 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 serverServer.
Currently the MessageStack
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 fieldscolumns, 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 fieldcolumn I use to identify a row in this table.
ALTER TABLE [dbo].[MessageStack] ADD CONSTRAINT [PK_MessageStack] PRIMARY KEY CLUSTERED ([ms_id] ASC) [ms_id] ASC ) WITH ( PAD_INDEX = OFF, WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS ONLINE = ONOFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON )
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
MessageStack table the longer queries are taking. Looking into this a bit more using things like sp_BlitzFirst
sp_BlitzFirst and sp_BlitzIndex
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!
I have tried to remove the clustered index and instead use a UDF to randomly generate the ms_id
ms_id using the SEQUENCE
SEQUENCE feature so that the ms_id
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
IncomingStack and not having a ms_id field
ms_id column. Instead a process moved all messages from this table to the messagestack
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
messagestack table which at peak can be around 700-800.
- Change ms_id to be a GUID field that uses NEWID() as a default value. I would still require an index on ms_id but maybe the randomness would would better than using SEQUENCE and a custom UDF. This however would require updating a load of code and stored procedures.
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.
Set the current index to not allow page or row locks. I don't know what the impact of this would be though.