sqlsathistory

Nr: 552

Back to Main list

SQLSaturday #552 - Lincoln 2016

Start Time (24h)	Speaker	Track	Title
08:30:00	William E Pearson III	Analytics and Visualization	Introducing Forensic Analytics with Power BI
08:30:00	Andy Galbraith	Enterprise Database Administration Deployment	Getting Started with Extended Events
08:30:00	Janis Griffin	Enterprise Database Administration Deployment	Top 10 Wait Types Everyone Should Know
08:30:00	Mindy Curnutt	Application Database Development	The Ins and Outs of SQL Indexes
08:30:00	Tim Plas	Cloud Application Development Deployment	SQL Server Databases in the Clouds
08:30:00	Kevin Kline	Professional Development	The Most Important IT Person in the Room: Why Data Experts Own the Future
10:00:00	William E Pearson III	Analytics and Visualization	Pragmatic Power BI: Transforming Data
10:00:00	Dana Stubben	Professional Development	WIT Panel Session
10:00:00	Brian Hansen	Enterprise Database Administration Deployment	Get Your Optimizer to Give up All Its Secrets
10:00:00	Cody Chapman	Strategy and Architecture	Ping! No Reply
10:00:00	Russel Loski	Cloud Application Development Deployment	Querying JSON in Azure Data Lake with USQL
10:00:00	Kevin Kline	Enterprise Database Administration Deployment	Configuration Option and Trace Flag Secrets: Surprising Behavior at the Flick of a Switch
12:45:00	Kevin Boles	Application Database Development	Know What Your Code is Doing to SQL Server!
12:45:00	Pam Shaw	Analytics and Visualization	Power BI – Exploring New Frontiers
12:45:00	Bill Fellows	BI Platform Architecture, Development Administration	What is Biml and why are SSIS developers excited about it?
12:45:00	Mindy Curnutt	Application Database Development	Fun with SQL Server Spatial Data
12:45:00	Ed Leighton-Dick	Application Database Development	Protecting Your Data with Encryption
12:45:00	Phillip Podhradsky	Application Database Development	Tips and Tricks for Performance Troubleshooting
14:15:00	Kevin Boles	Application Database Development	Query Plan Deep Drive
14:15:00	Dana Stubben	Analytics and Visualization	Microsoft R Explained
14:15:00	Matt Gordon	Enterprise Database Administration Deployment	Top 5 Tips to Keep Always On Always Humming and Users Happy
14:15:00	Rick Lowe	Enterprise Database Administration Deployment	Why Should I Care About ... Partitioned Views?
14:15:00	Pam Shaw	Information Delivery	Taking the Scare out of Monster Reports
14:15:00	Tim Plas	Enterprise Database Administration Deployment	Protecting SQL Data for Availability and DR
15:45:00	Matt Gordon	Enterprise Database Administration Deployment	New Features and New Speed in SQL Server 2016 Always On Availability Groups
15:45:00	Rick Lowe	Application Database Development	Weird stuff I saw ... while supporting a Java team
15:45:00	Mindy Curnutt	Enterprise Database Administration Deployment	SQL Server Bingo – Install, Migration Config
15:45:00	Ed Leighton-Dick	Enterprise Database Administration Deployment	Dammit Jim! Dr McCoy’s Field Guide to system_health (and the default trace)
15:45:00	Russel Loski	BI Platform Architecture, Development Administration	SSIS: Handling flat file changes using BIMLScript

SessionID: 49721

Introducing Forensic Analytics with Power BI

Back to calendar

Event Date: 19-11-2016 - Session time: 08:30:00 - Track: Analytics and Visualization

Speaker: William E Pearson III

Title: Introducing Forensic Analytics with Power BI

Abstract:

Despite the wealth of analytical technologies available today, 65% of fraud at public companies is detected through tips, management review, or simple accidents. Forensic Analytics combine human intuition and leading-edge technologies, and can provide superior detection methods to significantly improve the detection and investigation of illegal or unethical activities.

In this session, we explore Forensic Analytics: a set of techniques we can use to uncover irregularities in financial data. We also gain an understanding of goals of Forensic Analytics: to enable us to find out how — and why — these irregularities exist, and to find out their source(s) — especially when fraudulent activity is suspected. We discuss guiding principles that are key to isolating data anomalies, and outline a standard, repeatable process to Forensic Analytics. Throughout the session, we view examples of how we can employ Power BI to drive Forensic Analytics via various tests and reviews.

SessionID: 49979

Getting Started with Extended Events

Back to calendar

Event Date: 19-11-2016 - Session time: 08:30:00 - Track: Enterprise Database Administration Deployment

Speaker: Andy Galbraith

Title: Getting Started with Extended Events

Abstract:

Few subjects in Microsoft SQL Server inspire the same amount of Fear, Uncertainty, and Doubt (FUD) as Extended Events. Many DBA's continue to use Profiler and SQL Trace even though they have been deprecated for years. Why is this?

Extended Events started out in SQL Server 2008 with no user interface and only a few voices in the community documenting the features as they found them. Since then it has blossomed into a full feature of SQL Server and an amazingly low-impact replacement for Profiler and Trace.

Come learn how to get started - the basics of sessions, events, actions, targets, packages, and more. We will look at some base scenarios where Extended Events can be very useful as well as considering a few gotchas along the way. You may never go back to Profiler again!

SessionID: 50104

Top 10 Wait Types Everyone Should Know

Back to calendar

Event Date: 19-11-2016 - Session time: 08:30:00 - Track: Enterprise Database Administration Deployment

Speaker: Janis Griffin

Title: Top 10 Wait Types Everyone Should Know

Abstract:

There are over 800 wait types in SQL Server 2014, but there are only 10 (in my experience) that are seen very often. If you can learn these 10 wait types, what causes them and their solutions, you will be ahead of the performance analysis game. The list of 10 wait types to be discussed came from over 100 consulting engagements with customers, so come find out what they are and improve your performance troubleshooting skills.

SessionID: 54297

The Ins and Outs of SQL Indexes

Back to calendar

Event Date: 19-11-2016 - Session time: 08:30:00 - Track: Application Database Development

Speaker: Mindy Curnutt

Title: The Ins and Outs of SQL Indexes

Abstract:

This course will focus on the fundamentals of strategic SQL Server index design and creation. Terms that will be covered and clarified: clustered index, non-clustered index, uniqueness, covering index, fill-factor, scan, seek, key lookup, and fragmentation. This is a beginning level course for those who have always found indexes confusing and somewhat elusive. Lots of valuable information will be provided in a short amount of time.

SessionID: 54437

SQL Server Databases in the Clouds

Back to calendar

Event Date: 19-11-2016 - Session time: 08:30:00 - Track: Cloud Application Development Deployment

Speaker: Tim Plas

Title: SQL Server Databases in the Clouds

Abstract:

Application servers or services running on a cloud platform now have many options for their database engines. We discuss the various alternatives for providing Microsoft SQL Server services to cloud-based applications, including Microsoft Azure, Amazon AWS, and Google GCE. Why would you choose one approach over the other? What are the trade-offs on cost, performance, availability, security, ongoing maintenance, required skill sets, etc.

SessionID: 54621

The Most Important IT Person in the Room: Why Data Experts Own the Future

Back to calendar

Event Date: 19-11-2016 - Session time: 08:30:00 - Track: Professional Development

Speaker: Kevin Kline

Title: The Most Important IT Person in the Room: Why Data Experts Own the Future

Abstract:

You’ve just spent the last few days in deep technical training. You have learned many new techniques and best practices that you can take back to the office and use in your work to gain immediate improvements. But if you’re only interested in applying a handful of specific, tactical advantages, then you’ll miss out on the most exciting trend in the IT industry – our data-driven future. Share insights and inspirations in this talk with Kevin Kline, a founder and president emeritus of the Professional Association for SQL Server, to discover the broader trends that are pushing data professionals into greater prominence and strategies you can use to become the most respected, influential, and credible member of your organization’s technical staff.

SessionID: 50574

Pragmatic Power BI: Transforming Data

Back to calendar

Event Date: 19-11-2016 - Session time: 10:00:00 - Track: Analytics and Visualization

Speaker: William E Pearson III

Title: Pragmatic Power BI: Transforming Data

Abstract:

Once we’ve extracted data from a source, we typically need to clean, or otherwise change, that data in preparation for loading it into Power BI. In this session, BI Architect, SQL Server MVP and Analysis Services Maestro Bill Pearson will explore the options Power BI offers to unpivot, filter, sort, aggregate, and perform other needed operations to make these preparations. We’ll employ a dataset containing issues that commonly present real-world challenges, and work through the steps we can take in Power BI to transform the data to a state that works within Power BI. Moreover, we’ll define calculations, and get a glimpse of the M language that underlies transformational processes we construct.

SessionID: 53717

WIT Panel Session

Back to calendar

Event Date: 19-11-2016 - Session time: 10:00:00 - Track: Professional Development

Speaker: Dana Stubben

Title: WIT Panel Session

Abstract:

Women in Technology session engages the audience to discuss how we can energize the next generation to consider careers in technology. The participants on this panel today do not have all the answers. They will share a bit about themselves and how they were encouraged to pursue a career in technology. They will let us know what they are doing to encourage the young people in their lives to embrace technology, science, and math.

SessionID: 53856

Get Your Optimizer to Give up All Its Secrets

Back to calendar

Event Date: 19-11-2016 - Session time: 10:00:00 - Track: Enterprise Database Administration Deployment

Speaker: Brian Hansen

Title: Get Your Optimizer to Give up All Its Secrets

Abstract:

You know that execution plans can provide you with vital information to tune a query, but just how does that plan get generated? In this session, we will examine details the query optimization process that are not very well known. We will discuss how SQL Server parses a T-SQL statement then and begins applying rules to make it more efficient. Did you know that SQL Server can be coaxed into outputting hidden structures such as parse trees and memos? We#39;ll do just that, and then take it a step further with a unique visualization tool that I have created. With a better understanding of how SQL Server optimizes T-SQL (and sometimes doesn#39;t do it as well as might be hoped), you can write more efficient SQL statements.

SessionID: 54351

Ping! No Reply

Back to calendar

Event Date: 19-11-2016 - Session time: 10:00:00 - Track: Strategy and Architecture

Speaker: Cody Chapman

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? 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, 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: 54438

Querying JSON in Azure Data Lake with USQL

Back to calendar

Event Date: 19-11-2016 - Session time: 10:00:00 - Track: Cloud Application Development Deployment

Speaker: Russel Loski

Title: Querying JSON in Azure Data Lake with USQL

Abstract:

A low cost place to park your JSON data is in Microsoft's Azure Data Lake, a cloud repository for a variety of data. But how do you query this data? You can use Hadoop stack tools like Hive. Or why not put your SQL skills to use and write USQL? USQL is an ANSI SQL like language for querying data in the Azure Data Lake. After I give a quick demonstration as to how you can read a CSV file, I will demonstrate how to use a custom Extractor to read JSON files. We will look at the C# code already built to read these files. I will demonstrate how to deploy this code. And finally we will look at how to actually extract the data. Data is expanding at a dizzying speed. It is helpful to have up to date tools to handle new kinds of data.

SessionID: 54618

Configuration Option and Trace Flag Secrets: Surprising Behavior at the Flick of a Switch

Back to calendar

Event Date: 19-11-2016 - Session time: 10:00:00 - Track: Enterprise Database Administration Deployment

Speaker: Kevin Kline

Title: Configuration Option and Trace Flag Secrets: Surprising Behavior at the Flick of a Switch

Abstract:

If you’ve been a SQL Server DBA or Dev for any length of time, you’ve probably heard of various options that you can set at the instance- or database-level that can impact performance. Do you know what they are, when to use them, or what they do? And what about all of those trace flags? There are so many of them and many are undocumented. What gives? In this session, you’ll learn about a variety of ways to speed up IO, adjust memory, change plan and buffer cache behavior, debug the SQL query optimizer, and how to punch SQL Server in the face!

SessionID: 50591

Know What Your Code is Doing to SQL Server!

Back to calendar

Event Date: 19-11-2016 - Session time: 12:45:00 - Track: Application Database Development

Speaker: Kevin Boles

Title: Know What Your Code is Doing to SQL Server!

Abstract:

ORMs - oh how I LOVE them! As a consultant when I see them in play at a client I go KACHIIINNNNGGGG!! :-D Seriously though, they can provide some substantial benefits for coding throughput. But if you don't Read The Fine Manual and do some things right and avoid some major gotchas you will fall prey to one of my favorite Guruisms: "Anything that allows developers to slap code together more quickly is inversely proportional to the performance and scalability you will get from that code"! Although this talk is based on Entity Framework, most ORMs suffer from a lot of the same flaws.

SessionID: 53969

Power BI – Exploring New Frontiers

Back to calendar

Event Date: 19-11-2016 - Session time: 12:45:00 - Track: Analytics and Visualization

Speaker: Pam Shaw

Title: Power BI – Exploring New Frontiers

Abstract:

Everyone is eager to get started with Microsoft’s new offering: Power BI. It promises to be a great delivery system of information and analytics. This session will show some tips and tricks to getting started fast and deliver some real WOW factor to your users. We will explore bringing in data, enhancing it, and then displaying it. We will look at how to work with the standard visualizations as well as a few custom visualizations. We will discuss publishing options to allow us to show off our work. Come join the fun and see why there is so much ado about Power BI.

SessionID: 54059

What is Biml and why are SSIS developers excited about it?

Back to calendar

Event Date: 19-11-2016 - Session time: 12:45:00 - Track: BI Platform Architecture, Development Administration

Speaker: Bill Fellows

Title: What is Biml and why are SSIS developers excited about it?

Abstract:

This session is designed for you, the experienced SSIS developer who's been hearing about the Business Intelligence Markup Language, Biml, but has not taken the plunge. We will cover what the Biml language is and how using your familiar development tools, SQL Server Data Tools, SSDT, and BIDS Helper, you will be able to streamline the creation of SSIS packages.

We will work through progressively more complex examples from a "Hello World" equivalent to a to a full fledged, audited, truncate and reload pattern based on an external metadata repository.

You will leave this session armed with the knowledge of how Biml can help you achieve a consistent and reproducible approach for the creation of SSIS solutions.

SessionID: 54294

Fun with SQL Server Spatial Data

Back to calendar

Event Date: 19-11-2016 - Session time: 12:45:00 - Track: Application Database Development

Speaker: Mindy Curnutt

Title: Fun with SQL Server Spatial Data

Abstract:

This session is an introduction to the spatial features in SQL Server. In this session, you will learn how to create, procure and leverage spatial data. You will be made aware of tools you can use to integrate your own spatial data with a variety of public data sources such as the Census Bureau, National Weather Service, etc. Functions and T-SQL commands related to spatial data analysis will be demonstrated. We will end the session by using the geometry data type to actually mimic a bitmapped picture using SQL (that's the fun part!).

SessionID: 54384

Protecting Your Data with Encryption

Back to calendar

Event Date: 19-11-2016 - Session time: 12:45:00 - Track: Application Database Development

Speaker: Ed Leighton-Dick

Title: Protecting Your Data with Encryption

Abstract:

We’ve all seen the recent news stories about companies whose data has been stolen by hackers. What was once a rare event has become all too common, and companies large and small are at risk. While it isn’t always possible to prevent intrusions, you can reduce the risk by encrypting your data. In this presentation, I’ll show you the four ways that SQL Server provides to encrypt data: hashes, cell-level encryption, database-level encryption (also known as transparent data encryption), and backup encryption. We’ll also discuss the keys required for each type of encryption and discuss how to protect the keys themselves.

SessionID: 54401

Tips and Tricks for Performance Troubleshooting

Back to calendar

Event Date: 19-11-2016 - Session time: 12:45:00 - Track: Application Database Development

Speaker: Phillip Podhradsky

Title: Tips and Tricks for Performance Troubleshooting

Abstract:

“The application is too slow”...”reports are taking too long to load”… “my queries are running slow”. These are complaints we have all heard from our end users, and as we know, it is always the database that is the issue. In this session we will talk about different things to look for when assessing performance of queries. We will examine execution plans at a high level to see where problems may lie and look at some few different “gotchas” that can affect query performance. We will talk about indexes and statistics – what are they, what to index, and how they can help query performance.

SessionID: 50593

Query Plan Deep Drive

Back to calendar

Event Date: 19-11-2016 - Session time: 14:15:00 - Track: Application Database Development

Speaker: Kevin Boles

Title: Query Plan Deep Drive

Abstract:

Another in TheSQLGuru's Deep Dive series! Query plans - the roadmaps of how your query will be handled by the engine. TONs of information for the asute SQL Server user. Lets dig into them and see what we can learn from and about them and also talk about how they can be used to aid our query tuning efforts.

SessionID: 53709

Microsoft R Explained

Back to calendar

Event Date: 19-11-2016 - Session time: 14:15:00 - Track: Analytics and Visualization

Speaker: Dana Stubben

Title: Microsoft R Explained

Abstract:

Demonstrate advanced statistical analysis performed against SQL Server data.

SessionID: 53830

Top 5 Tips to Keep Always On Always Humming and Users Happy

Back to calendar

Event Date: 19-11-2016 - Session time: 14:15:00 - Track: Enterprise Database Administration Deployment

Speaker: Matt Gordon

Title: Top 5 Tips to Keep Always On Always Humming and Users Happy

Abstract:

Have you ever wondered what it takes to keep an Always On availability group running and the users and administrators who depend on it happy? Let my experience maintaining several production Always On Availability Groups provide you some battle-tested information and hopefully save you some sleepless nights. From security tips to maintenance advice, come hear about some less than obvious tips that will keep users happy and the DBA’s phone quiet.

SessionID: 53906

Why Should I Care About ... Partitioned Views?

Back to calendar

Event Date: 19-11-2016 - Session time: 14:15:00 - Track: Enterprise Database Administration Deployment

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. 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.

SessionID: 53971

Taking the Scare out of Monster Reports

Back to calendar

Event Date: 19-11-2016 - Session time: 14:15:00 - Track: Information Delivery

Speaker: Pam Shaw

Title: Taking the Scare out of Monster Reports

Abstract:

So the big wigs have come up with a “master” report that shows everything you could ever want on a single report. It includes grids and charts of varying sizes and complexities. And of course, they want it to be pretty as well.

This session discusses how to use SQL Server Reporting Services (SSRS) to bring it all together while avoiding pitfalls and mistakes. The demonstration shows you how to build a multipage, dashboard-style report to manage your report server, showing a multitude of grids and charts. Discuss the challenges with data reuse and formatting while exploring techniques for getting them to work.

SessionID: 54436

Protecting SQL Data for Availability and DR

Back to calendar

Event Date: 19-11-2016 - Session time: 14:15:00 - Track: Enterprise Database Administration Deployment

Speaker: Tim Plas

Title: Protecting SQL Data for Availability and DR

Abstract:

This session provides a comparison of SQL High Availability amp; Disaster Recovery options, presented by a practitioner who has implemented and managed pretty much all the SQL High Availability and Disaster Recovery approaches (and various combinations thereof). We will provide an overview of all the various HA amp; DR options, then compare trade-offs for complexity, usability, hardware, licensing, failover speed, initial costs, ongoing support costs, staff skill requirements, etc.

SessionID: 53829

New Features and New Speed in SQL Server 2016 Always On Availability Groups

Back to calendar

Event Date: 19-11-2016 - Session time: 15:45:00 - Track: Enterprise Database Administration Deployment

Speaker: Matt Gordon

Title: New Features and New Speed in SQL Server 2016 Always On Availability Groups

Abstract:

Microsoft has added new performance improvements and new features to SQL Server 2016’s implementation of Always On Availability Groups. This session will cover improvements that range from Standard Edition support for AGs to load balanced replicas to new ways to create those replicas. We’ll kick the tires on the improved Azure support and discuss the massive performance improvements in Always On as well.

SessionID: 53907

Weird stuff I saw ... while supporting a Java team

Back to calendar

Event Date: 19-11-2016 - Session time: 15:45:00 - Track: Application Database Development

Speaker: Rick Lowe

Title: Weird stuff I saw ... while supporting a Java team

Abstract:

After spending more than a decade completely devoted to the Microsoft stack, I did some time supporting a Java team. Yes, sure, Java is just another language so there aren't really and issues that are absolutely unique to the Java world. But it is a different stack, different culture, and different group of folks giving bad advice to developers ... just enough different to keep life interesting. Come hear about my misadventures with server side cursors, output parameters, integrated authentication, and NOCOUNT settings.

SessionID: 54296

SQL Server Bingo – Install, Migration Config

Back to calendar

Event Date: 19-11-2016 - Session time: 15:45:00 - Track: Enterprise Database Administration Deployment

Speaker: Mindy Curnutt

Title: SQL Server Bingo – Install, Migration Config

Abstract:

Get ready to play SQL Buzzword Bingo with the subject matter being SQL Install, Migration Configuration. Are you preparing to move a database from one SQL Server to another? Whether it's your first SQL install, a SQL Server Upgrade or changing from physical to virtual, there is a lot to consider. In some areas of the setup, you get only one chance to "get it right." Come to this session and find out all about the process - and have some fun while you're at it. First one to hit BINGO! Are you preparing to move a database from one SQL Server to another?

Whether it's your first SQL install, a SQL Server Upgrade or changing from physical to virtual, there is a lot to consider. In some areas of the setup, you get only one chance to "get it right." Come to this session and find out all about the process - and have some fun while you're at it. First one to hit BINGO! will win a fun and unique prize.

SessionID: 54381

Dammit Jim! Dr McCoy’s Field Guide to system_health (and the default trace)

Back to calendar

Event Date: 19-11-2016 - Session time: 15:45:00 - Track: Enterprise Database Administration Deployment

Speaker: Ed Leighton-Dick

Title: Dammit Jim! Dr McCoy’s Field Guide to system_health (and the default trace)

Abstract:

As DBAs, we are asked all sorts of impossible questions. Who dropped that important table last month? Why was everything slow last week? Who made that user a sysadmin? You’d have to be a mind reader to know the answers to some of those, right? Not necessarily! SQL Server can help you find information about these and other common problems with its default trace and system_health Extended Events session, but you have to know how to collect and interpret the data they provide. In this session, we’ll explore what system_health and the default trace are, what information they contain, and how to use them to find the answers you’re searching for.

SessionID: 54432

SSIS: Handling flat file changes using BIMLScript

Back to calendar

Event Date: 19-11-2016 - Session time: 15:45:00 - Track: BI Platform Architecture, Development Administration

Speaker: Russel Loski

Title: SSIS: Handling flat file changes using BIMLScript

Abstract:

You develop an SSIS package to load a flat file. You meticulously identify each of the columns and map the columns to the target table. Your package runs fine for months. Then the file format changes. It is back to the drawing board, selecting each column, setting the data types (type, length, etc.). In many cases, you actually have the file structure in an electronic form. Wouldn't it be nice to simply regenerate your SSIS package using that metadata?

In this session I will demonstrate how to automate the generation of SSIS packages using the open source BIML and BIMLScript. I will generate a set of packages to load all of the tables from one database into a new database using the results of an Information Schema view.

I will then demonstrate how to use this technique to handle changes in file structure. Using a simple Excel workbook I will represent the structure of a flat file and its relationship to a staging table. From this metadata, I will generate an SSIS package tha

This site is open source. Improve this page.