Day 1

Performance, Flexibility, Control: Everything You Need to Know About SQLCLR

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. The 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.

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
• ALTER ASSEMBLY
• Assembly versioning
• Changing code (without breaking everything)

Day 2

Surfing the Multicore Wave: Processors, Parallelism, and Performance

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. This day 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. 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.

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
• MAXDOP
• 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
  • CXPACKET
  • LATCH_EX
• Finding Out How Much Work is Being Done
• Finding Parallel and Serial Plans in the Cache