SQL Cruise Sessions

2017 SQL Cruise Alaska Abstracts

Bob Ward

Inside SQL Server In-Memory OLTP

Based on the successful 500 Level talk at the PASS Summit 2016, this session will provide an internals view of how In-Memory OLTP works for SQL Server 2016 and SQL Server vNext. Because this session is Advanced, it is intended for those who understand the basic fundamentals of In-Memory OLTP but want to learn how the “Hekaton” engine works behind the scenes to deliver the amazing 30x performance improvements seen from customers who have used In-Memory OLTP. This session will include a look behind the scenes at threads, data and index internals, transactions and concurrency, logging, storage, and natively compiled procedures. And since this will be an advanced level session, of course the demonstration of the looking at the Hekaton engine with the debugger is a must (and fun!)

Inside SQL Server Machine Learning Services: Database Intelligence at Scale

You are a DBA of SQL Server deploying or considering SQL Server 2016 or SQL Server 2017. Your manager says that Data Science is the “new thing” and there are Data Scientists in your company that need your help. SQL Server 2016 introduced a new capability called SQL Server R Services and you need to evaluate whether this feature is something your company can trust to deploy in production for an enterprise environment. SQL Server 2017 expands this feature to include Python and what we now called SQL Server Machine Learning Services. We will explore and understand exactly how SQL Server Machine Learning Services works behind the scenes so you know how to install it, manage it, optimize it, and have confidence to use this in real prediction analytic workloads. We will use demos so you understand all the components and look at a real enterprise customer case study as part of this talk.

DBA Scenarios for SQL Server on Linux

SQL Server on Linux. Can you believe it is happening? The only problem is that your background is Windows Server. You have spent your career understanding how to manage SQL Server in a Windows Server environment. So how will you learn to understand the important fundamentals of managing and troubleshooting SQL Server on a Linux Operating System? This session will cover common DBA troubleshooting scenarios on SQL Server on Linux. In this demo rich session, we will cover various every-day scenarios from troubleshooting startup and configuration to performance and bottleneck analysis. We will discuss existing and new tools that will enable you to effectively  manage and troubleshoot SQL Server on Linux. As a bonus we will discuss some internals of the SQL Server Linux architecture as I’ll provide Q&A from interviews with Slava Oks and Robert Dorr (aka the smarter #bobsql), two of the architects and developers behind this exciting new journey for SQL Server.

Argenis Fernandez

Modern Security Attack Vectors against SQL Server Environments

Today’s attacks are getting smarter, sneakier, and more dangerous than ever. In this session we are going to discuss the different vectors the bad guys are using today to gain access to SQL Server environments. Credential theft (using Mimikatz, for example) are increasingly popular ways to hop onto servers you weren’t supposed to have access to. Pass the hash has certainly evolved, but it’s still a massive issue out there. After this talk, you’re probably going to want to have a chat with your Security team at work.

SQL Server Faster: All-Flash Arrays for Database Professionals

We’ll begin this session with a 30-minute Storage 101 discussion, and then we’ll dive right into the main topic. Most databases are IO bound, with prevalent storage-related waits. In this session we will discuss data-reducing AFAs (all-flash arrays), a new generation of storage arrays that are radically different from what we were used to, and offer a myriad of features to make Database Administrators, Database Developers/Engineers and Systems Administrator’s lives easier, while greatly enhancing performance and data reliability. If you’re tired of seeing PAGEIOLATCH waits on your instance, you won’t want to miss this session.

Allen White

You Will Be More Efficient with PowerShell and SQL Server

Whether you manage two servers or two thousand, more and more is expected of us in our daily jobs and the only way to keep on top of the demand is to automate. PowerShell allows you to do that across your Windows Server environment, including SQL Server. In this workshop we’ll introduce you to PowerShell and then cover how you can use it to manage your servers. We’ll spend the first half of the day focusing on PowerShell itself, covering objects (which are just encapsulated data), how to use the pipeline to get the information you need in a way that makes sense, how to write your own functions and modules, how to access and manage Azure VMs and how to use PowerShell remoting to run scripts against thousands of servers. The second half of the day we’ll cover how you can use PowerShell to manage SQL Server, covering SMO (Server Management Objects, the Management API for SQL Server), the SQL Server Provider, which allows you to navigate SQL Server as though it were a file system, the SQL Server-specific cmdlets which allow you to query, backup and restore SQL Server as well as the cmdlets you’ll use to set up and manage Azure SQL Databases.

Troubleshooting and Tuning Query Performance with SQL Sentry and Plan Explorer

SQL Server performance problems can be challenging to diagnose, and the SQL Sentry and Plan Explorer tools provide the necessary information to both diagnose and troubleshoot where those problems actually are, and help you fix those issues.
Abstract     Troubleshooting query performance in SQL Server is often viewed as a black art. Every expert has their own set of tools they use to find and fix the problem areas of queries. SQL Sentry is the solution from SentryOne that monitors SQL Server and its underlying architecture to alert you to potential performance problems in your SQL Server environment. Plan Explorer is a completely free tool that provides one comprehensive tool to find and fix those queries that cause the worst performance of your systems. This session will introduce you to the SQL Sentry and Plan Explorer tools and teach you how to use them to solve those performance problems effectively.

SentryOne Product and Tools Deep Q&A

Details to Come.

Grant Fritchey

Exploring Execution Plans

Getting started reading execution plans is very straight forward. The real issue is understanding the plans as they grow in size and complexity. This session will show you how to explore the nooks and crannies of an execution plan in order to more easily find the necessary information needed to make what the plan is telling you crystal clear. The information presented here will better empower you to traverse the execution plans you’ll see on your own servers. That knowledge will make it possible to more efficiently and accurately tune and troubleshoot your queries.

DevOps and Data

Far too many people responsible for production data management systems are reluctant to embrace DevOps. The concepts behind DevOps can appear to be contrary to many of the established best practices for securing, maintaining and operating a reliable database. However, there is nothing inherent to a well-designed DevOps process that would preclude ensuring that the information stored within your data management system is completely protected. This session will examine the various methods and approaches available to the data professional to both embrace a DevOps approach to building, deploying, maintaining and managing their databases and protect those databases just as well as they ever have been. We will explore practices and plans that can be pursued using a variety of tooling and processes to provide DevOps methodologies to the systems under your control. You can embrace DevOps and protect your data.

Manage Your Azure Databases Using Powershell

Moving your databases to the cloud through the use of Azure SQL Database and Azure SQL Data Warehouse can be challenging. These challenges are exacerbated by the fact that you simply don’t have as much tooling available to manage and maintain your databases. Enter Powershell. Through the use of Powershell you can much more easily maintain your Azure databases. Further, once you start using Powershell to maintain the databases, you’ll be able to automate a lot more processes within your Azure environment. This session will provide you with the core knowledge to create your own Powershell scripts and it will give you a set of foundation scripts from which you can build your own. You don’t have to feel limited on what you can just because you’re on Azure. Powershell will empower you to get more done within your Azure environment.

How to Leverage Redgate SQL Clone for Success

Details to Come.

Andrew Kelly

Managing Large Volumes of Data

The amount of data that we consume on a daily basis is growing by leaps and bounds. Techniques that worked fine for managing the data in the past are often not suited to the larger data sets of today. A different mindset is needed to properly manage a large database and still keep it useable for everyone. We will see how utilizing techniques that take advantage of minimally logged and bulk operations are crucial in this regard. Batching operations is also necessary in most cases as well. And utilizing the features such as Partitioning and Column Store Indexes are becoming the norm. We will cover the key aspects of these features and techniques that make it possible to properly manage large amounts of data and still keep performance where it needs to be.

Getting a Handle on Your System Performance

Performance or lack thereof can and usually does come from many different aspects of SQL Server. You can’t just concentrate on the Hardware and ignore the software or vice versa. Many people feel it is just overwhelming and have a difficult time deciding where and how to spend their precious time and resources in tackling performance. Well fear no more. We will go thru each of the key aspects of SQL Server performance on both the HW and SW side utilizing proven techniques to help narrow down any performance issues and discuss how to address them. We will cover many aspects including I/O, Memory, CPU, Tempdb and of course query performance. We will utilize all readily available tools and custom code that you can hit the ground running with immediately. We will also cover some essential DMV’s, Extended Events and PowerShell code used to help get a handle on our system performance. The demos will make it clear that practical performance monitoring is not an overwhelming task and you too can use these tools and techniques to help identify where the problems are and more importantly where you should be spending your time to get the biggest ROI.

Query Store – Soup to Nuts

One of the most anticipated features in SQL 2016 is the Query Store but many of us still don’t know what it is all about or realize its full potential. We will start with an overview of the feature and all of the associated options along with the tables, views and DMV’s that make it all work. From there we will go thru an extensive set of demos that will outline just how this feature works. To finish it off we will explore in detail how to utilize the Query Store in real life and outline just what this feature can do for you on a daily basis. We will leave no nut un-cracked.