Troubleshooting Your First Low-performing T-SQL Query

https://app.pluralsight.com/library/courses/troubleshooting-first-low-performing-t-sql-query/table-of-contents

by Jared Westover

Module 1: Course Overview

  1. Course Overview

Module 2: Asking Questions about a Query

  1. Introduction
  2. What Is Low-performing?
  3. Developer Expectations
    • - This course uses the WiredBrainCoffee database
      - shows how to enable line numbers in SSMS
      - shows how to refresh IntelliSense local cache  
  4. Demo: Setup Your Environment
  5. Start With Questions
  6. Demo: Ask Questions
  7. Breakdown Complex Queries
  8. Demo: Common Table Expressions
    • - shows how to convert a derived query into a CTE
      - performance is about the same but CTE are more readable
  9. Demo: Nested CASE Expressions
  10. Summary

Module 3: Sources of Query Performance Issues

  1. Introduction
  2. Clicking Execute
    • overview of the execute phase: Parser -> Algebraizer -> Optimizer -> Execution.
  3. SQL Server Storage
    • discusses how sql server caches results in pages
  4. Return Fewer Pages
    • use where clause and indexes to return fewer pages
  5. Demo: Page Count
    • query to determine how many pages a table uses (sys.dm_db_partition_stats)
  6. Demo: Returning Fewer Pages
    • - DBCC DROPCLEANBUFFERS will clear page cache
      - removing ORDER BY from select query could speed up query. if you need the order by, consider an index
  7. Indexes Drive Performance
  8. Invalidate Index Seeks
    • careful not to invalidate seeks, forcing a full scan of an index
  9. Demo: Invalidate Index Seeks
    • examples where seek is invalidated
      WHERE CONVERT(CHAR(10), sDate,121) = '2019-03-03 ' ;
      WHERE LOWER(eEmail) = 'Sally.Friend@gmail.com';
      WHERE LTRIM(RTRIM((sp.Email))) = 'Sally.Friend@gmail.com';
      WHERE id LIKE '%500'
  10. Do You Need All the Columns?
  11. Demo: Problem With Select *
  12. Summary

Module 4: Performance Insights with SQL Server Tools

  1. Introduction
  2. Query Tuning Diminishing Returns
  3. Calculate Page Reads
    • Use STATISTICS IO to get insight into a query; how many logical vs physical reads
  4. Demo: STATISTICS IO
    • shows a query that can be used to determine how many pages a table has (using sys.dm_db_partition_stats, sys.tables, and sys.indexes)
    • You can turn on STATISTIC IO ON just for a single query
    • Result of STATISTICS IO is found in messages tab
    • Understanding logical and physical reads
  5. Exploring an Execution Plan
    • definition of Execution Plan: "A tool used for displaying the data retrieval methods chosen by the SQL Server Query Optimizer."
  6. Important Plan Operators
    • difference between seek and scan
    • you may want to remove ORDER BY clause as it may add overhead
    • key lookups happen when a column in a SELECT statement is not part of an index
    • a table without a clustered index is called a heap
  7. Demo: Reading an Execution Plan
    • how to show execution plan in SSMS
    • you can generate an execution plan without running the query (estimated plan)
  8. Demo: Important Plan Operators
  9. Dynamic Management Objects
    • definition: "Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance."
    • can be used to determine how many times sql server used an index, how many times a PROC has been executed and its execution time, view all your execution plans in cache, fetch all indexed sql server thinks it is missing, find queries reading lots of pages, etc
  10. Demo: Missing Indexes & Top Queries
    • data for Dynamic management views and functions is lost when sql server is restarted
    • shows query for fetching missing indexes
    • shows query for fetching queries with high logical reads
  11. Summary

Module 5: Query Tuning in the Real World

  1. Introduction
  2. Demo: Covering Index
    • use EXECUTE sp_helpindex to view index on a table
    • Covering indexes are indexes which "cover" all columns needed from a specific table, removing the need to access the physical table at all for a given query/ operation.
    • with a covering index, there is an opportunity to drop a duplicate index
    • example of using INCLUDE 
  3. Demo: Filtered Index
    • using a where clause created a filtered index
    • use case for filtered index: when you don't want to return soft deleted rows
  4. Demo: Index Column Order
    • shows a query for generating random numbers
    • index column order matters; put the most unique column first
  5. Demo: Using Compression
    • show a query for getting row count and size of an index 
    • can index can be compressed like this 'WITH (DATA COMPRESSION PAGE)'
  6. Demo: Rethinking Cursors
    • shows query that can make cursors faster (CURSOR LOCAL STATIC READ ONLY FORWARD ONLY FOR)
    • Cursors don't scale well over a larger dataset.
    • using 'ON 1 = 0' with MERGE for doing inserts only
    • using MERGE with OUTPUT and INTO
    • shows an example of converting cursor with set based operation
  7. Demo: User-defined Function Improvements
    • Microsoft made improvements to scalar user defined function in 2019
    • scalar user defined functions can be used to encapsulate repeating code
    • however, traditionality scalar user defined function have not been performant
  8. Take the Next Steps

Comments

Post a Comment

Popular posts from this blog

Angular Routing and Navigation Playbook

Image
https://www.pluralsight.com/courses/angular-routing-navigation-playbook by Lara Newsom Course Overview Course Overview Course Introduction and Code Examples Course Introduction and Code Examples Basic Angular Routing Setup Introduction to Angular Routing Discussion: Angular RouterModule Exports Demo: Bootstrap Angular RouterModule Discussion: Declaring Routes angular has tow route path match strategies: prefix based or full match prefix is the default Demo: Declaring Default and Home Routes two options for declaring a default route: {path: ' ', componenet: DefaultComponent} {path: ' ', redirectTo: 'home', pathMatch: 'full'} Discussion: Child Routes and Route Order Demo: Declaring a Wildcard Route wild card routes is: path: '**' to enabled logging all navigation events to the console, enable tracing Demo: Hooking into Router Events Routing in Standalone Component Applications Introduction to Standalone Components and Routing Demo: Bootstrapping Ro...
Read more

Working with Files in C# 10

https://www.pluralsight.com/courses/c-sharp-10-working-files by Jason Roberts Course Overview Course Overview Sandbox Managing Files and Directories Introduction Creating a New Console App Checking if a File Exists you can use to check if file path is an absolute path: Path.IsPathFullyQualified Getting the Parent Directory of a Path Checking if a Directory Exists and Creating New Directories Copying a File Moving a File Getting the File Extension from a File Name Checkpoint 01- Get the Parent Directory for a Path Changing a Filename Extension Checkpoint 02 - Get the Extension Part of a Filename Deleting a Directory Enumerating All the Files in a Directory Summary Exercise 01- Copying and Deleting Files Monitoring the File System for Changes Introduction Introducing the FileSystemWatcher Class The IntemalBufferSize Property Configuring Notification Filters Additional Properties Refactoring to Use a FileSystemWatcher use FileSystemWatcher to watch for file changes in a folder Checkpo...
Read more

Mastering Git

https://app.pluralsight.com/library/courses/master-git/table-of-contents by Paolo Perrotta Module 1: Course Overview Course Overview Module 2: The Four Areas: Introduction Version Check About This Training A Note About the Command Line Introducing the Four Areas The Working Area The Repository The Index Module 3: The Four Areas: Basic Workflow Revisiting the Basics Moving Data to the Right Moving Data to the Left Removing Files Renaming Files A Quick Summary Module 4: The Four Areas: Git Reset Understanding Reset A Reset Example More Reset Examples Module 5: The Four Areas: Advanced Tools Stashing Data Solving Conflicts Working with Paths Committing Parts of a File Introducing Switch and Restore Git Is a Toolbox Module 6: History: Exploring the Past Becoming a History Surgeon Referencing Commits to show changes in a commit: git show HEAD to show changes in a commit parent: git show HEAD^ Tracking Changes in History Browsing the Log Module 7: History: Fixing Mistakes Reviewing the Golde...
Read more