Let’s recap what LastModifiedDate and SystemModStamp dates are. They are both system fields that store date and time values for each record.

LastModifiedDate is automatically updated whenever a user creates or updates the record. LastModifiedDate can be imported with any back-dated value if your business requires preserving original timestamps when migrating data into Salesforce.

SystemModStamp is strictly read-only. Not only is it updated when a user updates the record, but also when automated system processes update the record. Because of this behavior, it creates a difference in stored value where ‘LastModifiedDate <= SystemModStamp’ but never ‘LastModifiedDate > SystemModStamp’.

So, how does this affect performance of a SOQL query? Under the hood, the SystemModStamp is indexed, but LastModifiedDate is not.

LastModifiedDate and SystemModstamp will differ when automated processes update the records, which will happen in the following scenarios (asynchronously in some cases):

a) The archive date is extended to greater than 365 days.
b) An existing picklist value is updated (not replaced with an existing picklist value).
c) A contact’s e-mail address is flagged as per the Email Bounce Management configuration.
d) The LastActivityDate field is modified
e) Roll-up summary field is created, which will update all the parent records’ SystemModstamp asynchronously. Recalculation will also take place if the Summary Type is updated.
f) Some Salesforce Internal backend processes also update SystemModstamp as SystemModstamp is used internally to signal that a record (or related data) may have changed and that internal processes may need to synchronize themselves to the new record data.

Task and Event SystemModStamp changed – https://help.salesforce.com/articleView?id=000352043&language=en_US&mode=1&type=1

Hard Delete of custom field also updates SystemModStamp:
The estimated time for a hard-delete process varies, and depends on the system demand to maintain overall performance. However, you can monitor and view the details of a hard delete of a custom field by going to the Background Jobs page. From Setup, enter Background Jobs in the Quick Find box, then select Background Jobs . A row with the name “Cleanup of custom field data when a custom field definition is hard deleted” indicates that a hard delete job is in progress. The Background Jobs page also shows the details of background jobs, including a percentage estimate of the progress.
https://help.salesforce.com/articleView?id=sf.fields_managing_deleted_fields.htm&type=5