Archive

Archive for July, 2016

Identifying In-Memory Objects When Querying the Transaction Log

July 18, 2016 Leave a comment

Continuing on the theme of SQL Server 2016, my boss sent me a copy of Kalen Delaney’s (b|tSQL Server 2016 In-Memory Internals white paper.  I delved into in-memory internals when SQL Server 2014 released, but then other projects got in the way of my delving time.

I created an in-memory table to explore the contents of the in-memory log records.  To do this, I used the table-based function fn_dblog_xtp. If you are familar with fn_dblog, fn_dblog_xtp contains the same column list, with 12 new columns added. A sample SELECT statement is shown below, with the new columns highlighted:

USE yourdatabase;

SELECT
f.[Current LSN],
f.Operation,
f.Context,
f.[Transaction ID],
f.operation_desc,
f.tx_end_timestamp,
f.total_size,
f.xtp_object_id
f.newrow_identity,
f.newrow_data,
f.newrow_datasize,
f.oldrow_begin_timestamp,
f.oldrow_identity,
f.oldrow_key_data,
f.oldrow_key_datasize,
f.xtp_description,
f.[Log Record Fixed Length],
f.[Log Record Length],
f.[Flag Bits],
f.[Log Reserve],
f.[Log Record]
FROM fn_dblog_xtp(NULL,NULL)
WHERE Operation LIKE '%HK%';

The two NULL parameters being passed are the start and end Log Sequence Numbers (LSN). Passing NULLs will return all log records. The WHERE Operation LIKE ‘%HK%’ will return only log records related to in-memory tables.

Transactions against an in-memory table will return a single row in the result set from fn_dblog, while the result set from fn_dblog_xtp will contain a row for all activity. For example, if you insert 100 rows into an in-memory table, fn_dblog will contain 3 records: a BEGIN TRANSACTION, a single row for the INSERT, and a COMMIT TRANSACTION. fn_dblog_xtp will contain 102 records: a BEGIN TRANSACTION, and a row for each row inserted, and a COMMIT TRANSACTION.

One of the new columns in fn_dblog_xtp is xtp_object_id. I tried to join this to the sys.objects table to return the object name, but the values didn’t match. After banging my head against my monitor for a while, I posed the question to #sqlhelp on Twitter. Andreas Wolter (b|t) responded that a correlation can be made using DMV sys.memory_optimized_tables_internal_attributes.  Excited, I tried to join fn_dblog_xtp to the DMV, but was disappointed.

On further examiniation, I determined the xtp_object_id from fn_dblog_xtp was a very large positive number while xtp_object_id from the DMV was a very large negative number. I looked closer and found that the first in-memory object created in a database will receives two xtp_object_id values, 2147483648 and -2147483648. Both of these values are increment for additional in-memory objects. So your second in-memory object will receive values of 2147483649 and -2147483647.

So, to bring a very long story to a close, you can join fn_dblog_xtp to the DMV using the following query:

SELECT 
f.[Current LSN],
f.Operation,
f.Context,
f.[Transaction ID],
f.operation_desc,
f.tx_end_timestamp,
f.total_size,
OBJECT_NAME(m.object_id) AS ObjectName,
f.newrow_identity,
f.newrow_data,
f.newrow_datasize,
f.oldrow_begin_timestamp,
f.oldrow_identity,
f.oldrow_key_data,
f.oldrow_key_datasize,
f.xtp_description,
f.[Log Record Fixed Length],
f.[Log Record Length],
f.[Flag Bits],
f.[Log Reserve],
f.[Log Record]
FROM fn_dblog_xtp(NULL,NULL) AS f
INNER JOIN sys.memory_optimized_tables_internal_attributes m
ON (f.xtp_object_id - 2147483648) = (m.xtp_object_id + 2147483648) 
WHERE Operation LIKE '%HK%'

Once again, I hope this saves some folks frustration interrogating the transaction log for in-memory objects.

Categories: Uncategorized

Fun With READ_ONLY_ROUTING_LIST Syntax

July 13, 2016 Leave a comment

I finally am getting to work with SQL Server 2016 and spent the past two days building out an Availability Group to test the new read-only replica functionality.  This allows connections with an ApplicationIntent of ReadOnly to round-robin between all instances in the READ_ONLY_ROUTING_LIST, load balancing the read-only activity.

For my test, I built out three replicas and set all three to be readable secondaries.  Using Microsoft’s documentation on configuring read-only routing, I coded the following:

ALTER AVAILABILITY GROUP SKREEBYHA
MODIFY REPLICA ON 'AGNODE1\SKREEBYAG'
WITH
(
PRIMARY_ROLE
(
READ_ONLY_ROUTING_LIST=('AGNODE2\SKREEBYAG','AGNODE3\SKREEBYAG')
)
);

To test the new setup, I used the SQLCMD examples from Eric Russo’s blog post on Load Balancing 101.  Running the following command:

SQLCMD -S SKREEBYLIST -K ReadOnly -d testdb -Q "SELECT @@SERVERNAME"

returned AGNODE2\SKREEBYAG in each case. I could not figure out what I was doing wrong. After much frustration, I noticed the routing check query at the bottom of Eric’s post:

SELECT ar_Primary.replica_server_name 'Primary',
ar_secondary.replica_server_name 'Route_to',
ror.routing_priority 'Priority'
FROM sys.availability_read_only_routing_lists ror
INNER JOIN sys.availability_replicas ar_Primary
ON ror.replica_id = ar_Primary.replica_id
INNER JOIN sys.availability_replicas ar_secondary
ON ror.read_only_replica_id = ar_secondary.replica_id
INNER JOIN sys.availability_groups ag
ON ag.group_id = ar_Primary.group_id
WHERE ag.name = 'SKREEBYHA'
ORDER BY 1,3

Running it returned the following results:

RoutingListResults

So, in technical terms, something was screwy.  Looking at Eric’s post again, I noticed his third example for setting a routing list:

ALTER AVAILABILITY GROUP SQL2016AG
MODIFY REPLICA ON 'MyNode3'
WITH
(
PRIMARY_ROLE
(
READ_ONLY_ROUTING_LIST= (('MyNode4','MyNode1','MyNode2'))
)
)

Notice the second set of parentheses around the READ_ONLY_ROUTING_LIST value. Based on this, I changed my code to:

ALTER AVAILABILITY GROUP SKREEBYHA
MODIFY REPLICA ON 'AGNODE1\SKREEBYAG'
WITH
(
PRIMARY_ROLE
(
READ_ONLY_ROUTING_LIST=(('AGNODE2\SKREEBYAG','AGNODE3\SKREEBYAG'))
)
);

and, voila, the routing list check query returned the following:

RoutingListResultsFixed

Rerunning the SQLCMD test, the read-only connections round-robined between AGNODE2 and AGNODE3.

Many thanks to Eric Russo (b|t) for a great blog post.  I hope this saves you some frustration when working with read-only routing.

 

 

SQL Saturday Columbus

It’s been a while since my last blog post.  Since then, I’ve been to PASS Summit, which was the best one yet.  I’ve presented at SQL Saturdays in Portland, Cleveland, and Iowa City. And, most excitingly, started a new job with BlueMetal.  I am in the middle of week 3 and am really excited about the new challenge.

I’ll be presenting on Using PowerShell to Automate Your Restore Strategy at SQL Saturday Columbus on July 16th.  It will be my first time in Columbus, Ohio and I am looking forward to seeing old friends, making new ones, and teaching and learning.  You can register and find more information at the link above.  If you are in the area, don’t miss the opportunity to get a free day of training while networking with your peers in the SQL Server community.