A gentle introduction to transaction isolation levels and Hekaton tables

I finished reading the Books Online and CTP1 whitepaper introduction to transactions and memory-optimized tables a couple of days ago. Although I know what the transaction isolation levels mean, I was sort of surprised by some of the descriptions and found them a bit confusing. So I decided to go back to basics and write some simple examples that demonstrated the transaction isolation levels.

Firstly though, I have to define some terms: Books online mentions differences with memory optimized tables between autocommit transactions and user/implicit transactions. “Autocommit transactions” refers to the fact that individual SQL statements are atomic by definition; they either commit or rollback as a unit. For example, if I issue a SQL UPDATE statement that updates 10 rows and 1 row fails update because of a constraint, the statement fails and 0 rows are updated at end-of-statement. In these examples, I’m using “user transactions” where 1-n statements are made part of a single transaction with the BEGIN TRANSACTION and the COMMIT/ROLLBACK TRANSACTION statements. Implicit transactions are another way to make multiple SQL statements transactional, I’m not using this here.

I’m using memory-optimized tables in a database named “simple”. To mention a bit more about the “environment”:

– The database simple snapshot_isolation_state and RCSI turned off at the database level (sys.databases)
– I’m running at default isolation level of read committed (dbcc useroptions)
– XACT_ABORT session setting is turned off (dbcc useroptions doesn’t have a row declaring it is on)

Memory-optimized tables support three transaction isolation levels in user transactions: SNAPSHOT, READCOMMITTED, and SERIALIZABLE. My first puzzle was the BOL describes SNAPSHOT as the “lowest” of the three where memory-optimized tables are concerned. I’ve always considered SQL Server’s SNAPSHOT isolation level as most like Oracle’s SERIALIZABLE isolation level (SQL Server couldn’t use the word serializable because that is implemented by locking in SQL Server). But in the world of memory-optimized tables and user transactions, SNAPSHOT is indeed a lower isolation level than READCOMMITTED. Hmmm.

Also, all three of these levels, when used with memory-optimized tables, can fail the user transaction at commit time, if there’s a conflict detected. SQL Server’s SNAPSHOT against “traditional” tables fail during user transactions at the statement where the conflict is detected.

So, on the examples on the simplest user-transaction behavior… complete script is at the end for you to try. I execute the statements in SQL Server Management Studio, one statement at a time. I’m not using compiled stored procedures.

1. SNAPSHOT

begin tran
select * from dbo.t1 with (snapshot)
– update a row in another session here
commit — OK

begin tran
select * from dbo.t1 with (snapshot)
– update a row in another session here
select * from dbo.t1 with (snapshot) — sees old value
commit — OK

The only “strange” thing here is that you can use SNAPSHOT against memory-optimized tables even though snapshot_isolation_state is OFF at a database level. That’s because memory-optimized tables don’t implement snapshot isolation level the same way as regular tables (e.g. no version store for them).

2. REPEATABLE READ
begin tran
select * from dbo.t1 with (repeatableread)
– update a row in another session
commit — Msg 41305, Level 16, State 0, Line 39 The current transaction failed to commit due to a repeatable read validation failure.

begin tran
select * from dbo.t1 with (repeatableread)
– insert a row in another session
commit — OK

begin tran
select * from dbo.t1 with (repeatableread)
– insert a row in another session
select * from dbo.t1 with (repeatableread) — does not see new row
commit — OK

Repeatable read doesn’t block after reading rows (no locking for memory-optimized tables), but fails at commit time if repeatable read semantics have been violated during the transaction (e.g. any of the rows have changed since you read them). Note that the transaction starts at the first SQL statement, NOT at the same you issue BEGIN TRANSACTION.

begin tran
– update row in another session before reading
select * from dbo.t1 with (repeatableread) — sees new value (and tx starts here)
commit — OK

begin tran
select * from dbo.t2 with (repeatableread) — transaction starts with first SQL statement
– update row in t1 in another session before reading
select * from dbo.t1 with (repeatableread) — sees the old value
commit — Msg 41305, Level 16, State 0, Line 72 The current transaction failed to commit due to a repeatable read validation failure.

3. SERIALIZABLE.

Note that the READCOMMITTED transaction failed if you changed a row you’d read, but did NOT fail if someone else inserted a row during your transaction. Failing a transaction when someone else inserts a row inside a keyrange you’ve read (table you’ve read, if you’re reading all the rows) only happens in serializable isolation level.

begin tran
select * from dbo.t1 with (repeatableread)
– insert a row in another session
select * from dbo.t1 with (repeatableread) — does not see new row
commit — OK, commits in repeatableread

begin tran
select * from dbo.t1 with (serializable)
– insert a row in another session
commit — Msg 41325, Level 16, State 0, Line 55 The current transaction failed to commit due to a serializable validation failure.

And there you have it!

However, it’s not always that simple. Here’s a couple of things that differ with respect to environment, right off the bat…
1. There are subtly different rules for “autocommit” transactions.
2. There are differences when you mix memory-optimzed and non-memory optimized tables in the same user transaction.

This is just meant to be a gentle introduction. The script is enclosed for your reference. For more information, refer to “Transactions in Memory Optimized Tables” topics in BOL.

Cheers, Bob

Here’s a hyperlink to the script:

hek_simple_tx

More XEvents in SQL Server 2014 CTP1 – don’t wait any longer

Don’t get me wrong, there’s almost no one who enjoys a good SQL Profiler diagnostic trace more than I do. But, starting in the early CTPs of SQL Server 2008, I’ve become impressed with the diagnostic enablement afforded through Extended Events. Enough to hack through that first beta (sans docs, with only metadata) and blog about it for a week. XEvents’ biggest weakness was that there was no GUI, and a guy named Jonathan Kehayias took care of that with his SSMS add-in.

In SQL Server 2012, it was announced that the old trace would be deprecated in favor of XEvents. The sheer number of events (618 in SQL Server 2012 RTM, with 9 more in SQL Server 2012 SP1) dwarfed the profiler events, as well as a nice GUI built into SSMS and programmatic interfaces for session definition, metadata, and trace-reading (including realtime) with a component called LINQ to XEvents, things were sort of sealed. That’s not even mentioning the actions, the powerful predicate system, and the series of targets.

Still, XEvents seems daunting for some, mostly (I think) because there’s too much XML involved. But c’mon, XPath is, for the most part, like navigating through a file system. And basic XPath is all you need, for the most part, to reformat the event data. Goodness, the XEvent data doesn’t even use XML Namespaces, to keep the querying simple.

After looking at the SQL Server 2014 CTP1 metadata, the results are in; things are settled. The score is:

– 763 XEvents total (that’s 145 new ones) vs. 0 new trace events (although SQL Profiler/event system is still around)
– 4 new packages, including 3 related to Hekaton (internals, compiler, and runtime).
– 68 events in the “sqlserver” packages, including about 10 more xtp (Hekaton) events in these packages, events to trace interactions with Windows Azure storage, for the new cardinality estimating code, and new columnstore functionality.

In a release where there are actually 3 new Event Notification event types (for CREATE/DROP AUDIT and DDL_DATABASE_AUDIT_EVENTS, remember Event Notifications?) the fact that there’s no new trace capability means YOU need to start learning/experimenting with Extended Events now. if you haven’t already. Go to a class, or a presentation, or read blogs or something… And you may want to brush up on your XPath…

A good place to start would be with Jonathan’s “An XEvent A Day” blog series. Or his two courses on Pluralsight about them. But start NOW, before you find yourself in SQL 2012 or 2014 with your diagnostic hands tied behind your back. How else can you look at wait stats for a specific workload or work with the T-SQL stack?

And BTW, Here’s a list of all the new events ([package name].[event name]) in 2014 CTP1 (since 2012 SP1):

hkenginexepkg.after_changestatetx_event – Fires after transaction changes state
hkenginexepkg.alloctx_event -
hkenginexepkg.attempt_committx_event – Is raised when a transaction is asked to commit
hkenginexepkg.before_changestatetx_event – Fires before transaction changes state
hkenginexepkg.compute_oldest_active_tx_hint – Indicates that the oldest active transaction hint has been recalculated
hkenginexepkg.dependency_acquiredtx_event – Raised after transaction takes a dependency on another transaction
hkenginexepkg.endts_acquiredtx_event – Fires after transaction acquires an end timestamp
hkenginexepkg.gc_cycle_completed – Indicates that a GC notification has been enqueued.
hkenginexepkg.gc_notification_enqueued – Indicates that a GC notification has been enqueued.
hkenginexepkg.redo_single_hk_record – Redo on a HK log record
hkenginexepkg.update_oldest_active_tx_stats – Indicates that an attempt is being made to recalculate the GC base generation.
hkenginexepkg.waiting_for_dependenciestx_event – Raised when we have explicitly waited for dependencies to clear
hkenginexepkg.xtp_checkpoint_file_flush – Indicates the point at which a given file has been flushed to disk.
hkenginexepkg.xtp_checkpoint_file_flush_complete – Indicates the point at which all in-flight buffers have been flushed to disk.
hkenginexepkg.xtp_checkpoint_file_read – Indicates reading of a file in XTP checkpoint recovery.
hkenginexepkg.xtp_checkpoint_write_io – Indicates that the checkpointing subsystem has issued or completed a write IO.
hkenginexepkg.xtp_create_log_record – Fires when the XTP engine creates a log record.
hkenginexepkg.xtp_root_file_write – Indicates that the write of the checkpoint root file is complete.
qds.query_store_async_shutdown_failed – Fired when query store encounters an error during async query store shutdown
qds.query_store_background_task_creation_failed – Fired if the background task for QDS data persistence could not be created
qds.query_store_background_task_initialization_failed – Fired if the background task for QDS data persistence could not be initialized
qds.query_store_background_task_persist_failed – Fired if the background task for QDS data persistence is not completed successfully
qds.query_store_begin_persist_runtime_stat – Fired immediately before current runtime statistics for a query plan is persisted to the database.
qds.query_store_bloom_filter_false_positive – Fired if the Bloom filter for QDS statement texts gives a false positive result
qds.query_store_check_consistency_init_failed – Fired when check consistency task fails during initialization
qds.query_store_database_initialization_failed – Fired if initialization of the Query Store for database has failed. The Query Store will be disabled for this database
qds.query_store_disk_size_check_failed – Fired when a check against Query Store on-disk size limit fails
qds.query_store_disk_size_info – Fired when a check against QDS on-disk size is performed
qds.query_store_execution_runtime_info – Fired when runtime information is sent to the query store.
qds.query_store_execution_runtime_info_discarded – Fired when runtime information sent to the query store is discarded.
qds.query_store_execution_runtime_info_evicted – Fired when runtime information sent to the query store is evicted.
qds.query_store_flush_failed – Fired when query store failed to flush dirty data
qds.query_store_loaded – Fired when query store is loaded
qds.query_store_notify_dirty_shutdown_on_partition_startup – Fired when previous instance of query store for the partition is shutdown by force in order to allow creation of a new instance
qds.query_store_notify_force_failure_failed – Fired when query store failed to notify force failure
qds.query_store_persist_task_init_failed – Fired when persist task fails during initialization
qds.query_store_plan_forcing_failed – Occurs when forcing of plan from qds fail
qds.query_store_plan_persistence_failure – Fired if there’s a failure to persist plan
qds.query_store_plan_removal – Fired when plan is removed
qds.query_store_query_persistence_failure – Fired if there’s a failure to persist query
qds.query_store_read_write_failed – Fired if the read/write to Query Store internal tables failed
qds.query_store_statement_not_found – Fired in case when statement couldn’t be found due to race condition or ambiguous user request.
qds.query_store_unloaded – Fired when query store is unloaded from memory
sqlos.nonpreemptive_long_syncio – record long sync io operation in nonpreemptive worker
sqlos.stuck_dispatcher_callback_executed – Stuck dispatcher callback executed
sqlos.wait_completed – Occurs when there is a wait completed on a SQLOS controlled resource.  Use this event to track wait completion.
sqlserver.after_natively_compiled_proc_entry_removal_on_drop – Fired after the procedure cache entry is flushed when dropping a natively compiled procedure.
sqlserver.before_natively_compiled_proc_entry_removal_on_drop – Fired before the procedure cache entry is flushed when dropping a natively compiled procedure.
sqlserver.before_redo_lsn_update – Occurs just prior to the update of the EOL LSN
sqlserver.buffer_pool_eviction_thresholds_recalculated – Lazywriter and/or worker clock has wrapped the BUF array and thresholds are re-calculated.
sqlserver.buffer_pool_extension_pages_evicted – Page is evicted from the buffer pool extension cache.
sqlserver.buffer_pool_extension_pages_read – Page is read from the buffer pool extension cache.
sqlserver.buffer_pool_extension_pages_written – Page or contiguous set of pages evicted into the buffer pool extension cache.
sqlserver.check_phase_tracing – Occurs when DBCC CHECK enters a new phase of the checking. Use this event to trace the phases of DBCC CHECK process.
sqlserver.check_thread_message_statistics – Occurs when a phase of DBCC CHECK is finished. Use this event to collect the number of messages a DBCC CHECK thread has sent or received.
sqlserver.check_thread_page_io_statistics – Occurs when a phase of DBCC CHECK is finished. Use this event to collect the number of logical, physical, and read-ahead IOs a DBCC CHECK thread has performed.
sqlserver.check_thread_page_latch_statistics – Occurs when a phase of DBCC CHECK is finished. Use This event to collect the number and time of page latch and IO latch waits.
sqlserver.clustered_columnstore_index_rebuild – Occurs when clustered index on the table was rebuilt. This event is raised three times for ALTER index rebuild operation on CCSI. The event is raised when the operation takes lock on index rebuild resource, when lock is taken on the table and when S lock on the table is upgraded to SCH_M lock to switch indexes in metadata.
sqlserver.column_store_index_build_low_memory – Occurs when Storage Engine detects low memory condition and the rowgroup size is reduced.
sqlserver.column_store_index_build_process_segment – Occurs when a segment is processed
sqlserver.column_store_index_build_throttle – Shows the statistics of columnstore index build parallelism throttling
sqlserver.columnstore_tuple_mover_begin_compress – Occurs when column store tuple mover starts compressing a deltastore.
sqlserver.columnstore_tuple_mover_end_compress – Occurs when column store tuple mover is done compressing a deltastore.
sqlserver.database_xml_deadlock_report – Produces a deadlock report for a victim, with information scoped to the victim’s database.
sqlserver.db_lock_acquired_from_cache – Occurs when a DB lock is acquired from the XactWorkspace DB lock cache.
sqlserver.db_lock_released_from_cache – Occurs when a DB lock is released from the XactWorkspace DB lock cache.
sqlserver.ddl_with_wait_at_low_priority – A DDL statement was executed using the WAIT_AT_LOW_PRIORITY options
sqlserver.diag_quantum_end – Occurs when the diag is notified of quantum end.
sqlserver.dyn_throttle_checkpoint – Occurs when checkpointing has been dynamically throttled with a new Checkpoint rate.
sqlserver.feature_extension – Occurs when received and parses data for a feature in feature extension.
sqlserver.file_read_enqueued – File read enqueued
sqlserver.file_read_throttled – File read throttled
sqlserver.file_write_enqueued – File write enqueued
sqlserver.file_write_throttled – File write throttled
sqlserver.hadr_tds_synchronizer_payload_skip – Hadron Tds Listener Synchronizer skipped a listener payload because there were no changes since the previous payload.
sqlserver.lock_request_priority_state – The priority state of a lock request
sqlserver.log_cache_write_block – Writing a log block to the log cache
sqlserver.logreader_start_scan – Outputs the replnextlsn value from the database table that the first replcmds scan uses as a starting point.
sqlserver.metadata_ddl_add_column – Occurs when an ALTER TABLE ADD column operation is updating base index.
sqlserver.metadata_ddl_alter_column – Occurs when an ALTER TABLE ALTER column operation is updating base index.
sqlserver.metadata_ddl_drop_column – Occurs when an ALTER TABLE DROP column operation is updating base index.
sqlserver.mixed_extent_activation – Track mixed extent activation and deactivation operations.
sqlserver.mixed_extent_allocation – Track mixed extent allocation operations
sqlserver.mixed_extent_deallocation – Track mixed extent deallocation operations.
sqlserver.mixed_page_allocation – Track mixed page allocation operations
sqlserver.mixed_page_deallocation – Track mixed page allocation operations
sqlserver.mixed_page_scan_file – Track the activity of SGAM scans for mixed page allocation
sqlserver.mixed_page_scan_page – Track the activity of SGAM scans for mixed page allocation
sqlserver.mixed_page_skipextent – Track the activity of SGAM scans for mixed page allocation
sqlserver.natively_compiled_proc_execution_started – Fired before a natively compiled procedure is executed.
sqlserver.optimizer_timeout – Occurs when the optimizer times out either due to spending too much time or hitting a memory limit.  Use this event to look at all the queries that are impacted by the optimizer timeout in a particular workload. This can be very useful when tuning a particular workload.
sqlserver.plan_affecting_convert – Occurs when a type convert issue affects the plan.  The expression value indicates the conversion that can cause inaccurate Cardinality Estimation or prevent from using a seek based plan in the query plan choice.  If performance is affected, rewriting the query could help.
sqlserver.process_killed_by_abort_blockers – A process is killed by an ABORT = BLOCKERS DDL statement
sqlserver.query_execution_batch_hash_aggregation_finished – Occurs at the end of batch hash aggregation.
sqlserver.query_execution_batch_hash_children_reversed – Occurs each time when hash join reverses build and probe side while processing data spilled to disk.
sqlserver.query_execution_batch_hash_join_spilled – Occurs each time when hash join spills some data to disk in batch processing.
sqlserver.query_optimizer_estimate_cardinality – Occurs when the query optimizer estimates cardinality on a relational expression.
sqlserver.query_optimizer_force_both_cardinality_estimation_behaviors – Both traceflags 2312 and 9481 were enabled, attempting to force both old and new cardinality estimation behaviors at the same time. The traceflags were ignored. Disable one or both of the traceflags.
sqlserver.query_store_failed_to_capture_query – Fired if the Query Store failed to capture query. The Query Store will not track statistics for this query
sqlserver.query_store_failed_to_load_forced_plan – Fired if the query failed to load forced plan from QDS. Forcing policy will not be applied
sqlserver.query_store_persist_on_shutdown_failed – Occurs when SQL Server fails to store dirty entries in Query Store on database shutdown.
sqlserver.selective_xml_index_no_compatible_sql_type – Occurs when a value() method is used in a query, specifying a sql type that differs from the type specified during selective XML index creation for that path.
sqlserver.selective_xml_index_no_compatible_xsd_types – Occurs when the user specifies a different resulting XSD type in a query, than the one that was used to promote the path during selective XML index creation.
sqlserver.selective_xml_index_path_not_indexed – Occurs when the path given in the user query is not promoted in the selective XML index, and thus the index is not used.
sqlserver.selective_xml_index_path_not_supported – Occurs when selective XML index is not used for a XML query due to the user using an unsupported path type.
sqlserver.session_recoverable_state_change – Occurs when the server determines a state change in term of session recovery on a connectionresilency-enabled connection.
sqlserver.spatial_guess – Occurs when the optimizer cannot get enough join selectivity information.  Use this event to look at the queries where spatial index could be used and the optimizer guesses the spatial selectivity.
sqlserver.unmatched_filtered_indexes – Occurs when the optimizer cannot use a filtered index due to parameterization.  For each such index an event is fired.  The unmatched_database_name, unmatched_schema_name, unmatched_table_name, unmatched_index_name fields give details of the filtered index that could not be used.
sqlserver.xfcb_blob_properties_obtained – Windows Azure Storage blob property is obtained from response header.
sqlserver.xfcb_failed_request – Failed to complete a request to Windows Azure Storage.
sqlserver.xfcb_header_obtained – Response header is obtained from request to Windows Azure Storage.
sqlserver.xfcb_read_complete – Read complete from Windows Azure Storage response.
sqlserver.xfcb_request_opened – A request is opened to Windows Azure Storage.
sqlserver.xfcb_send_complete – Request send to Windows Azure Storage is complete.
sqlserver.xfcb_write_complete – Request send to Windows Azure Storage is complete.
sqlserver.xtp_create_procedure – Occurs at start of XTP procedure creation.
sqlserver.xtp_create_table – Occurs at start of XTP table creation.
sqlserver.xtp_deploy_done – Occurs at completion of XTP object deployment.
sqlserver.xtp_matgen – Occurs at start of MAT generation.
sqlserver.xtp_offline_checkpoint_scan_start – Fired by XTP offline checkpoint when the checkpoint thread begins.
sqlserver.xtp_offline_checkpoint_scan_stop – Fired by XTP offline checkpoint when the checkpoint thread stops.
sqlserver.xtp_recover_done – Occurs at completion of log recovery of XTP table.
sqlserver.xtp_recover_table – Occurs at start of log recovery of XTP table.
sqlserver.xtp_storage_table_create – Occurs at just before the XTP storage table is created.
ucs.ucs_connection_rejected_by_proxy_whitelist – After a connection attempt to the UCS proxy endpoint is rejected by whitelist check
ucs.ucs_proxy_connect_next_hop – UCS proxy next hop connection
ucs.ucs_proxy_receive_proxy_connect_message – UCS proxy receive proxy connect message
ucs.ucs_proxy_route_add – UCS proxy route added
ucs.ucs_proxy_route_disable – UCS proxy route disabled
ucs.ucs_proxy_route_refresh – UCS proxy route refreshed
ucs.ucs_proxy_send_proxy_connect_message – UCS proxy send proxy connect message
XeXtpCompilePkg.cgen – Occurs at start of C code generation.
XeXtpCompilePkg.invoke_cl – Occurs prior to the invocation of the C compiler.
XeXtpCompilePkg.mat_export – Occurs at start of MAT export.
XeXtpCompilePkg.pit_export – Occurs at start of PIT export.
XeXtpCompilePkg.pitgen_procs – Occurs at start of PIT generation for procedures.
XeXtpCompilePkg.pitgen_tables – Occurs at start of PIT generation for tables.
XeXtpRuntimePkg.bind_md – Occurs prior to binding metadata for a memory optimized table.
XeXtpRuntimePkg.bind_tables – Occurs prior to binding tables for a natively compiled procedure.
XeXtpRuntimePkg.create_table – Occurs prior to creating memory optimized table.
XeXtpRuntimePkg.deserialize_md – Occurs prior to deserializing metadata.
XeXtpRuntimePkg.load_dll – Occurs prior to loading the generated DLL.
XeXtpRuntimePkg.recover_done – Occurs at completion of checkpoint recovery of a memory optimized table.
XeXtpRuntimePkg.recover_table – Occurs at start of checkpoint recovery of a memory optimized table.
XeXtpRuntimePkg.serialize_md – Occurs prior to serializing metadata.

Enjoy.

Cheers, Bob

Hekaton data and code – where does that stuff actually live?

When investigating new features, I’m prone to use what I call the Rumpelstiltskin method, that is, I’m happier if I know where these features “live” in the file system (not exactly Rumpelstiltskin, where the reward came by guessing his name, but hopefully I’m not stretching the analogy too far).

With this in mind, I started off to find where *exactly* Hekaton stores it’s compiled table code, it’s compiled procedure code, and it’s”backing store” data for persistent memory-optimized tables. For the experiment I used the Hekaton-ized Northwind sample database code from the SQL Server 2014 Books Online. I executed the code in stages, and looked at the appropriate file system locations, once I’d found them.

Creating or altering a database to contain a filegroup for memory-optimized data with a corresponding file produces the well-known “filestream directory structure”, as used by filestream (2008+) and filetable (2012+). With a file for a memory-optimized data filegroup, it contains the nested GUID-named subdirectories as though you had one table containing filestreams and one filestream-based column. However, the lowest level of directory isn’t empty; it contains 1 GB-worth of files. 8 of those files are 128 mb in size, and 14 other files are empty. And this is *before* adding any data. Or even any tables.

BTW, the filegroup for memory-optimized files can have more than on container (ADD FILE.. in DDL, directory as far of the file system goes). Adding a second “file” produces the corresponding “filestream” directory, but no GUI-named subdirectories (yet). So that’s where the data will live, because there has to be a persistent backing-store to populate the in-memory tables at next SQL Server startup time.

Now, let’s add a memory-optimized table and see what happens. I’d been browsing around the file system in the “SQL Server instance directory” (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL in my case) and noticed an subdirectory of binn named “xtp”. That’s where the C compiler and linker live (in the xtp\bin directory) and where the support libraries live (in the xtp\lib directory). Great.

I also noticed an empty subdirectory of the “data” directory named “xtp”. Creating a single memory-optimized table (Employees, in this script) produced an subdirectory under …\data\xtp with the name ’5′. Where 5 is the DBID of the database in question (hkNorthwind in my case). There are six files for each memory-optimized table, with names start begin with “xtp_t_5_277576027″. With memory-optimized tables:

xtp_t – is for table code

5 – refers to the DBID

277576027 – refers to the object_id of the table

The six files I saw had suffixes .c, .obj, .dll, .pdb (symbols), .out (intermediate compiler file), and .mat.xml. Not sure what .mat.xml is just yet (it contains an XML representation of the table metadata), but the other files are recognizable. Adding the other tables, I can see one set of these files for each memory-optimized table. Similarly, adding an native-compiled stored procedure produces the same six file types that begin with “xtp_p” (for procedure).

Interestingly, if I stop the instance and start it up again, these files only appear once I’ve *executed* the procedure. The files for tables appear after startup. So it sounds like these are lazy-instantiated and compiled when they are first used. This could be the case for the “table code” as well, but these would have to be instantiated at SQL startup, so that the tables could be (re)created and (re)populated in memory.

Turns out that I didn’t have to “browse-around” for the modules’ location. It shows up in sys.dm_os_loaded_modules, under the “name” column when the modules are loaded.

Back to data. All I do for now is what the script contains, populate the tables using INSERT statements. And this does nothing to the tables in the memory-optimized filegroup files. I’m assuming it writes to the 128 mb pre-allocated files. However, executing CHECKPOINT afterwards writes to one of the previously empty pre-allocated files, changing the size to 64mb. Bringing SQL Server (and the OS) down and up a few times populates more of these files to 64mb. And, after a while, I check back and I have 24 files total in the “filestream subdirectory”. All this appears to be consistent with the Books Online description under the “Storage/Defining durability for memory-optimized tables“. I’m not seeing any populated “Delta files” yet, because I haven’t UPDATEd or DELETEd any rows. That should work as advertised too.

So. Nothing too earth-shattering, but at least now I know “where everything is”. And Hekaton tables and procedures are less of an unknown “Rumpelstiltskin” today than they were yesterday.

Cheers, Bob

Thinking about Hekaton? Then think about collations.

Among the new features in SQL Server 2014, the most compelling, at least from my point of view, is the in-memory tables and procedures known as project Hekaton. Because the tables are organized completely differently than traditional SQL Server tables there are, naturally, some limitations around Hekaton tables and procedures. In looking over the list in Kalen Delaney’s CTP1 whitepaper, I’m sure most relational designers would be struck by the “no check constraints, no foreign keys, no triggers” limits. I was intrigued by the repercussions with respect to collation. Hekaton tables must use a BIN2 collation on all character columns that participate in indexes. In addition, the natively compiled procedures only support BIN2 collations for comparisons, sorting, and grouping. Since, BIN2 collations are unusual in my experience, I decided to look into this. Especially since you don’t need a copy of SQL Server 2014 to look into this now.

BIN2 uses a sorting/grouping/comparison algorithm based on Unicode codepoints. This means that sorting/grouping/comparison (I’ll use just the word “sorting” as a shortcut for a while) is, by definition, case-sensitive and accent-sensitive. Any upper-case character sorts before all lower-case characters. So if I create a BIN2-version of the authors table in the pubs database, the last name “del Castillo” sorts at the end of the list using “ORDER BY au_lname”. This would be surprising to most end-users and, as Michael Kaplan wrote in his “International Features in SQL Server 2000” whitepaper: “in general, one of the most effective ways to alienate end users of an application is to get a task such as basic sorting wrong.”

Maybe I’m worrying too much about this, as many/most folks in places other than US may be used to using the COLLATE clause on expressions *everywhere*, includng string literals. As Qingsong Yao writes, “A linguistic collation usually can sort one language correctly, but not all languages. For example, the Latin1_General_CI_AS collation does not follow the rule of French language.” But for those that aren’t familiar with COLLATE clause, unless you like binary sort, get used to the COLLATE clause. The equivalent for “ORDER BY au_lname” with COLLATE would be “ORDER BY au_lname COLLATE Latin1_General_CI_AS_KS_WS ASC”, replacing “Latin1_General…” by whatever collation you’re already using to sort. And we couldn’t use this with a natively compiled procedure.

Finally, I thought it would be nice to come up with recommendations for collation on an instance and database level. Qingsong Yao has some in his blog articles, such as:

1. Don’t alter a database collation (of an existing database)
2. Changing the collation of a column is not so trivial
3. Got Collation conflict, How to avoid this?

So after reading his suggestions, how about…

1. If you’re changing some tables in a DB to Hekaton, use a collation on columns in those tables.
2. If you plan to be going mostly or all Hekaton in a DB, create a new copy of the DB with a BIN2 collation at a database level
3. Do remember that tempdb uses the instance collation, so you can specify the COLLATE on temp tables or use the COLLATE DATABASE_DEFAULT syntax Kimberly mentions here.
4. If you’re going to have an all-Hekaton instance (not sure I’d do this because of limits on linked servers, etc. with compiled procs)
you could try a BIN2 collation at an instance level.
5. Don’t specify some columns with BIN2 and some non-BIN2 in the same table for Hekaton, because it may drive your programmers crazy, unless they’re already used to it.

Feel free to argue any of these points with me; if I think you have a better argument, I’ll change it.

But, to summarize this, before going to Hekaton, MAKE SURE and try things out (especially reports) with a BIN2 collation, and add the COLLATE clause when necessary to change things back to what users are expecting. Users DO like things fast, but the also like things “correct”.

Hope this makes your implementation smoother. And thanks to SQL Server Books Online, Michael Kaplan, Qingsong Yao, and Kalen Delaney’s “Inside SQL Server 2008 – Internals” for helping to provide some clarity on BIN2 collations and collations in general.

Cheers, Bob

Back to blogging, after a rather long hiatus

I was about to add a new blog entry, (coming soon) and realized that it’s been *months* since I blogged last, I’ve been rather busy lately, moving house after 15 years, and doing quite a bit of traveling (quite a bit for *me*, anyway) lately. I don’t think I’ve gone a month without a blog entry since early 2000′s. So, I’m getting back to it and more blog entries are coming. If you subscribe, I’ll try and make it worth your while.

Cheers, Bob

Speaking on Windows Azure SQL Database this month in Portland

The last time I spoke at the Portland SQL Server User Group was the first time I’d ever seen taken any questions on Windows Azure SQL Database, Microsoft’s Platform-as-a-Service (PaaS) offering that is a variation of SQL Server. So this month, on March 28, I’ll be doing a presentation on “Windows Azure SQL Database for the SQL Server Professional”.

I’ve been working with Windows Azure SQL Database almost since its inception, but, unlike the “box” version, they’ll be no discussion of versions or of what features were introduced when. Because there is only one version of Windows Azure SQL Database that you can be running…the current one!

Come and bring your questions. See you there.

@bobbeauch

I’m speaking on SQL Server service accounts next week

Just to let you know. The Portland (Oregon) SQL Server User Group has invited me to speak at the meeting on Thursday, Jan 24 at 6:30pm. I’ll be speaking on SQL Server service accounts, entitled “Who’s running my SQL Server services?”. I’ll be covering the evolution of service account choices and management in later versions of SQL Server and address how to manage these accounts and exactly what they control.

Full description of the talk and directions to the meeting are at the group’s website: http://pdxssug.com/. Hope to see you there!

@bobbeauch

Getting Windows Azure SQL Database diagnostics using Windows Azure cmdlets

I’ve been trying out the new supported Windows Azure PowerShell Cmdlets this week and I’d have to say I’m impressed. I’ve only used the ones for setup (Get-AzureSettingsFile and friends) and the ones for Windows Azure SQL Database (WASD) so far, and there’s a new one for WASD I really like. The original Codeplex WAAPS cmdlets only supported WASD Server and Firewall rule manipulation, the new ones also support a series of *-*AzureSqlDatabase ones, like Get-AzureSqlDatabase for example. I thought I should be able to “navigate” from what’s returned from the “Server” cmdlets down to the database, but unless I’ve missed something, that’s not possible as the Server cmdlets return SqlDatabaseServerContext. To get to Database cmdlets you need an IServerDataServiceContext and SqlDatabaseServerContext doesn’t implement that interface. Instead you need to use the cmdlet New-AzureSqlDatabaseServerContext, which requires a Server name and a PSCredential. The database commands work well, but…

The more interesting bit is what you can obtain through the context. You see, New-AzureSqlDatabaseServerContext does return a class that implements IServerDataServiceContext but that class is really ServerDataServiceSqlAuth. You should check that this is true in your script before using it by using the PowerShell “-is” operator. The ServerDataServiceSqlAuth includes 15 properties that return enumerations (actually they are WCF Data Services DataServiceQuery<T> objects) of interesting things. In fact there is an entire WCF Data Services object model for this which you can retrieve using RetrieveMetadata method on ServerDataServiceSqlAuth. Some of the interesting things are:

EventLogs – WASD event logs were recently introduced
Servers and ServerMetrics
Databases and DatabaseMetrics
Operations and OperationDetails – these are DAC operations you’ve done

So you can “monitor” your WASD through PowerShell, without ever hitting the Windows Azure Portal. Very Nice. If you didn’t understand that whole alphabet soup of class library references, here’s what it looks like in code. Remember, first you must use Get-AzureSettingsFile and Import-AzureSettingsFile to “configure” your administrative certificate.

# Get a SQLDataServicesSqlAuth (context), substitute name of your server
$cred = Get-Credential
$ctx = New-AzureSqlDatabaseServerContext -ServerName “[myserver]” -credential $cred

# check to see if you have the right class before going further
if ($ctx -is [Microsoft.WindowsAzure.Management.SqlDatabase.Services.Server.ServerDataServiceSqlAuth]) {

# gets EventLogs enumeration
$ctx.EventLogs
# or ServerMetrics enumeration
$ctx.ServerMetrics

Since this is really an “enumeration”, you could filter it in a loop like this:

# filter only events for master database
$ctx.EventLogs | Foreach-Object -Process { if ($_.DatabaseName -eq “master”) { $_ } }  #or whatever logic you want
# or even simpler
$ctx.EventLogs | where { $_.DatabaseName -eq “master” }

} # end of check for right class

So what else could one want? Well, if a had to say, it might be additional operations you can do with the portal, like wrapping the DAC Import-Export service (although I’ve been able to do this by hand, it would be nice to have a supported cmdlet) or setting up Windows Azure SQL Data Sync. Those may show up later on in time. Or the ability (this is probably do-able with WCF Data Services, I haven’t looked that hard yet) to filter the EventLogs or other enumerations before the fact. In the last line of code above, I’m fetching all the event log lines and filtering them at the client, rather than asking WCF Data Services for a subset. But for now, I’m happy to have the set of useful information and also the base functionality that these cmdlets provide. But of course, I do have an aversion to using web-based GUI for repeatable tasks.

Happy scripting, Bob.

@bobbeauch

I’m doing Expertseminarium in Stockholm in March

I’m happy to announce that I’ll be in Stockholm Mar 12-13 as a speaker in AddSkills’ Expertseminarium program. I’ll be doing 2 days full of SQL Server query optimization and internals information, troubleshooting, and analysis techniques. We’ll be diving into SQL Server using tools like Extended Events to get a good look into what’s going on with your system. Hope to see you there!

Cheers, Bob

@bobbeauch

Choosing Paths for Selective XML Index – Part 2 – Using the SXI XEvents

In part 1 of this series, we tried a simple example from the Books Online without any SXI index at all, and an SXI with all the paths covered. No XEvents were emitted in either case. Now let’s change the XSI around (by dropping and recreating, although we could use ALTER, so envision a drop in between each example) to see what triggers the XEvents and what the query plan effects are.

– first let’s back off one path
CREATE SELECTIVE XML INDEX simple_sxi ON Tbl(xmlcol)
FOR (
path123 =  ‘/a/b’ as XQUERY ‘node()’
– path124 =  ‘/a/b/c’
);

– now, you get XML Reader, XML Reader with XPath Filer, CI Seek on SIDX, CI Scan on base table
– and “selective_xml_index_path_not_indexed” XEvent for /a/b/c
SELECT T.id FROM tbl T WHERE T.xmlcol.exist(‘/a/b[c = "43"]‘) = 1

– how about adding the SINGLETON keyword and XQuery Type?
CREATE SELECTIVE XML INDEX simple_sxi ON Tbl(xmlcol)
FOR (
path123 =  ‘/a/b’ as XQUERY ‘node()’,
path124 =  ‘/a/b/c’ as XQUERY ‘xs:string’ SINGLETON
);

– now, you get a single CI Seek on SIDX and CI Scan on base table
SELECT T.id FROM tbl T WHERE T.xmlcol.exist(‘/a/b[c = "43"]‘) = 1

– how about an XML.value query instead of XML.exists?
– get XML Reader with XPath Filter, XML Reader, and CI Scan on base table
– and “selective_xml_index_no_compatible_sql_type” XEvent for /a/b/c
SELECT T.id FROM tbl T WHERE T.xmlcol.value(‘(/a/b/c)[1]‘, ‘nvarchar(10)’) = ’43′;

– For this to use SXI, we need:
ALTER INDEX simple_sxi ON Tbl FOR (add path124s = ‘/a/b/c’ as SQL nvarchar(10) SINGLETON);

– SXI CI Seek and CI Scan on base table, no XEvents
SELECT T.id FROM tbl T WHERE T.xmlcol.value(‘(/a/b/c)[1]‘, ‘nvarchar(10)’) = ’43′;

BTW, if you use ‘varchar(10)’ rather than ‘nvarchar(10)’ in the query, the SXI won’t be used. One last one, let’s see if the SXI will be used if we specify an incorrect XQuery type:

CREATE SELECTIVE XML INDEX simple_sxi ON Tbl(xmlcol)
FOR (
path123 =  ‘/a/b’ as XQUERY ‘node()’,
path124 =  ‘/a/b/c’ as XQUERY ‘xs:double’ SINGLETON
);

– We get “selective_xml_index_no_compatible_xsd_types” XEvent for /a/b/c
– And XML Reader with XPath Filter, XML Reader, CI Seek on SIDX and CI Scan on base table
SELECT T.id FROM tbl T WHERE T.xmlcol.exist(‘/a/b[c = "43"]‘) = 1;

So, to summarize, in this post and the last post we’ve shown:
The SXI cannot be used to search for mixed content.
The ‘AS XQUERY node()’ specification can be used to check for existance of non-terminating node.
The XEvents for SXI can guide you to understanding which nodes to specify.
You must use “As SQL…” hint in order for SXI to be used in XML.value method.
You use “As XQUERY…” hint to use SXI for XQuery predicates or paths in the XML.exist method.

To go back to the BOL examples and test your understanding, write a selective XML index create statement for the following:

SELECT T.record,
T.xmldata.value(‘(/a/b/c/d/e[./f = "SQL"]/g)[1]‘, ‘nvarchar(100)’)
FROM myXMLTable T

Cheers, Bob

@bobbeauch