sqlsathistory

Nr: 991

Back to Main list

SQLSaturday #991 - Iowa City – Virtual 2020

Start Time (24h)	Speaker	Track	Title
09:00:00	Ameena Lalani	Enterprise Database Administration Deployment	SQL Server 2019 - Intelligent Query Performance features
09:00:00	Kevin Feasel	Strategy and Architecture	The Curated Data Platform
09:00:00	Kevin Chant	Application Database Development	Azure DevOps duet (75 minutes version)
09:00:00	Peter Shore	Enterprise Database Administration Deployment	Infrastructure for the Data Professional: An Introduction
10:20:00	Kevin Mackey	Application Database Development	Don't just code it—Generate It
10:20:00	Ben Miller	Enterprise Database Administration Deployment	Gathering Operational Data for Trending with PowerShell
10:20:00	Tracy Boggiano	Enterprise Database Administration Deployment	Intro to Query Store
10:20:00	André Melancia	Enterprise Database Administration Deployment	Hacking SQL Server Is Not Enough
11:40:00	Dave Bland	Enterprise Database Administration Deployment	How to do a Complete SQL Server Assessment
11:40:00	Frank Gill	Application Database Development	Azure Data Studio, SQL Server Containers, and You
11:40:00	Kathi Kellenberger	Enterprise Database Administration Deployment	Introduction to Extended Events
11:40:00	Armando Lacerda	BI Platform Architecture, Development Administration	Azure Synapse back to back
13:00:00	Brian Hansen	Application Database Development	Window Functions in SQL
13:00:00	David Klee	Enterprise Database Administration Deployment	10 Cloudy Questions to Ask Before Migrating Your SQL Server
13:00:00	Sarah Patrick	Application Database Development	Sarah the Explorer: Navigating and Customizing the SharePoint Interface
13:00:00	Mark Runyon	Professional Development	Elevating Your Career into IT Leadership
14:20:00	H. Ross Reed	Enterprise Database Administration Deployment	SQL Server Policies - Underused Method for Configuring Your Database Server and Enforcing Standards
14:20:00	David Patrick	BI Platform Architecture, Development Administration	Intro to Power BI
14:20:00	Tracy Boggiano	Professional Development	Mental Health and Wellness in IT: Safeguarding our most precious resource
14:20:00	Jeff Iannucci	Application Database Development	Breaking Bad Habits: Solutions for Common Query Antipatterns
15:40:00	Erin Dempster	Application Database Development	Navigating Row Level Security
15:40:00	Frank Henninger	Enterprise Database Administration Deployment	Managing SQL Server on Windows Server Core
15:40:00	Taiob Ali	Application Database Development	Think like the Cardinality Estimator

SessionID: 105378

SQL Server 2019 - Intelligent Query Performance features

Back to calendar

Event Date: 01-08-2020 - Session time: 09:00:00 - Track: Enterprise Database Administration Deployment

Speaker: Ameena Lalani

Title: SQL Server 2019 - Intelligent Query Performance features

Abstract:

SQL Server 2019 is the latest release only 7 or 8 months old. It is time now to learn about some awesome features of this release. Like previous versions, SQL Server 2019 comes with a plethora of new features as well as with enhancements to the existing ones.

In this session, I will discuss and show demos of Intelligent Query Processing family of features. IQP has taken the performance of SQL Server to the next level, never seen or imagined before. We will see in action how with minimum or no changes from DBA or Developer, your query performance improves automagically just by going to compatibility level 150 (SQL Server 2019). You will leave this session with a solid understanding of some of the best new features of SQL Server 2019 and hopefully you will be motivated to try them yourself.

SessionID: 105827

The Curated Data Platform

Back to calendar

Event Date: 01-08-2020 - Session time: 09:00:00 - Track: Strategy and Architecture

Speaker: Kevin Feasel

Title: The Curated Data Platform

Abstract:

The breadth of data platform options available to us today is staggering: between relational, object, graph, and document databases, we have a wide array of storage options, as well as a huge number of access and analysis tools. This talk will walk you through data platform, data analysis, and data processing options. We'll discuss when to use (and when not to use) Spark, R, Airflow, and Power BI, as well as a range of other interesting tools and technologies. By the end of this talk, you may not have MongoDB or Neo4J, but you will know where they might (or might not!) fit in your environment.

SessionID: 105916

Azure DevOps duet (75 minutes version)

Back to calendar

Event Date: 01-08-2020 - Session time: 09:00:00 - Track: Application Database Development

Speaker: Kevin Chant

Title: Azure DevOps duet (75 minutes version)

Abstract:

Welcome to Azure DevOps Duet, a tale about how a development team and an operations team have to bond together and start using Azure DevOps for SQL Server related deployments.

This session will cover the process of developing a CI/CD process starting at getting the team on board and ending with making an actual release.

We will discuss

• the challenges of implementing DevOps
• setting up a database solution project
• getting started with source control
• testing your database releases using tSQLt
• setting up your Azure Devops pipelines

After this session you will have the tools and knowledge to get started with DevOps and get your development process to the next level.

If you want to see a session about using Azure DevOps with SQL Server, co-presented by a Microsoft MVP and somebody who is Microsoft certified for both, this is the session for you.

SessionID: 105918

Infrastructure for the Data Professional: An Introduction

Back to calendar

Event Date: 01-08-2020 - Session time: 09:00:00 - Track: Enterprise Database Administration Deployment

Speaker: Peter Shore

Title: Infrastructure for the Data Professional: An Introduction

Abstract:

It doesn’t matter if you are a Junior DBA, an accidental DBA or all the way up to a Senior DBA, the infrastructure your SQL Server environment runs on is important. Many among the DBA community came in as developers or perhaps directly into database administration roles it is equally possible that you have been out of the operations world long enough to have fallen out of the loop with what is happening. This session is intended to provide a full stack infrastructure overview so that you can talk shop with your cohorts in operations to resolve issues and maybe even be proactive. We will discuss, in an introductory fashion, hardware, network, storage, virtualization and operating system layers. Additionally, some suggestions as to where to find more information will be provided.

SessionID: 105009

Don't just code it—Generate It

Back to calendar

Event Date: 01-08-2020 - Session time: 10:20:00 - Track: Application Database Development

Speaker: Kevin Mackey

Title: Don't just code it—Generate It

Abstract:

Code Generators are all the rage—BIML, JS frameworks, .NET scaffolding, even the humble templates provide by SSMS for views, stored procedures, functions.

Table-driven applications allow one set of code to handle changing business needs. Metadata-driven code generation takes this a giant step further. If you can describe your code with an algorithm operating against a set of data, you can get a computer to produce the code.

• Source to Target mapping documents can be used to generate the code to effect the mapping.
• Service Broker activation stored procedures have a clearly defined structure. They can be mass-produced.
• Standard CRUD stored procedures can be stamped out cookie-cutter fashion.
• Business rules can be encoded in data and the code to implement them manufactured. Generated code has systematic errors. Fix the errors at the generator. All the code now works. If the code is so boring that a computer can produce it, why not spend your talent doing something interesting?

SessionID: 105256

Gathering Operational Data for Trending with PowerShell

Back to calendar

Event Date: 01-08-2020 - Session time: 10:20:00 - Track: Enterprise Database Administration Deployment

Speaker: Ben Miller

Title: Gathering Operational Data for Trending with PowerShell

Abstract:

SQL Server has a very good store of data about your SQL Server operations that you can gather for trending. These include index usage, index operational, disk space, database space, etc. In this session you'll first learn the basics of what this data is and what it can be used for. Second you will see the solution I have developed to gather this information, as well as how you can modify it to get more data very simply. And last you will see some data I've gathered and reports that show you what your environment is like. You will learn how simple it is to get a wealth of information using my favorite tool (PowerShell) and hopefully it will become yours. If you are interested in operational insights into what is happening in your database and how simple it is to gather this data, this session is for you. You will get all demo scripts. This session is demo heavy so come hungry.

SessionID: 105909

Intro to Query Store

Back to calendar

Event Date: 01-08-2020 - Session time: 10:20:00 - Track: Enterprise Database Administration Deployment

Speaker: Tracy Boggiano

Title: Intro to Query Store

Abstract:

In this session, we will look at the new Query Store feature in SQL Server 2016, 2017, and 2019. Query Store tracks changes in execution plans, allowing you to easily view performance differences and revert to older plans with a few clicks of the mouse in 2016.

Then in 2017, Microsoft added wait stats per query plan and Automatic Plan Correction capabilities. Allowing DBAs more tools to troubleshoot fires with and a way to automatically resolve issues.

In this session, we will walk through the features of the Query Store, so you can understand how to use them in SQL Server 2016, 2017, and 2019.

SessionID: 105929

Hacking SQL Server Is Not Enough

Back to calendar

Event Date: 01-08-2020 - Session time: 10:20:00 - Track: Enterprise Database Administration Deployment

Speaker: André Melancia

Title: Hacking SQL Server Is Not Enough

Abstract:

You already know how easy it is to hack SQL Server… But how do you protect your local and cloud servers against enemy agent attacks?

This is a demo-driven session, suited for DBAs, developers and security consultants. Both exploits and security recommendations to avoid them will be covered. Disclaimer: No actual crimes will be committed. Please do not send agents to my house again.

SessionID: 104034

How to do a Complete SQL Server Assessment

Back to calendar

Event Date: 01-08-2020 - Session time: 11:40:00 - Track: Enterprise Database Administration Deployment

Speaker: Dave Bland

Title: How to do a Complete SQL Server Assessment

Abstract:

Completing a full assessment on a SQL Server is essential to learning about not only the current state of the server, but also any area that may be impacting performance or security. If you have more than one DBA, it is also important to have all the DBA team members review the same areas when completing an assessment. During this session we will discuss when assessments are useful, what should be included in the assessment as well as how to interpret the results. We will use a SQL script that will capture all the data needed. This script will more than 88 data points, including fragmentation of indexes, performance counter values, server and database security information, queries that consume the most CPU time including execution plans as well as many other data points. This session will also cover how to document your findings and recommendations.

SessionID: 104312

Azure Data Studio, SQL Server Containers, and You

Back to calendar

Event Date: 01-08-2020 - Session time: 11:40:00 - Track: Application Database Development

Speaker: Frank Gill

Title: Azure Data Studio, SQL Server Containers, and You

Abstract:

Azure Data Studio (ADS) provides a cross-platform tool for SQL Server. With ADS, you can manage connections, develop and execute code, and build and run Jupyter Notebooks. Notebooks allow formatted text to be combined with executable code and can be render in a browser, making them ideal for wiki pages and runbooks. With the November 2019 release, both T-SQL and PowerShell code can be executed in a notebook. This allows docker commands to build and manage SQL Server containers from within a notebook. Join me for this introduction to ADS and SQL Server containers.

SessionID: 105816

Introduction to Extended Events

Back to calendar

Event Date: 01-08-2020 - Session time: 11:40:00 - Track: Enterprise Database Administration Deployment

Speaker: Kathi Kellenberger

Title: Introduction to Extended Events

Abstract:

Extended Events have been available since 2008. At the time, there was a steep learning curve, and many folks decided to wait for a GUI. Even though a GUI was provided in 2012, many still depend on Profiler and Trace to understand what’s going on under the hood of SQL Server.

During this session, you will learn how to make the transition to Extended Events. You’ll find out that it’s easier than you think!

SessionID: 105858

Azure Synapse back to back

Back to calendar

Event Date: 01-08-2020 - Session time: 11:40:00 - Track: BI Platform Architecture, Development Administration

Speaker: Armando Lacerda

Title: Azure Synapse back to back

Abstract:

Azure Synapse provides a complete platform to ingest and process data at scale in the cloud. In this workshop we will be learning and applying its features to all stages of data processing and understanding best practices and pitfalls. From ingesting to data lakes to transform data in big data services to apply machine learning models, including data remodeling, this workshop will guide to through a full implementation of Azure Synapse all the way to presentation and reporting.

SessionID: 104559

Window Functions in SQL

Back to calendar

Event Date: 01-08-2020 - Session time: 13:00:00 - Track: Application Database Development

Speaker: Brian Hansen

Title: Window Functions in SQL

Abstract:

No, they don't have anything to do with the operating by the same name. Instead, window functions allow us to calculate a single value based on a set of rows, and we have extraordinary control over which rows get used as the inputs. We'll start by covering what a window function is and will discuss the syntax and usage of the OVER clause in detail. We will also cover the three classes of window functions (ranking, aggregate and analytic) with focus on some commonly used functions as well some "gotchas" with some of these functions. You will learn how this class of functions can help you write more performant code.

SessionID: 105054

10 Cloudy Questions to Ask Before Migrating Your SQL Server

Back to calendar

Event Date: 01-08-2020 - Session time: 13:00:00 - Track: Enterprise Database Administration Deployment

Speaker: David Klee

Title: 10 Cloudy Questions to Ask Before Migrating Your SQL Server

Abstract:

Cloud adoption of database-related services continues to accelerate, but the choices for which service to select – IaaS, DBaaS, and PaaS – all impact the features available to your applications. Selecting a service is a critical first step in the migration process, and selecting poorly can lead to cost overruns and significant time spent going down the wrong path. The questions you do not ask or consider prior to a migration can make or break your cloud experience. The most important ten questions to answer, such as your availability requirements, database-level dependencies, migration strategies, and other critical concerns, will all be discussed so that you are ready to move to the cloud with confidence to your platform of choice.

SessionID: 105913

Sarah the Explorer: Navigating and Customizing the SharePoint Interface

Back to calendar

Event Date: 01-08-2020 - Session time: 13:00:00 - Track: Application Database Development

Speaker: Sarah Patrick

Title: Sarah the Explorer: Navigating and Customizing the SharePoint Interface

Abstract:

As the SharePoint Intern for Smithsonian Institution’s Office of the Chief Information Officer, I created an interactive organization chart in SharePoint so users can find information on all the Smithsonian units in one place. I will go in depth on how I used SharePoint web parts, HTML/CSS, and JavaScript to create an image map with modals to display each unit’s information and the obstacles I faced with the customizations.

SessionID: 105951

Elevating Your Career into IT Leadership

Back to calendar

Event Date: 01-08-2020 - Session time: 13:00:00 - Track: Professional Development

Speaker: Mark Runyon

Title: Elevating Your Career into IT Leadership

Abstract:

The skills that make you a brilliant technologist don't always map over when you want to take the next step into a leadership position. In this session, we will look at key skills to develop in order to prepare for the next step in your IT career.

SessionID: 104117

SQL Server Policies - Underused Method for Configuring Your Database Server and Enforcing Standards

Back to calendar

Event Date: 01-08-2020 - Session time: 14:20:00 - Track: Enterprise Database Administration Deployment

Speaker: H. Ross Reed

Title: SQL Server Policies - Underused Method for Configuring Your Database Server and Enforcing Standards

Abstract:

As a DBA for a financial clearing house and we are audited by multiple government regulators to

make sure our database instances are configured to standards. When configuring an instance we have a checklist of configurations which must adhere to those standards. Then those configurations must be monitored to make sure they remain set according to those standards. This is time consuming, and labor intensive. If only there was a quick way to template those standards so that they could easily be applied and enforced to database instances. Well one way that can be done with SQL Server Policies !! SQL Server Policies can be used to both configure and enforce standards within a database instance. SQL Server Policies have been around since SQL Server 2008 , but have not gained much traction in the industry. SQL Server Policies been the "Rodney Dangerfield" of SQL Server features getting no respect. I'll show you how I used this feature to configure and enforce standards.

SessionID: 105300

Intro to Power BI

Back to calendar

Event Date: 01-08-2020 - Session time: 14:20:00 - Track: BI Platform Architecture, Development Administration

Speaker: David Patrick

Title: Intro to Power BI

Abstract:

For anyone new to Power BI, we'll cover all the main features and walk through getting data into the tool (using both the desktop and online versions), creating visualizations, turning them into reports and adding them to dashboards. When we're through we'll have several BI components available to share via the Cloud!

SessionID: 105910

Mental Health and Wellness in IT: Safeguarding our most precious resource

Back to calendar

Event Date: 01-08-2020 - Session time: 14:20:00 - Track: Professional Development

Speaker: Tracy Boggiano

Title: Mental Health and Wellness in IT: Safeguarding our most precious resource

Abstract:

One in four Americans suffers from a mental health challenge each year. In the tech community, this number rises to 42%. The stigma associated with discussions around mental health, as well as how it can negatively impact your work and home life, make people reluctant to discuss the topic. Now is the time to start discussing mental health in tech and how to take advantage of the opportunity to safeguard our most precious resource: people. We'll discuss how mental health affects job quality, including sharing examples from my professional life. We'll discuss the four primary workplace factors which play a pivotal role in mental health challenges in the tech community. I'll provide tips on what is best to say and not say to people you know whose lives are being affected by their mental health. Lastly, we'll cover how to make your workplace a safe place where mental health can be talked about and how to encourage your company to offer resources to help maintain everyone's mental health.

SessionID: 105947

Breaking Bad Habits: Solutions for Common Query Antipatterns

Back to calendar

Event Date: 01-08-2020 - Session time: 14:20:00 - Track: Application Database Development

Speaker: Jeff Iannucci

Title: Breaking Bad Habits: Solutions for Common Query Antipatterns

Abstract:

Your query returns the correct results, but even with supporting indexes it seems slow. Can it go any faster? In this session, we focus on T-SQL query antipatterns – commonly used techniques that are unintentionally counter-productive. Through an interactive story of user requests, we identify several antipatterns, examine what makes them troublesome, and show alternative methods to improve performance.

SessionID: 104043

Navigating Row Level Security

Back to calendar

Event Date: 01-08-2020 - Session time: 15:40:00 - Track: Application Database Development

Speaker: Erin Dempster

Title: Navigating Row Level Security

Abstract:

New to SQL Server 2016 (okay, it's a few years old already, but it still feels new) is Row Level Security, which allows database developers and architects to effectively lock down tables, giving users access to a defined subset of data in the table. It sounds easy enough, but there are a number of design considerations and pitfalls to be aware of. This session will work through a few demos to implement RLS and dive into those pitfalls that need to be avoided. For anyone considering implementing Row Level Security or have tried but have run into issues, this should be a session you want to attend.

SessionID: 104062

Managing SQL Server on Windows Server Core

Back to calendar

Event Date: 01-08-2020 - Session time: 15:40:00 - Track: Enterprise Database Administration Deployment

Speaker: Frank Henninger

Title: Managing SQL Server on Windows Server Core

Abstract:

Lately it seems more sysadmins are embracing Windows Server Core as the OS of choice for virtualized servers. SQL Server is (almost) perfectly happy to run in this environment but how do you manage on an OS without a full desktop.

We'll cover what versions and features are supported, how to install sql server via a configuration file, and what common activities are changed when you move into a Core environment.

SessionID: 105242

Think like the Cardinality Estimator

Back to calendar

Event Date: 01-08-2020 - Session time: 15:40:00 - Track: Application Database Development

Speaker: Taiob Ali

Title: Think like the Cardinality Estimator

Abstract:

SQL Server uses a phase during query optimization, called cardinality estimation (CE). This process makes estimates bases on the statistics as to how many rows flow from one query plan iterator to the next. Knowing how CE generates these numbers will enable you to write better TSQL code and, in turn, influence the type of physical operations during query execution.

Based on that estimated rows, the query processor decides how to access an object, which physical join to use, how to sort the data. Do you know how the CE generates these numbers? What happens when you have multiple predicates, range predicates, variable values that are 'NOT KNOWN' to the optimizer, or you have predicate values increasing in ascending order? Do you know what will happen if your predicate is using a value that is outside of the histogram range?

In this session, I will show you how CE estimates in all these scenarios, and you will walk out better equipped to tackle those nasty, hard to solve query plans.

This site is open source. Improve this page.