GroupBy – Free SQL Server Training

GroupBy.org is free technical training by the community, for the community. Volunteer speakers submit abstracts at GroupBy.org and you – the attendee – vote to pick the sessions. Want to present your own session or just vote on others? Join us at GroupBy.org.

Have you ever developed slowly running queries and wondered how to make your own SQL Server queries go faster? I know I did. It is how I stumbled into my career in performance tuning. In this session, you will learn some T-SQL performance tuning tips that can be used in the office immediately. You will learn how to identify top offender queries, be able to benchmark queries. Finally, we will cover some coding patterns that are developer friendly but are not SQL Server database engine friendly and show you how to rewrite them so they can run faster.

There seems to be a common misconception that once you move from on premises SQL Server to Azure PaaS offerings a DBA is no longer required. This perception is wrong and in the session I’ll show you why. As a business intelligence consultant I develop data platform solutions in Azure that once productionised need administration. As the title suggests, be my Azure DBA. Maybe not DB for database. Maybe in Azure I need a DSA, a Data Services Administrator. Specifically we’ll cover a real business intelligence solution in Azure that uses Data Factory, Data Lake, Batch Service, Blob Storage and Azure AD. Help me administer this next generation data solution.

Creating indexes is one of the most powerful ways to improve performance in your SQL Servers. It's not easy to form indexes for a large and complicated query, and things only get more complicated when queries can come from vendor applications, ORM tools like LINQ, or any number of other sources.

When it's time to create the perfect index, there's guidelines and strategies to get it right, and then prove that it's helping. However, performance is not always about just one index, and general index strategy can also achieve great results. We'll focus on how to get the perfect index or indexes for your workload.

I want to show you how just a little neater and tighter code can also run a lot faster. These are some of the instant performance improvements that won’t change a lot of code, won’t generate weeks of testing and validation for your colleagues and can be relatively quick to implement.

But most of all, they’ll make the code prettier and easier to understand for the next person.

We’ll look at common ways that null values, implicit datatype conversions and foreign key constraints can radically affect the performance of your queries. Inspired by an actual client case, where I tuned a 20-hour query down to a few minutes, with only a minor code change.

This presentation is all-code, some execution plans, and no-powerpoint.

As the DBA for an Agile, Sprint-based team, you may feel like the bottleneck at every turn. You’ve stuck with the existing processes and tools because it’s what you know. After all, “If it ain’t broke, don’t fix it!” Right?

I have good news! With only a few new skills and free tools from Microsoft, you can join the Agile & DevOps revolution to help accelerate your team, product, and data infrastructure.

In this session I’ll use the newest version of SQL Server Data Tools (SSDT) to model an existing database and then demonstrate each step from modification to a working, automated deployment. I’ll highlight the configuration options that matter most, provide solutions for overcoming common problems across environments, and show you what to do once the release is ready for production.

After seeing the possibilities, you’ll be ready to change those old processes. And in no time at all, you’ll be the talk of the team as they try to keep pace with you!

You've been writing T-SQL queries for a few years now, and when you have performance issues, you've been updating stats and using OPTION (RECOMPILE). It's served you well, but every now and then, you hit a problem you can't solve. Your data's been growing larger, your queries are taking longer to run, and you're starting to wonder: how can I start getting better query plans?

The secret is often comparing the query plan's estimated number of rows to actual number of rows. If they're different, it's up to you - not the SQL Server engine - to figure out why the guesses are wrong. To improve 'em, you can change your T-SQL, the way the data's structured and stored, or how SQL Server thinks about the data.

This session won't fix every query - but it'll give you a starting point to understand what you're looking at, and where to go next as you learn about the Cardinality Estimator.

You identified a query that is causing performance issues, and your mission is to optimize it and boost performance. You looked at the execution plan and created all the relevant indexes. You even updated statistics, but performance is still bad. Now what?

In this session we will analyze common use cases of poorly performing queries, such as improper use of scalar functions, inaccurate statistics and the impact of parameter sniffing. We will learn through extensive demos how to troubleshoot these use cases and how to boost performance using advanced but practical techniques. By the end of this session, you'll have many powerful techniques to apply to solving query performance issues.

Today's applications are not using monolithic approach anymore and evolving into micro-services architecture. Monitoring tools are also evolving, micro-services approach have stepped into this area as well.

What you should do if you have several technologies under your responsibility? SQL Server and MySQL? Maybe Hadoop or PostgreSql? Should you use separated tool for each product? Should you use the same tool for Monitorig and Alerts, incidents management and notifications?

There are many cloud SAAS products in addition to traditional on-premise monitoring products and in this session, we will talk about their advantages and disadvantages.

The incredible Columnstore Indexes can increase your analytical query processing speed multiple times, they are updatable (Clustered from SQL Server 2014 and Nonclustered from SQL Server 2016 respectively), but they keep on supporting different sets of the functionalities – such as Change Data Capture (Nonclustered Columnstore) or LOBs (Clustered Columnstore), and this brings a great confusion onto the table.

This session will light up your path on when to use what functionality to use and when, even though sometimes one of the type of the Columnstore Indexes does not seems to appear as a default choice for your scenario.

Microsoft emphasizes use of containers for development and test on SQL Server 2017, and new solutions from Red Gate and Windocks add support for containers and SQL Server database cloning for the full range of the SQL Server family. We’ll explore use of containers for delivery of container environments, and their pros and cons. We’ll also look at SQL Server cloning, and how clones enable Dev and Test teams to work on large database environments for containers as well as existing SQL Server instances.

Containers and Clones are particularly well suited for Public and Hybrid Cloud, as it’s infrastructure agnostic. Container images are ultimately portable, and the built-in database cloning is especially useful on public clouds.

This session will cover the pros and cons of four typical configurations of AlwaysOn Availability Groups; stand-alone instances, fail-over cluster instances, multi-subnet, and a hybrid approach that I call disaster-recovery-on-the-cheap. It will also include their exclusive features such as read-only routing and backup off-loading. In addition, you’ll learn how to use the Availability Group Listener correctly and why you should be using Windows Server 2012 R2 or above, along with some of my own personal lessons learned.

SQL injection is one of the most common ways that hackers gain access to your SQL server. Do you know how to harden your queries and protect your data from malicious users?

This session will provide an overview of how SQL injection works and how to write injection-proof queries through a series of T-SQL demos. We’ll also take a look at why some commonly used techniques aren’t as secure as many people think.

If you ever write or maintain dynamic SQL queries, or work with developers who do, then this session is for you.

You’ve just been given a server that is having problems and you need to diagnose it quickly. This session will take you through designing your own toolkit to help you quickly diagnose a wide array of problems. We will walk through scripts that will help you pinpoint various issues quickly and efficiently. This session will take you through;

What’s on fire? – These scripts will help you diagnose what’s happening right now Specs – What hardware are you dealing with here (you’ll need to know this to make the appropriate decisions)? Settings – are the most important settings correct for your workload? Bottlenecks – We’ll see if there are any areas of the system that are throttling us. By the end of this session, you should have the knowledge of what you need to do in order to start on your own kit. This kit is designed to be your lifeline to fix servers quickly and get them working.

All code we’ll go through is either provided as part of this presentation or are open source/community tools.

There are tools available from Microsoft, as well as from a number of third party vendors, to ease the process of integrating database unit testing into your development environments and your continuous delivery pipeline.

This session isn't about any of those tools, but is an attempt to get back to basics - with the open source tSQLt framework - and address questions such as:

Why is database unit testing important?

Why is database unit testing difficult?

What do I need to test?

What do I not need to test?

What does a "good" unit test look like?

No particular experience of automated testing is required, we'll be talking about database testing "from the ground up". A good grasp of Transact-SQL constructs will be helpful in understanding the examples.

Do you work for an institution that fears its DBAs could go rogue on their production databases?

Do you want to revoke continuous sysadmin permissions from your DBA team in an attempt to avoid being the next WikiLeak?

Do you have concerns that the DBA team is querying sensitive databases?

Then you need Uptight Database Security. Come to this session to learn how to deploy a configurable permission model that provides on-demand access to SQL Server databases. This model allows DBAs to do routine work without sysadmin, and elevate permissions to sysadmin only during production emergencies or disaster recovery scenarios.

Attendees will leave this session with the fundamental knowledge and scripts to implement a low hassle solution that provides DBAs the minimum necessary access required to maintain a production database server.

Congratulations on your recent move into the world of SQL Server development!

You've got a handle on the basics -- SELECT, INSERT, UPDATE, and DELETE -- but now what? How do you know what's important to learn next, and what's most likely a waste of time?

In this session, we're going to answer these questions and more. You'll discover why set-based thinking is the foundation of SQL Server. You'll learn to use valuable T-SQL building blocks like window functions. I'll also share a handful of time-saving shortcuts that could save you an insane amount of typing, meaning you don't have to take another working lunch to clean up a poorly-formatted script.

BUT WAIT, THERE'S MORE!

In addition to learning new skills for today, you need to set yourself up for success next week, next month, and next year. That's why we'll also cover two things you can start doing right now to raise the incline of your career, and get you the recognition you'll deserve.

Journey with me through stories, concepts, and live demos, and you will emerge ready to kick-start your young SQL Server career.

You’re a DBA or Developer, and you have a gut feeling that these simple queries with an egregious number of columns in the SELECT list are dragging your server down. You’re not quite sure why, or how to index for them. Worst of all, no one seems to be okay with you returning fewer columns. In this session, you’ll learn why and when queries like this are a problem, your indexing options, and even query tuning methods to make them much faster.

I’m going to look into the basics of how encryption works and then we’ll learn how we can go about writing our own encryption routines within SQL Server. When we’re happy that those routines are secure, we’ll look at ways that we can go about cracking those routines.

This session will look at understanding how the underlying WSFC platform works to help us maintain availability of our databases. This is for DBAs and systems administrators who are responsible for administering SQL Server FCIs and AGs but are not so comfortable with figuring out why the databases went offline and what they can do about it. We’ll walk thru the troubleshooting process that I follow to quickly identify and resolve SQL Server FCI and AG availability issues.

You’ve just been handed a SQL Server, and you need to make sure there aren’t any skeletons in the closet. What things should you check to make sure everything is healthy? In this presentation, we’ll run you through the checklist I use when I provide a health check to a customer. First, I check for any resume generating events (RGEs) like broken backups. Then I check for best practices like email alerts and MAXDOP settings. Finally, these checks turn into more a scuba diving expedition, where we look for things that seem off and dig deeper. You won’t walk away with a perfect strategy, but you’ll have an idea of how one data professional does it.