SQLSaturday #768 - Wausau 2018
Event Date: 09/08/2018 00:00:00
Event Location:
- Northcentral Technical College
- 1000 W Campus Dr
- Wausau, Wisconsin
This event has completed. All data shown below is from the historical XML public data available.
If there are any data quality issues or corrections needed, please contact the webmaster for this site or submit a pull request for the appropriate file(s).
Sessions
This is a list of sessions from the event, based on the schedule in the XML files.
Title: How to Perform a Health Check
Abstract: Are you uncertain about the health and capacity of your SQL Server? Are you taking over a new server and need to get a better understanding of the health of the server? Are you uncertain if your servers are following industry best practices?
In this session we will start by discussing the importance of performing a Health Check. We will then show how to run the popular Blitz script by Brent Ozar. After that we will review the other key areas to check like the VM configuration, Windows Configuration, SQL Configuration, Security configuration, and Database configuration. Finally, we’ll show how to save the results for capacity and trending analysis
At the end of the session you will be able to: (1) Perform a Health Check on a server; (2) List the key areas for improvement; (3) Save the results for capacity and trending analysis.
Speaker(s):
- Edward Roepe
Track and Room: Enterprise Database Administration Deployment - CBI103
Title: Data Vault 2.0 An Agile BI DB Design Method
Abstract: Dan Linstedt, the creator of Data Vault 2.0, describes it as follows: The Data Vault Model is a detail oriented, historical tracking and uniquely linked set of normalized tables that support one or more functional areas of business. It is a hybrid approach encompassing the best of breed between 3rd normal form (3NF) and star schema. The design is flexible, scalable, consistent and adaptable to the needs of the enterprise. Data Vault 2.0 is not an app but a database modeling methodology that overcomes many of the shortcomings of DW modeling. Data vault attempts to solve the problem of dealing with change in the environment by separating the business keys from the descriptive attributes of those keys. This presentation will go over the basic parts of the Data Vault 2.0 model along with examples of each.
Speaker(s):
- Thomas Lane
Track and Room: Application Database Development - CBI109
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!
Speaker(s):
- Dave Bland
Track and Room: Enterprise Database Administration Deployment - CBI107
Title: The Ultimate Maintenance Plan
Abstract: Is your SQL Server running slower and slower? Do you prefer a proactive rather than a reactive approach to managing SQL Server? Do you worry about being able to recover in an emergency? If you answer yes to any of the above this session is for you.
We will start by exploring some of the maintenance options that are available. We will then explore the popular utility program by Ola Hallengren and how to implement. Next, we will explore the other areas of SQL Server that need to be maintained like history tables in MSDB. Finally, we will explore the different options when maintenance windows are tight.
At the end of this session you will be able to: (1) Choose a maintenance program for your SQL Servers; (2) Program and schedule the maintenance programs; (3) Ensure that the SQL Server is running at optimum performance each day.
Speaker(s):
- Edward Roepe
Track and Room: Enterprise Database Administration Deployment - CBI103
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.
Speaker(s):
- Jake Manske
Track and Room: Application Database Development - CBI103
Title: Recent SSMS Performance Tuning Enhancements
Abstract: SQL Server Management Studio has for some time been undergoing very rapid changes and improvements! Many of the improvements are focused on helping improve SSMS’s ability to help with performance tuning problems. The SQL Server engine itself has had many such improvements too. Join me for a demo-filled run down of some of the highlights of these changes!
Speaker(s):
- Kevin Boles
Track and Room: Enterprise Database Administration Deployment - CBI103
Title: Columnstore 101: The Five Ws of Columnstore Indexes
Abstract: Even though Columnstore Indexes were introduced with SQL Server 2012, their popularity and general use haven’t increased nearly as much as their functionality since then. If you’re someone that’s not had much experience with Columnstore Indexes and want to learn more about them, this session is perfect for you as I plan to cover the Five Ws of Columnstore Indexes: what they are, when to use them, where they are best utilized, and most importantly why they work so well. You’ll be the “who” and after the session, I expect you’ll feel more confident and excited to use this great feature.
Speaker(s):
- John Eisbrener
Track and Room: Application Database Development - CBI107
Title: Laying Down the Law with SQL Server Resource Governor
Abstract: Mixed workload patterns can present resource utilization and performance challenges even on the beefiest database servers. Neither prior testing nor careful code promotion alone can prevent runaway queries from disrupting normal business activities. In this session we’ll stress SQL Server 2017 to explore Resource Governor strategies for managing concurrency and access to resources. We’ll look at the improved observability and troubleshooting ability that Resource Governor brings to a busy database server. We’ll also discuss throttles outside of Resource Governor that can assist in these areas.
Speaker(s):
- Lonny Niederstadt
Track and Room: Advanced Analysis Techniques - CBI109
Title: Azure Data Factory V2: The cloud SSIS we have been waiting for?
Abstract: Azure Data Factory V2 is currently in preview and has a lot of great new SSIS-like features but is it good enough to replace SSIS? In this session we will go over some of the new control flow, branching, and scheduling features in Azure Data Factory and see how to make a fully cloud capable data orchestration engine. As a bonus we’ll also show how you can run SSIS packages directly in Azure!
Speaker(s):
- Joshuha Owen
Track and Room: Cloud Application Development Deployment - CBI107
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.
Speaker(s):
- Dave Bland
Track and Room: Enterprise Database Administration Deployment - CBI107
Title: Common TSQL Mistakes
Abstract: We are going to examine a variety of oopsies MANY developers fall prey too - some obvious, some pretty subtle and some down right sneaky! Lots of code examples with the bad AND good code presented. I GUARANTEE that you will find things here that will either prevent you from getting bad data, throwing unwanted errors or vastly improving your database application’s performance. I have given this talk over 100 times now and it is always very highly rated!
Speaker(s):
- Kevin Boles
Track and Room: Application Database Development - CBI103
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.
Speaker(s):
- Joshuha Owen
Track and Room: Cloud Application Development Deployment - CBI109
Title: How to use Execution Plans to find Performance Issues
Abstract: This is a introductory session on how a DBA and developers can use Execution plans to find performance bottlenecks. This will cover the fundamentals and cover some of the basic techniques that can be used to find hidden performance problems.
Speaker(s):
- Dave Bland
Track and Room: Enterprise Database Administration Deployment - CBI107
Title: SQL Server Defaults SUCK!!
Abstract: If you just click next-next-next and think you have a good-to-go SQL Server THINK AGAIN!! :-) There are umpteen things you just GOTTA change right out of the box if you want your potentially very expensive and always precious SQL Server resource to perform and behave optimally. This session will cover a laundry list from High Power Setting to data file growth and many useful items in between.
Speaker(s):
- Kevin Boles
Track and Room: Enterprise Database Administration Deployment - CBI103
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.
Speaker(s):
- Joe Obbish
Track and Room: Application Database Development - CBI109
Title: Azure Managed Instance: Reducing Maintenance Costs While Making Migration Simple
Abstract: While Azure SQL Database provides the full feature of the SQL Server database engine, it is designed primarily for newly developed, cloud-based applications. For existing applications running on traditional SQL Server instances, the cost and complexity of migration can be prohibitive. With the release of Azure Managed Instances, much of the cost and complexity have been removed. Join me for this session to learn about the differences between Azure SQL Database and Azure Managed Instances. Additionally, discover methods for migrating existing instances to Azure.
Speaker(s):
- Frank Gill
Track and Room: Cloud Application Development Deployment - CBI109
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, concurrency and scalability you will get from that code”! Although this talk is based on Entity Framework, most ORMs suffer from the same flaws.
Speaker(s):
- Kevin Boles
Track and Room: Application Database Development - CBI103
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.
Speaker(s):
- Joe Obbish
Track and Room: Application Database Development - CBI107
Title: JSON for the Data Mortals
Abstract: Do you see curly brackets stored in your database and wonder what that is? Are you a DBA, with developers storing JSON in your database? As a database administrator or developer, it’s important to understand JSON today. This session will explain what JSON is, where it came from, and why it is showing up all over the place. When you leave this session you will understand how to investigate, manipulate, and validate JSON in your database. You will walk away with example code of how to use Microsoft’s additions to SQL Server to use this data.
Speaker(s):
- Jim Dorame
Track and Room: Application Database Development - CBI109
Speakers
This is a list of speakers from the XML Guidebook records. The details and URLs were valid at the time of the event.
Dave Bland
Twitter: - @SQLDave29
LinkedIn: Dave Bland
Contact: https://www.davebland.com
Over 20 years of SQL Server experience that includes being a DBA, BI development and Application development using VB.NET. Dave currently is the Manager of the DBA team at Stericycle. Dave is a Friend of Red-Gate for 2019. He is a frequent presenter at SQLSaturday events and user groups around the country. Has been teaching SQL Server since version 2000 and is the SQL Server instructor at Harper College in Palatine, IL. Current certifications include: • Microsoft Certified Trainer • MCTS: SQL Server 2008, Implementation and Maintenance • MCTS: SQL Server 2008, BI Development and Maintenance • MCSA: SQL Server 2008 • MCSA: SQL Server 2016 • MCSE: SQL Server 2016 - BI • MCDBA: SQL Server 2000 • MCSD • MCSE: Data Management and Analytics
Kevin Boles
Twitter: - @TheSQLGuru
LinkedIn: Kevin Boles
Kevin Boles is a SQL Server expert, working exclusively with the product since v6.5. With over 25 years of database experience and over 45,000 man hours of SQL Server engine experience, he holds many related certifications, is an MCT and was a SQL Server MVP from 2007 to 2012. Kevin has been a very successful independent consultant for over 20 years. His passion is the relational engine, especially designing, building, analyzing and tuning high-performance database applications.
Jim Dorame
Twitter: - @DBAJD
LinkedIn: Jim Dorame
Contact: http://www.jamesdorame.com/
Jim Dorame has been working with SQL Server for over 20 years. Over those years he has learned a great deal from the SQL community. He has also given back to the community in the hopes that he may help someone in their career. He is always looking for something to learn and luckily Microsoft keeps adding to SQL Server.
Edward Roepe
Twitter: - None
LinkedIn: Edward Roepe
Contact: http://None
Ed Roepe is the founder and president of Perimeter DBA and lives in Atlanta GA. Along with a BSBA in Computer Science from the University of Florida, he holds certifications in just about every version of SQL Server since Version 4.2. His specialties include Performance Tuning, Health Checks, Security Checks, Virtualization, High Availability and Disaster Recovery. During his 25+ years of SQL consulting experience, he has taught DBAs the secrets of SQL success. He has managed many very large databases in areas of healthcare, financial services, universities, and transportation. Ed can be contacted at edward@perimeterdba.com
Kevin Boles
Twitter: - @TheSQLGuru
LinkedIn: Kevin Boles
Kevin Boles is a SQL Server expert, working exclusively with the product since v6.5. With over 25 years of database experience and over 45,000 man hours of SQL Server engine experience, he holds many related certifications, is an MCT and was a SQL Server MVP from 2007 to 2012. Kevin has been a very successful independent consultant for over 20 years. His passion is the relational engine, especially designing, building, analyzing and tuning high-performance database applications.
Thomas Lane
LinkedIn: Thomas Lane
Thomas has been living and breathing Microsoft databases for over twenty years and is now relishing his position as a DW Consultant at Talavant (www.Talavant.com). After receiving his Ph.D. in Chemistry and following three years as a post-doc at Marquette University, Thomas left the world of science for a career in IT. He has worked as a programming and database instructor, a VB and C# developer, and for the past fourteen years worked with SQL server as a DBA, ETL specialist, and Data Architect at various Midwestern companies. Thomas recently received his MSCE in Data Management and Analytics.
Dave Bland
Twitter: - @SQLDave29
LinkedIn: Dave Bland
Contact: https://www.davebland.com
Over 20 years of SQL Server experience that includes being a DBA, BI development and Application development using VB.NET. Dave currently is the Manager of the DBA team at Stericycle. Dave is a Friend of Red-Gate for 2019. He is a frequent presenter at SQLSaturday events and user groups around the country. Has been teaching SQL Server since version 2000 and is the SQL Server instructor at Harper College in Palatine, IL. Current certifications include: • Microsoft Certified Trainer • MCTS: SQL Server 2008, Implementation and Maintenance • MCTS: SQL Server 2008, BI Development and Maintenance • MCSA: SQL Server 2008 • MCSA: SQL Server 2016 • MCSE: SQL Server 2016 - BI • MCDBA: SQL Server 2000 • MCSD • MCSE: Data Management and Analytics
Joshuha Owen
Twitter: - SQLJosh
LinkedIn: Joshuha Owen
A Data and AI Premier Field Engineer by day and tech geek at all times. I enjoy working with data technologies as well as dabbling in other areas such as VR programming.
Joshuha Owen
Twitter: - SQLJosh
LinkedIn: Joshuha Owen
A Data and AI Premier Field Engineer by day and tech geek at all times. I enjoy working with data technologies as well as dabbling in other areas such as VR programming.
John Eisbrener
Twitter: - @johnedba
LinkedIn: John Eisbrener
Contact: http://www.dbatlas.com
John is a DBA (Default Blame Assignee) with over a decade of experience breaking and subsequently fixing, database solutions. He maintains separate swear jars for Oracle, Postgres, and Greenplum, but keeps the largest container reserved for SQL Server. When not accumulating loose change for his children’s college funds, John can be found trying to help others with their database solutions as the Principal Consultant for DB Atlas, speaking at various SQLSaturdays throughout the Midwest, or helping out with his local PASS chapter meetings in Madison, WI.
Joe Obbish
Contact: https://orderbyselectnull.com
Joe Obbish has nearly a decade of experience with SQL Server and other database platforms. He works as a query tuning, columnstore, and scalability expert within Epic, a large EHR and analytics vendor in Madison, WI. In less than two years, he became one of the top contributors for SQL Server on the DBA StackExchange community. Joe blogs about advanced columnstore and query tuning topics on his blog, http://www.orderbyselectnull.com. In his spare time, Joe enjoys baking and sharing these treats with his coworkers.
Kevin Boles
Twitter: - @TheSQLGuru
LinkedIn: Kevin Boles
Kevin Boles is a SQL Server expert, working exclusively with the product since v6.5. With over 25 years of database experience and over 45,000 man hours of SQL Server engine experience, he holds many related certifications, is an MCT and was a SQL Server MVP from 2007 to 2012. Kevin has been a very successful independent consultant for over 20 years. His passion is the relational engine, especially designing, building, analyzing and tuning high-performance database applications.
Joe Obbish
Contact: https://orderbyselectnull.com
Joe Obbish has nearly a decade of experience with SQL Server and other database platforms. He works as a query tuning, columnstore, and scalability expert within Epic, a large EHR and analytics vendor in Madison, WI. In less than two years, he became one of the top contributors for SQL Server on the DBA StackExchange community. Joe blogs about advanced columnstore and query tuning topics on his blog, http://www.orderbyselectnull.com. In his spare time, Joe enjoys baking and sharing these treats with his coworkers.
Jake Manske
Twitter: - @jmanske
Contact: https://concurrencyexchange.com
Jake has been a T-SQL developer since 2013, starting after he jumped ship from an academic life as a mathematics professor to the software industry. He focuses on solving difficult concurrency problems, large scale data warehouse ETL processes, data conversions, and understanding SQL Server optimizer behavior.
Edward Roepe
Twitter: - None
LinkedIn: Edward Roepe
Contact: http://None
Ed Roepe is the founder and president of Perimeter DBA and lives in Atlanta GA. Along with a BSBA in Computer Science from the University of Florida, he holds certifications in just about every version of SQL Server since Version 4.2. His specialties include Performance Tuning, Health Checks, Security Checks, Virtualization, High Availability and Disaster Recovery. During his 25+ years of SQL consulting experience, he has taught DBAs the secrets of SQL success. He has managed many very large databases in areas of healthcare, financial services, universities, and transportation. Ed can be contacted at edward@perimeterdba.com
Kevin Boles
Twitter: - @TheSQLGuru
LinkedIn: Kevin Boles
Kevin Boles is a SQL Server expert, working exclusively with the product since v6.5. With over 25 years of database experience and over 45,000 man hours of SQL Server engine experience, he holds many related certifications, is an MCT and was a SQL Server MVP from 2007 to 2012. Kevin has been a very successful independent consultant for over 20 years. His passion is the relational engine, especially designing, building, analyzing and tuning high-performance database applications.
Dave Bland
Twitter: - @SQLDave29
LinkedIn: Dave Bland
Contact: https://www.davebland.com
Over 20 years of SQL Server experience that includes being a DBA, BI development and Application development using VB.NET. Dave currently is the Manager of the DBA team at Stericycle. Dave is a Friend of Red-Gate for 2019. He is a frequent presenter at SQLSaturday events and user groups around the country. Has been teaching SQL Server since version 2000 and is the SQL Server instructor at Harper College in Palatine, IL. Current certifications include: • Microsoft Certified Trainer • MCTS: SQL Server 2008, Implementation and Maintenance • MCTS: SQL Server 2008, BI Development and Maintenance • MCSA: SQL Server 2008 • MCSA: SQL Server 2016 • MCSE: SQL Server 2016 - BI • MCDBA: SQL Server 2000 • MCSD • MCSE: Data Management and Analytics
Lonny Niederstadt
Twitter: - @sqL_handLe
LinkedIn: Lonny Niederstadt
Contact: http://sql-sasquatch.blogspot.com
For 18 years, Lonny has worked with EMR databases for Epic. His current work includes SQL Server performance and scalability investigations, and developing best practices. Lonny lives in Fitchburg, Wisconsin with his wife and two daughters. In his spare time, Lonny enjoys long walks in the woods, and considers himself extremely lucky every time he finds his way home again.
Frank Gill
Twitter: - skreebydba
LinkedIn: Frank Gill
Contact: http://skreebydba.com/
Frank Gill is a Senior Data Engineer at Concurrency With 21 years of IT experience, the first 8 as a mainframe programmer, he has developed a love of all things internal. Over the past several years, he has worked extensively with SQL Server solution in Azure, including Managed Instance. When not administering databases or geeking out on internals, Frank volunteers at the Art Institute of Chicago and reads voraciously.
Sponsors
The following is a list of sponsors that helped fund the event: