Friday, August 17, 2018

Internals of Performance Tuning Using SQL Server 2014 Syllabus

SQL Server 2014 Performance Internals Training Schedule


 Start Date

 End Date

 Class Days

 Class Time

 Class Type




 May 9, 2015

 Jun 28, 2015

 Sat & Sun.

 7 - 8:30 pm EST

 Live Instructor-Led

 $849 (USD)



Course Details:

  1. Internals of Indexes – Clustered and non-clustered index
  2. Database Files – data file, log file, good practices
  3. Understanding Lock Resources
  4. Lock Modes and Compatibility
  5. Lock Hierarchy
  6. Isolation Levels
  7. Identifying Blocking using DMV’s and Activity Monitor
  8. Lock WAIT Types
  9. Common causes of Blocking
  10. Locking hints and Lock Timeout
  11. Understanding Read_Committed_Snapshot
  12. What are deadlocks?
  13. How to detect deadlocks
  14. How to troubleshoot deadlocks
  15. Detecting and Diagnosing Spinlock Contention
  16. Best Practices to prevent Locks, blocks and deadlocks
  17. Common causes of high CPU
  18. Detecting queries causing high CPU’s
  19. Performance Counters to use for Identifying CPU Bottleneck
  20. Detecting inefficient query plans
  21. Troubleshooting Excessive Query Compilation and Optimization
  22. Detecting and Troubleshooting Intra-query Parallelism
  23. Identifying and Troubleshooting Unnecessary Recompilation
  24. Identifying and Troubleshooting Poor Cursor Usage
  25. Understanding System Threads that Consume CPU
  26. Best Practices to prevent CPU bottleneck
  27. Internals of Query Parallelism
  28. Troubleshooting Query Parallelism Issue
  29. What are WAITS and Queues?
  30. Troubleshooting WAITS and Queues
  31. Uncovering Query Fingerprints and Plan Fingerprints
  32. Detecting IO Issues
  33. Reviewing Performance Counters to Troubleshoot IO Issues
  34. Understanding SQL Server Virtual File Stats
  35. Tuning SQL Server to reduce or balance IO Load
  36. Data Compression
  37. Understanding CHECKPOINTS in SQL Server
  38. Flushing Transaction Logs
  39. Common causes of IO Issus and their resolution
  40. IO Bottlenecks
  41. Best Practices for Troubleshooting IO in SQL Server
  42. What are latches?
  43. How are latches used in SQL Server?
  44. Different types of Latch modes
  45. Latch Compatibility
  47. Understanding Super and Sub Latches
  48. Latch Classes
  49. How is TempDB Impacted due to Latching?
  50. Understanding Allocation Bottleneck
  51. Understanding Latch Contention
  52. What impacts Latch Contention?
  53. Diagnosing Latch Contention
  54. Analyzing Current WAIT Buffer Latches
  55. Type of WAITS for Executing Sessions
  56. Performance impact of T-SQL Operators
  57. Internals of Statistics and it’s selectivity and Cardinality
  58. Understanding Query Plans
  60. Internals of Common Operators (Joins (Nested Loop Joins, Merge Joins, Hash Joins, Bookmark Lookup, Sort, Table Spool, Aggregates)
  61. Common Tips (Missing Indexes, Execution Warnings, Missing Stats Warning, Avoiding Sort, Mismatched Data Type)
  62. Common Query Plan Issues
  63. Statistics Management
  64. Missing Indexes
  65. Non-SARGable Predicate
  66. Performance Impact of Local variables
  67. Issues with Multi-Statement TVF, Table Variables, TVP, Recursive CTE, Row Goals, Data Type Mismatch, Parameter Sniffing
  68. Optimizer Time out
  69. Working with Large amount of Memory
  70. Managing Large Number of Locks
  71. Managing and maintaining Large Plan Cache
  72. Diagnosing Query Execution Warnings
  73. Query Hint Techniques
  74. Internals of Plan Guide
  75. Indexed Views
  76. Partitioned Tables
  77. Distributed Query/Linked Server
  78. Column Store Index
  79. AlwaysOn – Performance feature - Statistics on secondary
  80. Performance related tips, tricks and techniques and scripts
Download Course Syllabus for SQL Server 2014 Developer Course
SQL Developer Training Value
Corporate and Onsite Training by SQL Operations
Contact SQL Operations
SQL Operations Services


Home   |   Training   |   Services   |   Risk & Health Audit   |   Career   |   About Us   |   Contact Us
Copyright 2018 by SQLOPERATIONS