About this course

This Microsoft SQL server training course teaches the practical and functional techniques and knowledge which can be implemented in the workplace from the get go. Students through this exhaustive course go on to understand SQL Server architecture, tempdb, optimizing transaction log operations, locking and blocking, indexing and statistics strategies, data file configuration, and transactions and isolation levels. This IT ops training also provides students with the valuable opportunity of learning how to comprehend and examine workload and specify problem creation avenues, in addition to learning the creation of baselines and benchmark SQL Server performance and finally their fixing procedure. Top of the line technology and methodologies are also covered such as in-memory tables along with stored procedures.

Course Objectives

A successful completion of this IT Ops training would make the students able to:

• Have a comprehensive and functional knowledge of memory optimized tables

• Make use of startup stored procedures

• Comprehend and Execute instant file initialization process

• Have extensive grasp over SQL data location

• Explore the practicality of the latest cardinality estimator

• Have a comprehensive and functional grip of startup stored procedures..

• Have a comprehensive and functional grip of transactions.

• Have a comprehensive and functional grip of SQL Server locking architecture.

• Have a comprehensive and functional grip of SQL on virtual machines.

• Have a comprehensive and functional grip of partitioned tables and indexes.

• Have a comprehensive and functional grip of Activity Monitor.

• Have a complete knowledge of how to monitor SQL using Transact-SQL.

• Have a complete knowledge of indexes and their importance.

• Produce and edit indexes

• Have a comprehensive and functional grip of Database Tuning Advisor.

• Have a complete knowledge of fragmentation of indexes and utilize page splits.

• Have a comprehensive and functional grip of indexed views.

• Have a complete knowledge of index best practices.

• Have a comprehensive and functional grip of histogram.

• Have a comprehensive and functional grip of incremental statistics.

• Have a comprehensive and functional grip of filtered statistics..

• Have a comprehensive and functional grip of statistics maintenance plan.

• Have a comprehensive and functional grip of tables and indexes.

• Have a comprehensive and functional grip of natively compiled stored procedures.

• Have a comprehensive and functional grip of the Analyze Migrate Report tool.

• Have a comprehensive and functional grip of the SQL Trace Default template.

• Set up and operate templates for slow paced queries

• Utilize profiler to identify deadlocks and blocked processes.

• Produce and implement plan guides

• Have a comprehensive and functional grip of plan freezing.

• Have a complete knowledge of SQLIO Utility and its uses.

• Have a comprehensive and functional grip of the Resource Governor.

• Have a complete knowledge of Live Query Statistics.

• Have a comprehensive and functional grip of performance DMVs.

• Have a complete knowledge of index types.

• Have a comprehensive and functional grip of index metadata.

• Have a comprehensive and functional grip of Index Data Management Objects.

• Have a complete knowledge of index storage strategies.

• Have a complete knowledge of monitoring indexes.

• Have a comprehensive and functional grip of statistics objects.

• Have a comprehensive and functional grip of new vs old cardinality estimator.

• Have a comprehensive and functional grip of computed columns.

• Have a comprehensive and functional grip of ascending keys

• Understand architecture.

• Have a comprehensive and functional grip of the hash index.

• Understand restrictions.

• Have a comprehensive and functional grip of in-memory data management views.

• Have a comprehensive and functional grip of the SQL Profiler Tuning template.

• Observe and figure out extensive and lengthy queries

• Have a comprehensive and functional grip of query hints.

• Authorize SQL Profiler to create plans

• Have a comprehensive and functional grip on the new cardinality estimator

• Have a complete knowledge of all performance issues.

• Comprehend database structures

• Have a grasp over SQL stored data

• Have a theoretical understanding of temporary table intervals

• Use and have complete knowledge of memory optimized tables

• Have a comprehensive and functional grip of table valued parameters.

• Have complete knowledge of concurrency.

• Have a complete knowledge of isolation levels

• Have a complete knowledge of SQL and Storage Area Networks (SAN).

Audience

The course is primarily designed to target administrators and maintainers of SQL databases, who aim for achieving the optimal performance of their SQL Server. These administrators and maintainers are also responsible for drawing queries against the data while ensuring the perfect execution of available workload.

The course also attracts secondarily to application developers involved in content creation through SQL server databases.

Prerequisite

• A comprehensive knowledge of MS Windows Operating systems and its practical implementation

• A functioning understanding of database administration, maintenance and transact – SQL.

Suggested Prerequisite Course

• Administering a Microsoft SQL Server Database Infrastructure (MS-20764)

• Querying Data with Microsoft Transact-SQL (MS-20761)