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.
Day 1Module 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?
- 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
- ALTER ASSEMBLY
- Assembly versioning
- Changing code (without breaking everything)
Day 2Module 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
- Context switching
- Clock interrupts
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
- Finding Out How Much Work is Being Done
- Finding Parallel and Serial Plans in the Cache
What You Will LearnThe 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.
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
Data Educaiton accepts Visa, MasterCard, Discover, American Express, PayPal, eCheck, personal & business checks and purchase orders.
Course Time: 9:00 AM - 5:00 PM
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:
15 Lincoln Street
Wakefield, MA 01880