SQLServerGeeks Virtual Symposium – SQL Server & Azure SQL – May 2021 – Recordings

A Slice of Time: Window Functions in SQL Server
by Kevin Feasel
Window functions are a powerful technique for analyzing data in SQL Server. In this session, we will understand when to use a window function like ROW_NUMBER() or LAG(), including specific use cases. We will understand the performance implications of window functions, including performance tuning of queries with window functions, using window functions to make certain classes of queries faster, and performance implications of having multiple windows in a single query. This talk will also review some of the limitations in SQL Server's implementation of window functions and how to work around many of these. Watch Now
Improving Analytics Performance With Columnstore Indexes by Edward PollackAdministrators and analysts often struggle with the storage and maintenance of reporting and analytics data. Traditional rowstore tables do not scale well when data is measured in billions of rows.Columnstore indexes provide a speedy and convenient way to store OLAP data within SQL Server. Compression allows the data to be much smaller than other storage methods, allowing for faster reads. Column metadata provides the tools needed for SQL Server to be able to service queries over massive data sets quickly and efficiently.The basics of columnstore indexes allow an administrator to improve the performance of analytics, but there is much more to learn! Understanding the internals of columnstore indexes allows querying to be even more efficient.Columnstore indexes have evolved greatly since their introduction in SQL Server 2012. This presentation explores this feature in SQL Server 2017 and SQL Server 2019 and how its newest features can turbo-charge your analytics workloads! Watch Now
Database DevOps with Containers
by Rob Richardson
Unlike CI/CD pipelines for applications, we can't just delete the database and pop up a new one with each new version. Let's start with the production database, and get SQL Server content to developers in containers, and then flow schema and lookup data back into production with migration tools. You can bring the reliability and automation of CI/CD pipelines to Database DevOps with containers. Watch Now
How to use the Cloud for Data (and actually save money)
by Emanuele Meazzo
I’ve seen too many companies move to the cloud “because we’ve heard that’s the way to go these days” and completely miss the expected results because it was a mere business decision without the necessary technical research.In this session we’ll explore the move to the cloud from a data professional perspective, helping you evaluate if moving to the cloud is the right thing for your business, and if so how to do it in order to avoid having performance issues and more expenses than before.We’ll then review the role of the Database Administrator to understand how it can evolve to bring even more (tangible) value to the business.At the end of this session you’ll be able to evaluate if and how to make the move in a way that will actually benefit the business, the developers and, of course, you! Watch Now
A deep dive into Docker
by Andrew Pruski
Running SQL Server in a Docker container has many benefits (and drawbacks!) that we as SQL professionals should be aware of.In this demo heavy session we'll dive deeper into running SQL Server in Docker containers. We'll cover: -What exactly is container isolation?Container networking,Persisting data for SQL Server in Docker containers,Building custom SQL Docker images,Running SQL containers with Docker Compose,This session is for anyone who has run SQL Server in a container and wants to learn more about the platform. Watch Now
Debugging without debugger: investigating SQL Server’s internal structures
by Hugo Kornelis
Have you ever wanted to know exactly how SQL Server stores data for temporary structures, such as for instance the spooled data in a Table Spool or Index Spool operator? No? I don't blame you. It's a bit like wanting to know who wattered the rubber tree that produced the rubber your tires are made off before you get in the car to drive to work. You really don't need it.
But you might still WANT to know. I did. And I figured it out.
Without ever touching the debugger (I'm a simple soul, that stuff is much too complex for me!), I figured out how I could look at the exact storage structures SQL Server creates and uses to store data in Table Spool and Index Spool operators.
If you are willing to commit to a fast-paced high-level internals session that is guaranteed to teach you exactly zero actually useful information, then this is the session for you.
Watch Now
Azure SQL Database – Where is my SQL Agent?
by Taiob Ali
You migrate your on premise SQL Database to cloud, taking advantage of PaaS offering of Azure SQL Database.  You heard the promise of spinning up databases on demand and being able to scale up resources during high peak and scale down when not in use. You also want to make sure you are performing integrity checks, index defragmentation and statistics update when necessary. There is no SQL Agent so how do you automate your jobs? Do you have time to do this manually each time? No. There are different options available to automate these long-running, manual, error-prone, and frequently repeated tasks to increase reliability, efficiency.
In this demo intensive session I will show you different options on how to automate these tasks. Some of these solution using on-prem infrastructure or services in Azure which are conveniently encapsulate within the common Azure portal experience.
At the end of this session you will have solid understanding about how to automate SQL Server Maintenance tasks including replacing SQL Agent functionality with multiple options.
Watch Now
Connect your data artifacts with Azure Synapse Analytics
by Wolfgang Strasser
In human brains, synapses are responsible to connect information pieces together. Over time, our knowledge increases when more and more synapses are connected together. Following that analogy, Azure Synapse Analytics wants to connect (all) information silos together and provide a single point of analytics.
Join this session, if you want to hear about the building blocks of Azure Synapse Analytics, how Data Engineers and Data Scientists and even BI developer can work together in a single service and see how easy it is to start with Synapse Analytics!
Watch Now
Azure Synapse Analytics Serverless SQL Pool
by Armando Lacerda
Azure Synapse Analytics is a collection of data processing engines. In this session Armando Lacerda will demo how the Serverless SQL pool, which is part of Synapse Analytics, can be used with Power BI to implement data exploration on top of a data lake. Watch Now
Method behind the magic of Indexes in SQL Server
by Deepthi Goguri
Do you want to learn how to view Indexes behind the scenes? Are you interested in learning how indexes quickly retrieve the data? In this session, we are going to see the Index internals, index maintenance and how indexes are used to get back the results. By the end of this session, you will know how to view index internals, B-tree structures, how this data is useful for effective index usage and get the best performance for your queries. Watch Now
Performance Features in SQL Server and Azure SQL
by Javier Villegas
We will cover all the latest features in SQL Server and Azure SQL that are intended to improve performance and make workloads more stable. We will cover Query Store, Automatic Query Tuning and Intelligent Query Processing. Watch Now
SQL Server 2019 – Intelligent Query Processing
by Satya Ramesh
SQL Server 2017 introduces Adaptive Query Processing (AQP) feature family that can help improving the performance of SQL Server. SQL Server 2019 added more features to the family and it is now named as Intelligent Query Processing (IQP). Some workloads can be really benefitted by enabling these features. In this session, you will learn about each feature of the IQP. Watch Now
Myths and Misconceptions about Locking & Blocking
by Klaus Aschenbrenner
You know the various Transaction Isolation Levels and Locking/Blocking very well in SQL Server? If yes, come and join my session where I will talk about various Myths and Misconceptions around Locking & Blocking that I have encountered over the years. We will take a whirl tour through some edge cases that will change forever how you think about Locking & Blocking in SQL Server. During this session we will cover the following areas:-NOLOCK can block-Shared Locks are blocking Shared Locks-Read Committed behaves as Repeatable Read-Online Index Operations are not Online Index Operations! Watch Now
Maintain Database Project and CI/CD using SSDT in practical terms
by Kamil Nowinski
A task seems to be easy. Maintenance a project of a database in the code repository, treat as master-version and do deployment evenly and frequently. Simple? Seemingly. The things become more complex as fast as a number of objects in database growing. While instead of one database, we have over a dozen. When databases have got the references to each other. And how about dictionary tables? Where to keep them and how to script? Additional issues are coming whilst we would like to control instance-level objects.
All these topics I will explain in the session focused on practical aspects of work with Microsoft Visual Studio Data Tools.
Watch Now