SQL Pass Summit 2016... day 5

Sadly this was the last day of the Pass Summit, but I think I was my most favorite day. First of all it didn't rain, it turned out to be a nice day.

08:00 AM - 09:15 AM
Data Partitioning for Database Architects and Mere Mortals [AD-303]
Speaker(s):  Dmitri Korotkevitch

Data partitioning is an essential technique that simplifies database administration tasks, improves system availability and reduces the cost of the solution. Contrary to popular belief, data partitioning is not limited to partitioned tables and the Enterprise Edition of SQL Server.

This session demonstrates the data partitioning techniques available in the various editions of SQL Server. It discusses benefits, downsides and the best use-cases for partition tables, local and distributed partitioned views and stretch databases. The session will also demonstrate how data partitioning helps boost performance of systems handling a mixed workload, improves cardinality estimations with large tables, and reduces the system’s storage cost. 

Finally, the session provides a set of guidelines on how to split the data between relational and non-relational storages in OLTP and IoT systems hosted in the Cloud.

The room was packed and I had to stand in the back...it also didn't help that this ession was on level 2, it took me a while to find this room

This session was okay, I knew most of the stuff covered, below are two slides that give a summary between native partitioning and partitionmed view

09:30 AM - 10:45 AM
Chalk Talk with Azure SQL Database PM Team [AD-501-M]
Speaker(s):  Tomas Talius  Lindsey Allen

Join us in an open dialog with Microsoft Partner Architect Tomas Talius and members of the Azure SQL Database team. Don't miss this unique opportunity to ask the hard questions. Seating is limited to 50 attendees so arrive early. Chalk Talk sessions are not recorded.

This and the session after this one were my two favorite sessions. A couple of big announcement.. First CTP of the next version of SQL Server will drop next month, this includes both the Linux as well as the Windows version
SQL Injection detection
SQL Server optimizer has machine learning built in
SQL Graph (Think Neo4J) SQL Graph adds graph processing capabilities to SQL Server
Adaptive Query Plan
There will be a big announcement about SQL Server Standard Edition on November 15th....  mmmm maybe the ability to use more memory? We will have to wait and see what will be announced

I will go into some of these announcements into more details in a separate blog post. That post can be found here: Some cool SQL Server announcements SQL Graph, Adaptive Query Plan, CTP1 of SQL vNext, SQL Injection detection

Here is a picture of the team... after I posted this I got a funny comment from Adam Machanic (see below picture)

This image would make a great cover for a Christian rock album. https://t.co/W8MkhCBJeY

— Adam Machanic (@AdamMachanic) October 28, 2016

11:00 AM - 12:15 PM
Chalk Talk with the SQL Server Team [DBA-501-M]
Speaker(s):  Lindsey Allen  Jakub Szymaszek  Jovan Popovic  Kevin Farlee  Tomas Talius  Sunil Agarwal  Joseph Sack

More or less the same concept as the session before it.

02:00 PM - 03:15 PM
Go, Go, QueryStore! [DBA-210]
Speaker(s):  Gail Shaw

One of the hardest things to do in SQL is to identify the cause of a sudden degradation in performance. The DMVs don’t persist information over a restart of the instance and, unless there was already some query benchmarking (and there almost never is), answering the question of how the queries behaved last week needs a time machine. Up until now, that is. The addition of the QueryStore to SQL Server 2016 makes identifying and resolving performance regressions a breeze.

In this session we’ll take a look at what the QueryStore is and how it works, before diving into a scenario where overall performance suddenly degraded, and we’ll see why QueryStore is the best new feature in SQL Server 2016, bar none.

Gail Shaw did a good job giving an overview of the Querystore and how one might use it

03:30 PM - 04:45 PM
Microsoft R for the Architect [BIA-323-M]
Speaker(s):  Buck Woody

This was the last session, if you have never been to a Buck Woody presentation..you are missing something

In this hands-on workshop you’ll cover a series of modules that guide you from a review of the R programming environment, the Cortana Intelligence Suite Process, the Cortana Intelligence Suite Platform, to the Microsoft R platforms including: Microsoft Open R, the Microsoft R Client, Microsoft R Server, SQL Server with R Services, R in Azure ML, and HDInsight with R. Final lab is an SQL Server R Services solution.

Buck gave a nice overview of R Services and data science on the Microsoft stack

And this was taken on my way to the hotel after all the sessions were done  :-(

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