• 100 Queen St W, Brampton, ON L6X 1A4, Canada
  • +1-800-961-0337
START DATE END DATE CLASS TIMINGS MODE LOCATION ACTION
12/17/2018 12/21/2018
  • VLT
Live Online
11/12/2018 11/16/2018
  • VLT
  • GTR
Live Online

55144: SQL Server 2014 Performance Tuning And Optimization Course 

55144: SQL Server 2014 Performance Tuning And Optimization course is intended to give the appropriate measure of Internals information, and abundance of functional tuning and improvement systems, that you can put into creation. The course offers a complete scope of SQL Server engineering, ordering and insights methodologies, enhance exchange log operations, tempdb and information document setup, exchanges and detachment levels, and bolting and blocking. The course likewise instructs how to make baselines and benchmark SQL Server execution, how to dissect workload and make sense of where execution issues are, and how to settle them. Scope of in-memory tables and put away methods alongside a prologue to this energizing new innovation is incorporated. 

55144: SQL Server 2014 Performance Tuning And Optimization Audience Profile

The essential gathering of people for this course is people who control and keep up SQL Server databases, and are in charge of ideal execution of SQL Server examples that they oversee. These people additionally compose questions against information and need to guarantee ideal execution of the workloads. 

The auxiliary crowds for this course are people who create applications that convey content from SQL Server databases. 

55144: SQL Server 2014 Performance Tuning And Optimization At Course Completion

In the wake of finishing this course, understudies will have the capacity to: 

  • Comprehend and use the new cardinality estimator. 
  • Comprehend and use memory-advanced tables. 
  • Comprehend execution. 
  • Comprehend and use startup put away techniques. 
  • Comprehend database structures. 
  • Comprehend and use Instant File Initialization. 
  • See how SQL put away information. 
  • See how SQL finds information. 
  • Comprehend impermanent table internals. 
  • Comprehend and use the new cardinality estimator. 
  • Comprehend and use memory-enhanced tables. 
  • Comprehend execution. 
  • Comprehend and use startup put away strategies. 
  • Comprehend database structures. 
  • Comprehend and use Instant File Initialization. 
  • See how SQL put away information. 
  • See how SQL finds information. 
  • Comprehend transitory table internals. 
  • Comprehend and use table esteemed parameters. 
  • Comprehend simultaneousness. 
  • Comprehend and use exchanges. 
  • Comprehend separation levels. 
  • Comprehend and use SQL Server locking engineering. 
  • Comprehend SQL and Storage Area Networks (SAN). 
  • Comprehend and use SQL on virtual machines. 
  • Comprehend SQLIO Utility and its employments. 
  • Comprehend and use apportioned tables and records. 
  • Comprehend and use the Resource Governor. 
  • Comprehend and use Activity Monitor. 
  • See Live Query Statistics. 
  • See how to screen SQL utilizing Transact-SQL. 
  • Comprehend and use execution DMVs. 
  • Comprehend files and their significance. 
  • Comprehend file sorts. 
  • Make and adjust records. 
  • Comprehend and use file metadata. 
  • Comprehend and use Database Tuning Advisor. 
  • Comprehend and use Index Data Management Objects. 
  • Comprehend fracture of records and use page parts. 
  • Comprehend file stockpiling systems. 
  • Comprehend and use listed perspectives. 
  • Comprehend checking lists. 
  • Comprehend list best practices. 
  • Comprehend and use insights objects. 
  • Comprehend and use histogram. 
  • Comprehend and use new versus old cardinality estimator. 
  • Comprehend and use incremental measurements. 
  • Comprehend and use processed sections. 
  • Comprehend and use separated measurements. 
  • Comprehend and use rising keys. 
  • Comprehend and use measurements upkeep design. 
  • Comprehend design. 
  • Comprehend and use tables and lists. 
  • Comprehend and use the hash record. 
  • Comprehend and use locally accumulated put away techniques. 
  • Comprehend confinements. 
  • Comprehend and use the Analyze Migrate Report instrument. 
  • Comprehend and use in-memory information administration sees. 
  • Comprehend and use the SQL Trace Default format. 
  • Comprehend and use the SQL Profiler Tuning format. 
  • Make and use a format for moderate running inquiries. 
  • Comprehend and distinguish long running inquiries. 
  • Utilize Profiler to identify stops. 
  • Utilize Profiler to identify a blocked procedure. 
  • Comprehend and use question clues. 
  • Make and test an arrangement manage. 
  • Permit SQL Profiler to help make an arrangement. 
  • Comprehend and use design solidifying. 

55144: SQL Server 2014 Performance Tuning And Optimization Course Outline 

Module 1: Course Overview 

55144: SQL Server 2014 Performance Tuning And Optimization module clarifies how the class will be organized and presents course materials and extra managerial information.

Lessons 

  • Presentation 
  • Course Materials 
  • Offices 
  • Essentials 
  • What We'll Be Discussing 

Lab : Course Overview 

  • None 
  • Effectively sign into their virtual machine. 
  • Have a full comprehension of what the course plans to cover. 

Module 2: SQL 2014 Architecture 

In 55144: SQL Server 2014 Performance Tuning And Optimization module, we will inspect the new Cardinality Estimator, investigate memory-streamlined tables, and clarify how SQL the two stores and finds data.

Lessons 

  • The New Cardinality Estimator 
  • Memory-Optimized Tables 
  • Understanding Performance for Developers 
  • Understanding Startup Parameters 
  • Startup Stored Procedures 
  • Database Structures 
  • Moment File Initialization 
  • How SQL Stores Data 
  • How SQL Locates Data 

Lab : SQL 2014 Architecture 

  • Designing Compatibility Level 
  • Startup Parameter 
  • Startup Stored Procedure 
  • Moment File Initialization 
  • Comprehend and use the new Cardinality Estimator. 
  • Comprehend and use memory-streamlined tables. 
  • Comprehend execution. 
  • Comprehend and use startup put away methodology. 
  • Comprehend database structures. 
  • Comprehend and use Instant File Initialization. 
  • See how SQL put away information. 
  • See how SQL finds information. 

Module 3: The Database Engine 

The SQL Server Database Engine is part into two noteworthy segments, which are the capacity motor and the social motor. The social motor is likewise called the question processor which is a more distinct term for what it really does. In this module, we investigate how the motors works, and cover ideas that will help you to comprehend their conduct. 

Lessons 

  • ? Four Important Concepts 
  • ? Brief Tables Internals 
  • ? Simultaneousness 
  • ? Exchanges 
  • ? Segregation Levels 
  • ? SQL Server Locking Architecture 
  • ? SQL and Storage Area Networks (SAN) 
  • ? SQL on VMs 
  • ? SQLIO Utility 
  • ? Apportioned Tables and Indexes 

Lab : The Database Engine 

  • Table-Valued Parameter 
  • Exchanges 
  • Vertically Partitioned Table Using Code 
  • Apportioning with Filegroups 
  • Apportioning Wizard 
  • Comprehend brief table internals. 
  • Comprehend and use table esteemed parameters. 
  • Comprehend simultaneousness. 
  • Comprehend and use exchanges. 
  • Comprehend segregation levels. 
  • Comprehend and use SQL Server locking engineering. 
  • Comprehend SQL and Storage Area Networks (SAN). 
  • Comprehend and use SQL on virtual machines. 
  • Comprehend SQLIO Utility and its employments. 
  • Comprehend and use divided tables and files. 

Module 4: SQL Performance Tools 

SQL gives various powerful observing instruments and in this module we investigate the devices accessible and clarify what they are intended to do. 

Lessons 

  • The Resource Governor 
  • Movement Monitor 
  • Live Query Statistics 
  • Observing SQL with Transact-SQL 
  • Dynamic Management Objects (DMOs) and Performance Tuning 

Lab : SQL Performance Tools 

  • Asset Governor 
  • Movement Monitor 
  • sp_who2 
  • Execution DMVs 
  • Comprehend and use the Resource Governor. 
  • Comprehend and use Activity Monitor. 
  • See Live Query Statistics. 
  • See how to screen SQL utilizing Transact-SQL. 
  • Comprehend and use execution DMVs. 

Module 5: Query Optimizing and Operators 

SQL Server question improvement remains an imperative piece of the present database applications. In this module we inspect the tuning procedure, alternatives for investigating, execution designs, Performance Monitor, and considerably more. 

Lessons 

  • Tuning Process 
  • Execution Monitor Tool 
  • SQL Query Processing Steps 
  • Understanding Execution Plans 
  • SET STATISTICS TIME and SET STATISTICS IO 
  • Information Access Operators 
  • Investigating Queries 

Lab : Query Optimizing and Operators 

  • ? Execution Monitor (perfmon) 
  • ? Evaluated versus Actual Plans and XML Plans 
  • ? Survey a Non-Trivial Execution Plan 
  • ? SET STATISTICS TIME and SET STATISTICS IO 
  • ? Information Access Operators 
  • ? DMVs 
  • ? View Information About Extended Events 
  • ? Wizard Templates 
  • ? Making a Session Without a Wizard 
  • ? Arranging MDW and Viewing Reports 
  • Comprehend the tuning procedure. 
  • Comprehend and use Performance Monitor apparatus. 
  • Comprehend SQL inquiry preparing steps. 
  • Comprehend and use execution designs. 
  • Comprehend and use SET STATISTICS TIME and SET STATISTICS IO. 
  • Comprehend and use Data Access Operators. 
  • Comprehend and use DMVs. 
  • Comprehend and use Extended Events. 
  • Comprehend and use sessions. 
  • See how to investigating inquiries. 
  • See how to arrange a Management Data Warehouse and view reports. 

Module 6: Understanding Indexes 

The whole idea of ordering is quick recovery. In this module we disclose all that you have to think about making, using, overseeing, and observing lists. 

Lessons 

  • Prologue to Indexes 
  • Record Types by Storage 
  • Record Types by Column Designation 
  • Making and Altering Indexes 
  • Metadata 
  • Information Management Views for Indexing 
  • Database Engine Tuning Advisor 
  • Record Data Management Objects 
  • SQL Server Fragmentation 
  • Examples 
  • File Storage Strategies 
  • Filed Views 
  • Observing Indexes 
  • File Dynamic Management Objects (DMOs) 
  • Best Practices 

Lab : Understanding Indexes 

  • Make Indexes 
  • File Metadata 
  • Database Tuning Advisor 
  • Missing Index DMOs 
  • Fracture and Page Splits 
  • Information Compression 
  • Filed Views 
  • Doubleclick to embed next Lab 
  • Comprehend files and their significance. 
  • Comprehend record sorts. 
  • Make and modify lists. 
  • Comprehend and use record metadata. 
  • Comprehend and use Database Tuning Advisor. 
  • Comprehend and use Index Data Management Objects. 
  • Comprehend fracture of lists and use page parts. 
  • Comprehend record stockpiling techniques. 
  • Comprehend and use filed sees. 
  • Comprehend observing lists. 
  • Comprehend record best practices. 

Module 7: Understanding Statistics 

Insights are viewed as a standout amongst the most vital parts of SQL Server Performance Tuning. In this module we look at insights and how to use them. 

Lessons 

  • Insights 
  • Cardinality Estimator 
  • Incremental Statistics 
  • Figured Columns Statistics 
  • Sifted Statistics 
  • Support 

Lab : Understanding Statistics 

  • Insights Objects 
  • Histogram 
  • New versus Old Cardinality Estimator 
  • Incremental Statistics 
  • Figured Columns 
  • Sifted Statistics 
  • Climbing Keys 
  • Insights Maintenance Plan 
  • Comprehend and use insights objects. 
  • Comprehend and use the histogram. 
  • Comprehend and use new versus old cardinality estimator. 
  • Comprehend and use incremental measurements. 
  • Comprehend and use registered sections. 
  • Comprehend and use separated measurements. 
  • Comprehend and use rising keys. 
  • Comprehend and use measurements support design. 

Module 8: In-Memory Database 

The most essential new component in SQL Server 2014 is the new In-Memory OLTP motor. In this module we will cover the engineering, limitations, and other energizing elements. 

Lessons 

  • Design 
  • Tables and Indexes 
  • Locally Compiled Stored Procedures 
  • Limitations 
  • Break down Migrate Report Tool 
  • In-Memory Data Management Views (DMV) 

Lab : In-Memory Database 

  • ? Making an In-Memory Enabled Database with Code and SSMS 
  • ? Making a Memory-Optimized Table 
  • ? View Hash Index 
  • ? Locally Compiled Stored Procedure 
  • ? AMR Tool 
  • ? In-Memory DMVs 
  • Comprehend design. 
  • Comprehend and use tables and records. 
  • Comprehend and use the hash record. 
  • Comprehend and use locally assembled put away systems. 
  • Comprehend confinements. 
  • Comprehend and use the Analyze Migrate Report instrument. 
  • Comprehend and use in-memory information administration sees. 

Module 9: SQL Profiler and SQL Trace 

The SQL Server 2014 Profiler is basically the same as the SQL Server 2012 Profiler. This is not astonishing in light of the fact that numerous new elements that were included SQL Server 2005 and SQL Server 2008 tended to holes distinguished in past variants. In this module, we cover both SQL Profiler and SQL Trace. 

Lessons 

  • SQL Trace 
  • SQL Trace Architecture 
  • SQL Server Profiler 

Lab : SQL Profiler and SQL Trace 

  • ? SQL Trace Default Template 
  • ? SQL Profiler Tuning Template 
  • ? Make a Template for Slow Running Queries 
  • ? Recognizing Long Running Queries 
  • ? Utilizing Profiler to Detect Deadlocks 
  • ? Utilize Profiler to Detect a Blocked Process 
  • Comprehend and use the SQL Trace Default layout. 
  • Comprehend and use the SQL Profiler Tuning layout. 
  • Make and use a layout for moderate running questions. 
  • Comprehend and recognize long running questions. 

Module 10: Query Issues and Hints 

In this module, we will analyze inquiry issues, how to recognize them, and how to determine them. We'll likewise cover question insights, design manages, and investigate design solidifying. 

Lessons 

  • Inquiry Hints 
  • Plan Guides 
  • Plan Freezing 
  • Join Orders 

Lab : Query Issues and Hints 

  • Make and Test a Plan Guide 
  • Permit SQL Profiler to Help Create a Plan 
  • Plan Freezing 
  • Comprehend and use inquiry indications. 
  • Make and test an arrangement direct. 
  • Permit SQL Profiler to help make an arrangement. 
  • Comprehend and use design solidifying.
  • Essential learning of the Microsoft Windows working framework and its center usefulness. 
  • Working learning of database organization and support. 
  • Working learning of Transact-SQL.

Awards