Sessions

» Presented by Allan Mitchell

Predicting the value of a variable over time is a very common business requirement. FX rates, portfolio values and help desk call rates to name but a very few. In SQL Server 2008 MS introduced a new time series algorithm to Data Mining (ARIMA) to go alongside the existing algorithm (ARTXP). In this session I will show you how to use time based analysis and look at the merits of each algorithm. This is a fast paced but fun session.

» Presented by Sascha Lorenz

This session examines a SSIS Packages creation process, which is focused on maximizing quality and agility while minimizing manual work. This approach to building solutions strives to integrate real world business objects and process as meta data framework in the Data Warehouse development cycle. The creation process uses the ability of SSIS to create packages programmatically with the .net framework.

» Presented by Steve Simon

Over the past few months many financial houses have been severely ‘hit’ by the current slump in the sub prime mortgage market. This has lead to severe losses and considerable client liability. Many firms are now starting to take a ‘serious look’ at the potential utilization of data mining techniques as part of their ongoing efforts to control their risk exposure. This beginner to intermediate level hands-on presentation is aimed at showing the attendee some of the ‘data mining’ prototypes solutions that we are currently developing with SQL Server 2005 / 2008, as a part of our ongoing efforts to manage our risk.

» Presented by Alberto Ferrari and Marco Russo

Data Warehouses have a solid foundation, with different modeling options formalized in several books and whitepapers. Nevertheless, when it comes to implement a Data Warehouse using the SQL Server suite, you have to make several decisions about technology and implementation, which are very specific to SQL Server. These decisions might cause the success or the failure of your Data Warehouse project. This session will introduce you to the SQLBI Methodology. This approach has been developed over the years focusing all the architectural and technological decisions that you have to make when implementing a complete Business Intelligence solution using SQL Server, Integration Services and Analysis Services. This methodology is thought for Data Warehouse of medium-large complexity but, having a reduced overhead compared to the classical Kimball methodology, it is usable on small Data Warehouses too. The great advantages of the SQLBI Methodology are the flexibility in terms of design and maintenance. The draft papers about SQLBI Methodology are downloadable from http://www.sqlbi.com/sqlbimethodology.aspx

» Presented by Andreas Wolter

Due to their popularity, Reporting Services solutions are doomed to keep expanding. There are few ready-made possibilities for attaining a uniform appearance of reports. This presentation will identify various possibilities and their limitations in keeping the design consistent. The icing on the cake will be the central implementation of design alterations and automatic modification of the optics of the reports live in production. The basis for it is Reporting Services 2008 with new options, but most of the techniques are also applicable with Reporting Services 2005.

» Presented by Alberto Ferrari

Slowly Changing Dimensions are common in data marts and, even if their theory is well known, implementing them in real world solution still deserve some attention. ETL code is difficult to write in a correct and optimized, we need to forget the SSIS SCD wizard and go for other advanced techniques. Moreover, when it comes to presenting SCD in an OLAP cube built with SSAS there are several techniques that might bring to a better user experience.

During the session we will show different ways of handling slowly changing dimensions both from the SSIS point of view, describing in detail the patterns to use to load and re-load SCD, and from the SSAS point of view, showing different techniques to present SCD to the user in order to get the best in terms of flexibility and query time.

The last topic of the session is dedicated to some of the most common errors in both modeling and implementation of slowly changing dimension.

» Presented by Markus Raatz

Starting with Reporting Services and Analysis Services 2005, it became possible to create reports based on OLAP cubes with an MDX query designer. Unfortunately, many of the features that Analysis Services provides, such as KPIs, Parent-Child dimensions, DISTINCT COUNT-measures and so on, were not very well supported when being queried using Reporting Services. To the surprise of many developers, this hasn't changed much with SQL Server 2008. In this presentation, we'll give you a few hints on how to overcome these obstacles and let SSRS and SSAS work together happily ever after.

» Presented by Chris Webb

Building aggregations is one of the most important ways of improving query performance in Analysis Services, but simply running the Aggregation Design Wizard doesn't always result in useful aggregations being built. This session shows the steps you need to go through in order to build aggregations that are actually going to be used by the queries that you're running.

» Presented by Alberto Ferrari

Large cubes are often splitted in different partitions, in order to speed up the processing and queries. Although partitions might be created at design time, for very large cubes there is the need to dynamically create partitions at run time, adding new partitions and deleting the old ones. We will show how to interact with the cube using AMO to dynamically create and delete partitions, basing the partitioning on the partitioning of the relational tables on SQL. The final result is an SSIS package that handles the partitioning and processing of the cube through several script tasks.

» Presented by Henk van der Valk & Thomas Kejser

Remember last years’ live demo on stage ? the ETL world record data loading 1 TB of data within 30 minutes?

... This year we will show some more optimization fun and show you the next step; how to plan for, transform and manage datasets of this size.

We will dive into some typical scenarios and show you how you can optimize the throughput significantly: What throughput to expect from an “out of the box “ SQL setup vs a fully optimized one ? Where to start and where to look ? You will learn how to optimize surrogate key lookups, joins on multiple large dimension tables with a staging table and insert data to a final fact table, aggregate fact tables (SUM/GROUP BY) and how to speed up about scan for large fact tables.

We will demo these optimizations live on the new 96 core Unisys ES7000 model 7600R Server with a sneak preview of SQL Server CTP1 utilizing all these nice CPU cores and running on Windows 2008R2.

» Presented by Donald Farmer

This session will outline the major enhancement that SQL server is making for BI in the SQL Server Kilimanjaro release. It will provide a high level overview of the new investments made around Self Service Analytics and Self Service Reporting, including the exciting new Project Gemini, featured in the keynote.

» Presented by Steve Simon

Within the financial industry, rapid and effective decision making is critical to our client relationships. Analytical tools such as OLAP cubes help make this a reality. In many 24 X 7 enterprises, data capture and cube updates occur on an ongoing basis throughout the business day. We are able to achieve this through the innovative utilization of SQL Server Integration Services (SSIS). This intermediate level hands-on presentation will show the attendee how we have utilized Analysis Management Objects (AMO) commands and SSIS to ‘fire’ our OLAP CUBE updating processes. The presentation will discuss the high level concepts behind Analysis Management Objects (and the XMLA that is generated from the commands), the scenarios under which we can benefit from their usage and then examine several practical business oriented implementations of the concept. Topics covered will be the updating of dimensions, measures, partitions, the cube and more. These are but a few of the methods that we employ within our industry, to ensure that we are able to help our clients make the necessary decisions so critical to our industry and to our customer-client relationships.

What the attendee will learn
The attendee will learn innovative ways in which Analysis Management Objects may be utilized in the updating of OLAP cubes. The attendee will learn new ways in which SQL Server Integrations Services can play an integral part in the updating of analytical data. The attendee will see the benefits and drawbacks of our techniques and will be able to apply what he or she has seen, within their daily business environment.

Prerequisites
The attendee should be familiar with SQL Server Analysis Services and have an adequate knowledge of the principles behind analytical data and OLAP cubes.

» Presented by Klaus Aschenbrenner

SQL Server 2008 provides you a lot of new features in the T-SQL programming language. This session makes a closer look at these new T-SQL features and shows you how you can use them in real world applications: • DATE, TIME, DATETIME2, DATEOFFSET Data Types • Spatial, HierarchyID Data Types • FILE STREAM storage attribute • Change Data Capture • Table Valued Constructors • Table Valued Parameters • MERGE T-SQL Statement

» Presented by Klaus Aschenbrenner

The introduction of the GEOGRAPHY data type in SQL Server 2008 provides you a lot of new features for location-aware database applications. This session shows you with a real world scenario how you can enhance a CRM (customer relationship management) application with the GEOGRAPHY data type. With the GEOGRAPHY data type you can do then queries like “give me all customers within a range of 50 km”. To better visualize your query results, this session also shows you how the GEOGRAPHY data type works together with the Virtual Earth platform offered by Microsoft.

» Presented by Meinrad Weiss

SQL Server Data Services = SQL Server 2008 in the cloud? Not at all! The presentation shows the difference between SQL Server Data Services and a traditional relational database like SQL Server. But it is also presented how it is possible to abstract the differences using ADO.Net and LINQ. It begins with a short introduction to the Azure platform and shows possible use cases for the new technology using some concrete examples.

» Presented by Sascha Lorenz

This session presents on overview of techniques to create your own web based OLAP Cube Client. The focus is on asp.net Ajax with the new MS Chart Controls and Silverlight 2.0 and how to use them to create in short time real world solutions.

» Presented by Davide Mauri

Set based solution are what any developer needs to have performance and scalability on his database solution. Unfortunately thinking in set is something that may seems to be difficult at the beginning and as so many developers go for a seems-to-be-easier procedural approach. But this "easier" way leads to lower performance and poor scalability, and, at the end, to a way more complex code, difficult to maintain and bug-prone. In this session we'll see how to use a set-based approach to solve problems, even those problems that for most developers seems solvable only using a procedural approach and than we will compare the two different approach to show the difference in terms of scalability, performance and complexity. You'll be amazed of the performance and maintability boost you can give to your applications!

» Presented by Dejan Sarka

Although temporal data is part of most of business applications databases, most RDBMS’, including SQL Server 2008, do not support it out of the box. However, SQL Server 2008 provides full infrastructure to build your own temporal data support. After introduction of temporal problems, we are going to develop interval CLR data type that supports validity time. Then we are going to discuss what kind of constraints pertain to temporal data, and how we can implement those constraints. We are going to use T-SQL to develop two very useful temporal operators: PACK and UNPACK. In addition, we are going to introduce the proposed sixth normal form that deals with temporal data, and show how you can implement it correctly.

» Presented by Dejan Sarka

XML data type is available from SQL Server 2005, and XML support is even enhanced in SQL Server 2008. However, there are not many DBAs and database developers using all of the XML possibilities in SQL Server. One of the obstacles for broader usage might be XQuery, the XML query language, which is not well known in relational community. In this session, we are going to explain basic XQuery structures and data types. After that, we are going to move to slightly more advanced topics, like using FLWOR expressions. We are going to show how you can iterate through XML nodes and even join two or more XML instances, and finally how you can design the returned XML instance.

» Presented by Andras Belokosztolszki

Stored procedures and functions encapsulate application logic that can be shared and reused in multiple applications. Invoking these stored procedures and functions appears simple until you need to pass structured data, such as arrays of objects, via a fixed number of parameters. For example, adding a sales order to your database via a stored procedure that has a variable number of order items requires either multiple round trips to the server, or a method of serializing the order items into a single parameter. The first solution is expensive because of network latency, but there is no standard approach to do the serialization for the second solution. In this session we will explore the various methods available in the last three versions of SQL Server for passing and handling parameters, including manipulating comma separated lists, sql_variants, XML, temporary tables, communication tables and table valued parameters.

» Presented by Dejan Sunderic

How to avoid and resolve: database round trips, improper indexes, joining too many tables, large resultsets, non-SARG able queries, ad-hock queries, cursors, blocking, large transactions...

» Presented by Simon Sabin

Many things aren’t possible, are complicated to achieve or just don’t perform when using TSQL. Most of these can be solved using CLR in SQL Server In this session we will look at using CLR to implement arrays, regular expressions matching, complex data formatting and caching of data. CLR objects can out perform the TSQL equivalent by orders of magnitude, we will show you how. After this session you will understand when, why and how to use CLR in SQL Server ensuring you are using the right solution for your application.

» Presented by Bill Graziano

When is a query plan reused? When will dynamic SQL give better performance results than stored procedures? What are the benefits of a compiled query plan? When should you turn on forced parameterization? What is the most efficient way to write SQL for reports that might have wildly different WHERE clauses? Why is my stored procedure very slow somtimes? How effecient is the SQL from LINQ? How effecient is the SQL from ORM tools?

» Presented by Bob Ward

One of the biggest requests I receive each year when I speak at PASS is to talk more about the internals of memory for the SQL Server Engine. So this year I will be presenting a very deep technical look at how the SQL Server 2008 Engine manages memory. I'll show you "How it Works" as a means to help you solve possible problems you may encounter involving memory with the SQL Server Engine. Because that is what "debugging" is all about….

Topics included in this presentation include:

• A very deep dive into the SQL Server Memory Architecture
• Busting myths about SQL Server and Memory
• Understanding SQL Server Memory DMVs and Perfmon Counters
• What is 3Gb, PAE, AWE, and VAS?
• Finally understanding the infamous "MemToLeave"
• Operating System Memory Issues like Working Set Trimming
• What has changed for SQL Server 2008?

This talk is not, well… just a "talk" . I always include demonstrations and live use of tools and this talk will not be any different. You should know this will be a very advanced session. I'm never one afraid to bring up the Windows Debugger in a session like this to demonstrate a particular point. Having said that, if you attend this talk, you will walk away with a better understanding of how memory works inside the SQL Server Engine. I believe this knowledge can help you better manage a SQL Server installation, resolve memory related issues, and develop better applications for SQL Server.

» Presented by Christian Bolton

SQL Server is obviously heavily dependent on storage but as requirements and technology become more complex the DBA role starts to become distant from the implementation as people in dedicated storage roles get involved due to the complexity and tell them they "don't need to worry about the details". Having a storage "black box" is great but it can lead to assumptions on both sides about configuration responsibilities that can end up not getting done.

In this presentation I'm going to cover some basic hard disk concepts like SATA and SCSI as well as look at RAID level performance differences, Direct Attached Storage versus SAN's, performance tuning tips like HBA Queue Depth and Sector Alignment and some of the most popular Windows and SQL Server storage configuration options like Allocation Unit size and database file placement.

» Presented by Bodo Michael Danitz

In the past, a SQL Server running a process that was sucking all resources from the box, thus slowing down all other users, had only one chance to survive: A DBA acting as the "process terminator". With SQL Server 2008, the DBA has been given a new powerful instrument to reign over the computer's resources and processes: The Resource Governor. What can you do with it, how, and not less important: What NOT?

» Presented by Thomas Grohser

"We need a server for that new application..." how many times have you heard that request. If you dare to ask which workload too expect you almost never get an usefull answer. In this sesstion I will present the techniques I use to predict the workload of applications and how to dimension servers for it. I will also share the expirience how much performance increase you can expect from scaling up your servers (covering the full range from 1 to 64 CPU systems, 1 GB to 1 TB memory and 1 to 2000+ disk drives) and how to compare servers or configurations.

» Presented by Steffen Krause

You need to know what changed in your database tables? SQL Server 2008 provides two new features for you that make change detection easy: Change Data Capture (CDC) and Change Tracking. In this session, we will compare the two options and explain when to use what. Then we go into details on configuration, performance and best practices of Change Data Capture that we learned while creating the whitepaper “Tuning the Performance of Change Data Capture in SQL Server 2008”.

» Presented by Brad McGehee

Many DBAs take routine database maintenance for granted. What they don’t understand is that the cumulative effect of poor database maintenance can significantly hurt performance. In this session, you will learn the key things all DBAs need to know in order to maintain their databases at peak performance, including:

• Physical File Defragmentation
• Database and Log File Management
• tempdb Maintenance
• msdb Maintenance
• Index Maintenance
• Statistics Maintenance
• Data Corruption Detection
• Database and Log File Protection
• Database Maintenance Monitoring

» Presented by Ross Mistry

DBAs and IT Decision Makers typically feel compelled to choose the right technologies to achieve high availability when designing a SQL Server infrastructure. Some of the challenges they face are illustrated in their questions - Should I use Failover Clustering, Database Mirroring, Log Shipping or Replication? Which alternative provides the best protection? How does Windows Server 2008 impact my decision? Can I combine these technologies for maximum protection? What is the difference between single instance and multiple instance failover clustering? This session will alleviate pressure and concerns by providing DBAs and IT Decision Makers with best practices and tips on how to design and choose the right SQL Server 2008 high availability alternative to meet their organizations HA and disaster recovery needs. Technologies covered include Failover Clustering on Windows Server 2008, Database Mirroring, Log Shipping and Peer-To-Peer Replication.

» Presented by Frederik Vandeputte

A SQL Server DBA's job has changed dramatically over last few years. In the era of data explosion DBAs are managing larger amounts of data and more servers than ever before. SQL Server has evolved from a relational database into full featured data platform. Knowing all the ins and outs became a very challenging job.

In this demo driven session Frederik will show you how features like compression, policy based management, resource governor, management data warehouse and others will make your job as a DBA a lot easier. If you are managing a large and multi server environment make sure to attend this session and learn how SQL Server 2008 can help you take your life back.

» Presented by Justin Langford

Failover clustering is a popular technology to improve SQL Server availability and this session will cover changes and new features of failover clustering in Windows Server 2008 and SQL Server 2008. We’ll consider how the new features improve the ways we design, build and maintain Failover Clusters in Windows Server 2008. In terms of design we’ll look at the new quorum models in Windows Server 2008 and deployment scenarios for each quorum configuration. We’ll look at changes to the cluster build process and methods of installing SQL Server on a failover cluster. Additionally we’ll consider maintaining a Windows Server 2008 cluster with tasks such as Adding/ removing cluster nodes and installing SQL Server service packs.

» Presented by Brad McGehee

DBAs often don't spend a lot of time watching tempdb performance. They think of it as a black box that takes care of itself. What many DBAs don't fully realize is how tempdb performance can affect SQL Server's overall performance. In this session, we will cover:

• How tempdb is used
• tempdb internals
• How to monitor tempdb
• How to optimize tempdb

» Presented by Thomas Grohser

See how you can archive zero data loss, high availability and scale ability by combining backup/restore, log shipping, database mirroring and replication. Learn how to overcome some limitations of database mirroring (e.g. replication, login/users, SQL Server Agent jobs and more). The session starts with the establishment of an SLA (service level agreement) shows step by step how to build the infrastructure and how to implement the technologies and ends with a demo that combines all the technologies discussed before.