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.

SQL Server 2014 Comparisons

Want to know what is the difference between the different SQL Server 2014 editions, then click on the link below

SQL Server 2014 Comparisons

Download SQL Server 2014 Express

Download SQL Server 2014 Express

These links are quick and easy ways of downloading SQL Server Express 2014

• SQL Server 2014 Express with Tools (Management Studio)
  • SQL Server 2014 Express x64
  • SQL Server 2014 Express x86

• SQL Server 2014 Management Studio
  • SQL Management Studio x64
  • SQL Management Studio x86

• SQL Server 2014 Express with Advanced Services
  • Advanced Services x64
  • Advanced Services x86

Useful SQL Server Information.

This is an great SQL Script to find out loads of information about your SQL Server Instance and operating system. Click on the link below to download.

It works from SQL Server 2008 R2 onwards.

SQL Server Information Download Link

Software Developer C# (Up to £45,000pa)

Role: Permanent
Reporting To : Team Lead
Salary : Up to £45,000 Negotiable dependent on Experience – (We are recruiting at all levels junior and above)
Hours: Monday – Friday 9.00am – 5.30pm


What’s the Software Developer role all about?
As we are a leading online retailer, millions of customers each year.


What will you get from us……

• We will give you the opportunity to learn and hone the best engineering practices including Continuous Integration, Test Driven Development and SOLID principles.
• Career progression opportunities
• Personal development paid for by ao.com (Guaranteed 5 days each year)
• The opportunity for International project experience

What will my responsibilities be as a Software Developer?
We have dedicated teams who work together to deliver software across the entire journey for our customers.
We have vacancies across teams involved in building software for every area of the ecommerce business including logistics, order processing systems, websites, supply chain and more.

• Uncovering non-functional requirements – (“How many of these need processing in an hour”)
• Choosing technology tools – “Have you guys seen this new java test runner?”
• Choosing the best solution for the problem – “A spread sheet is not the best tool for this task”
• Designing the solution – “Time to get the CRC cards out”
• Implementing the solution – “cutting code”
• Building quality in – red/green/refactor – TDD
• Constantly improving our software processes – “How could we get more value out of our daily stand up?”
• Constantly improving our engineering processes – “Is this a suitable candidate for automation?”

What skills do I NEED to have as a Software Developer?
We are looking for developers with experience in web based technology and development of both front end and back end systems

• A degree in computing science or related subject is an advantage but not compulsory
• We use the following technologies a lot, so commercial experience with them is beneficial, although you don’t need to be a master at them:

C# .Net (This is the bread and butter of the role and you will need a strong knowledge of the syntax)
ASP.Net
MVC
Javascript
HTML5
WCF
NUnit, Moq, ReSharper, SpecFlow
MS SQL 2008

If you can offer the following or are chomping at the bit to work alongside other likeminded team mates, keep reading.

• Solid understanding of the practical application of agile development methods
• Significant software development and infrastructure architecture experience in designing and architecting enterprise web applications
• You will be the heart of an autonomous delivery team, so you should be proactive and great at problem solving
• We prefer close collaboration over long-winded documentation, so you need to be a great communicator and have solid interpersonal skills
• Nurture a close relationship with your internal customer and helping elicit requirements
• Be an inspiration of innovation to the client
• Maintain strong expertise and knowledge of current and emerging technologies and products
• Have a desire to improve as a developer and be lifelong learner