Scale Out Database Applications Using SQL Server Service Broker (Video)

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.

Video resources:

SqlDependency class
Service Broker samples on CodePlex
ssbdiagnose utility

Clustered vs. Non-Clustered Index Internal Structures (Video)

Jon Seigel

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.

Video resources:

Blog post on primary key vs. the clustered index
CREATE INDEX statement reference
ALTER INDEX statement reference
Index navigation internals by example

Sample index data is from the AdventureWorksLT2008R2 sample database

Standard Configuration of tempdb (Video)

Jon Seigel

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.

Video Resources:

Read more about sys.dm_os_waiting_tasks
Read more about sys.dm_os_wait_stats
Read more about DBCC SQLPERF
sys.dm_os_waiting_tasks query to find PAGELATCH_* waits

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.

Improve and Manage the Performance of Vendor Databases (Video)

Jon Seigel

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.

Registered Servers and Central Management Server: In Action (Video)

Jon Seigel

In a previous post/video, I introduced Registered Servers and Central Management Server, and showed how to set up the server hierarchy.

In this video, I show you how to use these features to manage groups of servers using query windows and Policy-Based Management, and we see some of the pitfalls that may be encountered along the way.

Please subscribe to the YouTube channel to be notified first when I post a new video, and to see extra content that won’t appear here on the blog.