SQL Day 2021

Are you already an experienced programmer and know your way around the Microsoft SQL Server development language? Still, there are always problems with query runtimes that you cannot explain to yourself. Data is not written to database tables fast enough and you are looking for ways to optimize ETL processes?
These are all things that pose new challenges for a database developer daily. This workshop for database developers closes many of these gaps and uses practical examples to explain, analyze and solve the problems.
– Parameter sniffing
– Non SARGable queries
– Sort spills
– Implicit conversion
– NOLOCK / TABLOCK
– Temporary tables vs. Table variables
– Recursive CTEs
– partitioning
– scalar functions

This workshop is suitable for database developers who are already beyond the basics and want to sharpen the nuances. Ideally, you already have experience with execution plans and are very familiar with the SQL Server Management Studio.

This workshop is most hands on with prepared labs for the participants. All demos and hands on will be handled wit a BIG database (17 GB) and not with only a few records.

In this workshop, you will learn how to build, train, evaluate, and consume machine learning algorithms in your .NET apps using ML.NET.

In addition, you will also understand how TensorFlow and ONNX models can be integrated into a pipeline for deep learning or consumption purposes.

Finally, ML workflows can be set up for model lifecycle automation using MLOps, and that will also be explained

Power BI to takie przyjemne narzędzie… Szast, prast i tabele siedzą w modelu. Rach, ciach i mamy relacje. Baam i już coś widać na wykresach. I wtedy przychodzi otrzeźwienie bo użytkownicy biznesowi wcale nie są zachwyceni naszymi wykresami. Potrzebna jest jeszcze jedna kolumna, parę miar, tu podzielić jedno przez drugie, mały zastrzyk DAX’a tu i tam… I wszystko zaczyna się rozłazić a najczęstszą rzeczą ogladaną przez użytkownika jest mały, kręcący się krąg kropeczek…

Modelowanie wielowymiarowe to bagno, wygląda niewinnie i zielono z wierzchu, łatwo w nie wdepnąć i jeszcze łatwiej utonąć. Szczególnie w Power BI, gdzie dostajemy do dyspozycji ogromne możliwości: dwukierunkowa propagacja filtra, słabe relacje wiele-do-wielu, możliwość dynamicznego przełączania relacji w modelu i wiele, wiele innych.

Na tym właśnie skupimy się podczas warsztatu. Zastanowimy się od czego zacząć tworzenie modelu i o co zapytać odbiorców. Dowiemy się dlaczego dobra znajomość baz relaycjnych i SQL będzie nam przeszkadzać. Przejdziemy przez podstawy modelowania wielowymiarowego (i nie będzie to ani Komball, ani Inmon, ani DV2.0) Omówimy na przykładach ciekawe techniki, które wzbogacają a czasem po prostu umożliwiają realizację naszego projektu. Opowiemy o wadach i zaletach poszczególnych rozwiązań. Pokażemy kilka ciekawych scenariuszy raportów, które wymuszają modyfikacje modelu.
Spotkanie przesiąknięte praktyką i moimi doświadczeniami.

Azure Cosmos DB is battle tested non-relational database service by Microsoft. It is used by many mission critical applications, including Xbox, Office 365, Azure and Skype.

This workshop will help you take a more in-depth look at Azure Cosmos DB’s architecture, features and tools. We will start with an overview of Azure Cosmos DB’s infrastructure and architecture, then explore its main features and benefits. Additionally, we will use Cosmos DB Emulator for development purposes. In addition, we will look at the Data Migration tool to import and transform data as well as cover stored procedures, available SDKs, and Rest API of Cosmos DB. For the closing of this session we will configure Change Feed functionality using Azure Functions.

Contents:

History, Infrastructure and Architecture of Cosmos DB
What does Multi-Model mean?
Understanding Consistency Levels
Why Partitioning is crucial?
Managing Indexes in Cosmos DB.
Cosmos DB Constraints
Pricing
Using Cosmos DB Emulator
Querying Cosmos DB with SQL API
Setting up TTL

Data Type Problems of JSON
Creating Stored Procedures
How to Import&Transform Data
Available SDKs and Rest API
Spatial Data in Cosmos DB
Configuring Change Feed
Using Vs Code extension with Cosmos DB
Connecting to Cosmos DB From Sql Server 2019

For troubleshooting slow queries, looking at the execution plan is a good starting point. But sometimes, just looking at the plan does not help. Sometimes you need to dig deeper.

In this full-day workshop, you will learn everything you need to be take your understanding of execution plans to the next level. For almost every operator you can encounter in an execution plan, we will look at the inner workings, and look at properties that can affect their performance. We will also look at how operators in a plan interact with and affect each other.

Aside from explaining all of the common operators, we will also touch on several operators that were introduced or modified in the newer versions of SQL Server, and preview some changes that have been announced for future versions.

In short: After this workshop, you will be better prepared to look at execution plans, find the spo where it hurts, and then rewrite your query to get a faster execution plan.

If you have seen some execution plans but feel you need to bring your understanding to the next level, then this workshop is for you.

Many times, you wish you could have some more powerful tool than the Transact-SQL (T-SQL) language for working with data. You might need to enable your users to use word search in your string columns or in Office documents to give them similar experience like they get when they search the web. You might need to implement constraints and do the data analysis with functions that are simply not implemented in T-SQL. Or you might need to maintain multiple versions of data over the time and be able to query the state of the data from the past.
All these features mentioned are sometimes referred as “beyond relational”. However, data type and language support in a specific database management product has nothing to do with the relational model, which is purely logical model. For many years, we used to associate a RDBMS with the SQL language and simple data types; but there are no limitations for both in the relational model. Therefore, I prefer to use the term “beyond classical RDBMS”.
In this seminar, you will learn how to manipulate the data stored in your SQL Server and Azure SQL Database with advanced T-SQL language elements and with other languages. You will learn about Full-Text Search (FTS), integrating CLR functions written in Visual C# or Visual Basic in your T-SQL code, and work with system-versioned tables within SQL Server.
Besides executing CLR code inside the Database Engine, SQL Server supports executing code in external engines with external languages. R, Python, and Java support are covered in the seminar as well.
Tabular structures are not always the best for storing some kinds of data. Maybe your data has a bit more dynamic structure than you can represent with tables, or has a very specific structure, like hierarchy. This seminar introduces how to deal with such data inside a SQL Server database.
You will learn how to store and retrieve semi-structured data in XML or JSON format. You can extend the collection of the data types supported by SQL Server with your own CLR data types, written in C# or VB. This infrastructure was used also by Microsoft through the for spatial data types. Hierarchical data, like XML and JSON, are just a special case of a general data structures called graphs. You will learn also how to use graphs in SQL Server.

Snowflake to analityczna platforma dostarczana jako usługa (SaaS/DWaaS) w chmurze (Azure, AWS, GCP). Snowflake ma na celu zapewnienie hurtowni danych, która jest szybsza, łatwiejsza w użyciu i znacznie bardziej elastyczna niż tradycyjne podejścia.
W przeciwieństwie do innych ofert, to zupełnie nowa hurtownia danych zbudowana od podstaw z myślą o wykorzystaniu chmury i postępu w technologii baz danych. Usługa Snowflake nie jest oparta na istniejącej bazie danych ani żadnej istniejącej platformie Big Data. W 2019 trafiła do kwadratu liderów według Gartner’a w obszarze „Data Management Solutions for Analytics”. Sama obsługuje wszystkie aspekty autentykacji, konfiguracji, zarządzania zasobami, ochrony danych, dostępności, optymalizacji i wiele więcej.
Na warsztacie:
• poznasz architekturę i kluczowe koncepcje,
• własnoręcznie utworzysz hurtownię danych w chmurze,
• stworzysz model danych,
• załadujesz dane i zaczniesz je odpytywać,
• zobaczysz, jak łatwo możesz skalować i izolować workload bez przerywania pracy.
Nauczysz się takich funkcjonalnościami jak:
• Zero-copy Cloning,
• Time Travel,
• Undrop,
• Result Cache,
• Change Tracking,
• Snowpipe,
• i wiele więcej.
Na koniec zabierzesz Snowflake ze sobą i będziesz mógł się nim bawić przez 30 dni.
Przyjdź i weź czynny udział w warsztacie

Power BI is an amazing tool. It’s so easy to get started with. But when you need to implement it in an enterprise, it’s not so obvious how it should fit in. There are many ways to do this but in this workshop, Greg will show you how he implements these projects. And he’s done many successful projects. If you want to learn how to design better looking reports, this isn’t the session for you. Greg will be focussing on everything that comes before that.

One fine day you realize that you have to start tuning SQL Server for better performance. The need might have arisen from a recent performance issue, or the barrage of support tickets that are filling your inbox, or maybe your boss assigned this to you after realizing that SQL Server performance troubleshooting is not his cup of tea. Irrespective of the reason, the challenge is upon you to identify, diagnose and fix a variety of performance issue that is plaguing your SQL Server environment.

Performance troubleshooting can be categorized as proactive, real-time & reactive. If a well-defined methodology is followed during the proactive phase, you will have to fire-fight less during the real-time troubleshooting phase and you will have a lot of meaningful performance data to work within the reactive phase, which will further help you in preventing performance cases.

Join this demo-filled, full-day class, where you will learn the three approaches of SQL Server performance troubleshooting & tuning. You will learn the practical way, exactly like how you would troubleshoot in the real world with lots of code and scripts. Amongst many takeaways, here are some interesting ones:

Develop a performance troubleshooting methodology for the real-world
Learn how to benchmark and baseline (and also take away a framework, ready-to-use)
Analyzing and troubleshooting common wait types
Proactive, reactive and real-time CPU troubleshooting
Troubleshooting IO performance bottlenecks
Tracking SQL Server memory consumption and fixing OOM errors
Dealing with Tempdb madness
Top 6 query tuning techniques that will solve 75% of performance issues (real-world query tuning)
Top 3 indexing strategies that will fix 60% indexing woes
Get rid of deadlocks (yes, you hear it right)
SQL Server 2019 performance enhancements

In total, there will be five modules comprising of more than 55 demos, all from the real-world. Yes, a lot of content and the class will be fast-paced. The pre-con will focus on recent versions of SQL Server. All demos will be shown on SQL Server 2019. You will walk away with all the demo scripts, most of them being production-ready. You will also get PDF version of the content.

The class will end with a „Performance Tuning Online Quiz” and will help you measure yourself. The ones who score 100% will receive one-year complimentary access to Amit Bansal’s SQL Server Performance Tuning Fast Track Video Course.

Pre-requisite: You need to have at least two years of genuine SQL Server experience to gain maximum from this pre-con

In this one day, demo-heavy, scenario-driven workshop, members of the SQL engineering team will help you translate your existing SQL Server expertise to Azure SQL including Azure SQL Database and Azure SQL managed instance. At the end of the day, you should have a foundational knowledge of what to use when, as well as how to configure, monitor, and troubleshoot the “meat and potatoes” of SQL Server in Azure: security, performance, and availability. You will learn about not only the “what”, but also the “how” and the “why”. You’ll also walk away with resources and tools so you can go as deep as your scenario requires.