Stretch Database - SQL Server 2016

If you have read the previous blog posts, I have outlined what Azure databases are and how you should use them. With SQL Server 2016, you now have the ability to 'Stretch' your database into the cloud from a physical system within your organisation. This will allow you to store historic information that are a rarely accessed so that it can free up resources on your server.

To read more information about the 'Stretch' database feature in SQL Server 2016, just follow the link below.

More Information : Stretch Database

Microsoft Azure SQL Data Warehouse Overview

With Azure, you can also have SQL Server Data Warehouses. This is a great idea if your environment is several gigabits large and you want to be able to increase processing power without installing new hardware.

Just watch this video to find out more

Microsoft Azure SQL Database Overview

With SQL Server 2016 , it now has the ability to stretch your database to the cloud to a Microsoft Azure SQL Database.

Stretching your database gives you the ability to have some of your data locally within your business and some of your database placed in the cloud which is accessible worldwide.

There are many benefits of putting your databases in the cloud and to see a great overview of what Microsoft Azure SQL Database can do, just watch this video.

In-memory OLTP in SQL Server

If you want to know a lot of information about in-memory OLTP in SQL Server, then watch these two videos. They both explain how and when to use in-memory technology within your environment.

An overview of in-memory OLTP in SQL Server 2014

 

Microsoft SQL Server 2014: In-Memory OLTP for Database Developers

Operational Analytics - SQL Server 2016

With SQL Server 2016, there is now the ability to have operational analytics on OLTP systems.

This is a great idea and watch the video below to find out more.

Temporal in SQL Server 2016 - Creating/Querying Tables

Below are two example on how to create Temporal tables in SQL Server 2016
They will both store histroic information on data that gets into the table

-- A History table is not specified
CREATE TABLE dbo.Customer
(
    CustomerID        int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    CustomerName      varchar(50) NOT NULL,

    ValidFrom           datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo             datetime2 GENERATED ALWAYS AS ROW END   NOT NULL,  

    PERIOD FOR SYSTEM_TIME (
        ValidFrom,
        ValidTo
    )  
)
WITH ( SYSTEM_VERSIONING = ON );
GO


-- A History table is specified
CREATE TABLE dbo.Customer
(
    CustomerID        int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    CustomerName      varchar(50) NOT NULL,

    ValidFrom           datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo             datetime2 GENERATED ALWAYS AS ROW END   NOT NULL,  

    PERIOD FOR SYSTEM_TIME (
        ValidFrom,
        ValidTo
    )  
)
WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory) );
GO

To query Temporal tables, you can use the code below. The code below will automatically look in the CustomerHistory table if its not in the Customer table.

SELECT * FROM dbo.Customer FOR SYSTEM_TIME AS OF '2015-1-1'

Temporal Tables Limitations

* Usage of Replication technologies is limited.
* INSERT and UPDATE statements cannot reference the SYSTEM_TIME period columns.
* Direct modification of the data in a history table is not permitted.
* Temporal querying over Linked Server is not supported.
* History table cannot have constraints (PK, FK, Table or Column constraints).
* TRUNCATE TABLE is not supported while SYSTEM_VERSIONING is ON
* INSTEAD OF triggers are not permitted on either the tables.

Temporal in SQL Server 2016

Well I am so looking forward to using Temporal in SQL Server 2016, the ability to track changes in a table without creating triggers and custom history tables in a ad-hoc way sounds awesome.

Watch the video below to see how it works.

SQL Server 2016 - Extended Events - Deadlocks Tracking

Below is some code to track deadlocks in your server using Extended Events

CREATE EVENT SESSION [Deadlock Tracking] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(sqlos.task_time,sqlos.worker_address,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.username)) 
ADD TARGET package0.ring_buffer(SET max_memory=(102400))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Deadlock tracking is already tracked under the default 'system_health' Session, but if you just wanted  just to track deadlocks by itself, then the above code will work.

Once a deadlock occurs, it will produce a graph like the following so that you can investigate the problem further. 

You can also hover over the symbols to get more information about the deadlocks.

SQL Server 2016 - Creating you own System Health Check Extended Events

With SQL Server 2016, there is a default System Health Check session that is turned on automatically when the system starts. If you wanted to extend this System Health Check session to record additional events, it's not a good idea to modify the existing Session, but instead create a new System Health Check Session.

The code below is a script that you can use to create your new System Health Check Session, you just need to select where you are going to record the data (to a flat file or the ring buffer)

CREATE EVENT SESSION [system_health_New] ON SERVER 
ADD EVENT sqlclr.clr_allocation_failure(
    ACTION(package0.callstack,sqlserver.session_id)),
ADD EVENT sqlclr.clr_virtual_alloc_failure(
    ACTION(package0.callstack,sqlserver.session_id)),
ADD EVENT sqlos.memory_broker_ring_buffer_recorded,
ADD EVENT sqlos.memory_node_oom_ring_buffer_recorded(
    ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)),
ADD EVENT sqlos.scheduler_monitor_deadlock_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded,
ADD EVENT sqlos.scheduler_monitor_system_health_ring_buffer_recorded,
ADD EVENT sqlos.wait_info(
    ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([duration]>(15000) AND ([wait_type]>=N'LATCH_NL' AND ([wait_type]>=N'PAGELATCH_NL' AND [wait_type]<=N'PAGELATCH_DT' OR [wait_type]<=N'LATCH_DT' OR [wait_type]>=N'PAGEIOLATCH_NL' AND [wait_type]<=N'PAGEIOLATCH_DT' OR [wait_type]>=N'IO_COMPLETION' AND [wait_type]<=N'NETWORK_IO' OR [wait_type]=N'RESOURCE_SEMAPHORE' OR [wait_type]=N'SOS_WORKER' OR [wait_type]>=N'FCB_REPLICA_WRITE' AND [wait_type]<=N'WRITELOG' OR [wait_type]=N'CMEMTHREAD' OR [wait_type]=N'TRACEWRITE' OR [wait_type]=N'RESOURCE_SEMAPHORE_MUTEX') OR [duration]>(30000) AND [wait_type]<=N'LCK_M_RX_X'))),
ADD EVENT sqlos.wait_info_external(
    ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([duration]>(5000) AND ([wait_type]>=N'PREEMPTIVE_OS_GENERICOPS' AND [wait_type]<=N'PREEMPTIVE_OS_ENCRYPTMESSAGE' OR [wait_type]>=N'PREEMPTIVE_OS_INITIALIZESECURITYCONTEXT' AND [wait_type]<=N'PREEMPTIVE_OS_QUERYSECURITYCONTEXTTOKEN' OR [wait_type]>=N'PREEMPTIVE_OS_AUTHZGETINFORMATIONFROMCONTEXT' AND [wait_type]<=N'PREEMPTIVE_OS_REVERTTOSELF' OR [wait_type]>=N'PREEMPTIVE_OS_CRYPTACQUIRECONTEXT' AND [wait_type]<=N'PREEMPTIVE_OS_DEVICEOPS' OR [wait_type]>=N'PREEMPTIVE_OS_NETGROUPGETUSERS' AND [wait_type]<=N'PREEMPTIVE_OS_NETUSERMODALSGET' OR [wait_type]>=N'PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICYFREE' AND [wait_type]<=N'PREEMPTIVE_OS_DOMAINSERVICESOPS' OR [wait_type]=N'PREEMPTIVE_OS_VERIFYSIGNATURE' OR [duration]>(45000) AND ([wait_type]>=N'PREEMPTIVE_OS_SETNAMEDSECURITYINFO' AND [wait_type]<=N'PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROL' OR [wait_type]>=N'PREEMPTIVE_OS_RSFXDEVICEOPS' AND [wait_type]<=N'PREEMPTIVE_OS_DSGETDCNAME' OR [wait_type]>=N'PREEMPTIVE_OS_DTCOPS' AND [wait_type]<=N'PREEMPTIVE_DTC_ABORT' OR [wait_type]>=N'PREEMPTIVE_OS_CLOSEHANDLE' AND [wait_type]<=N'PREEMPTIVE_OS_FINDFILE' OR [wait_type]>=N'PREEMPTIVE_OS_GETCOMPRESSEDFILESIZE' AND [wait_type]<=N'PREEMPTIVE_ODBCOPS' OR [wait_type]>=N'PREEMPTIVE_OS_DISCONNECTNAMEDPIPE' AND [wait_type]<=N'PREEMPTIVE_CLOSEBACKUPMEDIA' OR [wait_type]=N'PREEMPTIVE_OS_AUTHENTICATIONOPS' OR [wait_type]=N'PREEMPTIVE_OS_FREECREDENTIALSHANDLE' OR [wait_type]=N'PREEMPTIVE_OS_AUTHORIZATIONOPS' OR [wait_type]=N'PREEMPTIVE_COM_COCREATEINSTANCE' OR [wait_type]=N'PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY' OR [wait_type]=N'PREEMPTIVE_VSS_CREATESNAPSHOT')))),
ADD EVENT sqlserver.connectivity_ring_buffer_recorded(SET collect_call_stack=(1)),
ADD EVENT sqlserver.error_reported(
    ACTION(package0.callstack,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)
    WHERE ([severity]>=(20) OR ([error_number]=(17803) OR [error_number]=(701) OR [error_number]=(802) OR [error_number]=(8645) OR [error_number]=(8651) OR [error_number]=(8657) OR [error_number]=(8902) OR [error_number]=(41354) OR [error_number]=(41355) OR [error_number]=(41367) OR [error_number]=(41384) OR [error_number]=(41336) OR [error_number]=(41309) OR [error_number]=(41312) OR [error_number]=(41313)))),
ADD EVENT sqlserver.security_error_ring_buffer_recorded(SET collect_call_stack=(1)),
ADD EVENT sqlserver.sp_server_diagnostics_component_result(SET collect_data=(1)
    WHERE ([sqlserver].[is_system]=(1) AND [component]<>(4))),
ADD EVENT sqlserver.xml_deadlock_report 
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Extended Events - SQL Server 2016

Well I have playing with Extended Events in SQL Server 2016 and its really good.





























As you can see from above, Extended Events is located in the same place as in SQL Server 2014 within the 'Management' tab.

If you didn't know, Extended Events has been designed to replace SQL Profiler and Trace Flags within SQL Server and SQL Profiler will be disappearing in future versions of SQL Server so you really need to understand Extended Events if you want to troubleshoot and new features that appear in SQL Server going foward.

To set up an new Extended Event session to troubleshoot your system, you can do the following

1) Right click the 'Sessions' tab within the Extended Events section and select 'New Session Wizard'
2) Selecting this will open up a wizard screen that will take you through setting up the extended event.

Once your Event has been set-up, it will appear in the 'Sessions' section.

If you didn't select the option to start the extended event session when you created the extended event, you can start it now by right-clicking the event and selecting 'Start Session'

You can view live event data that happens within your system by selecting the extended event and selecting 'Watch Live Data'

That is it for setting up Extended Events Session, very easy to do and a vast improvement to SQL Trace and SQL Profiler.

SQL Server 2016 - Installation

Today, I finally found time to install SQL Server 2016 - CTP2 on a Window 8.1 Desktop PC

The software can be found here

http://www.microsoft.com/en-gb/server-cloud/products/sql-server-2016/

Installation was just like every other installation of SQL Server so you shouldn't have any trouble installing it if you have installed SQL Server before.

The only extra thing I had to install on my desktop was Java SE Runtime Environment

At the time of writing the latest version can be found here

http://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.html

Other than that, it should be straightforward.