sqlsathistory

Nr: 752

Back to Main list

SQLSaturday #752 - Iowa City 2018

Start Time (24h)	Speaker	Track	Title
08:45:00	Jared Zagelbaum	Analytics and Visualization	Intro to Machine Learning
08:45:00	Sean Forgatch	BI Platform Architecture, Development Administration	Processing Big Data with Azure Data Lake Analytics
08:45:00	Arthur Daniels III	Enterprise Database Administration Deployment	Plan Cache performance tuning
08:45:00	Dave Bland	Enterprise Database Administration Deployment	Extended Events: What are they and How do I use them
08:45:00	Rick Lowe	Application Database Development	Why Should I Care About ... Partitioned Views?
10:10:00	Rick Bielawski	Application Database Development	Mastering queries into XML data.
10:10:00	Eric Fait	Analytics and Visualization	Power BI Bookmarks: Four Business Use Cases
10:10:00	David Klee	Enterprise Database Administration Deployment	Zen and the Art of Perfmon 2018
10:10:00	Andy Galbraith	Enterprise Database Administration Deployment	Intro to Powershell for the SQL Server DBA
10:10:00	Brian Hansen	Application Database Development	The Amazing Utility of ROW_NUMBER()
12:50:00	Rick Bielawski	Strategy and Architecture	Implementing security inside SQL Server
12:50:00	Turner Kunkel	Cloud Application Development Deployment	Azure Analysis Services: Purpose, Migration, Benefits, and Tools
12:50:00	David Klee	Enterprise Database Administration Deployment	Ping! (no reply)
12:50:00	Dan Mallott	Application Database Development	The Fruit Goes Enterprise: Full Stack .NET Development Using Only Mac-Native Tools
12:50:00	Joe Obbish	Application Database Development	Decoding the Cardinality Estimator to Speed Up Queries
14:15:00	Dave Bland	Enterprise Database Administration Deployment	Statistics - Why are they there and what do I do with them
14:15:00	Sourav Mukherjee	Application Database Development	Upgrade to SQL Server 2017: Unleashing the Power of SQL Server 2017
14:15:00	Frank Henninger	Enterprise Database Administration Deployment	Introducing dbachecks : Unit testing for your database infrastructure
14:15:00	Joshuha Owen	Cloud Application Development Deployment	Let's build a data app with Azure Functions!
14:15:00	Ed Leighton-Dick	Enterprise Database Administration Deployment	Availability Group Troubleshooting Beyond the Basics
15:40:00	Jared Zagelbaum	Cloud Application Development Deployment	Custom Activities in Azure Data Factory
15:40:00	Kevin Trojanowski	Application Database Development	SQL Unit Testing with T-SQLt
15:40:00	Joe Obbish	Application Database Development	Four Rules For Columnstore Query Performance
15:40:00	Pat Phelan	Strategy and Architecture	In memory database... Let's rethink that request!
15:40:00	Jake Manske	Application Database Development	Locking and its effects on queries and maintenance tasks

SessionID: 74074

Intro to Machine Learning

Back to calendar

Event Date: 23-06-2018 - Session time: 08:45:00 - Track: Analytics and Visualization

Speaker: Jared Zagelbaum

Title: Intro to Machine Learning

Abstract:

Come to this session to get your toes wet with machine learning. What are the types of questions it can answer? What are the types of models it can produce? What are some the of techniques used? There will be practical demos using Azure Machine Learning as well. Enough info to get you started, or enough to quit while you're ahead, either way, you'll come out with some concrete ideas of what ML is all about.

SessionID: 74667

Processing Big Data with Azure Data Lake Analytics

Back to calendar

Event Date: 23-06-2018 - Session time: 08:45:00 - Track: BI Platform Architecture, Development Administration

Speaker: Sean Forgatch

Title: Processing Big Data with Azure Data Lake Analytics

Abstract:

In this session of Getting Started with Azure Data Lake Analytics, we will learn about fundamental concepts and considerations for designing a Data Lake, how that fits into your organization, and then spend most of the time processing data with Data Lake Analytics using Microsoft's new Big Data language, U-SQL.

SessionID: 77842

Plan Cache performance tuning

Back to calendar

Event Date: 23-06-2018 - Session time: 08:45:00 - Track: Enterprise Database Administration Deployment

Speaker: Arthur Daniels III

Title: Plan Cache performance tuning

Abstract:

In this session, we'll discuss ways to manage execution plans inside the plan cache of busy and unpredictable SQL Servers. The plan cache keeps SQL Server healthy, but all it takes is one bad execution plan to decrease server performance.

Just like gardening, keeping a database healthy requires pruning. Techniques to manage, remove, and improve execution plans will be shown. The Query Store gives us power tools for this work, but it's not the entire solution.

SessionID: 80723

Extended Events: What are they and How do I use them

Back to calendar

Event Date: 23-06-2018 - Session time: 08:45:00 - Track: Enterprise Database Administration Deployment

Speaker: Dave Bland

Title: Extended Events: What are they and How do I use them

Abstract:

Extended Events are an essential part of properly monitoring and every DBA should know what they are and how to use them. This session will cover the basics of Extended events as well as an introduction to the system_health session. This session will give someone who has never used Extended events before the skills to start using them!

SessionID: 82424

Why Should I Care About ... Partitioned Views?

Back to calendar

Event Date: 23-06-2018 - Session time: 08:45:00 - Track: Application Database Development

Speaker: Rick Lowe

Title: Why Should I Care About ... Partitioned Views?

Abstract:

Partitioned tables are awesome and partitioned views are dead, right? Well, sure partitioned views are not sexy but there are still plenty of applications where they are critical. And not necessarily just for standard edition who aren't on the new service pack yet. If your data is time stamped, do you see different queries run against older data than new data? Do you with you could segment your data across multiple columns? Do you have data you need to partition horizontally? It's possible partitioned views may still be for you. Come hear why this is still an important topic long after cargo pants and trucker hats went away ... and possibly even after 2016 SP1.

SessionID: 74079

Mastering queries into XML data.

Back to calendar

Event Date: 23-06-2018 - Session time: 10:10:00 - Track: Application Database Development

Speaker: Rick Bielawski

Title: Mastering queries into XML data.

Abstract:

If you've never queried an XML column you definitely want to attend. While targeted to beginners this isn't a "Just do this" session. After this session I'd like you to truly understand even subtle nuances of basic query syntax. Seasoned veterans stand a fair chance of learning something new.

SessionID: 74240

Power BI Bookmarks: Four Business Use Cases

Back to calendar

Event Date: 23-06-2018 - Session time: 10:10:00 - Track: Analytics and Visualization

Speaker: Eric Fait

Title: Power BI Bookmarks: Four Business Use Cases

Abstract:

So you've gathered the requirements, made sure the ETL is perfect, the model queries in milliseconds, and the report is both functional AND fashionable. There's always that one person(and usually a few) that wants to see the data with a different visual, or with filters applied a certain way. In this session, we will go in depth on the bookmarking feature in a live demo using the framework of four business cases.

Power BI Pagination I'll take an existing dataset previously used for a SSRS report and add Page forward and backward buttons to it in Power BI Change and Reset ALL filters and slicers I'll reset/update filters and slicers across multiple tabs in a report with a single button Swapping out visuals with one click The director wants to see the visual but the VP wants to see the table Executive Presentation Create a finely tuned presentation with nothing but Power BI

Bring your own dataset and build out these scenarios with me as I demonstrate each one live from scratch.

SessionID: 77381

Zen and the Art of Perfmon 2018

Back to calendar

Event Date: 23-06-2018 - Session time: 10:10:00 - Track: Enterprise Database Administration Deployment

Speaker: David Klee

Title: Zen and the Art of Perfmon 2018

Abstract:

If your boss asked you for the list of the five most CPU-hungry databases in your environment six months from now for an upcoming licensing review, could you come up with an answer? Performance data can be overwhelming, but you can make sense of the mess. Twisting your brain and looking at the data in different ways can help you identify resource bottlenecks that are limiting your SQL Server performance today. Painting a clear picture of what your servers should be doing can help alert you when something is abnormal. Trending this data over time will help you project how much resource consumption you will have months away. Come learn how to extract meaning from your performance trends and how to use it to proactively manage the resource consumption from your SQL Servers.

SessionID: 78332

Intro to Powershell for the SQL Server DBA

Back to calendar

Event Date: 23-06-2018 - Session time: 10:10:00 - Track: Enterprise Database Administration Deployment

Speaker: Andy Galbraith

Title: Intro to Powershell for the SQL Server DBA

Abstract:

Many DBA's have the same outlook on Powershell - we all know we should use it, but we don't think we have time to learn one more tool that isn't even really part of SQL Server anyway. I was one of these DBA's for many years, but recently I have come to respect the power of Powershell. Come learn the basics of Powershell, how Powershell and SQL Server interact with each other, and finally how to use Powershell to automate common SQL Server tasks such as maintenance and SQL Server installations. If you need to interact with Windows or Active Directory, odds are Powershell is the right tool for the job - come learn how to use it!

SessionID: 82039

The Amazing Utility of ROW_NUMBER()

Back to calendar

Event Date: 23-06-2018 - Session time: 10:10:00 - Track: Application Database Development

Speaker: Brian Hansen

Title: The Amazing Utility of ROW_NUMBER()

Abstract:

When it was introduced way back in SQL Server 2005, the lowly ROW_NUMBER() function at first seemed to have little functionality other than simply counting rows. However, over time we have come to see this humble function is actually surprisingly useful in often unexpected ways. In this session we will discover the creative uses of ROW_NUMBER() in ways that you may likely not have yet considered. This session will be filled with little goodies that can you can use, whether you are a writing SQL for application code or for administrative functionality.

SessionID: 74078

Implementing security inside SQL Server

Back to calendar

Event Date: 23-06-2018 - Session time: 12:50:00 - Track: Strategy and Architecture

Speaker: Rick Bielawski

Title: Implementing security inside SQL Server

Abstract:

Having a good understanding what security features are available and what configurations are considered best practice can be crucial to your company's data security. Learn to view security differently and how key builtin security features can be leveraged to make your systems both more maintainable and more secure. This is very much a HOW TO session, both in terms of how to decide what feature combinations work best for your environment and how to implement those features.

SessionID: 74137

Azure Analysis Services: Purpose, Migration, Benefits, and Tools

Back to calendar

Event Date: 23-06-2018 - Session time: 12:50:00 - Track: Cloud Application Development Deployment

Speaker: Turner Kunkel

Title: Azure Analysis Services: Purpose, Migration, Benefits, and Tools

Abstract:

This presentation will briefly go over the history of OLAP and Microsoft's contribution to the technology.

The bulk of the conversation will then be an introduction to Azure's analysis services implementation - how to use it, how much it may cost, a comparison to on-premises implementations, and how to migrate from on-premises.
Then more intermediate subjects will be discussed, such as analysis of best practices and automation techniques, future features Microsoft is working on, and future research considerations for a more advanced look at Azure Analysis Services. Demos are included during several parts of the presentation.

SessionID: 77382

Ping! (no reply)

Back to calendar

Event Date: 23-06-2018 - Session time: 12:50:00 - Track: Enterprise Database Administration Deployment

Speaker: David Klee

Title: Ping! (no reply)

Abstract:

Oh! ASYNC_NETWORK_IO just went through the roof! What just happened?

Ever wonder how common tasks such as how servers communicate, how cross-subnet Availability Group failover to your disaster recovery site work, or why ‘they’ always recommend a dedicated network adapter for your WSFC? What do I need to know to better manage my databases in the cloud? De-mystifying the networking underneath your databases makes you a stronger database professional. This deep dive technical session will cover many of the critical aspects of enterprise-level networking and how your database servers communicate with the rest of the world. Topics and demonstrations include networking technologies, quality of service, VLANs, routing, firewalls, monitoring, and trending.

By the end of this interactive session, you will be able to check your own networks for their performance and know how to converse with the networking admins to help improve the overall performance.

SessionID: 77946

The Fruit Goes Enterprise: Full Stack .NET Development Using Only Mac-Native Tools

Back to calendar

Event Date: 23-06-2018 - Session time: 12:50:00 - Track: Application Database Development

Speaker: Dan Mallott

Title: The Fruit Goes Enterprise: Full Stack .NET Development Using Only Mac-Native Tools

Abstract:

For years, those of us who wanted to use a Mac and do .NET or SQL Server development have been caught in a "worst of both worlds"-type of situation. We have had to use some sort of Windows VM, which negates the relative advantages of using a Mac (notably the battery life).

The good news is that thanks to Microsoft's efforts, we can now do full stack .NET development without ever touching a Windows VM on our Macs! In this talk, I'll live code a simple service with database backend while introducing Mac-native tools such as Visual Studio Code, SQL Operations Studio and SQL Server for Docker. Along the way, I'll discuss key differences and limitations between the Windows tools and their Mac counterparts paying particular attention to the differences between SQL Ops Studio and SSMS. I'll also touch on the difference between Visual Studio Code and Visual Studio for Mac.

SessionID: 78899

Decoding the Cardinality Estimator to Speed Up Queries

Back to calendar

Event Date: 23-06-2018 - Session time: 12:50:00 - Track: Application Database Development

Speaker: Joe Obbish

Title: Decoding the Cardinality Estimator to Speed Up Queries

Abstract:

The cardinality estimator in SQL Server is a massively complex piece of software that is responsible for calculating the estimated number of rows for query plans. We will derive some of the simplest and most important rules for cardinality estimation. Armed with that knowledge, we will address errors in cardinality estimates which can cause poor query performance. Demos include filtering on multiple columns, using local variables, dealing with ascending keys, and more.

SessionID: 74258

Statistics - Why are they there and what do I do with them

Back to calendar

Event Date: 23-06-2018 - Session time: 14:15:00 - Track: Enterprise Database Administration Deployment

Speaker: Dave Bland

Title: Statistics - Why are they there and what do I do with them

Abstract:

In this session you will learn the basics of statistics and what they are used for. You will learn how to gather information about the statistics and how to use the information to improve performance. It is important the statistics are maintained to work properly. We will discuss what on going maintenance should be done on statistics.

SessionID: 77041

Upgrade to SQL Server 2017: Unleashing the Power of SQL Server 2017

Back to calendar

Event Date: 23-06-2018 - Session time: 14:15:00 - Track: Application Database Development

Speaker: Sourav Mukherjee

Title: Upgrade to SQL Server 2017: Unleashing the Power of SQL Server 2017

Abstract:

Unleashing the Power of SQL Server 2017. We will discuss many useful features that SQL 2017 offers. We will primarily discuss on Resumable online index rebuild, Automatic database tuning, graph database capabilities and other features. Will also touch base upon few of the other features on SQL Server 2017 SSIS and SSRS as well.

SessionID: 78583

Introducing dbachecks : Unit testing for your database infrastructure

Back to calendar

Event Date: 23-06-2018 - Session time: 14:15:00 - Track: Enterprise Database Administration Deployment

Speaker: Frank Henninger

Title: Introducing dbachecks : Unit testing for your database infrastructure

Abstract:

Have you ever setup a new instance and realized later that you skipped a configuration step? Did you get handed a database from another team and want to see how much work you have to do to clean up it’s configuration? Don’t you wish there was an easy way to validate that every instance in your environment was configured correctly?

In this session we’ll be learning about an exciting new powershell module called dbachecks which seeks to solve these problems. Built upon the backbones of dbatools and pester, this is a unit testing framework with over 100 predefined checks to validate your instances.

SessionID: 82025

Let's build a data app with Azure Functions!

Back to calendar

Event Date: 23-06-2018 - Session time: 14:15:00 - Track: Cloud Application Development Deployment

Speaker: Joshuha Owen

Title: Let's build a data app with Azure Functions!

Abstract:

Are you interested in learning how to leverage Azure Functions to create an app that can scale to demand?

In this session, we will walk through the architecture and components of creating an Azure-facing video processing application that can scan videos for text and provide that information in CSV files for analysis. We will cover the architecture, data flow, and Azure technology pieces including Azure Functions, Blob Storage, Storage Queues, Azure Data Lake Storage Analytics, and Power BI.

SessionID: 82211

Availability Group Troubleshooting Beyond the Basics

Back to calendar

Event Date: 23-06-2018 - Session time: 14:15:00 - Track: Enterprise Database Administration Deployment

Speaker: Ed Leighton-Dick

Title: Availability Group Troubleshooting Beyond the Basics

Abstract:

Your Always On Availability Group just failed. Quick! What do you do? Many of us go straight to the SQL Server log and the Availability Group dashboard. That makes sense - both are great troubleshooting tools, and the answer is often found in one of them. However, they don't always have the answer we seek. What then?

As a consultant, I've been able to troubleshoot a number of less-common scenarios first-hand. Join me as we walk through some of these scenarios to show you how to detect and correct them. Along the way, we'll also discuss alerts you can put into place on your servers to give you immediate feedback when something in your availability group goes wrong.

SessionID: 74073

Custom Activities in Azure Data Factory

Back to calendar

Event Date: 23-06-2018 - Session time: 15:40:00 - Track: Cloud Application Development Deployment

Speaker: Jared Zagelbaum

Title: Custom Activities in Azure Data Factory

Abstract:

There are a lot of impressive out of the box capabilities with Azure Data Factory. Also, just like in SSIS, you can extend these capabilities using custom code (.Net in V1 or any executable in V2). We’ll discuss the prerequisites for Azure needed to implement these activities, as well as step through some code examples in C#, including how to setup your development environment. Prior experience with Azure Data Factory is highly recommend for this session.

SessionID: 74705

SQL Unit Testing with T-SQLt

Back to calendar

Event Date: 23-06-2018 - Session time: 15:40:00 - Track: Application Database Development

Speaker: Kevin Trojanowski

Title: SQL Unit Testing with T-SQLt

Abstract:

Unit testing is an important part of application development and maintenance. Knowing that your latest change is not only providing the desired results but also is free from side-effects is critical to the rapid pace expected by your customers. This session will provide an introduction in to how you can easily create a library of unit tests around your database and its programmable objects.

SessionID: 78900

Four Rules For Columnstore Query Performance

Back to calendar

Event Date: 23-06-2018 - Session time: 15:40:00 - Track: Application Database Development

Speaker: Joe Obbish

Title: Four Rules For Columnstore Query Performance

Abstract:

You've read all the Books Online articles about Columnstore indexes for data warehouses, but everything sounds too good to be true. Can you really get the promised 10X query performance without changing any code? In this demo-packed session, you'll learn real world lessons from deploying Columnstore indexes in production, and how you can avoid many query performance issues by following four simple rules. This is an advanced session recommended for attendees who already know the basics of Columnstore technology.

SessionID: 82185

In memory database... Let's rethink that request!

Back to calendar

Event Date: 23-06-2018 - Session time: 15:40:00 - Track: Strategy and Architecture

Speaker: Pat Phelan

Title: In memory database... Let's rethink that request!

Abstract:

Many times users or management ask for something that they've heard about but don't quite understand, in hope of solving a problem that doesn't quite make sense. It is possible to put a SQL Server database in memory, and memory is faster than disk, so this has just got to be a good idea!

This session looks at what you can do to put a database in memory, at what problems that can help, and at what problems it won't affect noticeably. It also looks at the recommended way to put tables into memory and the problems that will solve.

SessionID: 82193

Locking and its effects on queries and maintenance tasks

Back to calendar

Event Date: 23-06-2018 - Session time: 15:40:00 - Track: Application Database Development

Speaker: Jake Manske

Title: Locking and its effects on queries and maintenance tasks

Abstract:

Multi-user systems are affected by how SQL Server locks resources to manage concurrent transactions. Understanding how SQL Server uses locks to isolate transactions from one another is important for both writing code and running maintenance tasks.

In this session, we will discuss lock modes in SQL Server and how they impact your queries. We will show how different isolation levels change which locks are taken and for how long they are held. We will look at ways to avoid unnecessary blocking for maintenance tasks like index rebuilds. Assertions will be backed up with demo-based evidence.

After this session, you will have the understanding you need to start writing application code and maintenance jobs suitable for concurrent systems by anticipating blocking issues due to locking.

This site is open source. Improve this page.