SQL Server 2014 Performance Tuning and Optimisation

This five-day course is designed to give the right amount of Internals knowledge and wealth of practical tuning and optimisation techniques, that you can put into production. The course offers a comprehensive coverage of SQL Server architecture, indexing and statistics strategies, optimise transaction log operations, tempdb and data file configuration, transactions and isolation levels, locking and blocking. The course also teaches how to create baselines and benchmark SQL Server performance, how to analyse workload and figure out where performance problems are and how to fix them. Coverage of in-memory tables and stored procedures along with an introduction to this exciting new technology is included.

After completing this course, students will be able to:

  • Understand and utilise the new cardinality estimator
  • Understand and utilise memory-optimised tables
  • Understand performance
  • Understand and utilise startup stored procedures
  • Understand database structures
  • Understand and utilise Instant File Initialisation
  • Understand how SQL stored data
  • Understand how SQL locates data
  • Understand temporary table internals
  • Understand and utilise the new cardinality estimator
  • Understand and utilise memory-optimised tables
  • Understand performance
  • Understand and utilise startup stored procedures
  • Understand database structures
  • Understand and utilise Instant File Initialisation
  • Understand how SQL stored data
  • Understand how SQL locates data
  • Understand temporary table internals
  • Understand and utilise table valued parameters
  • Understand concurrency
  • Understand and utilise transactions
  • Understand isolation levels
  • Understand and utilise SQL Server locking architecture
  • Understand SQL and Storage Area Networks (SAN)
  • Understand and utilise SQL on virtual machines
  • Understand SQLIO Utility and its uses
  • Understand and utilise partitioned tables and indexes
  • Understand and utilise the Resource Governor
  • Understand and utilise Activity Monitor
  • Understand Live Query Statistics
  • Understand how to monitor SQL using Transact-SQL
  • Understand and utilise performance Dynamic Management Views (DMVs)
  • Understand indexes and their importance
  • Understand index types
  • Create and alter indexes
  • Understand and utilise index metadata
  • Understand and utilise Database Tuning Advisor
  • Understand and utilise Index Data Management Objects
  • Understand fragmentation of indexes and utilise page splits
  • Understand index storage strategies
  • Understand and utilise indexed views
  • Understand monitoring indexes
  • Understand index best practices
  • Understand and utilise statistics objects
  • Understand and utilise histogram
  • Understand and utilise new vs old cardinality estimator
  • Understand and utilise incremental statistics
  • Understand and utilise computed columns
  • Understand and utilise filtered statistics
  • Understand and utilise ascending keys
  • Understand and utilise statistics maintenance plan
  • Understand architecture
  • Understand and utilise tables and indexes
  • Understand and utilise the hash index
  • Understand and utilise natively compiled stored procedures
  • Understand restrictions
  • Understand and utilise the Analyse Migrate Report tool
  • Understand and utilise in-memory data management views
  • Understand and utilise the SQL Trace Default template
  • Understand and utilise the SQL Profiler Tuning template
  • Create and utilise a template for slow running queries
  • Understand and identify long running queries
  • Use Profiler to detect deadlocks
  • Use Profiler to detect a blocked process
  • Understand and utilise query hints
  • Create and test a plan guide
  • Allow SQL Profiler to help create a plan
  • Understand and utilise plan freezing

The primary audience for this course is individuals who administer and maintain SQL Server databases and are responsible for optimal performance of SQL Server instances that they manage. These individuals also write queries against data and need to ensure optimal execution performance of the workloads.

The secondary audiences for this course are individuals who develop applications that deliver content from SQL Server databases.

Before attending this course, students must have:

  • Basic knowledge of the Microsoft Windows Operating System and its core functionality
  • Working knowledge of database administration and maintenance
  • Working knowledge of Transact-SQL

All our public scheduled training courses offer delegates the following facilities which are all included at no extra cost: 

  • Mini Continental Breakfast, with a choice of Croissants, Pastries, Cereals, Juices, and of course Tea and Coffee
  • Buffet Lunch
  • A Selection of Soft Beverages are available throughout the Training Day
  • Full Course Manual (Official Curriculum Courseware for Technical Courses where Designated)
  • Course Exercise Files on CD\DVD or Online
  • Course Stationery Pack (Pen, Pencil and Paper)
  • Course Certificate (Vendor Accredited where Designated)
  • FREE Internet Access during your visit (Wi-Fi as Available)

There are no records to display

Email This Page to a Friend
module by Spiral

Course Details:

Duration: 5 Days
Delivery: Instructor led
Type: Course
Setting: Classroom
Language: English
Audience: IT Professionals
Code: MS55144
Exam: -
Level: 200

Associate Microsoft Trainer

Associate with Nexgenn

As a Microsoft Certified Learning Partner we have a commitment to our customers to provide the highest level of training standards possible. To this end we continue to seek out talented individuals with a natural flair for communicating technical subject matter to a wide variety of audiences.

With customers both here in the UK and Europe, we deliver classess in native english covering a wide spectrum of products.

We are always looking to add to our existing portfolio of trainers. You must already have achieved your MCT qualification and have some commercial training delivery experience. Perhaps you may have already chosen a specialist subject to focus on such as Microsoft SharePoint Server, SQL Server, System Center, Exchange Server or generalise in Windows Server – We’d like to hear from you.

In the first instance please send us your CV to our Human Resources department. Please also include a link to your online transcript.

Need to know more about our associate program, give us a call on 0845 600 6061