Advanced SQL & Optimization for Microsoft SQL Server

Length: 5  Days
Prerequisites:  SQL syntax:  SELECT, INSERT, UPDATE, DELETE:
Recommended: " Introduction to Microsoft SQL Server"


Overview
Soaring Eagle presents "Advanced SQL Optimization for Microsoft SQL Server," to DBAs, System Administrators and Client Application
Developers. Attendees will learn how MS SQL Server processes queries. Learn to identify problematic statements and queries in
an application design before they are implemented, saving time and money. Students will estimate query performance on paper and
use those estimates to identify implementation issues with queries.

Key Benefits:  When you complete this course you learn how to write efficient SQL Statements, Understand When to Reorganize a Query.
You will be able to Recognize Performance Issues and understand how stored procedures function.


Audience: Developers and Database Administrators who will be writing queries to be used inside applications.


Outline


Chapter 1. Fundamentals of Query Processing

• Define The Basic Steps in Query Processing
• Identify Possible Bottlenecks

Chapter 2. Query Troubleshooting

• Learn to Use SQL Server Tools to Analyze Queries
• Learn to Read Output from Query Plans
• Define Problematic Queries
• Interpreting Showplan_All
• Set Statistics Time
• Understanding SSMS
• Profiler Traces/ Saving Profiler Information

Chapter 3.  SQL Statement Processing

• Observe How the Optimizer Treats SQL Structures
• Gain Experience in Working with Troubleshooting Tools
• Understand the Role of a Worktable
• General Statement Processing Steps
• Query Optimization Steps
• Query Tracking
• Order by Processing
• Group By Aggregate Function Processing
• Bookmark Lookup

Chapter 4. Presentation of Class Participants' Problem Queries

• Scenarios and Resolutions
• Raise and Consider Problem Queries

Chapter 5. Single -Table Optimization

• Examine Detailed Topics in Query Optimization
• Indexes with SARGs
• Improvised SARGs
• Clustered vs. non-clustered indexes
• Queries with OR
• Index Covering
• Forcing Index Selection
• Table Scans
• Index Limitations
• Primary Key vs Clustering and Non-clustering
• Optimizer Selection Criteria
• Index Covering
• Composite Indexes
• Index Statistics

Chapter 6. Join Processing

• Understand Join Optimization
• Consider Special topics in Multi-table Optimizaton
• Join Order
• Indexes and Joins
• Overriding the Optimizer
• Self Joins
• Outer Joins/ Hash Joins/ Merge Joins
• Nested Loops
• Force Plan

 Chapter 7.  Subqueries

• Learn to Distinguish Subquery Types
• Write Effective Subqueries
• Understand Subquery Optimization
• Correlated Subqueries
• Processing & Flattening
• Subqueries vs Joins

Chapter 8.  Datatype Conversion & Conflicts

• SQL Server Datatypes
• Datatype Conversions
• Implicit & Explicit Datatype
• Float & Real Conversion Styles
• Money Datatypes
• Conversion Styles

Chapter 9.  Views

• Understand Views
• Improve Code Maintenance
• Gain Modularity
•  Use Views for Secuiry
• Understand the Check Option
• Calculated Columns
• Indexed Views

Chapter 10.  Union Queries

• Understand How to Use Union
• Learn When Union is Useful
• Look at Union Optimization

Chapter 11.  Cursors

• Understand the Fundamentals of Cursor Processing
• Compare Row Processing to set Processing
• Understand the Benefits and Drawbacks of Cursors
• Compare Nested Cursors and Multi-table Cursors
• Fetching Rows
• Acquiring Locks

Chapter 12.  Stored Procedures

• Review Stored Procedure Coding Recommendations
• Define Appropriate Error Handling, Return Status and Parameter Techniques
• Understand Compilation Issues
• Examine Performance Factors
• Executing with Parameters

Chapter 13. BLOBs

• In Most shops this chapter should be deprecated in favor of varchar(max) and varbinary(max)
• Understand the Implementation of Text and Image Data
• Use Text and Image Modification and Retrieval Statements
• Understand the Drawbacks of BLOBs
• Alternatives to BLOBs

Chapter 14. Full Text Search

• Preparing a Full text Index
• Creating a Full Text Index
• Performing a Full Text Query
• Verifying Full Text Index
• Full Text Query
• Proximity Search, Variation Search, Weighted Search
• Freetext

Chapter 15. Session Configuration

• How to Use the Set Command to Control a Session's Behavior
• How to List Session Configuration Options
• How to set the Default Configuration Options
• Set Deadlock Priority
• Set Lock_Timeout
• Set Concat_Null_Yields
• Set Cursor_Close_On_Commit
• Set Fips_Flagger
• Set Identity_Insert
• Set Language
• Set Arithabort
• Set Fmtonly
• SetNocount
• Set ANsi_Defaults
• Set Showplan
• Set Forceplan
• Set Statistics
• Default Configuration Options
• View User Configuration Options

Chapter 16. Nulls

• What is a Null
• Null Truth Tables
• Issues with Nulls
• Four Value Logic

Chapter 17. Advanced Select Options

• Hierarchy
• Merge
• Except
• Intersect

Chapter 18.  Dynamic Queries

• Creating Queries for Execution
• Pros & Cons
• More Complex Queries
• Select List
• WHERE & FROM Clauses

Chapter 19. Common Table Expressions

• Basics
• Recursion
• Recursion Limits

Chapter 20.  Specific SQL Problems, Solutions & Issues

• Learn to Build Crosstab Reports and Queries
• Report on Vector Data
• Characteristic Functions
• Populate Tables With random Data Using SQL
• Consider Miscellaneous Oddities about SQL Server

Chapter 21.  Case Study: Worst, Bad Better

• From a Real-Life Problem

 

Back to Course Listing