PASS Summit 2017: Day 1

Hey friends!  After a one-year hiatus I am BACK at the PASS Summit and ready to blog the day 1 keynote 🙂  I will update this post throughout the morning so refresh every so often to see the changes.  You can also follow along on Twitter – check out the #PASSsummit hashtag.

Reminder: My session is today at 1:30 in 6B, Query Store and Automatic Tuning in SQL Server, I hope to see you there!

Today’s keynote is headlined by Rohan Kumar (who I just got to meet thank you Mark Souza) and he’s stated that it will be a lot fun – you can preview what’s coming here.  Rohan is the General Manager Database Systems Engineering for Microsoft, and there are a fair number of demos coming our way.


PASS President Adam Jorgensen starts off the day – this is the 19th PASS Summit.  Holy cow.  The PASS keynote is being streamed live via PASStv if you’re not available to be here in person.  If you are at the Summit this week and you have any [problem with your SQL Server implementation that you need answered, go to the Microsoft Clinic.  It is on the 4th floor near the Community Zone, and there are numerous Microsoft Engineers available to help.  It’s an amazing resource at this conference.

Adam takes a moment to thank the individuals that volunteer for PASS – the organization is primarily run by volunteers, and that includes the PASS Board.  The Board will have an open meeting on Friday at 2PM which anyone can attend. I f you have feedback or want to better understand how things work, definitely attend.  Outgoing PASS Board members are Jen Stirrup and Chirs Woodruff.  New elected members are John Martin, Diego Nogare, and Chris Yates.  Adam takes a moment to thank outgoing Past President Tom LaRock and Exec Board member Denise McInerney as their time on the Board comes to a close.

Please take time to meet our sponsors in the Exhibit Hall.  The support of our sponsors makes *so* many things possible not just at Summit, but throughout the year.

And Rohan takes the stage…

SQL Server 2017

Data, AI, and Cloud are three disruptive technology trends…and we need to figure out how to better migrate data to the cloud (I’m asking: how do we make it easier?).

At it’s core, the modern data estate enables simplicity.  It takes in any type of data, and allows a hybrid setup between on-premises and the cloud.  Rohan asks how many people believe they can move their data/solution to the cloud?  About 1% of the attendees raise their hand.  He then asks how many people think that deploying to the cloud or on-prem is what’s needed in the future?  The majority of people raise their hands.

SQL Server 2017 was released October 2, 2017, and SQL Server 2016 was released April 1, 2016…that’s a very fast release cycle for Microsoft, and that’s been possible because of the cloud-first approach, which translates to an increased cadence of SP and CU releases.  Reminder: in SQL Server 2017 there’s a shift to CU releases every month, and no more SPs.  Glenn blogged about this in September.  Rohan brings Bob Ward and Conor Cunningham on stage for the first demo.  They’re wearing Cowboys jerseys.  *sigh*  If you see Bob this week ask him how the Rangers did this year…

Bob and Conor step through a demo showing the performance benefit of a new HPE DL580 Gen 10, using persistent scalable memory NVDIMMs – a query that takes 15 seconds on SSDs takes about 2 seconds on the HP storage.  And it’s cheaper?  I’m deferring to Glenn for the hardware details!!

Bob introduces a “typical” parameter sniffing issue – and then shows how to use Automatic Plan Correction (which relies on Query Store under the covers)…which I’ll be showing today in my session as well!

New features in SQL Server 2017:

  • Advanced Machine Learning with R and Python
  • Support for graph data and queries
  • Native T-SQL scoring
  • Adaptive Query Processing and Automatic Plan Correction

There is much more available in 2017, as noted in the release notes.

Docker Containers

Tobias Ternstrom and Mihaela Blendea take the stage to talk about containers running SQL Server.  Mihaela shows the build definition, which starts a container based on the SQL Server build.  On top of that, restore production database to it and run any additional scripts (e.g. obfuscate and remove some data), then push out the images.  Tobias starts typing in a command line window…this I love.  He knows what he’s doing, but he’s always kind of winging it.  Tobais gives a sneak peak of a tool that shows up as being named Carbon, but Rohan introduces it as Microsoft SQL Operations Studio.  It works on Windows, Linux, and Mac to connect to a SQL Server database.  So at some point SSMS will be deprecated?  Yeah…just like Profiler 😉

Rohan comes back and talks a bit more about the cloud-first approach.  Azure SQL Database is updated regularly, and on a monthly basis new CUs are being pushed out (CU1 for SQL Server 2017 has ALREADY been released).  Multiple terabytes (yes TERABYTES) of telemetry data are captured every day from Azure implementations.  This feedback goes right into making the product better (how else do you think they’re able to release builds and CUs faster?).

Managed Instances

New deployment option in Azure: Managed Instances.  It’s currently in private preview, but you get an entire SQL Server instance with PaaS benefits.  This allows for much more of a lift and shift migration with minimal code changes.  Microsoft is also working on a database migration service – this will not be easy and may not work for every solution, but it’s a step in the direction of making that process better and more reliable.

Working with Large Data/BI Solutions

The next data is showing performance and scale with Azure SQL Database hosted by Danielle Dean, a Principal Data Scientist at Microsoft.  Reading in a lot of data – ingesting patient vitals into Azure database (1.4 million rows/sec via columnstore and in-memory).  Azure Machine Learning Workbench is then used to take an existing model and put it into Azure SQL Database.  Switching to SSMS (it’s not dead yet folks!!) you can query that model (it “looks” like a table), and use a stored procedure to predict against the model.

Scott Currie, the creator of Biml, on stage to talk about using the new Azure Data Factory with Biml.  I’ll admit, this isn’t a technology I know, so I”m just listening at this point 🙂

Azure SQL Data Warehouse Designed from ground up to separate out storage and compute so that you can scale each independently.  This design is very flexible and powerful, and provides significant ability to scale (up to 60 nodes currently), and it’s secure.  Also launched in early in October: Azure SQL Data Warehouse Compute-Optimized Tier.  This was a result of feedback from customers who had some DW queries that were running REALLY slow in Azure.  The solution caches column segments (data) locally, and this cache survives failures, which then provides high performance for DW queries.  Julie Strauss, a Principal Group Program Manager comes on stage to demo this.

Why are these behavioral analytic queries so compute-intensive?  It’s a combination of the data that’s needed and the complexity of the query.  Two kinds of analysis – funnel and cohort.  Both use telemetry from customer interactions/purchases from web site clicks.  The sophistication of the query is taking the vast about of data (100TB) and then fold it many times to create the different cohorts – the query takes about 6 seconds to read through that 100TB of data.  I’d like to know how this is done…

PowerBI quick demo against data with 100+ million rows.  Model built from Visual Studio sourcing data from Azure SQL Data Warehouse – very easy to deploy the model and then generate different visuals in PowerBI (clicky clicky drop was the “official” term used…I’m not kidding).  Ability to also scale in Azure very quickly so only using resources really need (and thus only pay for what need and use).

Ok, there was one more demo but I’ll admit, I’m fading.  🙂

Rohan is wrapping up the keynote and talks about community and all of us working together and lifting each other up.  Rohan gives a shout out to members of the community that have really given a lot back to others.  He also mentioned Denny Cherry, a member of the community who had surgery a couple weeks ago.  I had a recent update from a colleague that Denny is doing well – please send good thoughts his way!

And that’s a wrap.  Off for a day of learning – have a great one friends!

Query Store Pre-Con at PASS Summit: More Details

In the time since my last post about my Query Store pre-con at the PASS Summit (it’s just about a month away!) I’ve gotten several more questions about the session.  I’ve consolidated them and provided answers here, and if anyone still wants to know more, please email me!

What version of SQL Server will you be running?

With the announcement on Monday from Ignite about SQL Server 2017, I can now say for certain that is the release I will be using.  I will call out differences between SQL Server 2016 and SQL Server 2017 as needed.  I don’t expect everyone who’s attending to be running 2016 or 2017, but I do expect that you’re in the planning stages for an upgrade, are going to start planning soon, or you’re looking for some solid reasons to share with your company about why you should upgrade.  I would argue that Query Store is a very good reason to upgrade, but that is definitely not the only one (check out the new features list for SQL Server 2016 and SQL Server 2017 – including enhancements to Query Store).

Will you have labs during your session?

No, but I will make all scripts available for attendees, and if you want to follow along during the day, you can get the scripts that morning.  I tweak demos right up until a session – it’s what I expect to be doing the Sunday before.  I’ll have all the scripts on a share that attendees can access the morning of the 30th.  I *love* the idea of labs during a pre-con, and I know that some other presenters do them.  Huge props, as it’s not easy to do.  In order to cover everything I want to cover, I don’t have enough time for in-depth labs that everyone can walk through from start to finish in the time allotted.  But again, feel bring to a laptop with SQL Server 2017 installed, along with the latest SSMS, and you can follow right along.

Are you giving anything away?

Besides knowledge?  😊  Yes, I will have codes for 30 days of free access to Pluralsight, which gives you access to over 50 courses from the SQLskills team, including my 3-hour course on Query Store.  If there’s something you miss during the pre-con, you can watch the Pluralsight course to pick it up, or further enhance what you learned.  I’m also working on a new course about the enhancements to Query Store in SQL Server 2017 and Automatic Tuning, which I’m also covering in my full day session.

Additional thoughts…

I’m also hosting a panel session at Summit and the organizer has emailed me to discuss how we handle questions during the session.  In the case where there are a large number of attendees, sometimes people hesitate to ask questions, and sometimes it can slow down the flow of the session.  While I am not setting records in terms of attendance, there are a lot of people that have signed up (which is fantastic) and I do want to make sure that everyone has a chance to ask their questions.  I’ve thought about using Twitter, and I know that Slack has been suggested as an option.  I’m still thinking about it.  If you’re signed up and have a preference, feel free to let me know in the comments!

That’s it for the moment, but please keep those questions coming.  Again, I want to make sure this is the right session for you to attend – there are a lot of great choices this year!  Either way, I look forward to seeing at Summit next month!


Query Store Pre-Con at the PASS Summit: Is it right for you?

I received an email over the weekend asking about my pre-con at the PASS Summit, my general session at the Summit, and my Query Store course on Pluralsight. The individual wanted to know the requirements for the pre-con, and what overlap exists between these three. Great question.

First, feel free to review the abstracts for all:

PASS Summit Pre-Con, October 30, 2017 (Seattle, WA)
Solving Common Performance Problems Using Query Store

PASS Summit General Session, Date TBA (Seattle, WA)
Query Store and Automatic Tuning in SQL Server

Pluralsight, online
SQL Server: Introduction to Query Store

None of these courses require pre-existing knowledge of Query Store.

The Pluralsight course starts at the beginning and walks you through configuring and using Query Store in SQL Server 2016 in find performance issues both retroactively and proactively (3 hours total).

The general session at the Summit discusses Query Store at a high level (what it is and how to use it), and talks about the new Automatic Tuning feature in SQL Server 2017 (Automatic Plan Correction) as well as Automatic Index Management in Azure SQL Database (75 minutes).

My full day pre-con covers everything from Pluralsight and the general session, and a whole lot more. If you want:

  • all the gory details on what Query Store is, how to configure it, and what data exists
  • best practices
  • a slew of demos about how you can use it to find performance issues (out-of-the-box Query Store methods and custom options not documented elsewhere)
  • tons of scripts that you can take home and use immediately
  • details about the wait statistics data added to Query Store in SQL Server 2017
  • additional uses for Query Store (beyond what’s documented)
  • how to use Automatic Plan Correction in SQL Server 2017 (automatic and manual) and what Automatic Index Management looks like in Azure SQL Database
  • an overview of how to visualize Query Store data

then the pre-con is going to give you all that and whatever else I can find time to fit in.  It is a packed day and I am in the thick of pulling all the slides and demos together (so if there’s something you want to see/know that you don’t see mentioned here, feel free to email me!).

I hope this answers any questions you have about my pre-con at Summit and other Query Store sessions available, but if not, just contact me!

Have a great week, and to my friends in the south – the Stellato family is keeping you in our thoughts.