SQL Pass Summit 2016... day 4

I got to the conference center, nice an early, as you can see if was still not completely light after I finished my breakfast.

This is day four for me, but it's day two of the regular conference. Today started with the the second day keynote.




8:15 AM - 10 AM

Keynote

The first thing we learned about was some stuff about financials as well as the member growth. A BA day in Chicago was announced as well. 

The main keynote presenter was David DeWitt. David talked about data warehouse technologies, The three products discussed were Amazon Redshift, Snowflake, and SQL DW.

I took some pictures, I will only post 6 of those so that you get an overall idea of what was discussed

Hash Key Partitioning

Round Robin Partitioning

Table Replication

Here are the summary slides for each product

Microsoft Azure SQL Data Warehouse

Redshift

Snowflake

10:15 AM - 11:30 AM
SQL Server 2016 R Services - How Can You Make Your Apps Intelligent Today? [AD-310-M]
Speaker(s):  Umachandar Jayachandran  Nellie Gustafsson  Jarupat Jisarojito

Attend this session to get an overview of SQL Server 2016 R Services and how you can use R in T-SQL today to make your applications more intelligent and build predictive logic.

This was an introductory session to R services and how you can call R from within SQL Server. I took a picture of Nellie and UC

01:30 PM - 02:45 PM
Lightning Talks 101
Speaker(s):  Hugo Kornelis  Wayne Sheffield  Russ Thomas  Rob Volk  Jeff Renz

This session is one of four Lightning Talks sessions, each featuring five 10-minute talks on a variety of topics.

Lightning Talks 101 focuses on the AppDev track, featuring:

1. Hugo Kornelis - "Managing Execution Plans"

2. Wayne Sheffield - "How Referential Integrity Helps SQL Server Optimize Queries"

3. Russ Thomas - "GIT Your Scripts"

4. Rob Volk - "Variations on SQL_VARIANT"

5. Jeff Renz - "Do This... NOT This!"

So this was kind of an interesting idea, 5 really short sessions. The sessions were interesting, however some of them were running out of time



Somewhere around lunch time I snapped this pic

My ex boss is Scottish, so of course I had to send this to him.....



03:15 PM - 04:30 PM
Focus Group

I was invited with 9 other people to be part of a PASS focus group, this was an interesting session, we basically were giving feedback about PASS, the PASS sessions, as well about the changing roles in the IT industry

04:45 PM - 06:00 PM

UNPLUGGED: SQL Server 2016 [AD-404-M]

Speaker(s):  Scott Klein  Rohan Kumar  Lindsey Allen 

SQL Server Unplugged is your opportunity to meet Rohan Kumar and the SQL Engineering Team – the individuals who own the relational database at Microsoft. The session is hosted by Scott Klein and the show is your avenue to ask questions and talk directly to Rohan and Engineering PMs about anything and everything related to SQL Server and Azure SQL Database, and get “unplugged” scoop and insights into the inner workings of the SQL Server engineering team. No NDA material will be discussed during this session.

A picture of Mark Souza, Rohan Kumar and Lindsey Allen

This sessions was very interesting, I even asked a question. The question I asked was if in the future they can make an option so that a temporary table could be hekatonized (created as a In Memory OLTP table), The answer was that they are looking into it.

There was also something funny that happened

Person: "Will you add sharding to SQL Server?"
Rohan: "No"
Person: " Oracle just announced this yesterday"
Rohan "Ok, it's more real now"

The room erupts in laughter  :-)

They also announced that Python will come to SQL Server, right now we have R, but it looks like Python will be coming down the road as well

One more interesting  thing announced was resumable index rebuild. I am not even sure how this would work... maybe like how you can stop and resume index defragmentation??

There was also the raffle, but in my case it might as well not have been.

SQL Pass Summit 2016... day 3

Just like the other two days, day three in Seattle started early....very early. This is day three for me, but this is the first day of the general sessions. This year over 6000 people came to attend the Pass Summit.

My day started with a 6:50 AM session

6:50 AM -  8 AM

Why Use AWS for Running Your Databases
A session explaining how you can run databases on Amazon Web Services, they explained the difference between running RDS and EC2. Overall the presenter gave a nice overview of what is all available within the Amazon cloud

8:15 AM - 10 AM

Keynote

Keynote was well attended, Joseph Sirosh Corporate Vice President for the Data Group, Microsoft started the keynote (he is not pictured here)

Below are some pictures I took of the keynote, I have mostly one liners below the images. The keynote was not bad, it wasn't the best ever but I think it was better than the last couple of years.

ACID doesn't mean what you think it means

You can run queries against a variety of data source straight from SSMS

Intelligent Database Service...this means taking the intelligence out of the apps and moving it into the database, think of running R service inside the database.

This Data Warehouse on Azure will be general available in a couple of weeks

A lot of people are already looking at SQL Server running on Linux

Installing SQL Server on Linux. And just like that, by being available on Linux, Microsoft just doubled the number of platforms that SQL Server runs on :-)

The Planet of the Apps. Apps completely dominate how much data they use and also how much data they generate

Some stuff about DocumentDB

DocumentDB is Rick Grimes favorite weapon to kill walkers, 43 million walker eliminated each day....

Here is what the explosion of data looks like when you look at it as rice

A look from the back of the room...


10:15 AM - 11:30 AM

Powershell Jumpstart for SQL Server DBAs [DBA-102] presented by Michael Fal

Powershell can be intimidating. There are many challenges to using it, especially for those not familiar with its nuances. Once understood, the language can be used for effective and robust automation that brings together SQL Server with other Windows components, such as the file system or Active Directory. 

In this session, we will cover the Powershell fundamentals, how you can use Powershell itself to learn about concepts and syntax, and techniques for using Powershell with SQL Server. We will focus on core language patterns that you can use immediately, take the mystery out of the code, and help you get started writing your own PowerShell scripts. Attendees will not only acquire an understanding of Powershell, but see practical examples of how it can be used with SQL Server, along with a set of resources they can use to learn more about the language.

I attended this session with 3 of my co-workers, I saw some interesting stuff, I only played around a little with powershell and I will spend more time with it for sure.


01:30 PM - 02:45 PM
03:15 PM - 04:30 PM
Inside SQL Server In-Memory OLTP [DBA-500-HD] presented by Bob Ward

Hekaton is the original project name for In-Memory OLTP and just sounds cooler for a title name. Keeping up the tradition of deep technical “Inside” sessions at PASS, this half-day talk will take you behind the scenes and under the covers on how the In-Memory OLTP functionality works with SQL Server. 

We will cover “everything Hekaton”, including how it is integrated with the SQL Server Engine Architecture. We will explore how data is stored in memory and on disk, how I/O works, how native complied procedures are built and executed. We will also look at how Hekaton integrates with the rest of the engine, including Backup, Restore, Recovery, High-Availability, Transaction Logging, and Troubleshooting. 

Demos are a must for a half-day session like this and what would an inside session be if we didn’t bring out the Windows Debugger. As with previous “Inside…” talks I’ve presented at PASS, this session is level 500 and not for the faint of heart. So read through the docs on In-Memory OLTP and bring some extra pain reliever as we move fast and go deep. 

This session will appear as two sessions in the program guide but is not a Part I and II. It is one complete session with a small break so you should plan to attend it all to get the maximum benefit.

This was a 3 hour session and Bob also cut the break down to 15 minutes...30 minute breaks are for wimps he said  :-)

This was a very good and very fast sessions, the way you attend these sessions is that you attend it in person and then you watch the recorded session at least two more times...and only then will you be able to understand everything.

Of course Bob was very cruel, he had the Cowboys use In Memory OLTP tables and the Eagles were stuck with regular disk based tables....  Kidding aside, if you ever have a chance to attend one of Bob's session, make sure you do... who else would run the debugger against SQL Server and step through a Hekaton Stored Procedure



04:45 PM - 06:00 PM

This Batch-Mode Window Aggregate Operator Will Change Your Life! [AD-400] presented by Itzik Ben-Gan

Window functions introduced a paradigm shift in solving T-SQL querying tasks. They allow more elegant, and often more efficient, solutions compared to traditional predicate-based solutions. Still, prior to SQL Server 2016, there were a number of inefficiencies in their optimization. SQL Server 2016 introduces the new batch-mode Window Aggregate operator, which changes everything as far as performance is concerned. This session describes and demonstrates this new operator, and also provides tips and tricks to enable its use in cases you might not have expected.

Itzik is a very good presenter and he always shows cool stuff

In this session he showed how you can make SQL Server run in batch mode instead of row mode by having a dummy columnstore index. You create a dummy table by adding a where clause and you do for example  WHERE Col1 =2 AND Col1 = 1, this is a filtered index with a filter that can never be true. This session was very good


06:00 PM - 08:00 PM
Exhibitor Reception

This was the time to go see the vendors, learn about the products, meet and greet with your peers, there was also food.

 A view of the hall

 Another view of the hall

 We are sooo tired from sitting whole day long, we need a massage

Some swag I managed to grab, these shirts will be mostly to work out in.

That's all for today, tomorrow is another jam-packed day....

SQL Pass Summit 2016... day 2

Day two in Seattle started early....very early....after turning and tossing since 2 AM, I decided to get out of bed at 3 AM. I read a little, hit the gym, showered and wrote the following blogpost: SQL Pass Summit 2016... day 1.... just looking around
At 7 AM it was still pitch dark, Here is a pic of what it looked like at that time

I went down to the lobby, met a co-worked and we walked to the Washington Convention Center. We had breakfast, we were joined by another co-worker, we also talked with some people at the table.

After breakfast, it was time to go to our sessions. I went to the Locking, Blocking, Versions: Concurrency for Maximum Performance [DBA-331-PC] session by Kalen Delaney

This is the description of this session

Failing to design an application with concurrency in mind, and failure to test an application with the maximum number of expected simultaneous users is one of the main causes of poor application performance.

SQL Server offers two methods for an application to provide data consistency: Pessimistic and Optimistic Concurrency Control. In this seminar we’ll discuss what consistency might mean to your applications, and describe the details of how each of the concurrency models works internally. We’ll examine the costs and trade-offs between the two concurrency models that SQL Server 2014 supports through different transaction isolation levels. Finally, we’ll examine the tools available for analyzing and troubleshooting blocking problems, including metadata views and Extended Events. You will learn:

• why concurrency management is a crucial part of database and application tuning
• how each of the transaction isolation levels impacts concurrency
• the details of how SQL Server implements pessimistic concurrency through locking, and when locking causes blocking
• how the snapshot-based isolation levels implement optimistic concurrency, and the problems to be aware of
• how In-Memory OLTP allows maximum concurrency with no locking
• how to use the metadata views and Extended Events to troubleshoot concurrency problems and monitor the resources used to manage concurrency.


Here is a picture of Kalen presenting


The session was pretty good, there was some stuff I knew, some stuff I didn't know but should have known, some stuff I knew at one point but forgot. We had an one hour break for lunch and I must tell you the lunch was excellent, here is also a pic of what I ate. You can see, there is some salad, carrots, potatoes, stuffed tomatoes, salmon, pork and also some rice




After lunch we went back to the session. I was there a little early and decided to play around with some code. I came up witht the following teaser, I then posted it on this blog SQL Pass Summit... SQL Teaser Transactions

The session ended at 4:30 PM, we then walked back to our hotel to take a one hour break. We met up at 6:15 PM in the lobby and went back to the Washington Convention Center for the PASS Summit 2016 Welcome Reception At the welcome reception I saw many old friends like Brent Ozar, Mladen Prajdic, Jason Strate, Thomas LaRock, Rob Farley, Tim Ford, Kevin Kline and many more. Sometimes I miss being a SQL Server MVP, because these are the folks I hung out with back in the day.  There were a ton of people at the reception, here is a small selection of them.

While being at the PASS Summit 2016 Welcome Reception I left my iPad in my hotel room to record a time lapse, this ran between 6:15 and 8:30, here is what that looks like

 

SQL Pass Summit... SQL Teaser Transactions

I am sitting in Kalen Delaney;s session on Locking, Blocking, Versions: Concurrency for Maximum Performance and had a great idea for a SQL Server Teaser. <<<<<

Without running it, what will printed when the following two blocks of code are run?

BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN

WHILE @@TRANCOUNT > 0
BEGIN
 PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)
 COMMIT
END

PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)


GO

-----------------------

BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN
BEGIN TRAN

WHILE @@TRANCOUNT > 0
BEGIN
 PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)
 ROLLBACK
END

PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)

Now run it.... Were you right?

What about the code below, what will happen if you run these two code blocks? Does it matter that the transactions are created in separate batches?

BEGIN TRAN
GO 7

WHILE @@TRANCOUNT > 0
BEGIN
 PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)
 COMMIT
END

PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)


GO

-----------------------
BEGIN TRAN
GO 7

WHILE @@TRANCOUNT > 0
BEGIN
 PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)
 ROLLBACK
END

PRINT '@@TRANCOUNT == ' + STR(@@TRANCOUNT)

PASS Summit 2016 sessions I am attending

I will be at the PASS Summit this year. After a long and hard look at the available sessions, I decided to attend the following sessions. It was hard at times to decide what sessions to go to because sometimes there were two sessions that were at the same time that I really wanted to see. I guess, I will watch those later since most of the sessions are recorded anyway

Tuesday, October 25, 2016

8:30 AM - 4:30 PM

6B 

 [DBA-331-PC] Locking, Blocking, Versions: Concurrency for Maximum Performance

This is a pre-conference full day session, myself and a couple of co-workers will attend this sessions

Wednesday, October 26, 2016

10:15 AM - 11:30 AM

TCC Tahoma 3-4 

 [DBA-102] Powershell Jumpstart for SQL Server DBAs

1:30 PM - 4:30 PM

606-609 

 [DBA-500-HD] Inside SQL Server In-Memory OLTP

4:45 PM - 6:00 PM

6E 

 [AD-400] This Batch-Mode Window Aggregate Operator Will Change Your Life!

Here is what day one of the regular PASS Summit 2016 sessions looks like for me, the keynote is not displayed here.

Thursday, October 27, 2016

10:15 AM - 11:30 AM

606-609 

 [AD-310-M] SQL Server 2016 R Services - How Can You Make Your Apps Intelligent Today?

1:30 PM - 2:45 PM

618-620 

 [LT-101] Lightning Talks 101

3:15 PM - 4:30 PM

611-614 

 [DBA-322-M] SQLCAT : Early Customer Experiences with SQL Server R Services

4:45 PM - 6:00 PM

6E 

 [AD-404-M] UNPLUGGED: SQL Server 2016

Here is what day two of the regular PASS Summit 2016 sessions looks like for me, the second day keynote is not displayed here.

Friday, October 28, 2016

8:00 AM - 9:15 AM

2AB 

 [AD-303] Data Partitioning for Database Architects and Mere Mortals

9:30 AM - 10:45 AM

602-604 

 [AD-501-M] Chalk Talk with Azure SQL Database PM Team

11:15 AM - 12:30 PM

6B 

 [AD-403-M] Inside SQL Server 2016 R Services - Architecture, Security, Performance, and Troubleshooting

2:00 PM - 3:15 PM

6B 

 [DBA-210] Go, Go, QueryStore!

3:30 PM - 4:45 PM

611-614 

 [BIA-323-M] Microsoft R for the Architect

The final day looks like this, I am sure my brain will be mush by the time I get this far.



And here is what the weather looks like for that week...

I guess that is typical Seattle weather.....

[Edit]
Added after I attended the PAss Summit

If you want to read about my time at SQL Pass 2016, read these posts


SQL Pass Summit 2016... day 2
SQL Pass Summit 2016... day 3
SQL Pass Summit 2016... day 4
SQL Pass Summit 2016... day 5


You might be thinking, where is day 1?  The day 1 posts is mostly about Seattle, if you are interested in that one, here it is:  SQL Pass Summit 2016... day 1.... just looking around

If you are going to PASS Summit 2016, build your schedule today

If you are going to the SQL Server Pass summit this year, you can start building your schedule already. Don't wait too long because some events seat only a limited number of people. Take the Chalk Talk with the SQL Server Team [DBA-501-M] session for example
Here is what is says on the page for that session

Speaker(s):  Lindsey Allen 

Duration: 75 minutes

Track: Enterprise Database Administration & Deployment

Join us in an open dialog with members of the SQL Server 2016 Program Management team. Don't miss this unique opportunity to ask the hard questions. Seating is limited to 50 so arrive early. Chalk Talk sessions are not recorded. 

As you can see this session sits only 50 people, so make sure to make a note of that, this way you know to get there early

Some other sessions are also going to be either very popular or they might be in a smaller room, so add those sessions to your schedule first.

You can see all the sessions here: http://www.sqlpass.org/summit/2016/Sessions/Schedule.aspx


That's all

See you at the PASS summit in October

Living in the NYC tri-state area? Attend the SQL 2016 Launch Event in NYC on June 2nd

Got this info from my co-worker Daryl earlier today.

On June 2nd Microsoft will hold a SQL 2016 Launch Event in New York City. The Microsoft Offices at 11 Times Square (at 8th and 42nd) in New York is where this event will be held.


WHEN
Thursday, June 2, 2016 from 8:00 AM to 2:00 PM (EDT)

WHERE
Microsoft Offices - 11 Times Square, New York, NY 10036

I might be there but I don't want to promise anything. You can sign up here for this event: https://www.eventbrite.com/e/sql-2016-launch-event-nyc-ny-tickets-25477835893

How to disable tests in tSQLt

The tSQLt unit test framework that we use to do our DB tests does not allow for disabled tests, this is because each test is just a stored proc. Right now the only way is to either rename the proc so it doesn’t start with test or to remove the test

I was thinking about this in the shower this morning….here is what I came up with

Here is a quick way how to prevent a test from running when the tSQLt tests run.
Put the code below into each unit test that you want disabled, you should put this code right after the CREATE PROCEDURE  ProcName AS part

This code will pick the correct name and schema, so you can use the same exact code in each test

DECLARE @IsTestDisabled bit = 1 -- set this to 1 if you don't want the test to run

IF @IsTestDisabled =1
BEGIN
 DECLARE @SchemaName varchar(1000)
 SELECT @SchemaName = QUOTENAME(SCHEMA_NAME(schema_id)) +'.'
 FROM sys.procedures WHERE object_id = @@procid

 PRINT 'Disabled Test: ' + @SchemaName +  QUOTENAME(OBJECT_NAME(@@procid))
 RETURN
END

So if we grab the example test from the tSQLt website

We would change it from this

CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given conversion rate]
AS


BEGIN
    DECLARE @actual MONEY;
    DECLARE @rate DECIMAL(10,4); SET @rate = 1.2;
    DECLARE @amount MONEY; SET @amount = 2.00;

    SELECT @actual = FinancialApp.ConvertCurrency(@rate, @amount);

    DECLARE @expected MONEY; SET @expected = 2.4;   --(rate * amount)
    EXEC tSQLt.AssertEquals @expected, @actual;

END;
GO

To  this

CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given conversion rate]
AS

DECLARE @IsTestDisabled bit = 1 -- set this to 1 if you don't want the test to run

IF @IsTestDisabled =1
BEGIN
 DECLARE @SchemaName varchar(1000)
 SELECT @SchemaName = QUOTENAME(SCHEMA_NAME(schema_id)) +'.'
 FROM sys.procedures WHERE object_id = @@procid

 PRINT 'Disabled Test: ' + @SchemaName +  QUOTENAME(OBJECT_NAME(@@procid))
 RETURN
END


BEGIN
    DECLARE @actual MONEY;
    DECLARE @rate DECIMAL(10,4); SET @rate = 1.2;
    DECLARE @amount MONEY; SET @amount = 2.00;

    SELECT @actual = FinancialApp.ConvertCurrency(@rate, @amount);

    DECLARE @expected MONEY; SET @expected = 2.4;   --(rate * amount)
    EXEC tSQLt.AssertEquals @expected, @actual;

END;
GO

Now you will get the following in your tSQLt test run output

Disabled Test: [testFinancialApp].[test that ConvertCurrency converts using given conversion rate]

This will be right above this text

+----------------------+
|Test Execution Summary|
+----------------------+

I want this printed so we know we have disabled tests when we get a email whenever Jenkins kicks off these tests…..


To enable the test again, all you have to do is change the value from 1 to 0 here. So instead of

DECLARE @IsTestDisabled bit = 1

You would make it

DECLARE @IsTestDisabled bit = 0

To see all my tSQLt posts, visit this link: http://sqlservercode.blogspot.com/search/label/tSQLt

SQL Server 2016 will be generally available on June 1, 2016

Mark your calendars, SQL Server will be generally available less than a month from now, June 1st 2016.

You don't have a reason not to play around with SQL Server 2016, in March it was announced that the SQL Server 2016 Developer edition will also be available as a free download.

Also today, Lenovo published a new #1 TPC-H 30 TB world record using SQL Server 2016 and Windows Server 2016 on Lenovo System x2950 X6

To find out about all the difference between the various editions, download this pdf: SQL Server 2016 editions –what’s new 

For the official announcement, see here: Get ready, SQL Server 2016 coming on June 1st