Query Execution Steps

• Parsing
• Algebraization – query tree built
• Optimization – analyze query tree and determine the optimal way to execute it
• Execution

Query Optimization

• Process of selecting one plan from many possible plans
• Cost-based – aimed at finding lowest estimated cost of execution.  But it is an estimate that is not always correct and you can override the optimizer by using hints
• If a query is extremely complex, it may take longer to optimize than it takes to execute.  Query optimization is throttled (see KB907877) by complexity and can timeout.

Controlling Plan Caching

• Application-side parameterization
• Stored Procedures
• Forced Parameterization – Database-level option that makes SQL Server more aggressive in parameterizing queries
• Optimize for ad hoc workloads – New option in SQL Server 2008 – Stub executed on first execution with full plan cached after second execution

Implementing Forced Parameterization

• This is like the Turbo Button for SQL Server
• But, it is not turned on by default because it could break or dramatically slow down queries upgraded from SQL 2000 to 2005 or 2008
• Should perform analysis before turning it on, but it is a huge performance boost in most cases and should fix anything that it slows down as it will be worth it.

Working Around the “Bad Plan” Issue

• OPTION (RECOMPILE)
• EXECUTE … WITH RECOMPILE
• OPTIMIZE FOR (@VAR = VAL)
• OPTIMIZE FOR UNKNOWN (new in SQL Server 2008)

Resources

• http://msdn.microsoft.com/en-us/library/ee343986.aspx
• E-Mail him for the scripts

Passing a Set

• N Rows = N Executed Statements – One server rountrip per execution, or all executions in one batch

Pass as XML

Resources

• Entity Framework Contrib
• Entity Framework Runtime Model Adapter
• Silverlight Hierarchical Entity Framework

Contact Information

Tim Laverty

• Twitter – @timlaverty
• E-Mail – timlav@microsoft.com

Diego Vega

• Twitter – @divega
• E-Mail – diego.vega@microsoft.com

Wow, just saw the first AV technical problem of all the sessions I’ve attended.  The swap from PowerPoint machine to demo laptop failed.  But, it was fixed by the technician in about a minute.

Script Task

• Now has support to set variables as read-only for the scope of the script task
• Under 2008+, instead of VBA, the engine uses VSTA, and now supports C# in addition to earlier VB.Net support
• There should be no issues going between 32-bit and 64-bit, but you must debug in 32-bit because it will not stop on breakpoints when running under 64-bit.

Task Component – The Free Stuff

• Class propertied follow component object model
• Default Persistence
• Base Task UI can be extended

Task Component Timings

• Initialize –> Validate –> Execute

Resources

• SSIS Team Blog
• SSIS Community Tasks and Components

I totally ran down two batteries before the last session so I wasn’t able to cover it (yet).  But, I was able to get a precious power-outlet proximate seat in the session, so I am back.

Namespace – System.Xml.Linq

• XDocument
• XElement
• XPath

VB-Only Features

• XML Literals – still not supported in C# 4.0

Downloads

www.pdsa.com/teched

Out-of-Browser lets us give applications a proper home.  Some make sense running inside the browser.  Others need to be a more seamless part of the overall desktop experience.

Sync Framework for Silverlight

• Not yet released
• Handles syncing data from SQL Server or Azure into Isolated Storage for offline access

New is Silverlight 4 Out-Of-Browser

• WebBrowser hosting – WebBrowser control and WebBrowserBrush
• Window customization
• Toast notifications
• Elevated Trust

Web Browser Control

• Actually hosts the actual browser on the machine
• Only available out-of-browser
• Anything that web browser can host, can be hosted in the WebBrowser control, such as Flash, PDF and even Silverlight

Window Customization

• Startup location control
• Closed and cancellable Closing events
• Control of border design, borderless for example

Install Options

• SLLauncher has silent install and uninstall options
• SLLauncher with /emulate runs an emulation mode that allows launch without installing

Trusted Applications

• For the most part, they are not sandboxed
• File System Access
• Native Integration – COM Interop
• Custom Window Chrome
• Digitally Signed XAP files

Relaxed Sandbox

• Enable cross-domain HTTP networking
• Eliminate socket port range restrictions

Local File System Access

• Access to “My” Files folders
• Supports System.IO types

Custom Window Chrome

• Borderless window with rounded corners for example

COM Interop

• Enables platform specific code
• Automation Objects on Windows
• Silverlight will not install native components

Digitally Signing XAP Files

• Automatic updates of the installed XAP will only occur of the XAP is signed
• The elevated trust prompts are not “warning” style when the XAP is isgned

Resources

• David Poll’s Blog

RIA Services makes you productive building forms over data applications.  This will be a demo heavy application.

Reference application is Tailspin Travel App

RIA Services

• Database access code plumbing
• Authentication
• Validation

Nuggets

• You do not have to add a service reference for the server-side RIA application because the EnableClientAccess decorator in the domain service class tells RIA to generate a service proxy in the client application.  This is done as a generated file.
• If using POCO, the class must contain a member that is a unique identifier, and that identifier has to be decorated with the Key decorator.
• The Invoke decorator can be used to project a custom method to the client.
• The Query(ResultLimit=x) decorator can be used to limit the number of records returned by a query
• The OutputCache decorator enables you to control caching. so you can force rarely changing lookup table data to be cached locally on the client.
• When paging, to get total count of records available, which RIA does not do by default, use query.IncludeTotalCount = true

Resources

• Deepesh Mohnani’s Blog

For some odd reason, 8AM seems to be coming earlier on each morning of the conference!  So, I missed the first 30 minutes of the session and look forward to rewinding the session video when it’s posted.  This post will definitely have some updates after that time.

Nuggets

• When using the Silverlight Client HTTP Stack instead of the browser stack, the browser does not cache anything.  So, if you load images via the Client HTTP Stack, you get no benefit from them being cached.
• Fiddler is your friend.  It is a great first level check to check site and service security.

Decomposing Requirements to a Logical Design

• Good data design impacts Performance, Security, Storage and Integrity
• Poor data design can be attributed to 50-75% of project issues
• Good design is easily communicated to business users and developers

Who Designs (and Why)?

• Business Person or Business Analyst – Organization has strong talent in this area, data is viewed as a business asset and viewed as a “flow” pattern
• Developer – Mature development shop with no dedicated data professional staff, generally using a formal ALM process
• DBA or Data Professional – Full-time data professional available with holistic view of data

Design Process

• Create and refine your business requirements – this is often under-done and leads to failure – spend a lot of time on this.  And then some more.
• Break out Nouns
• Refine the Nouns – Some Nouns belong to other Nouns, some have things in common with other Nouns
• Define Relationships between Nouns
• Define Data Types

The red steps are the Normalization process.

Principals of Normalization

• Normalize to the level necessary, not to the level possible

Nuggets

• The database diagram tool is SSMS is pure evil.  Do not use it.
• Use one of the third party tools, even if commercial, over the poor tools Microsoft provides
• People cry a lot in my class.  I was up all night.  See, you actually are training to be a DBA!

Resources

• Buck Woody’s Blog
• Data Design

Reference Application – Job Management and Timekeeping System for DSL Electric

Lesson #1: Pick a Pattern and Stick to It

• MVVM works very well for Silverlight Line-of-Business applications
• Provides consistency across code

MVVM Pattern Enablers

• Data Binding – Silverlight has awesome data binding engine
• ViewModel – Glue between the server and the screen – Gathers the data to be presented
• Commanding – if control doesn’t support Commanding, you can use the Expression Blend Command Behavior to implement one
• Messaging

If you’re going to get into this pattern, that the time to figure it out.  In the long haul, it is worth using.

Lesson #2: Data Binding and Nested Control

• Binding data in nested control scenarios can be challenging
• Data may be out od scope – Items nested in a datagrid

Lesson #3: Notify Users of Successes (and failures)

• Silverlight provides rich animation and media capabilities – Use them.
• Animations can be used to visually indicate success or failure
• Can minimize the number of times the user has to click on a message box
• MediaElement provides a simple way to play sounds

Lesson #4: Get an Agent – A Service Agent

• Make service calls from a service agent
• Allows for better code re-use
• Allows ViewModel classes to stay focused and clean
• Can minimize wrapper code

Lesson #5: Extend Existing Controls

• Extend controls rather than building from scratch
• TextBox –> FilteredTextbox
• ComboBox extended to deal with primary keys better
• AutoCompleteBox –> AutoCompleteComboBox

Downloads

• Sample Code and Slides

Resources

• Dan Wahlin’s WebLog