Optimizing Performance for Database Instances Part 1
The Optimizing Performance for Database Instances Part 1 module provides you with the instructions and devices to develop your hands-on skills in the following topics: Working with tempdb database, Working with dynamic management views, Working with memory manager.
Already have an account? Sign In »
The Optimizing Performance for Database Instances Part 1 module provides you with the instructions and devices to develop your hands-on skills in the following topics.
- Working with tempdb database
- Working with dynamic management views
- Working with memory manager
Lab time: It will take approximately 40 minutes to complete this lab.
Four exam objectives are covered in this lab.
- Demonstrate how to optimize the performance of tempdb by configuring it
- Demonstrate how to set the memory options to optimize memory configuration of the server
- Demonstrate how to obtain information on wait statistics using dynamic management view
- Demonstrate how to monitor and analyze storage issues, I/O issues using DMV
Exercise 1 - Working with tempdb Database
The tempdb is a system database that stores all types of database objects such as user-defined tables, indexes, global and local temporary tables, and so on. The tempdb database does not allow you to perform certain tasks. Some of them are as follows:
- Backup or restore the database.
- Drop the database.
- Set the database to OFFLINE mode.
- Rename the database.
In this exercise, you will learn to work with the tempdb database.
Exercise 2 - Working with Dynamic Management Views
Dynamic management views (DMVs) provide server state information that helps to monitor the database, diagnose, and troubleshoot problems. The DMVs are present in the sys schema. The DMVs begin with dm_*.
In this exercise, you will learn to work with dynamic management views.
Exercise 3 - Working with Memory Manager
The memory manager component of the SQL Server dynamically determines the allocation of memory for the database. There are many server configuration options to set up the memory usage such as index create memory, min server memory, max worker threads, min memory per query, and so on.
In this exercise, you will learn to work with the memory manager component of the SQL Server.
IT & Cybersecurity certification hands on practice labs and practice exams for certifications and skill development.
See the full benefits of our immersive learning experience with interactive courses and guided career paths.