Optimizing Performance for Database Instances Part 1

Introduction

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.

Exam Objectives

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.