For SQL Server 2014 in-memory tables we need methods for monitoring the number of inserts, updates, deletes and DML errors occurring against our tables. Fortunately, Microsoft have supplied us with a DMV that we can make use of. It’s named sys.dm_db_xtp_object_stats. Here are the results it shows after various operations:
Insert 1 million rows into an in-memory table:
Next let’s deliberately violate the primary key constraint:
So that shows 3 insert attempts and 3 unique constraint violations.
Now let’s do some deletes:
Now let’s do an update in one query window, leave that transaction open and then attempt to update the same row in another query window. Here’s the code for the first query window:
BEGIN TRAN UPDATE dbo.TestTable WITH (SNAPSHOT) SET col2 = 16 WHERE col1 = 15 COMMIT TRAN
and here’s the code for the second query window:
SET NOCOUNT ON DECLARE @retry INT; SET @retry = 50; WHILE (@retry > 0) BEGIN BEGIN TRY BEGIN TRANSACTION; UPDATE dbo.TestTable WITH (SNAPSHOT) SET col2 = 18 WHERE col1 = 15; SET @retry = 0; COMMIT TRANSACTION; END TRY BEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK TRANSACTION; PRINT 'Error ' + CAST(ERROR_NUMBER() AS VARCHAR(100)) + ', ' + CAST(ERROR_MESSAGE() AS VARCHAR(1000)); SET @retry = @retry - 1; WAITFOR DELAY '00:00:02'; END CATCH END
Each of the updates attempted in the second query window returned this:
Error 41302, The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted.
Here are the results from the DMV after 1 minute 40 seconds (50 attempts, each attempt 2 seconds apart):
So 51 update attempts of which 1 (the one in the first query window) was successful.
In addition to the DMV we also have some new performance monitor counters for XTP:
One final observation in terms of activity occurring against this in-memory table is as follows. The InsertTestData natively compiled stored procedure was used to do the initial 1 million row population. For conventional stored procedures we would see the execution statistics using the sys.dm_exec_procedure_stats DMV. Unfortunately, we have no such DMV for execution statistics for natively compiled stored procedures. From a monitoring and support perspective such a DMV would be very useful.
UPDATE: Regarding the very last paragraph mentioning monitoring via sys.dm_exec_procedure_stats the reason we see no entries for natively compiled stored procedures is that for performance reasons this is disabled by default. As mentioned in the following article you can use sys.sp_xtp_control_proc_exec_stats to switch on monitoring (also sys.sp_xtp_control_query_exec_stats for query level stats):