This Track includes video recordings of 12 of our most popular SQL Server sessions taught by industry recognized, hands-on specialists. Topics range from Performance to Troubleshooting to Table Partitioning to SQL Server Private Cloud ... and a whole lot in between. See SQL Server Track session abstracts below.
Practical Uses of Windows Functions
Window functions allow you to perform data analysis computations elegantly and efficiently. Support for window functions was added in two major milestones. SQL Server 2005 introduced ranking and limited aggregate window functions, and SQL Server 2012 enhanced aggregate window functions with framing options, as well as introduced offset and statistical window functions. This session first explains the design of window functions and then covers practical uses of the functions, demonstrating how you can use them to address business challenges.
Troubleshooting SQL Server with SysInternals Tools
In this demo-packed session, we will explore the use of SysInternals tools such as Process Monitor, Process Explorer, and many others to troubleshoot various problem scenarios with SQL Server. This session isn't for the faint of heart and is tailored toward those adventurous DBAs who often like to step out of their comfort zone. A fair knowledge of Windows OS internals is recommended.
Maximizing Plan Reuse
To get peak performance from your SQL Server instance, you need to ensure that the majority of your database calls reuse a previously compiled query plan. Lack of proper plan reuse can add an enormous amount of overhead to your system and can affect overall performance. We will start with an overview of how the plan cache works, which will help you identify the existing plans that aren't being reused—and more importantly, we'll show you how to reverse that behavior. Finally, we'll explore several options for dealing with situations in which you might not have control over how calls are made to the database.
Efficient Interval Management in SQL Server
An interval represents a set of values between some low and some high values. In reality, there are different kinds of intervals that you might need to represent in your database, such as temporal (e.g., sessions, appointments, periods of validity). To represent temporal intervals in SQL Server, most people use two attributes holding the lower and upper points in time. You also might need to detect relations between intervals, such as overlap (e.g., "return all contracts that were active during an input period represented by the inputs @lower and @upper"). Some of the classic querying methods that are used to provide answers for such requests suffer from fundamental optimization problems. This session explains the existing optimization problems, presents solutions for those problems that can be applied in SQL Server, and describes the potential for additions to SQL Server that could improve interval treatment in the future.
Encryption in SQL Server
Securing your data is becoming increasingly important for data professionals. This session will cover the encryption features available in SQL Server to help you better secure data. Transparent Data Encryption, symmetric keys, asymmetric keys, and hashing functions will be covered in a number of demos.
SQL Server Table Partitioning from the Ground Up
In this session, we will explore the SQL Server table partitioning features that were introduced in SQL Server 2008. We'll look at how to create partitioned tables and indexes, as well as the internals of the table. We'll also discuss how to move data from one partition to another and from one table to another with minimal to no impact on users. Understanding these techniques will help you improve query performance and reduce downtime when loading and pruning data.
SQL Server Index Internals
Proper indexing is critical to every SQL Server installation. Having the correct indexes is imperative; having the incorrect or excessive indexes can be detrimental to system performance. This session will dig deep into what a SQL Server index is, when to choose a specific index, and what those indexes are actually doing inside the engine.
What DBAs Need to Know About Hekaton
Hekaton, Microsoft's new "in-memory tables" architecture in SQL Server, promises to seriously change the type of database solutions that you need to architect and administer. In this session, we will examine this new technology's architecture and internals, new T-SQL syntax, and various use cases. We'll then discuss how it affects your SQL Server environment, including performance considerations, troubleshooting, administration, and high-availability design considerations.
SQL Server Transaction Log Internals
The transaction log plays the most critical role in any SQL Server database. In this session, we discuss the importance of the transaction log and the roles it plays inside the database engine. We look at how logging and recovery work, the checkpoint process, write-ahead logging, and steps you need to take as a DBA to ensure proper management of the transaction log for a SQL Server database.
Build Your Own SQL Server Private Cloud
Do you know what SaaS and DaaS are? If not, you should. SQL Server as a Service (SaaS) and Database as a Service (DaaS) are otherwise known as the private cloud—the ability to offer your customers (external or internal) a reliable, scalable, and manageable way to easily deploy SQL Server systems and databases. Customers simply want to push a button and get what they asked for, which is what the private cloud experience should allow them to do. Even if you're already in the cloud, learn how to take it to the next level and design a service offering that will increase your organization's agility.
Managing SQL Server Performance with Extended Events
One of a DBA's primary responsibilities is managing the performance of a SQL Server environment. When performance problems arise, DBAs need to have the correct tools in place to be able to dig in and discover the issues that are occurring. Although it's one of the newer tools in the DBA toolbox, Extended Events is one of the most powerful tools available. In this session, we will discuss performance management responsibilities for DBAs and provide a foundation, through Extended Events, to understand and resolve performance issues.
TempDB Performance Troubleshooting and Optimizing
Every SQL Server instance relies on the tempdb database. Whether through explicit use with #temp tables or @table variables, or implicit use through working space for many other operations, most tempdb databases get quite a workout. This session will dig into many of the hidden issues that can kill server performance. Learn how to spot tempdb performance issues, drill into the cause, and take the steps necessary to solve the problem.