msdb Database

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail. The sizes of msdb files may vary slightly for different editions of SQL Server Database Engine.

The following operations cannot be performed on the msdb database:

• Changing collation. The default collation is the server collation.
• Dropping the database.
• Dropping the guest user from the database.
• Enabling change data capture.
• Participating in database mirroring.
• Removing the primary filegroup, primary data file, or log file.
• Renaming the database or primary filegroup.
• Setting the database to OFFLINE.
• Setting the primary filegroup to READ_ONLY.

To know more about other System Databases Click Here

Wait Stats Cont..

For more information on the wait conditions firstly please refer this post

The below mentioned are basic explanations of each of the major wait types. Appearance of any of these wait types high up will certainly help direct your subsequent investigations.

CXPACKET

Often indicates nothing more than that certain queries are executing with parallelism; CXPACKET waits in the server are not an immediate sign of problems, although they may be the symptom of another problem, associated with one of the other high value wait types in the instance

SOS_SCHEDULER_YIELD

The tasks executing in the system are yielding the scheduler, having exceeded their quantum, and are having to wait in the runnable queue for other tasks to execute. This may indicate that the server is under CPU pressure

THREADPOOL

A task had to wait to have a worker bound to it, in order to execute. This could be a sign of worker thread starvation, requiring an increase in the number of CPUs in the server, to handle a highly concurrent workload, or it can be a sign of blocking, resulting in a large number of parallel tasks consuming the worker threads for long periods.

LCK_*

These wait types signify that blocking is occurring in the system and that sessions have had to wait to acquire a lock of a specific type, which was being held by another database session. This problem can be investigated further using the information in the sys.dm_db_index_operational_stats

PAGEIOLATCH_*, IO_COMPLETION, WRITELOG

These waits are commonly associated with disk I/O bottlenecks, though the root cause of the problem may be, and commonly is, a poorly performing query that is consuming excessive amounts of memory in the server. PAGEIOLATCH_* waits are specifically associated with delays in being able to read or write data from the database files. WRITELOG waits are related to issues with writing to log files. These waits should be evaluated in conjunction with the virtual file statistics as well as Physical Disk performance counters, to determine if the problem is specific to a single database, file, or disk, or is instance wide.

PAGELATCH_*

Non-I/O waits for latches on data pages in the buffer pool. A lot of times PAGELATCH_* waits are associated with allocation contention issues. One of the best-known allocations issues associated with PAGELATCH_* waits occurs in tempdb when the a large number of objects are being created and destroyed in tempdb and the system experiences contention on the Shared Global Allocation Map (SGAM), Global Allocation Map (GAM), and Page Free Space (PFS) pages in the tempdb database.

LATCH_*

These waits are associated with lightweight short-term synchronization objects that are used to protect access to internal caches, but not the buffer cache. These waits can indicate a range of problems, depending on the latch type. Determining the specific latch class that has the most accumulated wait time associated with it can be found by querying the sys.dm_os_latch_stats DMV.

ASYNC_NETWORK_IO

This wait is often incorrectly attributed to a network bottleneck. In fact, the most common cause of this wait is a client application that is performing row-by-row processing of the data being streamed from SQL Server as a result set (client accepts one row, processes, accepts next row, and so on). Correcting this wait type generally requires changing the client-side code so that it reads the result set as fast as possible, and then performs processing.

Good article on Performance Tuning SQL Database Performance Tuning for Developers

Another good article for Developers  MetaDapper: Data Mapping and Conversion Made Easy With the Right Tools

Tempdb Database

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

• Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
• Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
• Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
• Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged.

In SQL Server, tempdb performance is improved in the following ways:

• Temporary tables and table variables may be cached. Caching allows operations that drop and create the temporary objects to execute very quickly and reduces page allocation contention.
• Allocation page latching protocol is improved. This reduces the number of UP (update) latches that are used.
• Logging overhead for tempdb is reduced. This reduces disk I/O bandwidth consumption on the tempdb log file.
• Setup adds multiple tempdb data files during a new instance installation. This task can be accomplished with the new UI input control on theDatabase Engine Configuration section and a command line parameter /SQLTEMPDBFILECOUNT. By default, setup will add as many tempdb files as the CPU count or 8, whichever is lower.
• When there are multiple tempdb data files, all files will autogrow at same time and by the same amount depending on growth settings. Trace flag 1117 is no longer required.
• All allocations in tempdb use uniform extents. Trace flag 1118 is no longer required.
• For the primary filegroup, the AUTOGROW_ALL_FILES property is turned on and the property cannot be modified.

The tempdb database is similar to the operating system paging file. It’s used to hold temporary objects created by users, temporary objects needed by the database engine, and row-version information. The tempdb database is created each time SQL Server is restarted.

For other SQL Server system databases click here

Master Database

The master database contains metadata about your database. It contains the database configuration and file location, logins and configuration information about the instance.

The main difference between the resource and master database is that the master database holds data specific to your instance, whereas the resource database holds the schema and stored procedure needed to run your instance, but does not contain any data specific to your instance. You should always have regular backups of the master database.

For other SQL Server system databases click here

System Databases in SQL Server

System database in SQL Server are crucial, and you should leave them alone most of the time, The only exception to that rule is model database, which allows you to deploy changes such as stored procedures to any new database created. If a system database is tampered with or corrupted, you run the risk that SQL Server will not start. It contains all the stored procedures and tables needed for SQL Server to remain online.

master Database  – Records all the system-level information for an instance of SQL Server.

msdb Database – Is used by SQL Server Agent for scheduling alerts and jobs.

model Database – Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.

Resource Database – Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

tempdb Database – Is a workspace for holding temporary objects or intermediate result sets.