TSQL Recipes – 2014 Edition

Announcing…the book

book_coverAt long last the wait is over. After much blood, sweat and more blood and sweat, the next edition of the SQL Server Recipes book is finished.

This edition brings several changes and quite a bit of re-write. It has been updated for many of the new SQL Server 2014 (well, new, at least until SQL 2016 hits mainstream) features that were added. In addition, we revisited some of the other features that had been omitted from previous editions to try and give it a more encompassing feel.

All of that said, the book is not a comprehensive listing of every command, jot or tittle in SQL Server. There were a finite number of pages and the features in SQL Server just has far too many features and nuances to cover within a single edition.

Despite the limitation of page quantity, we do feel that this is a pretty comprehensive book covering a wide array of features with several examples of how to perform various tasks along with use-cases for when to use the example.

When you crack the covers, you will find examples of how to perform backups to blob storage in Azure, create In-memory OLTP tables, restore from blob storage, and in some cases you will see how to use Extended Events to help with troubleshooting. That is just a small sampling of the contents that fill the almost 900 pages in this book.

Combine this reference book with the previous editions, and you will have an excellent resource for SQL Server.

You can purchase the book on Amazon – here.

Now for the mushy stuff

I am grateful to the folks at Apress for letting us come on board with this book and continue writing about SQL Server and creating a great resource.

I am grateful to the other authors Jonathan Gennick and Wayne Sheffield for helping push this along. The collaboration and support provided by these guys was fantastic. Their patience was also exceptional.

Equally important was the patience and understanding afforded by my family. Writing does take a significant amount of time and they sacrificed so I could continue on this project.

Thanks to all these folks for helping make this a great achievement!

SQL Server and Defaults

TSQL2sDayWhat is that default setting?

SQL server does a fantastic job of having numerous settings at the server level and at the database level predefined for you. Additionally, the OS has a bunch of settings that are predefined. These are notoriously the default settings. Or as some would say: “Set it and Forget it Settings.” Sadly, the set it part never really happens – except during the install.

Today is the second Tuesday of the the month, and that means it is TSQL Tuesday. As I mentioned in a previous article, this month the topic is hosted by Andy Yun (blog | twitter). Andy has chosen to have everybody talk about default settings in SQL Server. You can read everything Andy has said in his invite for the month.


Defaults Defaults Defaults

I could ramble on about database settings that get changed, but I have already addressed the changing of database settings – here. While I did address the changing of the settings, I did not address that it is an awesome thing to use to find out who might be changing those default settings (at the database level) that you have already set and optimized for the instance.

Or I could go on about how the OS has various settings that are far less than optimal for SQL Server. A good example would the that tree hugger setting that I talked about last month. But rather than do that, I will advise that you just read that article – here.

Or we can belabor the point about a fantastic setting that equates to NOLOCK. But, that isn’t a default setting and one would need a pretty good reason to change the default setting to READ UNCOMMITTED. So, just read a bit about the coolness you can see in the execution plans when the NOLOCK directive is used – here.

There are just so many wonderful default settings within SQL Server (or within the Windows OS) that could be discussed or that have already been discussed.

About all of those settings, I will say this. Database Settings are not “One Size Fits ALL.” But that is what a default setting is trying to do. If you find default settings within a database, then you should probably evaluate the setting and make sure it is set appropriately.

What default to discuss then?

Rather than talk about a plethora of settings that could / should be changed, I want to speak about one I doubt most would consider. This is the default setting that can only be adjusted from between the ears!

This is a default behavior I have noticed with many DBAs. I have heard various names for this behavior and most are probably quite accurate. The default setting that I think should be changed is one that will lead to a longer more fulfilling career. Let me see if I can describe the behavior well enough.

Often times, companies will purchase monitoring software to help alert to problems within the environment. This is a good thing. A DBA can’t be available 24x7x365. So the software should be configured and tuned to each database to allow for different tolerance/alert thresholds. This is a good thing too – if it is done. When done properly, the DBA can get a good nights rest as well as feel confident the environment is running properly.

If the software is not properly tuned and configured, the DBA will probably rectify that sooner rather than later. This is not the big behavior to change.

What I do see all too often is a complete reliance on the software that is monitoring – to a fault. I have seen hundreds of DBAs just sit and watch the pretty little dials and gauges on the screen of the software and only react when one of the items turns red. A career surely can’t be built off of this kind of behavior.

Rather than sit there and wait for something to fail, why not proactively check the servers? Why not try to build a script repository that will do everything (and more) that the monitoring software can do? While building that repository, think of the skills that will be gained and the knowledge that can be retained across jobs! In addition, I have been some places where a script repository was able to replace the purchased software and saved 100’s of thousands of dollars per year in software maintenance costs.

One of my favorite statements is that a “Senior DBA should be able to script his own solutions!” Being able to create monitoring scripts to replace that canned app will certainly get you to that next level. It will also get you out of that default behavior of complete reliance on the canned software and imminent career stagnation.

Learn a little and grow your career.

Oh, and there are some great monitoring tools out there. They can provide a great asset to a company – if and when used properly.


PASS Summit 2015 – Guess What?

Comments: No Comments
Published on: July 13, 2015

Quick run the other way!

PASS Summit will never be the same!


Well, because for the first time in the history of me, I have been selected to speak at such a prestigious event.

I'm Speaking Graphic_Large

Isn’t that just crazy?

In some ways it seems crazy. When I received the email I was exhilarated and completely astonished. I had hoped I would be selected, but did not entirely think it would happen this year.

I have to confess that I am honored to be one of the many to have been selected. This is really cool. You may be wondering why I am blogging about this opportunity soooo late after the speakers were announced. Well, the answer is rather straight forward. Life has been a bit crazy the past few weeks.

The last night of SQL Cruise I received a phone call from my family letting me know that my grandfather had become fatally ill. I spent the next several days with him in the hospital until he passed away a week later. Then there were the holidays and the funeral. And to cap that off, I flew down to Phoenix to speak to the two user groups last week. SO, it has been a whirlwind!

All of that said, I am announcing it now and really am excited to be a apart of such an AWESOME event.

Now for the surprise.

When I discovered which session had been selected, I was very surprised. I expected one of my Extended Events sessions to be picked. Nope! Instead, one of my other fun sessions was picked. We will be talking about Compression in SQL Server at Summit.

I am hopeful we are able to make this an exciting and enjoyable session for the attendees. I hope the attendees will also be able to take the information home and be able to use it for better (or worse) in their environments.

I do hope to see many of you in attendance. On the agenda for the attendees in this session will be:

  1. Dive into functions available to help learn about compression
  2. Evaluation of compression settings and possible savings
  3. Dive into the Pages
  4. A quick look at the CD and compressed data types
  5. Levity

Of course, I could just decide to change this on a whim – buahaha!

New Extended Events for 2016

As we begin to get a grasp of all that is available with SQL Server 2016 and all of the new features, it is a great idea to see what else has been made available to us in order to help support and troubleshoot these new features. With that, it is well worth a look at the new changes to Extended Events to support all the shiny new stuff.

Here is a list of what I could find as of CTP 2 (I have yet to look at CTP 2.1). This list alone has some really cool stuff in it.  Browse through it and you will be able to find new stuff to help support and administer things like Stretch Database as well as JSON. You won’t find these events within Trace or profiler – only XE.

The presence of these new events (even some new ones for Query Store) is quite enough to get me excited for that next release of SQL Server. There is some great stuff coming down the pipe.

I have randomly highlighted some of the new events to support some of these new features. (And since this is only CTP 2.0, one can be sure there will be changes made to this list with the actual release of SQL Server 2016.)

SQLVersion EventName PackageName EventDescription
Microsoft SQL Server 2016 (CTP2.0) availability_replica_database_fault_reporting sqlserver Occurs when a database reports a fault to the availability replica manager which will trigger a replica restart if the database is critical
Microsoft SQL Server 2016 (CTP2.0) backup_restore_progress_trace sqlserver Prints backup/restore progress trace messages with details
Microsoft SQL Server 2016 (CTP2.0) batchmode_sort_spill_file sqlserver Record the spill file read/write information for batch mode sort
Microsoft SQL Server 2016 (CTP2.0) batchmode_sort_status sqlserver Record batch mode sort status
Microsoft SQL Server 2016 (CTP2.0) cl_duration XtpCompile Reports the duration of the C compilation.
Microsoft SQL Server 2016 (CTP2.0) clr_context_dump sqlclr ClrContextDump triggered.
Microsoft SQL Server 2016 (CTP2.0) column_store_expression_filter_apply sqlserver An expression bitmap filter was applied on a rowgroup column batch.
Microsoft SQL Server 2016 (CTP2.0) column_store_expression_filter_bitmap_set sqlserver An expression bitmap filter was set on a rowgroup column at rowgroup compile time.
Microsoft SQL Server 2016 (CTP2.0) columnstore_delete_buffer_closed_rowgroup_with_generationid_found sqlserver Delete buffer can not be flushed due to existence of one or more closed rowgroups with generation ID.
Microsoft SQL Server 2016 (CTP2.0) columnstore_delete_buffer_flush_failed sqlserver Columnstore delete buffer flush failed.
Microsoft SQL Server 2016 (CTP2.0) columnstore_delete_buffer_state_transition sqlserver Occurs when closed delete buffer state changes.
Microsoft SQL Server 2016 (CTP2.0) columnstore_delta_rowgroup_closed sqlserver A delta rowgroup was closed.
Microsoft SQL Server 2016 (CTP2.0) columnstore_no_rowgroup_qualified_for_merge sqlserver A user invoked a REORG command but based on the policy, no rowgroup qualified.
Microsoft SQL Server 2016 (CTP2.0) columnstore_rowgroup_compressed sqlserver A compressed rowgroup was created.
Microsoft SQL Server 2016 (CTP2.0) columnstore_rowgroup_merge_complete sqlserver A MERGE operation completed merging columnstore rowgroups together.
Microsoft SQL Server 2016 (CTP2.0) columnstore_rowgroup_merge_start sqlserver A MERGE operation started merging columnstore rowgroups together.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_begin_delete_buffer_flush sqlserver Columnstore tuple mover started flushing a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_compression_stats sqlserver Statistics about the movement of a deltastore to a compressed rowgroup, including duration, size, etc.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_delete_buffer_flush_requirements_not_met sqlserver Occurs when column store tuple mover was not able to acquire required locks for flushing a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_delete_buffer_truncate_requirements_not_met sqlserver Occurs when column store tuple mover was not able to acquire required locks for truncating a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_delete_buffer_truncated sqlserver Columnstore tuple mover truncated delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_delete_buffers_swapped sqlserver Columnstore tuple mover swapped delete buffers.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_end_delete_buffer_flush sqlserver Columnstore tuple mover completed flushing a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_met_requirements_for_delete_buffer_flush sqlserver Occurs when column store tuple mover has acquired required locks and is ready to start flushing a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_met_requirements_for_delete_buffer_truncate sqlserver Occurs when column store tuple mover has acquired required locks and is ready to start truncating a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_x_dbfl_acquired sqlserver Occurs when X Delete Buffer Flush Lock is acquired.
Microsoft SQL Server 2016 (CTP2.0) compressed_alter_column_is_md_only sqlserver Occurs during an alter column operation. Indicates whether the alter column is metadata only or not.
Microsoft SQL Server 2016 (CTP2.0) connection_accept sqlserver Occurs when a new connection is accepted by (or duplicated into) the server. This event serves to log all connection attempts.
Microsoft SQL Server 2016 (CTP2.0) connection_duplication_failure sqlserver Occurs when connection duplication fails
Microsoft SQL Server 2016 (CTP2.0) data_purity_checks_for_dbcompat_130 sqlserver Occurs when an operation that may require a data purity check for dbcompat level 130 occurs.
Microsoft SQL Server 2016 (CTP2.0) database_recovery_times sqlserver Database recovery times
Microsoft SQL Server 2016 (CTP2.0) database_tde_encryption_scan_duration sqlserver Database TDE Encryption Scan
Microsoft SQL Server 2016 (CTP2.0) database_transaction_yield sqlserver Occurs when a database transaction yields execution due to TDS buffer being full.
Microsoft SQL Server 2016 (CTP2.0) ex_raise2 sqlos Raised exception
Microsoft SQL Server 2016 (CTP2.0) fulltextlog_written sqlserver Errorlog written
Microsoft SQL Server 2016 (CTP2.0) global_transaction sqlserver Occurs when global transaction is started.
Microsoft SQL Server 2016 (CTP2.0) hadr_db_log_throttle sqlserver Occurs when DB log generation throttle changes.
Microsoft SQL Server 2016 (CTP2.0) hadr_db_log_throttle_configuration_parameters sqlserver Occurs when DB log generation throttle configuration parameters are read.
Microsoft SQL Server 2016 (CTP2.0) hadr_db_log_throttle_input sqlserver Occurs when HADR Fabric log management component updates log throttle.
Microsoft SQL Server 2016 (CTP2.0) hadr_db_marked_for_reseed sqlserver Occurs when a HADR secondary DB falls too far behind primary and is marked for reseed.
Microsoft SQL Server 2016 (CTP2.0) hadr_db_remote_harden_failure sqlserver A harden request as part of a commit or prepare failed due to remote failure.
Microsoft SQL Server 2016 (CTP2.0) hadr_log_block_send_complete sqlserver Occurs after a log block message has been sent. This event is only used for failpoints.
Microsoft SQL Server 2016 (CTP2.0) hadr_partner_log_send_transition sqlserver Log send transition between log writer and log capture.
Microsoft SQL Server 2016 (CTP2.0) hadr_partner_restart_scan sqlserver Restart partner scans for this partner.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_backup_state_change sqlserver Physical Seeding Backup Side State Change.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_failure sqlserver Physical Seeding Failure Event.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_forwarder_state_change sqlserver Physical Seeding Forwarder Side State Change.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_forwarder_target_state_change sqlserver Physical Seeding Forwarder Target Side State Change.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_progress sqlserver Physical Seeding Progress Event.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_restore_state_change sqlserver Physical Seeding Restore Side State Change.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_submit_callback sqlserver Physical Seeding Submit Callback Event.
Microsoft SQL Server 2016 (CTP2.0) hadr_send_harden_lsn_message sqlserver Occurs when we’re crafting a message to send containing a new hardened LSN on a secondary. Test only.
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_configuration_state sqlserver Indicates session state changes
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_dump_dropped_message sqlserver Use this event to trace dropped HADR transport messages throughout the system.
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_dump_failure_message sqlserver Use this event to help trace HADR failure messages.
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_dump_preconfig_message sqlserver Use this event to help trace HADR preconfig messages.
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_sync_send_failure sqlserver Synchronous send failure in hadr transport.
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_ucs_registration sqlserver Reports UCS registration state changes
Microsoft SQL Server 2016 (CTP2.0) json_depth_error sqlserver Occurs when depth of json text being parsed is bigger than 128.
Microsoft SQL Server 2016 (CTP2.0) json_parsing_error sqlserver Indicates json parser error. Occurs when json format is not valid.
Microsoft SQL Server 2016 (CTP2.0) json_stackoverflow_error sqlserver Json parser stack overflow.
Microsoft SQL Server 2016 (CTP2.0) json_unescaped_character sqlserver Jsonparser hitted unescaped character in json string.
Microsoft SQL Server 2016 (CTP2.0) log_pool_cache_miss sqlserver Occurs when a log consumer attempts to lookup a block from the log pool but fails to find it.
Microsoft SQL Server 2016 (CTP2.0) log_pool_push_no_free_buffer sqlserver Occurs when log pool push fails to get a free buffer and bails out.
Microsoft SQL Server 2016 (CTP2.0) login_event sqlserver This is an abbreviated version of process_login_finish, containing only the columns required by external monitoring telemetry pipeline.
Microsoft SQL Server 2016 (CTP2.0) notify_on_clr_disabled sqlclr Event_ClrDisabled has been triggered in ClrHost.
Microsoft SQL Server 2016 (CTP2.0) on_app_domain_failure sqlclr AppDomain hit a failure.
Microsoft SQL Server 2016 (CTP2.0) on_app_domain_unloading sqlclr AppDomain is unloading.
Microsoft SQL Server 2016 (CTP2.0) on_host_policy_callback sqlclr IHostPolicyManager received an event.
Microsoft SQL Server 2016 (CTP2.0) on_host_policy_failure sqlclr IHostPolicyManager received an event.
Microsoft SQL Server 2016 (CTP2.0) page_cache_trace sqlserver Modification of the page cache.
Microsoft SQL Server 2016 (CTP2.0) premature_systemthread_wakeup sqlos system thread is woken up prematurely
Microsoft SQL Server 2016 (CTP2.0) private_login_accept sqlserver TDS connection accept event that is logged to private MDS table.
Microsoft SQL Server 2016 (CTP2.0) private_login_finish sqlserver TDS login finish event that is logged to private MDS table.
Microsoft SQL Server 2016 (CTP2.0) process_login_finish sqlserver This event is generated when server is done processing a login (success or failure).
Microsoft SQL Server 2016 (CTP2.0) query_execution_batch_filter sqlserver Occurs when batch processing filters one batch using expression services.
Microsoft SQL Server 2016 (CTP2.0) query_execution_batch_spill_started sqlserver Occurs when batch operator runs out of granted memory and initiates spilling to disk of another partition of in-memory data.
Microsoft SQL Server 2016 (CTP2.0) query_execution_column_store_rowgroup_scan_finished sqlserver Occurs when row bucket processor finishes column store row group scan.
Microsoft SQL Server 2016 (CTP2.0) query_execution_column_store_segment_scan_finished sqlserver Occurs when row bucket processor finishes column store segment scan.
Microsoft SQL Server 2016 (CTP2.0) query_execution_column_store_segment_scan_started sqlserver Occurs when column segment scan starts.
Microsoft SQL Server 2016 (CTP2.0) query_memory_grant_blocking sqlserver Occurs when a query is blocking other queries while waiting for memory grant
Microsoft SQL Server 2016 (CTP2.0) query_memory_grant_usage sqlserver Occurs at the end of query processing for queries with memory grant over 5MB to let users know about memory grant inaccuracies
Microsoft SQL Server 2016 (CTP2.0) query_store_background_task_persist_finished qds Fired if the background task for Query Store data persistence is completed successfully
Microsoft SQL Server 2016 (CTP2.0) query_store_background_task_persist_started qds Fired if the background task for Query Store data persistence started execution
Microsoft SQL Server 2016 (CTP2.0) query_store_capture_policy_abort_capture qds Fired when an UNDECIDED query failed to transition to CAPTURED.
Microsoft SQL Server 2016 (CTP2.0) query_store_capture_policy_evaluate qds Fired when the capture policy is evaluated for a query.
Microsoft SQL Server 2016 (CTP2.0) query_store_capture_policy_start_capture qds Fired when an UNDECIDED query is transitioning to CAPTURED.
Microsoft SQL Server 2016 (CTP2.0) query_store_db_data_structs_not_released qds Fired if Query Store data structures are not released when feature is turned OFF.
Microsoft SQL Server 2016 (CTP2.0) query_store_db_diagnostics qds Periodically fired with Query Store diagnostics on database level.
Microsoft SQL Server 2016 (CTP2.0) query_store_db_settings_changed qds Fired when Query Store settings are changed.
Microsoft SQL Server 2016 (CTP2.0) query_store_db_whitelisting_changed qds Fired when Query Store database whitelisting state is changed.
Microsoft SQL Server 2016 (CTP2.0) query_store_generate_showplan_failure qds Fired when Query Store failed to store a query plan because the showplan generation failed.
Microsoft SQL Server 2016 (CTP2.0) query_store_global_mem_obj_size_kb qds Periodically fired with Query Store global memory object size.
Microsoft SQL Server 2016 (CTP2.0) query_store_load_started qds Fired when query store load is started
Microsoft SQL Server 2016 (CTP2.0) query_store_schema_consistency_check_failure qds Fired when the QDS schema consistency check failed.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_cleanup_finished qds Fired when size retention policy clean-up task is finished.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_cleanup_skipped qds Fired when starting of size retention policy clean-up task is skipped because its minimum repeating period did not pass yet.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_cleanup_started qds Fired when size retention policy clean-up task is started.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_plan_cost qds Fired when eviction cost is calculated for the plan.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_query_cost qds Fired when query eviction cost is calculated for the query.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_query_deleted qds Fired when size based retention policy deletes a query from Query Store.
Microsoft SQL Server 2016 (CTP2.0) query_trace_column_values sqlserver Trace output column values of each row on each query plan operator
Microsoft SQL Server 2016 (CTP2.0) recalculate_mem_target sqlos New Memory Targets which are set after RecalculateTarget is executed
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_db_ddl sqlserver Occurs when the database T-SQL ddl for stretching data is processed.
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_provision_operation sqlserver Occurs when a provisioning operation starts or ends.
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_query_rewrite sqlserver Occurs when RelOp_Get is replaced during query rewrite for Stretch.
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_table_ddl sqlserver Occurs when the table T-SQL ddl for stretching data is processed.
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_telemetry sqlserver Occurs whenever an on premise system transmits a telemetry event to Azure DB.
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_telemetry_rejected sqlserver Occurs whenever an AzureDB Stretch telemetry event is rejected
Microsoft SQL Server 2016 (CTP2.0) report_login_failure sqlserver This event is generated for a TDS login failure.
Microsoft SQL Server 2016 (CTP2.0) rpc_starting_aggregate sqlserver Occurs periodically, aggregating all occasions an rpc call is started.
Microsoft SQL Server 2016 (CTP2.0) rpc_starting_aggregate_xdb sqlserver Occurs periodically, aggregating all occasions an rpc call is started.
Microsoft SQL Server 2016 (CTP2.0) sql_batch_starting_aggregate sqlserver Occurs periodically, aggregating all occasions a sql batch is started.
Microsoft SQL Server 2016 (CTP2.0) sql_batch_starting_aggregate_xdb sqlserver Occurs periodically, aggregating all occasions a sql batch is started.
Microsoft SQL Server 2016 (CTP2.0) startup_dependency_completed sqlserver Occurs on the completion of a startup dependency in the SQL Server startup sequence
Microsoft SQL Server 2016 (CTP2.0) stretch_codegen_errorlog sqlserver Reports the output from the code generator
Microsoft SQL Server 2016 (CTP2.0) stretch_codegen_start sqlserver Reports the start of stretch code generation
Microsoft SQL Server 2016 (CTP2.0) stretch_create_migration_proc_start sqlserver Reports the start of migration procedure creation
Microsoft SQL Server 2016 (CTP2.0) stretch_create_remote_table_start sqlserver Reports the start of remote table creation
Microsoft SQL Server 2016 (CTP2.0) stretch_create_update_trigger_start sqlserver Reports the start of create update trigger for remote data archive table
Microsoft SQL Server 2016 (CTP2.0) stretch_database_disable_completed sqlserver Reports the completion of a ALTER DATABASE SET REMOTE_DATA_ARCHIVE OFF command
Microsoft SQL Server 2016 (CTP2.0) stretch_database_enable_completed sqlserver Reports the completion of a ALTER DATABASE SET REMOTE_DATA_ARCHIVE ON command
Microsoft SQL Server 2016 (CTP2.0) stretch_database_events_submitted sqlserver Reports the completion telemetry transfer
Microsoft SQL Server 2016 (CTP2.0) stretch_migration_debug_trace sqlserver Debug trace of stretch migration actions.
Microsoft SQL Server 2016 (CTP2.0) stretch_migration_queue_migration sqlserver Queue a packet for starting migration of the database and object.
Microsoft SQL Server 2016 (CTP2.0) stretch_migration_sp_stretch_get_batch_id sqlserver Call sp_stretch_get_batch_id
Microsoft SQL Server 2016 (CTP2.0) stretch_migration_start_migration sqlserver Start migration of the database and object.
Microsoft SQL Server 2016 (CTP2.0) stretch_sync_metadata_start sqlserver Reports the start of metadata checks during the migration task.
Microsoft SQL Server 2016 (CTP2.0) stretch_table_codegen_completed sqlserver Reports the completion of code generation for a stretched table
Microsoft SQL Server 2016 (CTP2.0) stretch_table_provisioning_step_duration sqlserver Reports the duration of a stretched table provisioning operation
Microsoft SQL Server 2016 (CTP2.0) stretch_table_remote_creation_completed sqlserver Reports the completion of remote execution for the generated code for a stretched table
Microsoft SQL Server 2016 (CTP2.0) stretch_table_row_migration_event sqlserver Reports the completion of the migration of a batch of rows
Microsoft SQL Server 2016 (CTP2.0) stretch_table_row_migration_results_event sqlserver Reports an error or completion of a successful migration of a number of batches of rows
Microsoft SQL Server 2016 (CTP2.0) stretch_table_unprovision_completed sqlserver Reports the completion removal of local resources for a table that was unstretched
Microsoft SQL Server 2016 (CTP2.0) stretch_table_validation_error sqlserver Reports the completion of validation for a table when the user enables stretch
Microsoft SQL Server 2016 (CTP2.0) stretch_unprovision_table_start sqlserver Reports the start of stretch table un-provisioning
Microsoft SQL Server 2016 (CTP2.0) trace_dump_deleted_object_table_row XtpEngine Dump deleted object table row
Microsoft SQL Server 2016 (CTP2.0) trust_verification_failed sqlserver Occurs when a SQL Server binary fails Authenticode signature verification.
Microsoft SQL Server 2016 (CTP2.0) ucs_negotiation_completion ucs UCS transport connection negotiation completed
Microsoft SQL Server 2016 (CTP2.0) unable_to_verify_trust sqlserver Occurs when SQL Server is unable to perform Authenticode signature verification on binaries.
Microsoft SQL Server 2016 (CTP2.0) xio_blob_properties_obtained sqlserver Windows Azure Storage blob property is obtained from response header.
Microsoft SQL Server 2016 (CTP2.0) xio_failed_request sqlserver Failed to complete a request to Windows Azure Storage.
Microsoft SQL Server 2016 (CTP2.0) xio_header_obtained sqlserver Response header is obtained from request to Windows Azure Storage.
Microsoft SQL Server 2016 (CTP2.0) xio_read_complete sqlserver Read complete from Windows Azure Storage response.
Microsoft SQL Server 2016 (CTP2.0) xio_request_opened sqlserver A request is opened to Windows Azure Storage.
Microsoft SQL Server 2016 (CTP2.0) xio_send_complete sqlserver Request send to Windows Azure Storage is complete.
Microsoft SQL Server 2016 (CTP2.0) xio_write_complete sqlserver Request send to Windows Azure Storage is complete.
Microsoft SQL Server 2016 (CTP2.0) xstore_acquire_lease sqlserver The properties of the lease acquire reques.
Microsoft SQL Server 2016 (CTP2.0) xstore_create_file sqlserver Creating an XStore file has been attempted with the options below.
Microsoft SQL Server 2016 (CTP2.0) xstore_debug_trace sqlserver Telemetry tracing event has occurred.
Microsoft SQL Server 2016 (CTP2.0) xstore_lease_renewal_request sqlserver Attempt to renew blob lease
Microsoft SQL Server 2016 (CTP2.0) xtp_alter_table sqlserver Occurs at start of XTP table altering.
Microsoft SQL Server 2016 (CTP2.0) xtp_ckptctrl_abort_checkpoint XtpEngine Indicates that the checkpoint close thread aborted a checkpoint.
Microsoft SQL Server 2016 (CTP2.0) xtp_ckptctrl_close_checkpoint XtpEngine Indicates that the checkpoint close thread hardened a checkpoint.
Microsoft SQL Server 2016 (CTP2.0) xtp_ckptctrl_close_install_merge XtpEngine Indicates that the checkpoint close thread installed a merge.
Microsoft SQL Server 2016 (CTP2.0) xtp_ckptctrl_new_segment_definition XtpEngine Indicates that the checkpoint controller processed a new segment definition.
Microsoft SQL Server 2016 (CTP2.0) xtp_ckptctrl_storage_array_grow XtpEngine Indicates the XTP storage array has grown in size.
Microsoft SQL Server 2016 (CTP2.0) xtp_drop_table sqlserver Occurs after an XTP table has been dropped.
Microsoft SQL Server 2016 (CTP2.0) xtp_merge_request_start XtpEngine Indicates that an XTP storage merge was requested.
Microsoft SQL Server 2016 (CTP2.0) xtp_merge_request_stop XtpEngine Indicates that an XTP storage merge request ended.
Microsoft SQL Server 2016 (CTP2.0) xtp_merge_start XtpEngine Indicates that an XTP storage merge range is starting.
Microsoft SQL Server 2016 (CTP2.0) xtp_merge_stop XtpEngine Indicates that an XTP storage merge range completed.
Microsoft SQL Server 2016 (CTP2.0) xtp_redo_log_corruption XtpEngine Indicates that the redo process failed due to log corruption.
Microsoft SQL Server 2016 (CTP2.0) xtp_root_file_deserialized XtpEngine Indicates that the load of a checkpoint root file is complete.
Microsoft SQL Server 2016 (CTP2.0) xtp_root_file_serialized XtpEngine Indicates that the write of the checkpoint root file is complete.

There you have it, currently 165 new events available to help monitor and troubleshoot SQL Server.

Database Drops in SQL 2012

In the previous article on this topic (which can be read here), I discussed the problem of having a database get dropped and the need to find out who dropped the database and when they dropped it.  In that article, I demonstrated how the information (at least some of it) could be found through the use of Extended Events.

What I forgot to mention is the method I shared was for SQL Server 2014. While the events do exist for SQL Server 2012, there is a minor modification that needs to be made in order to avoid the dreaded error message that says something like:

Msg 25713, Level 16, State 23, Line 1
The value specified for event attribute or predicate source, “object_type”, event, “object_created”, is invalid.

I am sure many of us would rather not have to deal with such a terrible thing as an error when we want to do something that should just work. Well, here is the fix for that error if you tried to deploy that XE Session to a previous version (such as 2012).

Do you see that change? Instead of using the map_value in 2012, one must use  the map_key. This was a good change in 2014 to allow us to use human friendly terms instead of needing to lookup the map_key for everything like in 2012.

In following the theme from the previous article, here is the rest of the setup for testing this XEvent session to see how it would trap that data and how to parse the data from the session.


Nice and simple for a test. This should be enough to have trapped some data for our XEvent session.  Let’s parse the data and take a look.

With the XEvent session started, and a database that has been created and dropped, this last query will produce some data to help track who has been adding or removing databases from your SQL Instance.  If you have run the example code, you should see data very similar to what is shown in this next image.


In the attached image, I have separated the pair of statements for the DB create from the pair of statements for the DB drop (recall that I mentioned each will fire twice).  I hope this serves you well in your attempts to reign in the control of your environment and discover who may be creating rogue databases or even dropping the databases that are essential to the business.

SQL Cruise Mediterranean+

Comments: 2 Comments
Published on: June 24, 2015

This is a post that is looooong overdue. I have intended to write some of this at least since the first SQL Cruise I attended back in 2013 (in the Caribbean). Now with three Cruises under the belt and plenty of awesome experiences, it is high time to follow through on those intentions.

Official_SQLCruise_2015For those that have not heard, SQL Cruise is a mix of training and vacation. You get a little (or a lot) of SQL and then you get a little (or a lot) of vacation mixed with the opportunity to see places you may not have otherwise visited. Last but certainly not least is the opportunity afforded to all attendees to meet other people they may have never otherwise gotten a chance to meet. This last benefit may be the most important facet of SQL Cruise in that the networking done during the cruise will last a long time and can certainly open a few doors if/when necessary.

One great example of this networking occurred on the first cruise I attended. In the meetings outside of the scheduled training, one of the other cruisers (he is now an MCM and MVP) asked a pretty important question about an issue within his work environment. A performance monitoring package they ran for all of their clients was causing some serious problems. The problems affected about 30% of all of the servers which numbered over 1500. The application on the affected servers would stop responding and they would no longer receive metrics or alerts to conditions being raised.

This problem was significant enough that they engaged Microsoft and Microsoft engineers had been collecting metrics and logs for over six months (at the time). There was a series of try this and try that and all of it resulted in no change whatsoever. So this fellow cruiser brought the issue to the cruise with him. In chatting with the cruiser (oh, and he had to fly to the US from Europe in order to attend), I discovered that the symptoms he was enduring were quite similar to some things I had seen within SSIS packages for example. We discussed a quick fix which he took back to his employer.

Through a series of tests and deployments, this Cruiser and his employer rolled out the fix to all of the servers in the environment. This fix ended up saving them so much money in labor and other costs, that his employer sent him and his family (eight people in total) back to SQL Cruise in the Caribbean the following year. The ROI for this Cruiser and his employer was huge! Additionally, he and I have become friends thanks to the Cruise. This is the sort of stuff that defines SQL Cruise.

This year, we had the chance to repeat the Cruise by attending the Mediterranean version. This installment was a whirlwind of touring Europe. Some started in London before proceeding to Barcelona and then on to such places as Pompeii, Rome, Pisa, Cannes, Monaco, and Mallorica before returning to Barcelona to continue on to Berlin or London before returning home. For me, we chose to start in Paris and then taking the bullet train on to Barcelona.

Med Sunset

The tourism was fast and furious. The training was faster and more furious. And in the end, the European / Mediterranean trip was gone before we knew it. All who attended surely walked away with great memories and with having learned something (whether it be cultural, historic, or SQL in nature).

If you have the chance to attend a SQL Cruise, I would say do it. The training comes from the technical leads as well as the attendees in the rooms. Often, the tech lead will even defer to any of the other professionals in attendance. Especially in the case of Trace Flags as we learned this past week – in every session and in every office hours meeting. Just ask Grant Fritchey (blog | twitter) about it some time – he loves talking about Trace Flags.

Reading Extended Event File Session Data

Using Extended Events to trap/trace information allows the trapping of that information to various targets. One of the targets I will frequently tell people to use is the file target. The reasoning for this is the opportunity this affords to be able to review the output  at a later time whether the event session is running or not.

Along  with that recommendation, I also show some quick code to pull the data back from the session target (the file on disk). This code is written to dynamically pull the file information out of the XE Dynamic Management Views to make it a little easier on the user (and a bit less likely to have a typo). That code looks a bit like the following:

This works really well when the session is running. It pulls the desired file path and name back from the DMVs and one is able to parse the session data. But what if that session is stopped?

Well, if the session is stopped, we have a problem. With a stopped session, the session above will not produce any results. The short of this is that SQL Server removes the entries from the sys.dm_xe_session_targets and sys.dm_xe_sessions DMVs. Due to that, there is a bit of a tweak to be able to query those files for the sessions that have been stopped.

To query those files, one could rewrite the previous query to something like this:

Now, I bet you may be asking why stop an Extended Event session from running. Afterall, extended events is extremely light weight and has little impact on the server, right? True that may be, there is still a cost for traces. If you can just run a trace for a targeted time period, then why not limit the amount of tracing?

Looking at the code, you can see that I reverted to a less dynamic approach to read those event files. Instead of trying to get that info direct from the database, I am just telling SQL Server where to find the files on disk (note the filepath that is passed).

There you have it. A means to query these trace files whether the XE trace is running or stopped.

Execution Plans in Extended Events

Extended Events is a wonderful tool. Execution Plans are also some wonderful things – or are a wonderful tool as well. Both of these tools are fantastic for troubleshooting. Combined, they can potentially be even better.

Or, they could just cause some hair loss and be a fairly complicated pain in your butt. Hopefully the combination of the two will be more useful than painful for you. And today, I will discuss one of the pains that could be an issue when combining these two tools. I will also discuss some options for getting around this pain point between XE and Execution Plans.

Let’s start by taking a quick look at two Extended Events events that can expose execution plans for you. These two events are query_pre_execution_showplan and query_post_execution_showplan. Here are a couple sample sessions set to trap these events.


With these sample sessions ready, we need a quick description of what is happening. I have built the sessions (at least the TrapEstExecPlans session) to go to both a file and the ringbuffer targets. This was done to test the behavior on both targets to ensure consistency in behavior between the different targets.

Also, I added a bunch of actions that are available to query, but the sample queries shown later will not use the data for those actions. This is only due to the examples I have chosen to share.

With those sessions created, go ahead and start them, then run a query like the following:

After  having executed this query, I can start taking a look at the data captured. I will only query the TrapEstExecPlans session in this post because the behavior between the two sessions and events is consistent.

Keep in mind that I talked about having both a ringbuffer and a file target for this session. I am only going to share the query from the ringbuffer target because the behavior from the memory target and the filetarget is the same.

If I take a look at the execution plans shared from the results of that query, I might see something like this:

Plan from XE


If I look at the execution plan at runtime for that same query, I would see something like this:

Exec Plan

Between the two plans, I have color coded the various attributes of the plan to pair the matching attributes and make it easier to spot the differences. In this case, the differences are in the first two nodes. The plan from Extended Events does not have the connection properties, but is also missing a few other important things like the StatementType and StatementText. Looking at the plan from XE in the gui, you would see something like this:


Instead of this:


Those minor differences in the XML of the execution plans can lead to somewhat of a pain. Alas, there is a fix for that. And the fix comes down to doing one of a few things already available to us in the XE session data. We can either lookup the plan_handle that was trapped by the action, or we can lookup the QueryPlanHash that is available in the XML from the execution plan data that has been trapped. With that information, one can likely retrieve the stored execution plan and catch the rest of the missing components of that execution plan.

There is one other option and that is the action that traps the sql_text. The sql_text that is captured can lead us to understand what kind of plan (select, update, delete, etc) we are looking at from the XE session data.

So, while it is a bit of a nuisance that the two sources of execution plans does not produce the same plan, it is not the end of the world. There is still adequate information available from the XE session data to figure out the missing pieces of the execution plan.

Extended Events, Birkenstocks and SQL Server

TSQL Tuesday

I bring you yet another installment in the monthly meme called T-SQL Tuesday.  This is the 67th edition, and this time we have been given the opportunity to talk about something I really enjoy – Extended Events.

Props to Jes Borland (blog | twitter) for picking such an awesome topic. There is so much to cover with extended events, it’s like a green canvas ready for anything you can imagine.



I will save the explanation here for later when hopefully it all ties together for you (well, at least buckles up).


While that is all fun and playful, let’s get down to the serious side now. One of my favorite quick fixes as a consultant is to come in and find that the server is set to “environment friendly” / “green” / “treehugger” mode. You can read more about power saving cpus from my friend Wayne Sheffield here.

That poor old cpu thing has been beat up pretty good. But how can we tell if the server is running in that mode if the only thing we can do is look in SQL Server (can’t install cpu-z, or don’t have adequate permissions on the server to see windows settings – just play along)? Lucky for us there is this cool thing called Extended Events.

In SQL Server we have this cool event called perfobject_processor. This particular event has some really cool metrics that it captures.  One such metric is the frequency. The frequency is an indicator to us whether the server has the cpu set to balanced, high performance, or power saver. Having that in mind, let’s create a session to trap this data and experiment a little with the cpu settings.

Well, that looks amazingly easy and straight forward. I am telling the session to trap the additional CPU information such as numa_node_id and cpu_id. You can eliminate those if you wish. They may be beneficial when trying to identify if there is an issue on a specific processor though.

To experiment, I will break out the age old argument provoker – xp_cmdshell. I will use that to cycle through each of the power saving settings and look at the results. Here is the bulk of the script all together.

And now for the XE Parser.

If I parse through the extended event after each change of the power scheme, I would be able to see the effect of each scheme change in the event session as well as in a tool such as Resource Monitor. Here is what I was able to see with each of the changes.

Balanced Saver

From Resource Monitor:


And the XE data:


This is my default power scheme. On my laptop, this is ok. For a production SQL server, this will cause problems.

High Performance

fullpower fullpower_results

Quickly, you should be able to spot that the blue line in the graph, and the numeric values from the XE session correlate to the processor giving you everything it has. This is good for SQL Server.

Power Saver


See how that blue line falls off sharply?




Supporting that steep fall in the graph, we can see that the XE trapped the percent of max frequency as 36%. You might be lucky and attain 36%. Don’t be surprised if you see something even lower. Please don’t use this setting on a production box – unless you want to go bald.

We can see that we have great tools via Extended Events to help troubleshoot various problems. As I said, this is one of my favorites because it is a very common problem and a very easy fix.

SQL Server is not GREEN! Do not put birkenstocks on the server and try to turn the server into a tree hugger. It just won’t work out that well. Set your fileservers or your print servers to be more power conscientious, but this is something that will not work well on SQL Server.

Final thought. If you have not figured out the birkenstocks, well it is a common stereotype with environmentalists in some areas that they may wear woolly socks and birkenstocks.

No wool socks were harmed in the making of this blog post!

Monitoring SQL Server

TSQL2sDay150x150Welcome to the fabulous world of blog parties, SQL Server and what has been the longest running SQL Server related meme in the blogosphere – TSQLTuesday.

This month we are hosted by Catherine Wilhemsen (blog | twitter) from Norway. And interestingly, Catherine has asked for us to talk about monitoring SQL Server.  Wow! Talk about a HUGE topic to cover in such a short space. Well, let’s give it a go.

I am going to try and take this in a bit of a different direction, and we shall see if I have any success with it or not.

Direction the First

Monitoring is a pretty important piece of the database puzzle. Why? Well, because you want to try and find out before the end-users that something is happening. Or do you? It is a well established practice at many shops to allow the end-users to be the monitoring solution. How does this work, you ask?

It works, by waiting for an end-user to experience an error or some unexpected slowness. Then the user will either call you (the DBA), your manager, the company CEO, or (if you are lucky) the helpdesk. Then, the user will impatiently wait for you to try and figure out what the problem is.

The pros to this solution involve a much lower cost to implementation.  The cons, well we won’t talk about that because I am trying to sell you on this idea. No, in all seriousness, the con to this approach could involve a lot of dissatisfaction, job loss, outages, delays in processing, delays in paychecks, dizziness, fainting, shortness of breath, brain tumors, and rectal bleeding.  Oh wait, those last few are more closely related to trial medications for <insert ailment here>.

If you are inclined to pursue this type of monitoring – may all the hope, prayers, faith and luck be on your side that problems do not occur.

New Direction

This methodology is also rather cheap to implementation.  The risk is relatively high as well and I have indeed seen this implementation. In this new approach, we will require that the DBA eyeball monitor the databases all day and all night.

At the DBA’s disposal is whatever is currently available in SQL Server to perform the monitoring.  It is preferred that only Activity Monitor and Profiler be used to perform these duties. However, the use of sp_who2 and the DMVs is acceptable for this type of duty.

The upside to this is that you do not incur any additional cost for monitoring over what has been allocated for the salary of the DBA. This an easy and quick implementation and requires little knowledge transfer or ability.

The downside here is – well – look at the problems from the last section and then add the glassed over stoner look of the 80s from staring at the monitor all day.

If you have not had the opportunity to use this type of monitoring – consider how lucky you are.  This has been mandated by several companies (yes I have witnessed that mandate).

Pick your Poison

Now we come to a multi-forked path.  Every path at this level leads to a different tool set.  All of these tools bare different costs and different levels of knowledge.

The pro here is that these come with lower risk to those suspicious symptoms from the previous two options. The con is that it will require a little bit more grey matter to configure and implement.

You can do anything you would like at this level so long as it involves automation.  You should configure alerts, you should establish baselines, you should establish some level of history for what has been monitored and discovered. My recommendation here is to know your data and your environment and then to create scripts to cover your bases.

One last thought, no matter what solution you decide to implement, you should also monitor the monitor. If the DBA collapses from long hours of eyeball monitoring, who will be there to pick him/her up to resume the monitoring?

If you opt to not implement any of these options, or if you opt to implement either of the first two options, I hope you have dusted off your resume!

«page 1 of 19

August 2015
« Jul    


Welcome , today is Sunday, August 2, 2015