Karen discusses five (plus a few more) database design blunders with tips on how to avoid them. Audience members will also be able to contribute their war stories of design fails, WTHs and D'ohs.
In this session, we are going to explain and test different DW features in SQL Server 2012, including star join optimization through bitmap filters, table partitioning, window functions, columnstore indices and more.
An in-depth dive into physical table structures
The Microsoft BI stack has a number of tools for data visualization - Excel, Power View, native Reporting Services, and Performance Point. Come see each visualization applied to the new tabular model in Analysis Services.
Ever deployed an Analysis Services cube that worked perfectly well with one user on the development server, only to find that it doesn’t meet the required volumes of user concurrency?
This session looks at some of the different methods available to load slowly changing dimension data into a data warehouse, and compares the relative performance given different data scenarios and traditional storage compared with FusionIO
Learn how to monitor Analysis Services with SQL Sentry Performance Advisor. Get tips on best practices, monitoring counters and options plus improve your understanding of how Analysis Services uses memory and where it differs from SQL Server.
This session will do a brief overview of Analysis Services 2012 performance topics, and drill into some common methods for investigating performance issues. The talk will be adjusted based on the audience interests.
Join X-IO to learn how customers like Redknee and Temenos are combining SQL Server 2012 with X-IO intelligent storage to generate higher performance at significantly less cost. Learn why storage is on the verge of a revolution.
In this session we will deep an in-depth look at some of the most common query plan operators. We'll look at what they do, how they do it and the circumstances in which they are chosen. We'll also take a look into the ups and downs of each
Are your big queries using every available clock tick, or are they lagging behind? And if your queries are already going parallel, can they be rewritten for even greater speed? In this session you will learn how to take full advantage of parallelism.
Large, complex queries need memory in which to work--workspace memory--and understanding the how's, when's, and why's of this memory can help you create queries that run in seconds rather than minutes.
In this deep(!) dive session. I will walk you through the internal storage format of MDF files. I'll cover how SQL Server stores its own internal metadata, how it knows where to find your data, and how to read it once found.
The Column Store Index is an
exciting new technology in SQL Server 2012. Using column stores, you can
unlock new levels of performance for data warehouses – often gaining an order
of magnitude speedup on queries.
This talk will describe how the new ColumnStore index technology in SQL Server 2012 makes queries go faster. Covering details of the storage and execution model, how this model interacts with modern CPUs to deliver significant performance benefits.
In this first of two sessions, we review the architecture of SQL Server and its BI components and deployment options for optimal performance. We'll also discuss how to optimize data warehouse load operations.
The addition of spatial data to SQL Server 2008 is one of the most important in terms of integration in line-of-business applications. This talk will discuss the new features and performance enhancements in SQL Server 2012.
This session will present you with a fascinating behind-the-scenes deep-dive view of the new column store index feature. How do column store indexes work? How are they built? And how can they yield such enormous performance boosts to some workloads?
The fill-factor index option has a huge impact on the performance of your DB. By using a different approach for specific use cases this session will give you the tools to find the most optimal fill-factor for your tables.
System Center Advisor assesses your servers’ configuration and helps you proactively avoid downtime, performance degradation, and data loss. It only takes 5 minutes to setup and is accessible wherever you have a web browser.
Snapshots without snapshots...is that possible? Take a "Classic" snapshot fact table, add some temporal data theory and you'll get a new fact table than can store snapshot data without doing snapshots. A life saver when you have a lot of data.
This session reviews the purpose of NUMA, how it changes the internal behaviours of Windows and SQL Server 2012 and NUMA related performance monitoring.
Based on healthcheck reviews of hundreds of SQL Servers across dozens of customers, I'll talk you through the 10 most commonly made mistakes by action or inaction and what you can do to make sure your SQL Servers get a clean bill of health.
For the most DBAs and DEVs the TempDb is a crystal ball. But the TempDb is the most critical component in a SQL Server installation and is used by your applications and also internally by SQL Server.
SQL Server optimizer doesn't use and index seek for execution of your query although the query is high selective? What is better, when and why: LIKE vs: SUBSTRING, IN vs. EXISTS, SUBQUERY vs. JOIN. Why you should not use the UPPER or LOWER functions?
Do you have data warehouse queries that run too long? In this session we’ll address how columnstore indexes speed up queries, best practices for creating and using columnstore indexes, and how to diagnose and treat potential issues.
In this session, we'll examine the query plan cache to see what plans are saved, what plans are reused, when plans are recreated, methods for observing the contents of the plan cache, and finally,
methods for manipulating plan reuse and recreation.
It's Friday, 05:00pm. You are just receiving an email that informs you that your SQL Server has enormous performance problems! What can you do? How can you identify the problem and resolve it fast?
Organizations risk being overwhelmed by data. How can you effectively provide a “single version of the truth”, while unlocking the key trends and insights that will allow your business to succeed? Come to this session to find out how.
In this session, I will take simple SQL statements, the stuff you write every day, and bump up the scale until things start breaking
It's important to keep a baseline of performance metrics that allow us to know when something is wrong and help us to track it down and fix the problem. This session will show you how to use PowerShell to gather your baseline and how to report it.
Do you have complex dimensions in your data warehouse? Parent-child, late arriving, type 3 or type 6? In this session, we'll cover some SSIS patterns for handling each of these, along with tips for making them perform well.
How do you do database maintenance in an enterprise environment? In this session I will go through how you can do backup, integrity check, index and statistics maintenance using Ola Hallengren's Maintenance Solution.
Based on my experience in creating OrcaMDF, an open source MDF file parser, I'll go through the primary storage structures, how to parse pages, headers, internal base tables, b-tree structures as well as the supporting IAM, GAM, SGAM and PFS pages.
Service Broker was introduced in SQL Server 2005 to provide asyncronous messaging in your database applications. In this session we'll walk you through the basics of Service Broker and show how you can use it to build highly scalable applications.
In this session we will look at some common practices I have seen in the field that cause performance problems. We will diagnose the cause of the problem and the resolution to the problem.
In this session Aaron Bertrand and Steve Wright of SQL Sentry, will illustrate how SQL Sentry provides unparalleled insight, awareness and control over the true source of performance issues in SQL Server.
So your customer's dot.net application is ready to ship and it is dog slow. This session focuses on the tools and methology that SQL consultants use to test and improve performance with dot.net applications.
When loading a Fast Track Data Warehouse it is important to ensure that your data is optimally laid out for Sequential I/O. Fragmentation is therefore the enemy. Know your enemy. Learn what it is, how it occurs and prevent it from happening to you!
Virtualisation changes the way you need to monitor the performance of a virtualised instance of SQL Server. In this session I will demonstrate a balanced and well-rounded approach to performance monitoring in the virtual world along with best practices to avoid poor virtualised performance.
In this session we will take a deep dive into gaining an understanding of things that can affect transaction log performance and look into methods of prevention and troubleshooting of everyday gremlins.
This session will explore a handful of T-SQL practices - why they happen, why they're bad, and how we can work around them.
In this section we will show how to avoid performance problems caused by poor query design (functions in WHERE clause, data type conversions…) and explain how local variables and parameters affect the generation of execution plan.
This session was first given @ SQLBits 8, with the upcoming Denali release we can not only revisit this topic once more, but add to it further showing techniques and enhancements to the "Waits" analysis now possible with the latest SQL release
In this session, I will talk about the lessons we have learned and the methodology we follow when diagnosing and resolving issues with real customer workloads running on 64 and 128 logical cores
PowerPivot can be a great troubleshooting / performance tuning tool for a dba besides just loading all the data in a database and start querying. I'll show the pro's and cons of PowerPivot while trying work with waitstats, profiler data etc.
An introduction to LINQ covering what it is, why people choose to use it, and how you can help your developers when troubleshooting and performance tuning as you previously did through stored procedures.
This session will be presented jointly by Justin Langford and Gavin Payne . The focus of the session is the approach to a cross-team Performance Troubleshooting engagement where multiple stakeholders were involved.
The talk will go back to SQL Server 7.0 when we have introduced “parallel query” in SQL Server for the first time. Lubor will share our initial “parallelism” challenges and how this feature has been developing through the subsequent releases
Cube tuning is a key part of any BI project and it gets more so as cubes get bigger. Here are a series of tuning procedures to follow for cubes large and small.
In this session Aaron Bertrand and Steve Wright of SQL Sentry, will illustrate how SQL Sentry provides unparalleled insight, awareness and control over the true source of performance issues in SQL Server.
Do you already wanted to know how SQL Server 2008 stores a database file physically on the hard drive? In this session you will learn the internal structure of a SQL Server 2008 database file.
Jon Reade examines the performance benefits of SSDs in a SQL Server environment.
Writing your first SSIS custom components can seem like a very steep learning curve. In this session i shall walk you through a simple skeletal one to start you on your way.
Building performant data flows takes more than just dragging a few boxes onto a design surface. In this session I'll demonstrate that SSIS perf tuning is less about fine-grained tweaks and more about designing packages correctly in the first place.
This session will cover the "Dark Arts" of SQL Server namely performance tuning. Rather than a traditional look at query tuning it will focus on common misconceptions and a look a why sometimes you get query plans that do "odd" things
Dan Cardno, Idera's Senior Sales Engineer, will focus on the suite of SQL Performance solutions offered by Idera and how they can help anyone tasked with the responsibility of administering SQL server databases.
Ever wanted to know exactly what is happening underneath the covers in your queries. Let me show you.
Not to be confused with the Rugby / Football ditties, but actually an in-depth look @ "Waits" Side of the well-known "Waits & Queues" methodology
With focus in this session on interpreting the information in the (DMV) sys.dm_os_wait_stats
In this session we will look at some of the practices that you shouldn't follow when developing a SQL Server database. We will cover items such as query design, table design, indexes, constraints and more.
After this session you will have some practices that you know you should avoid in your SQL Server database if you want the best performance. If you have them you will know what you need to do to resolve them.
Some tips and tricks which I have picked in my last few projects which can help you optimize cube design , optimize query and processing performance .
DAC(Data Tier Applications) a new feature introduced in SQL Server 2008 R2, learn how this new feature can fit into your database deployment lifecycle strategies, monitor the health & performance of your DAC applications using Utility Explorer
The technique of Recency Frequency Intensity/Monetary is a powerful analytical technique for identifying data patterns as well as business performance. An introduction to the technique will be given, however the main focus of the session will be on demonstrating on how RFI/M can be performed using a number of SQL features such as Data Windowing, the OVER clause and PARTITION BY, CROSS APPLY and Common Table Expressions and how you can nest the table expressions. The session should be of benefit to both inexperienced and experienced SQL coders and analysts, each construct will be explained as well as the query plans produced. Demo's will be done on AdventureWorks which we actually discover is going out of business!
attend this session to understand exactly how the optimiser decides on its plans
In this session with examples we will continue to cover how to identify inefficiencies in parallel query execution.
Part I was presented during SQLBits VI in London, if you missed it, view the Webcasts @ http://webcast.sqlworkshops.com.
This session provides an overview of a recent customer engagement to investigate storage performance problems and evaluate potential solutions. The session include details on measuring performance of the existing storage solution, profiling SQL Server disk IO activity and tools to simulate load and evaluate performance.
An open forum panel discussion with members of the SQL Customer Advisory team.
See how the optimiser chooses the operators it does through real world examples.
A number of techniques have been discussed for scaling SQL Server on big-iron systems. Some apply to transaction processing, others to data warehouses. However there is very little available guidance on the impact of each for specific application characteristics. Learn which techniques are absolutely essential and which contribute a only few percent.
A look at the basics of CLR integration with SQL Server, focusing on the nuts and bolts of CLR objects, followed by some practical examples.
PANIC IN THE DATACENTER! Your databases are approaching - or surpassed - the Terrible Terabyte mark. You're pouring money into the SAN, but your data isn't pouring back out as fast as you want. You're terrified to DBCCs or index maintenance because everything takes forever, and you don't have big maintenance windows.
Need to eek out a bit more oomph from your dataflows. This session might be of some use.
Transaction Replication is a widely used feature in SQL Server though its internals are sparsely understood.
In this session we will look under the hood and gain indepth understanding about Transactional Replication architecture and its corresponding components like logreader and distribution agents, Reader/Writer threads, etc. We will learn methods of identifying and monitoring replication latency and methods of troubleshooting and eliminating bottlenecks to improve Replication Performance.
An overview of some everyday TSQL tuning techniques.
SARGability relates to the ability to search through an index for a value, but many DB Pros don’t really get it – especially in regard to joins – leading to queries which don’t run as well as they should. No slides here, just demos...
Do you wonder about SSIS performance? Well I do, and I've compiled my research into this session. We'll cover various design patterns for solving common problems like inserts vs. updates, is it faster to use a lookup, or can you just catch the errors and process them afterwards? As well as the richer patterns we'll look at some straight comparisons between two components that can be used to do perform the same task and ask which one is quicker?
This session will investigate using Stream Insight, SQL Server and Analysis Services to provide an example framework to monitor cube usage as well as suggest a mechanism for highlighting areas for performance and security enhancements.
Fast Track is a new reference data warehousing architecture provided by Microsoft. More than this it represents a new way of thinking about data warehousing. A Fast Track system is measured by its raw compute power - not by a DBAs ability to tune an index. Fast Track is an appliance-like solution that delivers phenomenal performance from a pre-defined, balanced configuration of CPU, memory and storage using nothing but commodity hardware.
Of particular interest in a Fast Track system is the way in which the storage and SQL Server are configured. To achieve the fantastic throughput without using SSDs requires some careful configuration. This configuration is designed to make use of Sequential I/O to dramatically improve disk I/O performance.
Interested? If you have a large data warehouse that's seen better days or perhaps you are about to embark on a new warehousing project then you should be! Fast Track is a great solution with a fantastic value proposition.
In this one hour session we'll aim to get under the skin of Fast Track and get some answers as to how it delivers such great throughput on commodity hardware. In the process we'll aim to answer the following questions:
* When might I need Fast Track?
* What is Sequential I/O?
* How does Sequential I/O improve performance?
* What do I need to do to get Sequential I/O?
* How can I monitor for Sequential I/O ?
* What may I need to change in my ETL to get the benefit of sequential I/O?
Still reading? I'll save you a front row seat....
Increase the "out of the box" performance and throughput of SQL Server.
Encapsulating common code in fucntions is one of the first things you learn as a programmer. However with SQL Server functions can be very bad for performance. In this session we will examine scalar functions in both TSQL and in .Net.
You will come away from this session understanding the pitfalls of TSQL functions and how you can make them run 100 times faster.
A challenge to traditional patterns of processing, storing and retrieving the precious data that we are responsible for
Understand the Query Optimiser from the man who knows!
Learn to tune Analysis Services 2008 query performance
A typical day of DBA and new features of SQL Server 2008 can help - save a minute.
In this session with examples we will cover how to identify inefficiencies in parallel query execution. We will also investigate some invisible symptoms!
Keywords: MAXDOP, CXPACKET, SLEEP_TASK & SOS_SCHEDULER_YIELD.
I don’t have a quad core laptop yet, so I will do the best with my dual core!
Bad performance is often systemic of poor queries which are systemic of bad schema design which is systemic of non-relational thinking which is systemic of project time constraints and lack of understanding of Database Design. In this talk/tutorial I'll work my way through Normalisation, we'll look at the Relation Model and how to think in sets - it's very important; throughout I'll be referring to Codd and Date's teachings. Theory aside I'll do all my demonstrations in SQL Server - concurrency, indexing, good T-SQL practices and advice.
Learn to love SCOM!
Out of the box the SCOM Management Pack for SQL Server is not popular with DBA's. This demo based session will take you through the process of how you can extend SCOM to properly monitor and analyse SQL performance. Ultimately providing performance management scorecards and dashboards for your CIO.
With examples we will discuss tips and tricks that will be useful for Developers, DBAs and Consultants.
If you've ever needed to review large Performance Monitor (PerfMon) logs and find problems quickly - you'll know how time consuming it can be to identify problems.
Performance Analysis for Logs (PAL) is a great tool to quickly highlight performance problems - taking the leg-work out of PerfMon log analysis.
This session covers the new scalability features of SQL Server Reporting Services 2008 and demonstrates how to build a high performance reporting platform to deal with your enterprise reporting needs.
SQLSentry Optimizing SQL Server Performance
MDX Studio can be used to analyze complex MDX expressions, monitor performance characteristics of MDX queries and get insight into how MDX interacts with other UDM features such as attribute relationships.
This session aims to enlighten the audience on improving Log file design, implementation and management for extracting maximum performance from SQL Server using real world examples and strategy. The session will focus on SQL Server 2005, SQL Server 2008 and SQL 2008 R2.
Learn how the Analysis Services cache works, and how you can pre-load it with data to ensure optimal query performance.
So you heard about the new spatial functionality in SQL Server 2008, rushed back to your database and added geography and geometry columns to all your tables, eager to create the next Google Earth-beating application. You then click the Execute button and wait.
And wait.
And wait some more. (You get the idea).
Spatial data is a rather unique beast, and designing efficient spatial queries requires specific techniques when compared to other, more traditional types of data.
In this session, we look at how the SQL Server database engine satisfies spatial queries, the theory behind spatial indexes, demonstrate the effects of altering the bounding box, use the spatial system DMVs and stored procedures to your spatial database
Explore the Fast Track Data Warehouse appliances from HP & Microsoft with case studies, implementation thoughts, and roadmap.
In this session we will take a deeper look at how SQL Server uses I/O and how you can design the I/O system to meet the requirements of your applications.
In this presentation, I will introduce the Madison architecture and provide a roadmap with major milestones for this product
In this session we will analyze and resolve a real world SQL Server Performance issue. We will first identify a customer’s query performance issue and understand the reason for the non optimal performance of their application. After understanding the limitations it will of course be natural to invent and perform non TCO increasing magic to make SQL Server really fly. We will also provide few words of caution for those who might abuse the newly acquired magical powers.
This deep dive, no fancy power point show is equally designed for developers and administrators. If you are one of those who would rather perform the magic on your own laptop than just observe it being demonstrated, send me an email so I can send you the demo scripts to prepare yourself for the adventure.