Proactive Database Administration

Author: Jon Seigel

Database applications can be scaled up or out by using SQL Server Service Broker, which provides asynchronous messaging and queuing infrastructure. The great thing is that the framework of this functionality is already built into your existing databases! In this video, I explain the basics of how a Service Broker application works and the database objects behind creating your own Service Broker applications.

Clustered and nonclustered indexes share many of the same internal structures, but they’re fundamentally different in nature. In this video, I compare the similarities and differences of clustered and nonclustered indexes, using a real-world example to show how these structures work to improve the performance of SQL queries.

The tempdb system database is used for many purposes from materializing temporary tables to storing row version information for snapshot isolation. The default configuration of tempdb may not perform well for many production workloads. In this video, three important considerations to configure tempdb for optimal performance are discussed, including a more in-depth visualization about why creating more data files can be a big advantage.

An advanced option I didn’t mention in the video is to enable Trace Flag 1118 (note: this trace flag is undocumented), which changes the allocation behaviour in tempdb to not use mixed extents. You can read more about this setting here.

Sometimes 3rd-party applications store their data in SQL Server, and the client is left to manage the database themselves.

In this video, I explore a few options you as a DBA have to manage the performance of the database, and offer suggestions for supported ways to improve the performance of both the database and the SQL Server instance used to contain it.