DBMIRROR_DBM_MUTEX: The world of Redo Operations

There are times when you just run into something and you can deem less important and continue. And there are times, where you have to stop and deal with it. In the past few weeks this wait type has simply come up way too many times. First, there is a great empirical blog post by Joe…

0

How to create a Deadlocked Scheduler Scenario at will

  First, please do not try this on your production server, because it will actually work. Second, this example is to illustrate how Deadlock scheduler issues can and do occur with the goal of increasing your understanding of the problem. Over the years of working with SQL Server, I learned that to understand a technology…

3

Meditation: Replicate Data from Multiple Instances into a Single Reporting Database

  Somebody approached me recently with a question like this: Question: We’ll be hosting our existing application in one data center, but we’ll be setting up 2 more instances in two different data centers. Is possible to use replication to move all the data into an single database so that we can report on all…

0

Choosing what SQLIO tests to Run and Automating the Tests

Many companies rely on IO stress-testing tools like SQLIO Disk Subsystem Benchmark Tool (aka SQLIO) to perform an I/O subsystem validation before they deploy SQL Server in production. Over the years, I have seen a variety of SQLIO tests being used and many of them are not really necessary because they do not mimic SQL…

9

Meditation: The Tao of the Deadlock Scheduler in SQL Server

Just thought I would summarize the concept of a SOS Scheduler and a Deadlocked Scheduler since many have inquired over the years for a brief summary of these concepts   UMS/SOS Scheduler in SQL  Server Since SQL Server 7.0, SQL  Server has used its own scheduling mechanism, called UMS (User-mode scheduler)  in 7.0 and 2000 and…

1

Tasks, Workers, Threads, Scheduler, Sessions, Connections, Requests – what does it all mean?

With this meditation I attempt to explain what some of the more common concepts that get used with SQL Server thread management and scheduling are. Parable: There was an all-powerful, but humble and benign Master, whom the workers revered and humbly served. The master accepted requests from other kingdoms and graciously agreed to grant all…

18

Meditation: Is the IOCP listener actually listening?

What is the IOCP Listener? The IOCP listener is a dedicated system thread that accepts incoming connections to SQL Server. It is a SQL Network Interface (SNI)-layer thread that listens for TCP/IP sockets or Named Pipes traffic (VIA is going away but still in the code). When a connection comes in, the listener accepts it…

0

Meditation on Locked Pages in Memory and User Rights

A colleague had the following question not too long ago Question: Does SQL Server check for “OS permissions” such as Locked Pages In Memory (LPIM) and if so does it use the Windows “macro-style” constants for them – such as SE_VOLUME_MANAGE? The reason I ask is that I’m still wanting to determine “major things” that…

0

SQL RPC vs. Windows RPC

  Question: “If one executes stored procedures using the RPC events mechanism (instead of Language events ), do any additional RPC ports need to be open (as is the case with SSIS )? Configuring the Windows Firewall to Allow SQL Server Access – http://msdn.microsoft.com/en-us/library/cc646023(v=sql.100).aspx   Answer: This confusion is introduced by “name overloading”, i.e “RPC”…

0