MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.


I'm not curious

SQL Server: Performance Troubleshooting Using Wait Statistics

Course Summary

Where do you start looking when trying to diagnose a performance problem with SQL Server? The answer is to use wait statistics! This course will introduce you to the powerful 'waits and queues' performance tuning methodology. You'll learn how SQL Server's


  • +

    Course Syllabus

    ● Introduction
        ◦ Introduction
        ◦ Where to Start Troubleshooting?
        ◦ Doctor, Doctor... My Knee Hurts!
        ◦ Is the Symptom the Root Cause?
        ◦ Interpreting the Data
        ◦ Course Structure
    ● SQL Server Threading Model
        ◦ Introduction
        ◦ What are Threads?
        ◦ Parallelism
        ◦ Parallel Threads Example
        ◦ Controlling Parallelism
        ◦ Thread Scheduling
        ◦ Components of a Scheduler
        ◦ Schedulers in SQL Server
        ◦ Thread States
        ◦ Transition from RUNNING to SUSPENDED
        ◦ The Waiter List
        ◦ Transition from SUSPENDED to RUNNABLE
        ◦ The Runnable Queue
        ◦ Resource Governor Example
        ◦ Transition from RUNNABLE to RUNNING
        ◦ Putting It All Together
        ◦ Summary
    ● Waits
        ◦ Introduction
        ◦ What are Waits?
        ◦ What are Queues?
        ◦ Waits and Queues Methodology
        ◦ Wait Times Defined
        ◦ Graphical View of Wait Times
        ◦ sys.dm_os_waiting_tasks DMV
        ◦ sys.dm_os_wait_stats DMV
        ◦ Filtering Benign Waits
        ◦ Storing Wait Statistics
        ◦ Clearing Wait Statistics
        ◦ Using Extended Events
        ◦ Summary
    ● Latches and Spinlocks
        ◦ Introduction
        ◦ What are Latches?
        ◦ Types of Latches
        ◦ B-tree Page Split Example
        ◦ Latch Contention
        ◦ Tempdb Latch Contention Example
        ◦ sys.dm_os_latch_stats DMV
        ◦ What are Spinlocks?
        ◦ Spinlock Internals
        ◦ Spinlock Contention
        ◦ sys.dm_os_spinlock_stats DMV
        ◦ Clearing Latch and Spinlock Statistics
        ◦ Using Extended Events
        ◦ Transaction Log Example
        ◦ Summary
    ● Troubleshooting Patterns
        ◦ Introduction
        ◦ What is Relevant?
        ◦ Top Wait Types Worldwide Survey
        ◦ CXPACKET Wait Explanation
        ◦ CXPACKET Wait Example of Non-Skewed Scan
        ◦ CXPACKET Wait Example of Skewed Scan
        ◦ CXPACKET Wait Solutions
        ◦ PAGEIOLATCH_XX Wait
        ◦ PAGEIOLATCH_XX Wait Solutions
        ◦ ASYNC_NETWORK_IO Wait
        ◦ WRITELOG Wait
        ◦ WRITELOG Wait Solutions
        ◦ PAGELATCH_XX Wait
        ◦ PAGELATCH_XX Wait Solutions
        ◦ PAGELATCH_XX Wait Solutions (part 2)
        ◦ LCK_M_XX Wait
        ◦ LCK_M_XX Wait Solutions
        ◦ SOS_SCHEDULER_YIELD Wait
        ◦ SOS_SCHEDULER_YIELD Wait Solutions
        ◦ Using Extended Events to Examine Call Stacks
        ◦ OLEDB Wait
        ◦ PREEMPTIVE_OS_XX Waits
        ◦ PREEMPTIVE_OS_CREATEFILE Wait
        ◦ PREEMPTIVE_OS_WRITEFILEGATHER Wait
        ◦ PREEMPTIVE_OS_WAITFORSINGLEOBJECT Wait
        ◦ BACKUPXX Waits
        ◦ DBMIRRORXX Waits
        ◦ HADR_XX Waits
        ◦ TRACEWRITE and SQLTRACE_XX Waits
        ◦ LATCH_XX Waits
        ◦ ACCESS_METHODS_XX Latches
        ◦ FGCB_ADD_REMOVE Latch
        ◦ DBCC_XX Latches
        ◦ Miscellaneous Wait Types
        ◦ More Miscellaneous Wait Types
        ◦ Miscellaneous Latch Classes
        ◦ Miscellaneous Spinlocks
        ◦ Summary
    ● Summary
        ◦ Introduction
        ◦ Waits, Latches and Spinlocks
        ◦ Resources
        ◦ Summary
     


Course Fee:
USD 29

Course Type:

Self-Study

Course Status:

Active

Workload:

1 - 4 hours / week

This course is listed under Development & Implementations and Data & Information Management Community

Attended this course?

Back to Top

Awards & Accolades for MyTechLogy
Winner of
REDHERRING
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url

Back to Top