SQL Training

Extreme Query Performance

This two-day course is designed to teach you to leverage SQL Server’s built-in parallelism features in conjunction with the SQLCLR framework in order to achieve massive improvements in query performance. These techniques have been used on numerous projects to quickly bring large query run times from hours to seconds.

The integration of the Common Language Runtime (CLR) into SQL Server 2005 and 2008 provides SQL Server developers with a vast toolset for solving complex business, technical, and performance problems. You will learn the ins and outs of working with SQLCLR routines, including best practices for leveraging them in real-world scenarios.

Today’s server hardware ships with a truly massive amount of CPU power. And while SQL Server is designed to automatically and seamlessly take advantage of available processing resources, there are a number of options, settings, and patterns that can be exploited to optimize parallel processing of your data. After attending this seminar, you will be in full control of your CPUs, able to compel your server to use every clock tick to your end users’ advantage.


Coming Soon!

Instructor: Adam Machanic

Dates: TBD

Location: Your City?

Course Detail PDF Download of: Extreme Query Performance

Download the course details for yourself or your employer!

Day 1

Module 1: Overview (What SQLCLR Can Do)
  • Why .NET in the data tier?
  • Why does it perform so well?
  • What kinds of business and technical challenges can it solve?
  • Is it hard to manage?

Module 2: Introduction to SQLCLR: UDFs
  • Creating your first SQLCLR method
  • CREATE ASSEMBLY and related statements
  • Permission sets and security basics
  • SqlTypes library
  • Interaction concerns for nullable types and new SQL Server 2008 data types
  • SqlFunction attribute
  • Determinism, precision, and system data access
  • Dealing with Visual Studio deployment issues
  • IEnumerable and table-valued UDFs
  • Performance and UDFs

Module 3: SQLCLR Stored Procedures and Data Access
  • Stored procedures vs. functions
  • Introduction to the in-proc data access model
  • The context connection
  • SqlContext class
  • SqlPipe class
  • Exceptions and exception handling
  • When does it make sense to use a CLR stored procedure?
  • When should a CLR UDF be used instead?
  • A few thoughts on SQLCLR triggers
  • Use cases
    • Data access and performance
    • Streaming algorithms
    • Better administration through SQLCLR
    • Solving sequential and time series problems

Module 4: Security, Reliability, and Design Considerations
  • What do the permission sets REALLY mean?
  • HostProtection
  • Code Access Security
  • Database trustworthiness
  • Strong named assemblies
  • Designing for least privilege
  • Designing for reuse
  • Creating robust utility classes

Module 5: User-Defined Aggregates and Types
  • Where do UDAs fit and what problems do they solve?
  • What are their limitations?
  • SqlUserDefinedAggregate attribute
  • The various methods that make up a UDA
  • Solving business, analytical, and performance problems using UDAs
  • SqlUserDefinedType attribute
  • INullable interface
  • Serialization and ordering
  • Static and instance methods
  • The Parse method
  • Design considerations
  • Use cases for types and static methods

Module 6: Streaming Parallel Processing
  • Basics of SQLCLR parallel processing
  • How threading works in the CLR world
  • Design considerations for streaming interfaces
  • The QueryParallelizer framework

Module 7: Troubleshooting and Ongoing Maintenance
  • Deployment concerns
  • The SQLCLR management UDAs
  • Assembly versioning
  • Changing code (without breaking everything)

Day 2

Module 1: History and Theory
  • CPU Evolution Over Time
  • Heat and Dissipation
  • Moore’s Law
  • Amdahl’s Law
  • Gustafson’s Law
  • How This All Fits Together

Module 2: Windows Process/Thread Internals
  • Processes
  • Threads
  • Context switching
  • Quantums
  • Clock interrupts
  • Affinity

Module 3: SQL Server Scheduler Internals
  • SQLOS workers and schedulers
  • Waits and queues
  • Important DMVs

Module 4: Query Processor Parallelism
  • Parallel Iterators
  • Non-Parallel Iterators
  • Row Distribution Strategies
  • How to Read and Mine Data From Parallel Query Plans

Module 5: Controlling Parallelism at the Query Level
  • Ideal Parallel Query Patterns
  • Parallelism Inhibitors and Workarounds
  • How SQLCLR threading and intra-query parallelism can work together

Module 6: Server Settings that Influence Parallelism
  • Affinity Masks
  • Worker Threads
  • Cost Threshold
  • Resource Governor
  • OLTP vs. OLAP vs. Mixed Workload Considerations

Module 7: Monitoring Parallel Processes
  • Parallel Task Architecture and the Tasks DMV
  • Parallel Waits and the Waiting Tasks DMV
    • LATCH_EX
  • Finding Out How Much Work is Being Done
  • Finding Parallel and Serial Plans in the Cache

Intended Audience


What You Will Learn

The first day starts with an introduction to SQLCLR: what it can do for you and why it was added to SQL Server. Next, you will learn about the basic SQLCLR modules: user-defined functions and stored procedures. Your knowledge of the basics will be extended with insights into some of the SQLCLR internals―particularly, the security model―and you will learn how to create robust, secure, and high-performance frameworks. The second half of the day goes even deeper, taking a look at user-defined types and aggregates, an advanced streaming framework, and a review of how to deploy and manage your solutions in production environments. All along the way, performance, scalability, and maintainability will be stressed.

Day 2 starts with an overview of CPU scheduler internals from the perspective of both Windows and the SQL Server Operating System (SQLOS). Next, you will learn about intra-query parallelism, the method that SQL Server uses to break up large tasks into many smaller pieces, so that each piece can be processed simultaneously. You will come to understand how to read parallel query plans and how to interpret parallel wait statistics, as well as best practices for the various parallelism settings within SQL Server. Finally, you will be introduced to techniques for exploiting parallelism at the query level: patterns that can help the optimizer do a better job of parallelizing your query.

Instructor Bio:

Adam Machanic

Adam Machanic is a Boston-based database consultant, writer, and speaker, as well as the founder of Data Education. He has been involved in dozens of SQL Server implementations for both high-availability OLTP and large-scale data warehouse applications, and has optimized data access layer performance for several data-intensive applications.

Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including SQL Server 2008 Internals (Microsoft Press, 2009) and Expert SQL Server 2005 Development (Apress, 2007).

Location: Your City?

Registration: Coming Soon! Click here to request this course

Visa, MasterCard, Discover, American Express, PayPal, eCheck, Check, Purchase Order

Data Educaiton accepts Visa, MasterCard, Discover, American Express, PayPal, eCheck, personal & business checks and purchase orders.

Course Dates: TBD (2 Days)
Course Time: 9:00 AM - 5:00 PM
Course Detail PDF Download of: Extreme Query Performance Download the full course details in a convenient PDF

Using a Purchase Order or Check?

If a purchase order is required for invoice, please send us the P.O. number using our contact form. Be sure to indicate the course title and attendee name(s) in your message.

We accept personal and business checks for course registrations up to 15 business days before the start date of the desired course. If paying by check, remember that it can take up to 5 business days to arrive at our office through postal mail depending on your location. Please include a note telling us the course title and attendee name(s), and make your check payable to:

Data Education
15 Lincoln Street
Suite 226
Wakefield, MA 01880

Refund & Cancellation Policy

If Data Education cancels the course, you will receive a 100% refund. Registration is fully transferable to a course of the same or lesser value within one year’s time of the initial course. If you cancel your registration with more than 30 days notice, Data Education will refund the fee minus $250 for processing. If you cancel your registration with between 16 and 30 days notice, Data Education will refund 75% of the fee. If you cancel your registration with between 7 and 15 days notice, Data Education will refund 50% of the fee. Cancellations unfortunately will not be allowed as of 6 days before the beginning of the course.