Course Details

Previous Page


Microsoft SQL Server 2012: Database Monitoring and Performance Tuning


Overview/Description
Target Audience
Expected Duration
Lesson Objectives
Course Number



Overview/Description
To keep SQL Server 2012 databases running as efficiently as possible, there are a variety of performance tuning and monitoring tools. The SQL Server Profiler enables you to use trace files and logs to analyze a database's performance. You can also use the Windows System Monitor to monitor SQL Server 2012 using counters, which monitor specific aspects of the server for performance issues. SQL Server 2012 includes the Activity Monitor, which provides an overview of the system's performance in real-time. SQL Server 2012 provides a the Resource Governor, which allocates specific resources to specific sessions, and a data collector, which collects specific data and stores it in a management data warehouse. Extended Events can be used to monitor performance information based on events that occur in the instance using sessions. In SQL Server 2012, concurrency issues may arise that require you to intervene by detecting the locking issue and possibly terminating a process that could be blocking a session from performing its task. Concurrency issues can arise when multiple users are trying to access the same data in a database. This course demonstrates how to use SQL Server Profiler, System Monitor, Activity Monitor, Extended Events, and the data collector to monitor and tune the performance of the database. It also addresses how to detect and resolve locks. This course is one of a series in the SkillSoft learning path that covers the objectives for the Microsoft SQL Server 2012 exam 70-462: Administering Microsoft SQL Server 2012 Databases. This exam is one of the requirements for the Microsoft Certified Solutions Associated (MCSA): SQL Server 2012 certification.

Target Audience
Professionals typically pursuing a career as a Database Administrator; individuals who do not work directly with SQL Server as part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators among others; Database Administrators who as part of their daily task perform installation, maintenance, and configuration tasks

Expected Duration (hours)
2.5

Lesson Objectives

Microsoft SQL Server 2012: Database Monitoring and Performance Tuning

  • describe the importance of database monitoring
  • recognize how SQL Server Profiler is used to monitor a database
  • describe the characteristics of SQL Server 2012's performance monitoring tools
  • create an Extended Event session using the CREATE EVENT SESSION statement
  • configure the management data warehouse to support data storage on instances of SQL Server that are using the data collector
  • recognize how Resource Governor is used to manage resources
  • monitor databases
  • access data to solve a server issues
  • describe how to manage SQL Server workload using Resource Governor
  • recognize how locking is used to secure data integrity in a SQL Server 2012 database
  • describe how to minimize deadlocks
  • manage deadlocks
  • describe how locking is used in a SQL Server 2012 database
  • use the features of SQL Server 2012 to manage and resolve deadlocks
  • Course Number:
    md_msdb_a08_it_enus