SQL Server System Databases

Master:

The master database is the database that holds all the system-level information on the SQL Server Instance.

Msdb:

The msdb database is used by SQL Server Agent to schedule alerts and jobs.

Model:

When creating a new database, SQL Server sets the default settings based on the model database. For example, Database Size, Collation Information, Recovery Model, AutoGrowth Setting etc.

Tempdb:

I’ll mostly tell the tempdb database.

Because it is necessary to make different configurations according to the type and intensity of the system.

When we create temporary objects such as temp tables, these objects use the tempdb database.

Therefore, it may be necessary to increase the number of tempdb files on an instance with high transaction intensities.

Using the script on the link below you can see how much of your tempdb file reads and writes, and how long your read and writes wait.

https://www.brentozar.com/sql/tempdb-performance-and-configuration/

If your Reads and Writes exceed 20 ms, you can increase the tempdb file count.

As I mentioned in the article “How To Create a Database On SQL Server“, it is useful to set tempdb’s auto growth option, Initial Size.

You can also see how you can increase the number of files in the same article.

What queries fill the tempdb?

You can also find the answer of this question on the following article.

“Which Queries Fill the Tempdb“