Development (DEV) Sessions
Estimation and Statistics: The Basis of Query Optimization (Level 400)
At the heart of SQL Server is the cost-based query optimizer. Stop and think about that a minute: The optimizer attempts to give the best query-processing plan based on the cost of the work that needs to be undertaken. How does it know the cost of the work before it has done the work? This isn’t a conundrum. It doesn’t know the cost; it estimates! How does it estimate? It uses statistics based on past plans. This session will dive deep into how the optimizer decides on a certain plan, the things that can go wrong, and how you can have influence over these choices.
Data, Faster: SQL Server Performance Techniques with SQLCLR (Level 500)
Sometimes it seems like business users are constantly more demanding: They want more data, more analytics, more insight, and the output needs to be faster, faster, faster. And so we database developers struggle to write ever more complex queries, tune our hardware, carefully choose indexes...and sometimes it’s just not enough. Where do you go when you’ve hit the performance wall? As more and more developers are discovering, a great choice is to make use of SQL Server’s CLR capabilities. User-defined aggregates, table valued functions, and user-defined types are more than just logical constructs; properly applied, they can make complex, logic-driven queries many times faster. This session will show you when and how to use these powerful tools in ways you may not have previously considered. If you’re ready to take your SQL Server development skills to the next level, this session is definitely your starting point.
FILETABLEs: What They Are and How to Use Them (Level 300)
Have you ever needed to access documents in your database as if they were files in the file system? SQL Server 2012 introduces a new method for managing large data objects (BLOBs) in a database. FILETABLEs let you access data using T-SQL, just like any other table inside the database, while at the same time providing access to the data using the OS File I/O API, just like any other folder in the file system. In this session, you will learn how to upgrade your document management solutions by migrating your large data to FILETABLEs. We will cover the two most typical migration scenarios: migrating from a distributed data store, where files are stored outside the database, and migrating from a homogeneous data store, where large objects are stored in tables.
FILLFACTOR - Pros and Cons (Level 300)
FILLFACTOR is a valuable option for fine-tuning index data storage and performance, especially for heavy workloads (INSERT and UPDATE) in OLTP systems. Join this session to learn how to determine the right FILLFACTOR setting for your indexes by using index statistics and its evaluation.
Fix Your EAV Data Model with a New EAV Design (Level 300)
In some cases, the Entity-Attribute-Value (EAV) data model is the only way to go for storing your data, but it can have performance side-effects. Come to this session to see how to build a hybrid EAV data model with a massive execution performance gain.
How the SQL Server Engine Thinks (Level 100)
When you pass in a query, how does SQL Server build the results? Let’s role play: Brent Ozar will be an end user sending in queries, and you will be the SQL Server engine. Using simple spreadsheets as your tables, you will learn how SQL Server builds execution plans, uses indexes, performs joins, and considers statistics. This session is for DBAs and developers who are comfortable writing queries, but not so comfortable when it comes to explaining nonclustered indexes, lookups, and sargability.
How to Call the Windows Azure REST API from CLR (Level 400)
Do you like the feature that lets you back up to Windows Azure BLOB storage? Do you know that you can enhance your code with many other Windows Azure features? In this session, you’ll learn how to cross the gap between SQL Server and the Windows Azure Platform using the CLR and the Windows Azure REST API. We'll cover Windows Azure queues, Windows Azure table storage, and Windows Azure BLOB storage and how to exploit them from your T-SQL code. We'll examine in detail how to construct a Windows Azure REST request and how to secure it using both shared key and certificate authentication. Finally, to give you a complete view of the topic, we'll look at debugging tools and techniques useful in the cloud.
Rewrite Queries with SQL Server 2012 (Level 300)
Learn how to optimize your queries using new features in SQL Server 2012. Among the topics we will cover are extensions of the ORDER BY clause that simplify the construction of an ad hoc query result set, the enhanced OVER clause with new options and windowing functions, the new SEQUENCE objects that can generate sequences of numerical values, a new command that improves error handling, and more.
SQL Anti-Patterns (Level 300)
Anti-patterns are a way of naming common design errors to make them easier to remember – even if you haven't done them yet. In this session, you will become acquainted with anti-patterns on a physical level, a logical level, and a SQL queries and application level. Plus, we’ll spice it up with some seriously foolish errors made by me and my colleagues. This session is relevant for both developers and DBAs.
SQL Server and XML Query (Level 400)
XML was added to the collection of SQL Server native data types in SQL Server 2005. Being a complex data type, XML is not only accompanied by a set of dedicated T-SQL functions, but also by a complete querying language. In this session, you will learn about the SQL Server 2008 implementation of the World Wide Web Consortium's XML Query Recommendation. You will see how to compose XML data – from existing SQL Server data or "from scratch," how to retrieve (relational) data from XML Documents to be used in a SQL Server database, and how to manipulate XML data using T-SQL and the XML Data Manipulation Language (XML DML). You've already mastered all the other SQL Server data types, why not take on XML?
StreamInsight: Complex Event Processing by Microsoft (Level 200)
In this Internet and Big Data age, applications increasingly have to process large amounts of data from event streams. Some developers may already be familiar with Reactive Extensions (Rx), but Microsoft offers another comprehensive platform for these scenarios: StreamInsight (SI). In this session, you will learn how SI can be a valuable addition to your toolbox if you need to develop applications that can handle complex event processing. See how you can start using this technology and how easy it is for .NET developers to jump in.
The Secret Life of APPLY: Helping Us Work Smarter, Not Harder (Level 300)
At the very least, a T-SQL developer should know that the APPLY operator lets you access data from a table-valued function in a dynamic manner. But why stop there? When getting the data you need requires going around your elbow to get to your nose, the savvy developer remembers that the APPLY operator can be used with any table expression. Have you ever wished you could get multiple columns or multiple rows from a correlated sub-query? What about needing to UNPIVOT a data set or parse some XML code? Have you simply wanted to find a way to handle complex calculations without having to cut and paste the entire formula in the WHERE or GROUP BY clause? This all-demo session answers those needs by revealing the secret life of APPLY and helping you count yourself among those ace developers who use this handy operator to work smarter, not harder.
T-SQL User-Defined Functions (Level 300)
User-defined functions in SQL Server are very much like custom methods and properties in .NET languages. At first sight, they seem to be the perfect tool to introduce code encapsulation and reuse in T-SQL. So why is this feature mostly avoided by T-SQL gurus? The reason is performance.
In this session, you will learn how user-defined functions feed the optimizer with misleading and insufficient information, how the optimizer fails to use even what little information it has, and how this can lead to shocking query performance. However, you will also see that there is a way to avoid the problems. With just a little extra effort, you can reap the benefits of code encapsulation and reuse, and still get good performance.
Using Your Brain to Beat SQL Server (Level 500)
Adam Machanic & Thomas Kejser
Thought you did everything right? Indexes are finely tuned. Statistics are fully scanned and updated. Your nice, big server seems to have plenty of power. But you are still not getting the performance you expect from SQL Server. Why is the optimizer failing you? SQL Server's query optimizer, often very good at coming up with decently performing plans, also regularly goes down the wrong path. The problem domain is simply too large and complex for it to adequately cover all cases.
When the going gets tough, your mind is a better machine for the job. So don't trust optimization to a mere piece of code! In this session, you will learn how to recognize various real-world cases where the optimizer fails. You will see why these queries aren't performing as well as they should, and how to work around the optimizer's lack of insight. You will learn to turn your brain into a better query optimizer and make your queries faster than you thought possible.