SQLSaturday #84 - Kalamazoo 2011 9/17/2011 12:00:00 AM SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. #sqlsat84 Kalamazoo Valley Community College - Texas Corners Campus Registration at Student Commons Area Kalamazoo MI 49009 http://www.sqlsaturday.com/images/sqlsaturday_gb_icon.png http://www.sqlsaturday.com/images/sqlsaturday_gb_image.png Conference Information general True 1 default Maps maps False 2 default Schedule of Events schedule True 3 default Sponsors List sponsorslist True 4 default Twitter twitter True 5 default Todo List todo False 6 default My Schedule myschedule True 7 default Feedback feedback False 8 default Key Events keyevents False 9 default Share share True 10 default 1327 BlueGranite, Inc. Platinum http://www.blue-granite.com http://www.blue-granite.com/bg_bi_170.png 60 170 1349 Idera Platinum http://www.idera.com http://www.idera.com/images/promos/Idera_60.jpg 60 170 1249 Confio Software Gold Sponsor http://www.confio.com http://www.confio.com/images/assets/ignite_confio_logo.gif 60 170 1447 Red Gate Software Gold Sponsor http://www.red-gate.com/ http://www.sqlsaturday.com/files/325ad8bc-31c3-4494-8352-7254149ffe62.jpg 60 170 1333 Fusion-io Silver Sponsor http://www.fusionio.com/ http://www.fusionio.com/images/fusionio-logo.gif 60 170 1419 SQL Sentry Silver Sponsor http://www.sqlsentry.net http://www.sqlsentry.net/images/ext/SQLSentryLogo148.jpg 60 170 1507 New Horizons Computer Learning Centers Silver Sponsor http://www.newhorizons.com/grandrapids http://www.newhorizons.com/LocalWeb/images/header_logo.gif 60 170 1247 CozyRoc Bronze Sponsor http://www.cozyroc.com/ http://www.cozyroc.com/sites/default/files/down/cozyroc-big.png 60 170 1543 TechSmith Bronze Sponsor http://www.techsmith.com http://www.sqlsaturday.com/files/0b372557-4734-4006-ad4f-ba5294dc25a4.jpg 60 170 1517 SQL Solutions Group Personal Sponsor http://www.sqlsolutionsgroup.com http://www.sqlsolutionsgroup.com/images/logo2.jpg 40 100 1266 ShannonLowder.com Blog Sponsor http://ShannonLowder.com http://shannonlowder.com/wp-content/themes/vigilance_pro/images/lowder-wp-logo.png 40 100 1296 SQLvariant Blog Sponsor http://sqlvariant.com/wordpress http://sqlvariant.com/BlogSupport/Images/SQLvariant_Logo.png 40 100 1541 Yanni Robel Blog Sponsor http://yannirobel.com http://yannirobel.com/wp-content/uploads/2011/04/logo.png 40 100 4462 tim costello Track 4 4380 Synchronous and asynchronous components, memory buffers, data type choices, Oh My! Tuning the data flow is all about choices. In this session we'll walk through some of the choices that can give you the biggest performance boosts in your ssis data flow. Together we'll work through the differences between synchronous and asynchronous components. We'll highlight components that should be avoided and explore alternates that can help us create the fastest data flow possible. 9/17/2011 4:00:00 PM 9/17/2011 5:00:00 PM 4483 Charles Otten Track 4 4380 A one hour session showing how SQL Server creates and manages indexes using a B-Tree. This will include discussions and demonstrations on clustered vs non-clustered indexes as well as how to build the best performing index solutions. 9/17/2011 9:00:00 AM 9/17/2011 10:00:00 AM 4506 Tom Groszko Track 4 4380 Change Tracking (the little sister to Change Data Capture CDC) will be demonstrated in detail in this session. This SQL server feature is available in Standard Editions of SQL server so everybody can use it. A complete example from server settings to each T-SQL statement will be explored. 9/17/2011 1:30:00 PM 9/17/2011 2:30:00 PM 4518 Thomas LaRock Forum Room 4230 Many database professional have little to no understanding about how to use wait events as part of an overall performance tuning process. And yet wait events are the secret sauce that many leading experts have been using for years. Attend this talk and you will gain an understanding of the SQLOS execution model, an understanding of how to use DMVs to find out what your queries are waiting for, and why using wait events could be the best thing to happen for your career as a DBA. 9/17/2011 2:45:00 PM 9/17/2011 3:45:00 PM 4526 Jeremiah Peschka Theatre 4240 Refactoring SQL is not like refactoring application code. This talk will cover proven SQL refactoring techniques that will help you identify where performance gains can be made, apply quick fixes, improve readability, and help you quickly locate places to make sweeping performance improvements. Jeremiah Peschka has years of hands on experience tuning SQL applications for performance, throughput, and concurrency. 9/17/2011 11:30:00 AM 9/17/2011 12:30:00 PM 4528 Jeremiah Peschka Track 5 4540 Computing running totals, averages by state or group, and other complex aggregations are not easy to implement in many relational databases. Thankfully, many relational databases include support for window functions. Window functions are an extension to the ANSI SQL standard. More than just another part of SQL, window functions enable complex ranking, ordering, and other aggregations over a subset of rows in a query. Understanding the capabilities of window functions lets you extend application functionality, build rich and complex queries, and reduce overhead in the application tier. 9/17/2011 9:00:00 AM 9/17/2011 10:00:00 AM 4529 Kendra Little Track 4 4380 Know those apps that need a complete rewrite from scratch, but management won't listen to you? Kendra Little has been there and she's figured out how to frame the big conversations. She'll give you seven strategies to improve scale and change application architecture, and explain concepts from sharding to caching along the way. You'll get a toolkit for each conversation: how to gather the right supporting data from the environment, who to talk to, and how to speak the right language to drive each change. 9/17/2011 11:30:00 AM 9/17/2011 12:30:00 PM 4531 Brent Ozar Theatre 4240 Storage Area Networks are like a bad black box: we put data in, we get data out, but we have no idea what's going on inside. Why is everything so slow and yet so expensive? Is it a SAN problem or a SQL problem? Learn what's happening inside the black box from Brent Ozar, a Microsoft Certified Master and former SAN administrator, who will demystify storage. 9/17/2011 10:15:00 AM 9/17/2011 11:15:00 AM 4533 Brent Ozar Forum Room 4230 Consultants work with technology, but they also deal with a lot of politics. Brent Ozar was a production DBA for years and sat through thousands of meetings. Now that he’s a consultant, he’s figured out how consultants handle political bombs during meetings, and he wants to share his favorite consulting lines with you. You’ll learn how to deal with dangerous developers, miserable managers, and cussing customers using simple lines that won’t blow up in your face. 9/17/2011 4:00:00 PM 9/17/2011 5:00:00 PM 4537 Andy Leonard Track 3 4370 This highly-interactive, demo-intense presentation is for beginners and developers just getting started with SSIS. Attend and learn how to build SSIS packages from the ground up. 9/17/2011 11:30:00 AM 9/17/2011 12:30:00 PM 4538 Andy Leonard Forum Room 4230 In this “demo-tastic” presentation, SSIS trainer, author, and consultant Andy Leonard explains the what, why, and how of an SSIS framework that delivers metadata-driven package execution, connections management, and centralizes logging. Key takeaways: 1) Developers can migrate packages from Development, through their lifecycle, to Production without editing SSIS Connection Managers properties. 2) A metadata-driven approach to SSIS package execution. 3) Demonstration of a centralized logging reporting application. 9/17/2011 10:15:00 AM 9/17/2011 11:15:00 AM 4712 Jason Strate Forum Room 4230 There are many ways to performance monitor your SQL Server environment. In this session we’ll review Extended Events, which is one of the newer SQL Server monitoring platforms. Learn the ins and outs of how to get detailed information on the errors and events that occur within SQL Server and how to dig into the information. With a few T-SQL statements, issues that could take weeks to research can be investigated in minutes. 9/17/2011 9:00:00 AM 9/17/2011 10:00:00 AM 4756 Jason Strate Theatre 4240 Do you know when your users are using the production login accounts from their workstations? Are you aware of changes being made to the development servers that may impact future deployments? Has the new Junior DBA start giving out access like candy? As DBAs, we are responsible for knowing and acting on all of this and much more. Fortunately, SQL Server provides a number of features that you can use to monitor and track user activity. In this session, we’ll look at these features and demonstrate how you can use them to the extent that your users find you creepy! 9/17/2011 4:00:00 PM 9/17/2011 5:00:00 PM 4791 Allen White Theatre 4240 The Policy-Based Management feature in SQL Server 2008 provides a great way to ensure your systems are configured consistently and correctly, but it can be tedious to implement on each server in your environment. PowerShell scripts allow you to automate the implementation of your policies so you can focus on more important problems. This session will walk you through how PBM works, how to define your policies in PowerShell, and how to set up Agent jobs to evaluate those policies regularly to let you know when you need to take action. 9/17/2011 1:30:00 PM 9/17/2011 2:30:00 PM 4964 Eddie Wuerch Track 5 4540 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 otherwise-hidden issues that can kill server performance. After this session, you will be able to spot tempdb performance issues, drill into the causes, and take the steps necessary to solve them. 9/17/2011 11:30:00 AM 9/17/2011 12:30:00 PM 4982 Aaron Bertrand Theatre 4240 SQL Server 'Denali' delivers many highly anticipated features that some feel are long overdue. In this session you will get a first-hand look at many of these new features relating to the engine (contained databases, AlwaysOn, and T-SQL enhancements), client tools (Management Studio, Books Online, and 'Juneau'), and changes to setup and system requirements. You'll also learn about some of the features that will not be making it into Denali, and deprecated functionality that will stop working in this or the next release. 9/17/2011 2:45:00 PM 9/17/2011 3:45:00 PM 4983 Aaron Bertrand Track 5 4540 Bad habits: we all have them. SELECT * is the obvious one; but in this session you will learn about various other habits and why they can be bad for performance or maintainability. Come learn how these habits develop, what kind of problems they can lead to, and how you can avoid them. Eliminating these habits will lead to more efficient code, a more productive work environment, and - in a lot of cases - both. 9/17/2011 10:15:00 AM 9/17/2011 11:15:00 AM 5055 Thomas LaRock Theatre 4240 When moving databases to a virtual environment the performance metrics DBAs typically use to troubleshoot performance issues such as O/S metrics, storage configurations, CPU allocation and more become unreliable. DBAs no longer have a clear, reliable view of the factors impacting database performance. Understanding the difference between the physical and virtual server environment adds a new dimension to the DBA tasks. This presentation describes the changes that DBAs need to make in their performance and monitoring practices. 9/17/2011 9:00:00 AM 9/17/2011 10:00:00 AM 5056 Jeff Moden Forum Room 4230 Don’t let the “Intermediate” rating scare you “Beginners” away. This “Black Arts” session is for anyone that uses T-SQL. There are dozens of things we do in SQL that require some type of iteration. 'Iteration' means 'counters' and 'loops' to most people and recursion to others. To those well versed in the techniques of 'Set-based' programming, it means some form of a 'Numbers' or 'Tally' Table, instead. SQL Server MVP Jeff Moden takes the mystery out of how this wonderfully compact tool works and how it easily replaces many types of loops. This session has been updated to include additional information about what “Pseudo-Cursors” are and how they work as well discovering why “Recursive CTE’s” can be your worst enemy. 9/17/2011 11:30:00 AM 9/17/2011 12:30:00 PM 5057 Jeff Moden Track 3 4370 Don’t let the “Intermediate” rating scare you “Beginners” away. This “Black Arts” session is for anyone that uses T-SQL. SQL Server MVP Jeff Moden demonstrates and explains simple methods for converting gobs of “vertical data” into horizontal numeric and textual data by date or other key using Cross Tabs and Pivots. This session includes the “Black Arts” method of “Pre-Aggregation” for incredibly high performance and how to dynamically and automatically produce reports based on a date. The methods learned in this session can be used to provide “dynamically pivoted” result sets to SSRS, Crystal Reports, and other reporting software. 9/17/2011 1:30:00 PM 9/17/2011 2:30:00 PM 5160 Wendy Pastrick Track 5 4540 Indexes make queries run faster, right? Not always, no. And finding the right indexes for your needs is often more of an Art than a Science. Attend this session as we take a visual tour of how indexes are laid out and the way data is accessed when indexes are utilized. We will discuss clustered indexes as well as non-clustered indexes and also when to use indexed views along with some other variations. 9/17/2011 2:45:00 PM 9/17/2011 3:45:00 PM 5171 Wendy Pastrick Track 4 4380 Issue a 'SELECT {data} FROM {myTable}' statement and users need to trust the data returned is accurate. What happens when multiple processes access the same data? What happens further if one of those process is an UPDATE or even a DELETE statement? How about if both processes will be updating that data? Understanding Isolation in SQL Server is paramount in ensuring data integrity in your system. We will cover the different levels of isolation and how they impact what a user views when accessing the data at a given point-in-time. 9/17/2011 10:15:00 AM 9/17/2011 11:15:00 AM 5193 Erin Stellato Track 3 4370 The Query Optimizer uses statistics to decide how to execute a query. If statistics are out of date, improperly sampled or simply don’t exist, then you could unintentionally deceive the Optimizer. Accurate statistics can help avoid poor query plans, so if you’re not familiar with how to read a histogram, the various options for updating statistics or want to know why sample size does matter, this session is for you! We will also delve into what happens when a value doesn’t exist in a statistic, discuss methods to manage statistics for volatile tables and examine the challenges that exist with statistics on partitioned tables. Expect a session filled with demos and real-world examples that will create a foundation of statistics knowledge. 9/17/2011 2:45:00 PM 9/17/2011 3:45:00 PM 5204 Doug Lane Track 5 4540 Do you remember what it was like to fall in love? Wasn't it wonderful? Lately it just hasn't been the same. You've been stuck in the same old routine, doing the same old charts and tables. Maybe Reporting Services doesn’t tell you what’s wrong. Maybe you're even thinking about seeing other reporting products. Maybe you and RS just weren't meant to be together. In this session, Doug will re-ignite your passion for RS by showing you how to design for better performance, explaining what RS has been trying to tell you through logs (but you haven't been listening), and demonstrating some new -- and easy -- tricks you may not have known RS can do. After this session, you'll want to spend some time alone with your new old flame. 9/17/2011 4:00:00 PM 9/17/2011 5:00:00 PM 5260 Tim Ford Forum Room 4230 Taking the periodic table of elements into consideration I've created The Periodic Table of Dynamic Management Objects as a reference tool for these functions and views that have become so critical for today's SQL Server DBA to performance tune and gain metadata insights into their various SQL instances. In navigating the table we will examine key DMVs and DMFs of interest: requests, sessions, wait stats, indexes, system information will all be covered. We will also delve into how these DMOs - just like their elemental counterparts - can be combined to yeild interesting insights into your servers. We will also look at how things can go awry as well! Like mixing vinegar and baking soda, sometimes combining DMOs can cause a big mess too! 9/17/2011 1:30:00 PM 9/17/2011 2:30:00 PM 5267 Craig Purnell Track 4 4380 NoSQL (or, more correctly NoRel) is a moniker applied to a family of non-relational software systems for storing data that powers some of the largest sites on the Internet. Amazingly enough, non-relational database technology has been around a very long time. This session will include a high level discussion of some of the pros and cons of this technology compared to traditional systems like Oracle and SQL Server; as well as a survey of the most popular 'NoSQL' platforms and who uses them. We will also cover a brief history of relational and non-relational databases as it is germane to the topic 9/17/2011 2:45:00 PM 9/17/2011 3:45:00 PM 5271 David Levy Track 3 4370 The days of hiding amongst rows of servers, focusing purely on operational tasks are quickly coming to an end. DBAs that want to hide in their foxhole, focusing purely on operational tasks are quickly being outsourced. This session will cover the steps to becoming a highly valued leader in your organization by building relationships, automating mundane tasks, standardizing processes, and making education a priority. 9/17/2011 10:15:00 AM 9/17/2011 11:15:00 AM 5284 Rob Kerr Track 3 4370 This session focuses on SQLServer-based BI solution delivery. Capabilities discussed will include Excel Services, PerformancePoint Services, Business Connectivity Services, Reporting Services Integrated Mode, and PowerPivot. This session will emphasize real-world best practices in technology selection, solution design, security, and deployment. 9/17/2011 9:00:00 AM 9/17/2011 10:00:00 AM 5303 Scott Schommer Track 5 4540 The future of BI is self-service reporting and PowerPivot is the conduit to that goal. We will explore how to use the PowerPivot add-in for Excel 2010 to tie different data sources together and deliver analytics. In this session you will learn how to use PowerPivot (within the familiar interface of Excel) to combine different data sources such as Analysis Services cubes, TSQL queries, Stored Procedures, Excel spreadsheets, flat files and data from the web to create basic PivotTables and PivotCharts in Excel. The data these new spreadsheets overcomes the 1,048,576 row limitation in Excel 2007/2010, contains refreshable data sources and has excellent data compression and lighting fast speed thanks to the utilizati 9/17/2011 1:30:00 PM 9/17/2011 2:30:00 PM 5784 Matthew Mace Theatre 4240 In this Lunch session see how BlueGranite – a Microsoft’s 2011 Partner of the Year award winner and top industry partner – has helped customers leverage the Microsoft BI platform to help increase profitability through deeper business analysis. Through an end-users eyes, learn how Microsoft Business Intelligence and SQL Server 2008 R2 can provide an up-to-the-minute view of the health of your business. See real world-examples and use cases from a live solution; including the use of SSAS OLAP cubes, Microsoft Excel, PerformancePoint Services, and mobile platforms. 9/17/2011 12:30:00 PM 9/17/2011 1:30:00 PM 5785 Shelly Noll Forum Room 4230 We often hear about the advantages of diversity in the workforce, but it can be difficult to relate to one another when we all have different backgrounds and experiences. Please join our panelists Kendra Little, Wendy Pastrick, Yanni Robel and Erin Stellato as they explore lessons learned as women working in information technology. They will discuss ways they have been able to leverage their differences to compliment a team and share communication techniques they’ve acquired from working in diverse environments. All attendees are welcome. 9/17/2011 12:30:00 PM 9/17/2011 1:30:00 PM 5786 Charles Otten Track 3 4370 There are many ways to solve problems with SQL Server, and the performance of the various solutions can vary dramatically. This session will focus on specific server settings and T-SQL practices to create efficient running solutions. We’ll discuss how functions can hurt and help performance, approaches to removing cursors in favor of set based approaches, tricks to find missing indexes, how to determine the actual cost of your queries and much more. 9/17/2011 4:00:00 PM 9/17/2011 5:00:00 PM