Archive for category In-memory tables

In-memory table DML statistics

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:

inmemobjstats1

Next let’s deliberately violate the primary key constraint:

inmemobjstats2

So that shows 3 insert attempts and 3 unique constraint violations.

Now let’s do some deletes:

inmemobjstats3

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):

inmemobjstats4

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:

inmemobjstats5

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):

http://sqlserver-help.com/2014/01/23/a-z-of-in-memory-oltp-usage-of-sys-dm_exec_query_stats-and-sys-dm_exec_procedure_stats/

 

Advertisements

Leave a comment