Performance Analysis of tempdb in SQL Server databases

Source: Internet
Author: User
SQLServer has four important system-level databases: master, model, msdb, tempdb.1.SQLServer System database introduction SQLServer has four important system-level databases: master, model, msdb, and tempdb. master: records all system-level information of the SQLServer system, including metadata of the instance range, endpoints, linked servers, and system configuration settings.

SQL Server has four important system-level databases: master, model, msdb, and tempdb. 1. SQL Server System database introduction SQL Server has four important system-level databases: master, model, msdb, and tempdb. master: records all system-level information of the SQL Server System, including metadata of the instance range, endpoints, linked servers, and system configuration settings.

SQL Server has four important systems: master, model, msdb, and tempdb.

  1. SQL Server System database Introduction

SQL Server has four important system-level databases: master, model, msdb, and tempdb.

Master: records all system-level information of the SQL Server System, including metadata of the instance range, endpoints, links, and system configuration settings. It also records the existence of other databases and the location of these Data Query files. if the master node is unavailable, the database cannot be started.

Model: The template used for all databases created on the SQL Server instance. Because tempdb is created every time you start SQL Server, the model database must always exist in SQL Server.

Msdb: used by the SQL Server proxy to plan alarms and jobs.

Tempdb: a global resource available to all users connected to the SQL Server instance. It stores all temporary tables, temporary worksheets, temporary stored procedures, large types of temporary storage, and intermediate result sets, table variables and cursors. In addition, it is used to meet all other temporary storage requirements.

  2. Internal Operating Principle of tempdb

Unlike other SQL Server databases, tempdb stops at SQL Server and automatically drops and re-create upon restart. by default, a new 8 MB (mdf file: 8 MB; ldf file: 1 MB, autogtouth is set to 10%) recovery model is a simple tempdb database.

After the tempdb database is created, DBA can create data objects, temporary tables, temporary stored procedures, and table variables in other databases and add them to tempdb. when tempdb activities are frequent, it can automatically increase because it is a simple recovery model, which minimizes log records and continuously truncates logs.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.