If you have ever seen the BACKUPTHREAD wait type in the sysprocesses table or sp_who2 output and wondered what it is, I have found the answer.
The basic explanation is “Used when waiting for a backup thread to complete. Wait time may be very long (minutes, hours).” Basically what this means is that there is a backup running and something is waiting for it to complete.
When i saw this show up I was running a restore. That restore had three entries in the sysprocesses table. The first was the main kpid, with two child kpids. The parent kpid was the wait type of BACKUPTHREAD while it was waiting for the child kpid to finish processing. In my case the wait time was short, and it seamed to switch from this wait type to an IO wait type.
Service Broker is a transaction message queueing system build into Microsoft SQL Server. It provides you with in order, guaranteed single read, message processing that is handled and managed with T/SQL code. This makes it extremely easy to send and process messages either within a single database, or send those messages to a remote database on another server for processing. Messages are sent as XML documents so a message payload can contain a single field of data or a multiple row record set as a single message.
For those familiar with Microsoft Message Queue you will find that Service Broker is very similar to MSMQ but is native to the Microsoft SQL Server.
Server Broker can route messages from database to database, or server to server. Messages can be processed or routed to another server for processing there. Queues can be setup to hold messages for processing by an application or job, or have the messages processed as soon as they arrive by an activation procedure which is simply a procedure which is fired as messages arrive. Activation procedures can be run as a single thread or several threads pulling from the same queue at once.
Look for a future blog posting on configuring and using the service broker to send and process messages.
Take your XML execution plan and save it to a file with a file extension of sqlplan (such as MyQuery.sqlplan) and double click on it. It will open in the SQL Server Management Studio and show you the plan in the GUI plan viewer making it much easier to read than the XML version.
Watch our webcast featuring Meagan Longoria, Kevin Kline and Denny Cherry as they explore how to make communications clearer, especially during these stressful situations by improving your report visualization techniques.
As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.
And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.