Indexing is by far the most important aspect to database performance and health. But, do you have the right indexes? Do you have too few, or too many? And, are the indexes the RIGHT indexes? In this workshop we'll cover what makes an index useful and how to create the RIGHT indexes for a variety of different problem scenarios. Primary topics covered: index internals, indexing strategies and lots of insight into whether or not your strategies are working! If you want better performance, better cache utilization, easier maintenance - you need the RIGHT indexes. To create a more effective indexing strategy, this is the place to be—even if you cannot change your schema!

There's a common misconception that database maintenance isn't critical, or is something best left to the DBA (if you have one). It's not true! If your application includes a SQL Server database then neglecting database maintenance can lead to poor performance, data loss, and application down-time. This session will give you a good understanding of the top areas of database maintenance to watch out for and some simple guidelines you can follow to ensure your databases don't cause avoidable problems for you or your customers.

If your application includes a SQL Server database then you've had to choose some data types and design some tables. But how can you tell whether the design you chose helps or hinders SQL Server as it stores and processes your data? Paul used to be responsible for the SQL Server Storage Engine and in this session he'll explain how SQL Server stores and retrieves your data and what choices you can make to allow SQL Server to perform well. This session will give you the insight you need to make correct design choices!

Understanding how stored procedures are created, stored, compiled/optimized and executed is critical for any application that uses them. It might seem simple that a created stored procedure would be compiled and that subsequent executions would leverage the procedure's compiled plan but the compiled (and optimized) plan may not be good for ALL executions. In this session I will show you how to know when stored procedures plans should not be saved as well as your MANY options that exist to achieve better and more consistent performance.

• Records, pages, allocation bitmaps

• What is logging?
• How recovery works
• How the transaction log is structured
• Recovery models

• Understanding data types
• Character data, overflow and LOB
• LOB considerations
• Date and time data types
• Heterogeneous data types
• Sparse columns
• Application inconsistencies in types

• Concepts/motivating factors in Partitioning
• SQL Server 2000+ Partitioned Views
• SQL Server 2005+ Partitioned Tables
• Partitioning Design Techniques Combined
• Partitioned and non-partitioned Indexes
• Implementing the Sliding Window Scenario

• Data Access Patterns
• Covering
• Using INCLUDE (SQL Server 2005+)
• Using Filters (SQL Server 2008+)

• Indexing for AND
• Indexing for OR
• Indexing for Joins
• Indexing for Aggregates
• Indexed Views (Overview – detailed if time permits)

• Database layout considerations
• Growing and shrinking
• Instant initialization

• Fragmentation and its causes
• Detecting and removing

• Types of backups
• Backup strategy
• How restore works
• Restore options

• Page protection options
• IO errors and monitoring
• DBCC CHECKDB
• Best practices
• Interpreting output
• Recovering from corruption

Whether you’re designing from the ground up or tuning a third-party application, this workshop will show you ways to tune even if you can’t change the code or schema. We’ll cover designing for performance, indexing for performance, and maintaining performance. These areas sound very generic but we’ll show you the top things you can put into production straight away to give you the biggest bang for your buck. Topics will include effective table design, vertical and horizontal partitioning, data type best practices, clustering key choice, nonclustered index strategies, fragmentation analysis, index and statistics maintenance, and more!

In this workshop, we’ll take a holistic look at the BI features in the latest version of SQL Server by reviewing the architecture requirements, exploring the implications for existing BI applications, and introducing new capabilities that support the transformation of data into business insight. We’ll discuss the scalable data warehousing capabilities supported by “Madison”, master data management with Master Data Services, and complex event processing with StreamInsight. We’ll also explore the new self-service reporting and analysis features in Reporting Services and Gemini. Of course, you’ll see demonstrations of the new features in this workshop, but you’ll also have a chance to ask lots of questions and to get a look “under the hood” to better understand what you’ll need to do to get the R2 BI features up and running properly. You’ll also learn how to prepare your data environment to leverage these features and how best to manage the user experience.

In a database-centric application, whether the application performs well is usually highly dependent on whether the database code performs well. At the same time, we’re moving from traditional data access techniques like ODBC and ADO.NET to database code generation and object-relational mapping layers such as the ADO.NET Entity Framework. This gives application developers a choice of putting database code in stored procedures, using SQL code in applications, or using LINQ and having your SQL generated for you. In this one-day workshop we’ll cover how knowing how the SQL query processor, plan caching, and parameter processing works can vastly change your application’s database performance. You have control of this performance no matter how you choose to access SQL Server and covers performance issues both inside and outside the server. Some of the topics we’ll cover include:

• Does using LINQ relieve the developer of database performance responsibility?
• Does writing stored procedures guarantee good performance? And are all stored procedures equally good performance-wise?
• Are stored procedures always faster than dynamic SQL?
• Is SQLCLR a good way to write stored procedures?

It's amazing how many myths and misconceptions have sprung up and persisted over the years about SQL Server - after 10 years helping people out on forums, newsgroups, and customer engagements, Paul's heard it all. Are there really non-logged operations? Can interrupting shrinks or rebuilds cause corruption? Can you override the server's MAXDOP setting? Will the server always do a table-scan to get a row count? These are just a few of many, many myths that Paul will debunk in this fast-paced session on how SQL Server operates and should be managed and maintained.

Some of the most misunderstood parts of SQL Server are its logging and recovery mechanisms. The fact that the transaction log exists and can cause problems if not managed correctly seems to confound many DBAs. Why is it possible for the transaction log to grow unbounded? Why does it sometimes take so long for the database to come online after a system crash? Why can't logging be turned off completely? Why can't I recover my database properly? Just what is the transaction log and why is it there? In this in-depth session Paul will unravel the mysteries of the transaction log - it's architecture and behavior under different recovery models - as well as how logging and recovery work in SQL Server. This is essential knowledge you need for understanding how backup, restore, log-shipping, database mirroring, and other technologies work.

In many situations, database backups are critical for recovering from a disaster, but there are lots of misconceptions about how backups work and what a good backup strategy is. The purpose of taking backups is, of course, to be able to restore them at some point - but that can sometimes be easier said than done, depending on what you want to be able to restore. In this in-depth session, Paul will explain how the three most common types of backups work and how they can be combined into an effective backup strategy. Paul will also cover how restore works, the three recovery options for restoring a backup, and a variety of useful examples. You don't want to find out that your backups are unusable when disaster strikes - this session provides the knowledge you need to make sure you can recover. (It is recommended that you attend the Logging and Recovery session before this one.)

SQL Server 2008 has a variety of features that aid in supporting multiple servers – from Centralized Management Servers to Multi-server Script Execution to Policy-based Management and Performance Data Collection. Attend this session and see how you can easily execute scripts on multiple servers, create policies that verify (and possibly set) – your most critical database settings with only a few clicks. And, much of this can be done against your existing and earlier versions of SQL Server (limitations and restrictions will be covered). Finally, collecting performance data from SQL Server 2008 servers and storing this data in a management data warehouse lets you better analyze trends and tune the most expensive queries through interactive click-through reports (the reports are similar to Performance Dashboard). The combination of all of these options means that your SQL Server 2008 servers have the most capabilities but even having a SQL Server 2008 management server to help manage your existing (2000/2005) servers is a must. This session will be demo packed and filled with practical solutions!

Using indexes to "cover a query" is one of the more important tuning strategies possible in SQL Server. So much so that many of the best indexing performance features target specifically this - covering. We'll look at indexed views (added/improved in SQL Server 7.0 and 2000), INCLUDE (added in SQL Server 2005) and filtered indexes (added in SQL Server 2008). In this session, we'll discuss the pros and cons of covering, the different strategies possible to achieve it - as well as when it's a good idea to consider covering and when it's a must!

SQL Server uses a cost-based optimizer which means that each query/batch submitted is evaluated in a variety of ways to determine the execution plan. If a query needs to process 10 rows the plan might be quite different than a query that must process millions of rows... but how does SQL Server know how many rows to process if all it knows is your query? The answer is statistics. Statistics are the basis of estimation for selectivity and join density - they are the most important piece of information that the optimizer has to help it do a better job. So, how do you make sure that your statistics are good, accurate, and up-to-date? Where are these stored? What do they look like? Are there cases where statistics just aren't good enough? How do you deal with these situations? Come to my session and find out!

Since the invention of the GUID (April 1, 1995) my life as a DBA and "tuner" has been busy. I've seen databases designed with GUID keys run fairly well with small workloads completely fall over and fail because they just cannot scale. And, I know why GUIDs are chosen - it simplifies the handling of parent/child rows in your batches so you can reduce round-trips or avoid dealing with identity values. And, yes, sometimes it's even for distributed databases and/or security that GUIDs are chosen. I'm not entirely against ever using a GUID but overusing and abusing GUIDs just has to be stopped! Please, please, please - database developers - come to this session so I can give you better solutions and explanations on how to deal with your parent/child rows, round-trips and clustering keys!

Now a conference staple, Kimberly and Paul come loaded with slides and highlights from all of their sessions of the conference. If you don't ask questions, they're start adding to the content discussed previously by diving deeper and tying in discussions they've had in breaks, after their sessions and with your questions. This is really YOUR time to ask questions! This session seems unfocused but is often not only informative but highly interactive and fun.

Analysis Services stored procedures are nothing like the stored procedures you create for your SQL Server databases. Not much is written about this feature, so you might wonder whether you need to bother with them at all. Put simply, you can simplify your MDX queries, combine cube data with relational data, or personalize calculations for specific users - just to name a few possibilities with a little C# (or VB) .NET code and MDX. Come to this session to learn some practical ways you can take advantage of Analysis Services stored procedures.

Reporting Services comes with lots of new features in Kilimanjaro, including the Shared Component Library, map controls, data feeds, lookup functions, and other enhancements. In this session, you'll learn the tips and tricks necessary to use these features to full advantage in your report development projects.

Sure, that Gemini application look pretty in the demo, but its real beauty lies in the power of the calculations that give meaning to raw data. DAX is the new expression language that provides this power - taking familiar Excel functions to new levels, adding new functions to perform tasks never before possible in Excel, and allowing you to add a business logic without knowing a thing about dimensions and measures. In this session, you'll learn how best to get started with DAX so that you can take full advantage of Gemini's power.

The Kilimanjaro release includes a new feature, called Master Data Services, to support master data management (MDM). You might be wondering, "If I'm already cleansing and conforming heterogenous data in my centralized data warehouse, why do I need Master Data Services?" Or conversely, "if I implement Master Data Services, do I really need a data warehouse?" Come to this session to learn the answers to these questions and how to use Master Data Services to integrate MDM into your data warehouse architecture.

This session covers the performance implications of using two new Microsoft data access frameworks. I’ll look at the good, the bad, and the ugly aspects of code generation with the frameworks using SQL Profiler, plan cache monitoring, and query plans. I’ll cover what the frameworks bring to the table when compared to using T-SQL stored procedures or using dynamic SQL and the obvious benefits to the application programmer. T-SQL code generation enhancements in .NET 4.0 versions of Entity Framework and Linq to SQL will also be explored.

SQL Server 2008 R2 (Kilimanjaro release) will put spatial data support (including Bing Maps support) inside SQL Server Reporting Services, extending the data type’s usefulness to include maps and direct integration. And the spatial data types are even supported in the MapPoint application through a custom add-in. I’ll also show how to use SQL Server Spatial Data with Bing Maps directly, walking through a simple code example of how it’s done. Finally show how you can write your own extensions to the spatial library with examples.

This session looks at support in SQL Server for hierarchical data, comparing and contrasting the methods of maintenance and query. I’ll focus on SQL Server 2008’s new hierarchyID data type, with a look at performance and ease-of-use compared with the adjacency model, nested set model, and XML storage and query.

While typical relational database applications are query-driven, event-driven applications have become increasingly important. Event-driven applications are characterized by high event data rates, standing queries, and millisecond latency requirements requiring the data to be queried (and possibly summarized) while it’s in-flight. These requirements are shared by various scenarios across verticals such as manufacturing, oil and gas, power utilities, financial services as well as IT and data center monitoring. Microsoft’s StreamInsight product (now in CTP) uses an event provider model to produce, process, and consume streams of data. A LINQ query provider gives you the ability to query and morph data streams in-flight.

Although SQL Server Azure database applications (database in the cloud) and Data Tier Applications (an application management feature in SQL Server 2008 R2) are totally different features, they are both implementations of non-traditional ways to manage applications. The set of SQL Server functionality that each one offers is amazingly similar. This talk will show how to take a “traditional” application in a SQL Server database and either deploy it to the cloud or deploy it as a DAC application for local consumption and easier application deployment mobility.