Columnar Databases and SQL Server Columnstore Indexes

Lately I have been working on Vertica - a column based database from HP and I have to admit that I am amazed by its capabilities and the functionalities it offers, especially when we are talking about relatively big data. The basic difference between SQL Server and Vertica is the way they store data- SQL server being a row store database and Vertica being a column store database. With Denali, SQL Server has also come up with something on the similar lines with ColumnStore indexes (codenamed Apollo). In this article I will talk about columnar databases, how they are different from the row store databases that we use everyday and why do we need them. Also, I will try to figure out what SQL Server has to offer with Apollo and how it stands up to any other column store database.

Column store databases

A column store database is a database which stores table data as sections of columns of data rather than as rows of data. That means all the values for one particular column will be stored together and these sets of data might be distributed for different columns. Different column values for same row are linked by pointers within. When any query is run against a row store database, it fetches data from all the columns for the rows that match the query conditions. In contrast, when the same query is run against a column store database, only those columns are read which are required by the query. So if you consider a table with 30 columns and your query needs( this is often less than 15% of the columns in a typical fact table, according to a Microsoft whitepaper) only five of those fields the column store database will at least give you 6x performance over its row store counterpart (provided the data in each column is similar). Considering the fact that disk access is still the slowest operation for our computers, this becomes significant because of much lesser pages to be read from the disk. Also, the columns which are not used in the query are not loaded at all in the memory which becomes essential to avoid frequent PLE drops when dealing with large amount of data.

Also, most of the column store databases use high values of compression for the data they store. The extremely high values of compression are results of the redundancy we usually see in columns. Again, the values in the same column are invariably of the same type, unlike the data you will see in a row, thus facilitating even more compression. Most of the column store database systems use more than one type of compression schemes based on the type of values stored in the column. For instance, Vertica uses Run Length Encoding schemes to store values in a column that are highly repetitive. These encoding schemes allow the database system to compress each column separately based on the underlying data values which can’t be done in a row store database because of different types of values stored in each row for each column. 

So if column store databases are so good, why do we use row store databases at all? Moreover, why row store databases are ruling the market when they are so inept when compared with column store databases? The point is that row store databases have their own advantages. You can’t use a column store database for a small table with hundreds of OLTP transactions per minute. A row store database will be much faster than a column store database if you want to see all the columns of the table in your resultset (because different columns are stored at different places on the disk and need multiple disk seeks). In a nutshell, there are specific use cases where you should use column store databases. The rise of column store databases has a lot to do with the rise of BI and big data. So generally column store databases should be used where traditional row based databases fail in delivering a satisfactory performance. Usually, such cases will be when you are trying to run analytic queries on significantly big data or when you need only few columns of a large table with a number of columns (a fact table for a star schema perfectly suits the description). Row store databases fail because they are just not designed for the purpose.

ColumnStore indexes in SQL Server

Now that you have a basic understanding of what a columnstore is, let’s talk about this new feature of Denali which is known as ColumnStore Index (codenamed Apollo). A columnstore index stores each column in a separate set of disk pages as compared to a heap/ B-tree which store multiple rows per page.

 

It brings the obvious advantages of a column store, easily compressible data and fetching only the data that we actually need in the query, to a normal SQL Server table. The columnstore index in SQL Server employs Microsoft’s proprietary VertiPaq technology. SQL Server columnstore indexes are pure column stores. That means that the data is stored and compressed in column-wise fashion and individual columns can be accessed separately from other columns.  Another type of column store is hybrid column stores which stores data as set of rows, but within that set of rows, data is organized and compressed in column-wise fashion.

Once you create a columnstore index on a table the query optimizer will choose to use a heap/B-tree or the index created by you based on the query and the cost of the query. If the optimizer chooses the columnstore index, but you feel that the underlying B-tree can perform better, you can always query hints to use the B-tree instead of the columnstore index. Remember that SEEKS are not supported by columnstore indexes. That means, if you use the table hint FORCESEEK, the SQL query optimizer will not consider the columnstore index and will rather go for the underlying B-tree or heap. Also, SQL Server columnstore indexes can use batch mode processing to even better the performance of the query.

While the performance benefits of columnstore index is quite nice, one can argue using alternative methods to achieve the same functionality, namely pre-computed summary tables or indexed views. These alternatives will surely work but they don’t offer the flexibility that columnstore indexes do. Pre-computed summary tables will approximate the effect of a columnstore but they will not work you have to change your query. Say, even if you have to add just one aggregate function to the query, you will need to change the structure of the summary table. Having a columnstore index on relevant columns will give you the flexibility to run any query with any aggregate function without changing anything in the base table. Also, if you don’t have a fixed set of queries to be run it can become very difficult to maintain pre-computed tables. Another alternative can be to use covering indexes. In most cases the columnstore index will be much more compressed that the covering index. If the query is too selective the covering index might work faster than the columnstore index (keep in mind that column stores are always better for big data and almost always poorer for OLTP queries). Also, columnstore index can use batch mode of processing to substantially speed up the query.

Limitations on using a columnstore index

Now that you know the advantages of columnstore index in SQL Server, it’s high time you know the flip side of the coin. SQL Server columnstore index comes with its own set of limitations, and unfortunately there are a lot of them. Among the numerous limitations the most glaring one is the inability to update a table with a columnstore index. Microsoft actually proposes some workarounds to achieve this in the whitepaper on columnstore indexes but this can definitely turn out to be the single biggest factor that actually stops you from using a columnstore index. A columnstore index can’t be clustered though Microsoft says that it will change with the future releases of SQL Server. Also, you cannot modify a columnstore index using ALTER INDEX statement. If you want to do so,  you must drop and recreate the columnstore index. You cannot do a custom sort in a columnstore index by using ASC/DESC because columnstore  indexes are ordered according to the compression algorithms. You cannot combine columnstore indexes with either replication or change data capture. As columnstore indexes already use compression you cannot combine it with page or row level compression offered by SQL Server. There are some more limitations that you can refer to in the whitepaper or on the msdn page for columnstore indexes.

I agree that the excitation created by use of column stores in a mainstream row store database is somewhat dampened by the numerous limitations that comes bundles with it but nonetheless it is a great initiative by Microsoft to try and come up with something meant for big data. The future looks destined for huge growth in the amount of data that we deal with and that will only prompt the growth of columnar databases. Right now SQL Server columnstore indexes stand nowhere when compared to full columnar database in terms of functionalities and performance but at least the guys at Redmond know what they are doing and we can definitely hope for a plethora of improvements in columnstore indexes in the future SQL Server releases. Also, I am looking forward to a more direct and tighter integration of columnstore indexes and SQL Server Analysis Services. I hope you enjoyed reading this little article. Your comments and feedback are welcome as always.

Spool Operators: Index spool and Window spool

In my previous article I talked about a few spool operators (Eager Spool, Lazy Spool and Rowcount Spool), now its time to talk about the remaining spool operators that the query optimizer uses to make our queries run faster. In this article I will talk about the Non Clustered Index Spool. Then I will talk about Window Spools which are used by a number of window functions being introduced in Denali. In the end, I will talk about the common scenarios SQL  Server query optimizer employs spool operators and how can we avoid them by simply keeping in mind a few things. Let’s get the things rolling then.

Non Clustered Index Spool

Every DBA who has had the opportunity to do performance tuning knows the importance of indexes in SQL Server. Still we will see tables without indexes (or without proper indexes). The Non Clustered Index Spool is used to better the read performance of queries which access such tables which are not properly indexed (or indexed at all). When SQL Server encounters such queries, where it needs to read data from tables that are not indexed it might create a temporary index on the spooled data in the TempDB. The query optimizer then uses this index as any other non clustered index to boost the performance of the query. The Index Spool scans the input rows and places a copy of the rows in a hidden file stored in TempDB (just like any other spool) and builds a non clustered index on the rows. Now this hidden index can be used in the query for seek operations just like any other index is used. To better understand index spool operators I will show just one query and the execution plan that SQL Server query optimizer uses for the query,

USE AdventureWorks

GO

SELECT *

FROM sales.SalesOrderDetail S1

WHERE S1.LineTotal > (

SELECT AVG(S2.linetotal)

FROM sales.SalesOrderDetail AS S2

WHERE S2.ModifiedDate < S1.ModifiedDate)

What I am doing with this query is pulling all the sales orders which have the order value (LineTotal) more than the average order values for all the orders that were placed before them. To figure out what the query optimizer thinks about this query, we will see the execution plan

So as you can see, the query optimizer thinks that it should create an index on the table to fetch all the data in the TempDB so it is going for creating an index spool(and that is taking about half of the time the query is using). So according to the query plan, first a clustered index scan is used to read all the data from the table and that data is being pulled by the index spool which then creates an index on that data in the tempdb. As the spool is an Eager spool( I talked about Eager and Lazy spools in my previous article) it will pull all the data at one go from the table scan.  Let’s see if the query optimizer is smart enough to take his own decisions and to do that we need to check the existing indexes on the SalesOrderDetail,

So, the query optimizer is once again spot on as there is no index covering for either of the two columns (LineTotal and ModifiedDate) used in the query. The best thing about index spools are that they tell you exactly what you can do to remove them. So if we create a non-clustered index on the table covering the columns that are used in the query, we should see something interesting in the query plan,

create nonclustered index IX_Test on sales.SalesOrderDetail(ModifiedDate,linetotal)

Now if we run the same query again and see the execution plan we can see the index spool is gone. Now instead of going for a clustered index scan, the query optimizer goes for an index seek on the index we just created. Also, if you want to check the total query cost, it has also decreased by a substantial amount and the improvement will only get better once the table size grows as the query will never go for a complete table scan now.

Window Spool

This will be the last spool operator that I will discuss. Window spools are new to SQL Server just like a lot of window functions that are coming bundled with SQL Server 2012 aka Denali. Window functions are set of functions that allow us to work on a set of data that is known as the Window. Different database systems are offering a number of Window functions for quite some time now but SQL Server has generally missed out on these functions. There were some advances with SQL Server 2005 but after that we had to wait till Denali to see further substantial improvements in Window functions. But do we need them anyways? The answer would be yes as Window functions allow us to have a critical functionality of dealing with details within an aggregation. If we group by some field in a select query we lose access to the details within the group but sometimes we need at least some information about the groups that we lose because of aggregations. One such example would be to get the rank of salespersons per geographical region based on their targets achieved. Window functions let us do exactly that. They partition the data into different windows (in the example above, windows will be different geographical regions) and let us work on different windows of data. We will see just one example and then we will move to our spool operator,

USE TempDB

GO

IF OBJECT_ID('Window_tbl') IS NOT NULL

  DROP TABLE Window_tbl

GO

CREATE TABLE Window_tbl (Value INT)

GO

INSERT INTO Window_tbl(value)

values

(4),

(4),

(5),

(3),

(6)

GO

SELECT Value,

                  LEAD(Value, 2) OVER(ORDER BY Value DESC) AS "Lead"  

FROM Window_tbl

And the result is this,

So the Lead function tells us the value in the row that falls  after the offset defined by the second parameter. So in the example above, as the offset is two it gives us the values that are separated by the current value by two rows. NULL values will be returned if there are no rows defined by the offset parameter. So for 4 and 3 there are NULL values as there are no rows which are separated by two rows from the rows having 4 and 3. Let’s see the execution plan for the code that we just ran,

So the query optimizer is using a Window spool. So SQL Server stores the frame data( or the Window data) in a temporary ‘worktable’ in TempDB. SQL Server actually  uses two different ways to store the frame data. It can either go for a disk-based worktable or it can store all the frame data into an in-memory worktable. The difference being the in-memory worktable is much faster than the disk based worktable for obvious reasons. It goes for fewer scans/ logical/ physical reads than the disk based worktable. The question is can we control SQL Server to go for one particular type of worktable for the spools? Yes, we can. The in-memory worktable is used when we define the frame as rows and the frame contains less than 10000 rows. The on-disk worktable is used when the window functions are used with the default frame (i.e range) or the frame has more than 10000 rows.

OVER (
       [ <PARTITION BY clause> ]
       [ <ORDER BY clause> ]
       [ <ROW or RANGE clause> ]
      )

We can easily check which kind of worktable is being used by SQL Server. We can either use the xEvent ‘window_spool_ondisk_warning’ in SQL Server 2012 or we can set the statistics IO on and look at the output.

SET STATISTICS IO ON

SELECT Value,

 LEAD(Value, 2) OVER(ORDER BY Value DESC) AS "Lead"  

FROM Window_tbl

(5 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Window_tbl'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The count values for the ‘Worktable’ tell us that it has used an in-memory worktable. For a disk-based worktable you will see these counts to be much higher. Because of the performance differences, it is always good to deine the window as ROWS instead of RANGE.

Avoiding Spools

As with all performance tuning methods, there is no sureshot method to avoid spools but definitely there are some best practices that can help to a good extent. SQL Server might go for spools for queries using ‘Select into’ if the isolation level is read isolation. It can be avoided by lowering the isolation level to read committed. Also, use of NOLOCK hints should also help in such cases. (NOTE: Be careful before you change the isolation level of your systems). SQL Server might go for spools in user defined functions to ensure that no DDL change will break the ongoing operation of the function. This scenario can be avoided by using schema binding in functions. If the UDF is non-schema bound then the query optimizer will generate the spool operator. In the case of a UDF where it is not accessing any base table or view, such spooling will never be utilized. So in the case of the schema bound UDF this spool operation does not occur and performance is increased. Window spools can be avoided easily by defining the frame as ROWS instead of RANGE (provided the frame size is less than 10000 rows). Similarly, its pretty simple to avoid non clustered index spools. Mostly, you would be creating indexes covering the columns used in the query( other wise the optimizer will create the same index in tempdb known as spools). Reworking a query that used an OR in the where statement might help sometimes.

So this was an attempt to make the spool operators a little more understandable. I hope you enjoyed learning about different types of spools and ways to avoid them. Your comments and feedback are welcome as always. 

Spool Operators: Eager spool, Lazy spool and Rowcount Spool

There are a lot of operators in the query execution plan that we generally overlook( I do this for those operators which I have no clue about). Sometimes these operators prove out to be pivotal in the performance of the query and we are forced to take good care of them. Spool operators are one such category of operators that we generally don't pay much attention to. But the very existence of spools in your query plan hints(most of the times) that the query is not optimized and SQL Server was forced to use spools to make the best out of your query. In this post, I will talk about what spools are and when they are used. I will go into details of Eager spool, Lazy Spool and Row Count Spool and we will see an interesting problem- known as the Halloween Problem. In the next post I will talk about the remaining spool operators- Table spool, Non-Clustered Index Spool and Window Spool and few guidelines to avoid spools. Sounds interesting ? Let's dive in then.

The query optimizer uses many techniques to optimize the queries we write and to avoid any problems, so that the query performs better. The spool operators are good examples of how optimizer works on behalf of the developer to make sure the query performs well. Though there are many spool operators (five to be precise in SQL Server 2008/ six in Denali), they all share the fact that they save their intermediate query results on the TempDB database and use this temporary area to search a value. This is not an explicit #temp table, but a work table for operations within the processing necessary for a given query's behavior. The optimizer uses this process whenever it knows that the intermediate result is complex to calculate. So the computations are done once and the results stored in the temporary space are used for further operations. Also, the optimizer might go for spooling if it finds out that the density( the more dense a column is, more rows that column returns for a given query) of the column is very high. 

Before getting started with different spool operators, I will talk about few terms. To start with let's understand Rewind and Rebind. A rewind is an execution using the same value that has already been calculated in the immediately preceding execution.  A rebind is defined as an execution that needs a value that is still to be calculated. For a spool it is very important to distinguish between rewinds and rebinds. Showplan operators can be categorized into Blocking and Non-Blocking operators. A blocking operator is one which halts the query execution until all rows are read from the input. Only then the actions will be performed and result would be returned. The SORT operator is a blocking operator. It reads all the rows, sorts the data and then returns the ordered rows. Non-blocking operators read one row from their input and return the output for each row.

Eager Spool

Eager spool stores all the rows received from the preceding operator into a hidden temp table in TempDB. "Eager" means that the spool will read all the rows that were returned from the previous operator at one time. So that makes the eager spool a blocking operator as it fetches all the rows at one go and stores it in a temp table. If the operator is rewound and no rebinding is necessary, the spooled data is used instead of rescanning the input. If rebinding is required all the spooled data is discarded and the spool is rebuilt by rescanning the input.

To make it clear I will resort to storytelling, literally ! There were a bunch of DBAs, hardworking and dedicated as ever, who were sacrificing their night of Halloween and burning some midnight oil for the greater good. They were trying to give a 10 % hike to every employee in the organisation who earned less than $25000. So what's the big deal you might say. Its just a matter of one update. And update they did. But  then something happened that nobody was able to believe. While for other employees it may have been proved a Halloween gift, it was no less than horror for those poor DBAs. They found that all the employees were earning more than $25000 after the update. This is what the kids know as the Halloween Problem.

Let's try to figure out what actually happened with the update. Wikipedia defines this problem as "the Halloween Problem refers to a phenomenon in database in which an update operation causes a change in the physical location of a row, potentially allowing the row to be visited more than once during the operation. This could even cause an infinite loop in some cases where updates continually place the updated record ahead of the scan performing the update operation." This is exactly what happened there. The update kept on updating the records unless each record satisfied the where clause.

Let's dive a little deeper. When we update a field which is a key in an index, first the index is used to fetch the data for a row and when the update is done the index is also updated with the new value. Now, consider the salary field as the updatable column. Now this is sorted in the index. Once the first value for salary is updated in the index, it becomes greater than the second. So it is moved back to the second place as the index is sorted. Now for the update, when the parent query wants the second row, again the index is used. As the index has already returned the first row it will now return the second row. But that was already updated!!!

SQL Server deals with the Halloween Problem using an Eager spool. So when the update query is run, it again calls for the index. But instead of fetching one row, it spools the entire table. Now for the second row, records are not read from the index but from the temp table that the spool has created. So there is no multiple updates on the same row and DBAs can now rest on Halloween nights. SQL Server query optimizer may also create eager spools in other scenarios, when it feels like data should be cached for further operations. So much for the eager spool, let's talk about the Lazy Spool now.

Lazy Spool

Being a member of the great Spool family, Lazy Spool is also destined to get data from some table and store in some temporary table in the TempDB. But, Lazy Spools work a little differently than Eager spools. Lazy Spools read data only when individual rows are required. That means a Lazy Spool reads data and stores the rows in temporary tables only when the parent operator asks for a row. So the difference with the Eager Spool is visible as an Eager Spool reads all rows at once into a temporary table. Again, not to say Lazy Spool is a non-blocking operator. The query optimizer uses the Lazy Spool operator to optimize some queries by avoiding reading the same values multiple times. Effectively, SQL Server works with one chunk of data in all operations( until the parent operator needs different data). Otherwise, it would be constantly fetching new data with each iteration of the parent operator.

Row Count Spool

Quoting from msdn, "The Row Count Spool operator scans the input, counting how many rows are present and returning the same number of rows without any data in them. This operator is used when it is important to check for the existence of rows, rather than the data contained in the rows. For example, if a Nested Loops operator performs a left semi join operation and the join predicate applies to inner input, a row count spool may be placed at the top of the inner input of the Nested Loops operator. Then the Nested Loops operator can determine how many rows are output by the row count spool (because the actual data from the inner side is not needed) to determine whether to return the outer row." That's all that I could have said to explain it but I have a sample query to show when  a Row Count Spool might be used and then we will try to figure out why the query optimizer does that and how it helps with respect to our query.

Use AdventureWorks

GO

select * from

Sales.SalesOrderDetail SOD1

where not exists( select 1 from Sales.SalesOrderDetail SOD2

                where SOD2.modifieddate= '2001-07-01 00:00:00.000'

                and SOD2.orderqty>10)

When we look at the query plan for this query we will see this,

So you can see that the plan is using the Row Count Spool operator to check the Exists sub query. The clustered index scan reads all rows in the table and passes the rows to the Nested loop operator to be joined with the subquery result. The Left Anti Semi join here means that only one side of the join will be actually returned to the select query. So, only the rows returned from SOD1 will be returned by the select. For each row in SOD1, the loop will see the Row Count Spool to check if the value exists or not. If SQL Server would not have used Row Count Spool for this query, it would have required to execute the subquery for each row returned by the parent query. So effectively, SQL Server uses Row Count Spool to avoid reading the SOD2 once for every row returned by the parent query. If you want to see the difference in query execution had SQL Server not chose to create a Row Count Spool, you might want to refer to Fabiano’s articles on Spool operators(links shared below).

I hope you enjoyed this tour of Spool Operators as much as I enjoyed learning about them. Though spools are one of the ways SQL Server query optimizer tries to tune our ill-written queries, they should also be seen as an opportunity to tune the query. I will talk about the remaining spool operators and few guidelines to avoid them in your query plans in the next article. Your comments and feedback are welcome as always.

You might want to go through the fabulously written articles by Fabiano,

http://www.simple-talk.com/sql/learn-sql-server/showplan-operator-of-the-week---row-count-spool/

http://www.simple-talk.com/sql/learn-sql-server/operator-of-the-week---spools,-eager-spool/

http://www.simple-talk.com/sql/learn-sql-server/showplan-operator-of-the-week---lazy-spool/              

Difference between sys.objects and sysobjects

According to Books Online (BOL), many of the system tables from earlier releases of SQL Server are now implemented as a set of views in SQL Server 2005. These views are known as compatibility views, and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available in SQL Server 2000. However, the compatibility views do not expose any of the metadata related to features that are introduced in SQL Server 2005. Therefore, when you use new features, such as Service Broker or partitioning, you must switch to using the catalog views.

For example, sysobjects  is just a system table in SQL Server 2000. However, in SQL Server 2005, there is no such system table named “sysobjects”, it is implemented as a system view, just as background compatibility view only. Therefore, when we use new features, we need to switch to using the catalog views, here is sys.objects. In SQL Server 2005, since many of new features are used and stored in the system database, so these two views (sysobjects and sys.objects) return the result will certainly different.

For more information, please visit the following links:

Compatibility Views (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms187376(v=SQL.90).aspx

Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views: http://msdn.microsoft.com/en-us/library/ms187997(v=SQL.90).aspx

User-Schema Separation: http://msdn.microsoft.com/en-us/library/ms190387(v=SQL.90).aspx

Transaction Log full on TempDB

This post aims to give the readers a simple step-by-step methodology to deal with 'Transaction Log Full' situations. The general perception is that we should not care about transaction logs if the recovery model is set to Simple. But actually that's not always the case. A very common phenomenon happens when the transaction log on the TempDB blows up because of some spid in KILLED/ROLLBACK state. It happens because while rolling back the transaction also the transaction log is used. So let's get started with how to deal will this issue,


1. Do DBCC OpenTran to know which transaction is the actual culprit.
2. Check the spid that you got from step 1 using sp_who2. It will give you an idea of what is actually the reason  behind the issue and if you can kill it without affecting anything important.
3. If you can kill the spid, kill it. This will solve the transaction log issue. If not, see if you can increase the size of the log file using,

USE MASTER

GO

ALTER DATABASE TEMPDB MODIFY FILE (NAME ='templog' , SIZE= 8500MB )

Or add another file for the log,

ALTER DATABASE Tempdb

ADD LOG FILE

( NAME = tempdblog2 ,

FILENAME = 'E:\MSSQL.1\MSSQL\DATA\tempdblog2.ldf' ,

SIZE = 10MB,

MAXSIZE = 100MB ,

FILEGROWTH = 10MB)

4. The above step will solve the problem in most of the cases. But there is a possibility that the disk where the log file is has already grown to full.  in such case either you  can get more space added to the drive by calling your admin or you can shrink some database files of the databases that are lying on that drive.  Once you have more space on the disk you can go back to step 3 to fix the issue.

I hope this simple article will help few DBAs in dealing with this issue. As always your comments are welcome.

SQL Server Service Broker Demystified

Introduction

Service Broker was introduced in SQL Server 2005 and can be easily hailed as one of the best new features added to SQL Server. With Service Broker a database developer can create powerful asynchronous applications within a server or between different servers. The range of such applications may vary from simple workload queuing to complex cross-server auditing. Moreover, being part of the database engine, Service Broker enjoys qualities like transactional consistency, reliability, and security. In a nutshell, It is a powerful tool for a DBA and not that difficult to learn. 

I will start with the basic terminology and components that make up a service broker application (and in the process we will also have a look at the code snippets to create those components).

Synchronous vs. Asynchronous Messaging

What makes Service Broker so powerful is the asynchronous messaging system that allows developers to develop loosely coupled components that work independently to perform some task. While in a synchronous messaging the interacting components wait on each other till the message is received (that is, the sender will not continue until the receiver has received the message), in an asynchronous messaging system the sender and receiver keep doing their work independently.  The advantage of asynchronous communication (and thus of the service broker) is that the sender and receiver can overlap their computation because they do not wait for each other. 

Transactional consistency

In the service broker infrastructure, the delivery of messages between two parties( called endpoints as you will later see) is transactional. That is, if a transaction rollbacks, all service broker operations within that transaction will also roll back.

The heart of service broker architecture is a dialog, which according to Technet, is reliable, persistent, bi-directional, ordered exchange of messages between two endpoints.  An endpoint is the sender or receiver of messages. So, let's start with going through the components that make up Service Brokers.

Enabling Service Broker

First of all, we need to enable Service Broker on our database. To check whether Service Broker is enabled on your database you need to query the sys.databases catalog.

SELECT is_broker_enabled 
FROM sys.databases 
WHERE name = ‘Your_Database’ 
GO 

To enable Service Broker on your database, we use the alter database command,

ALTER DATABASE Yoour_Database 
SET ENABLE_BROKER 
WITH ROLLBACK IMMEDIATE 
GO 

Be careful while running this command as WITH ROLLBACK IMMEDIATE  will disconnect all the users.

Message Types

A message type is the definition of the format of the messages that will be part of the dialog between the two endpoints. Message type performs any validation required on the contents of the message before sending the message. If the message is to be sent to another database (on same or different server) the message type should exist on both the databases.

To create a message type you can use the Create Message Type command.

CREATE MESSAGE TYPE message_type_name
    [ AUTHORIZATION owner_name ]
    [ VALIDATION = {  NONE
                    | EMPTY
                    | WELL_FORMED_XML
                    | VALID_XML WITH SCHEMA COLLECTION
                      schema_collection_name
                   } ]
[ ; ]

Permission for creating a message type defaults to members of ddl_admin or db_owner database roles and the sysadmin server role. The arguments and their definitions are detailed below:

message_type_name: Is the name of the message type to be created. The message_type_name can be upto 128 characters. By convention, the name is a URL of the form //<hostname/pathname/name> but using a URL format is not required.

AUTHORIZATION owner_name: This defines who will be the owner of the message. When the current user is sa or dbo, it can be the name of any valid user. In all other cases, it must be either the current user, the name of the user who the current user has impersonate permission for or the name of the role the current user belongs to. When this clause is omitted the owner is the user who executed the statement.

VALIDATION: This specifies how the message body for messages of this type are validated by service broker. When this clause is omitted the validation defaults to none. The possible values are :

• NONE: No validation is performed. The message body may contain data or it may be NULL.
• EMPTY: The message body must be NULL.
• WELL_FORMED_XML: The message body must contain well-formed XML. When this clause is specified, the receiver loads the message into XML parser to ensure that it can be parsed. If the message fails this validation, it is discarded and an error message is sent to the sender.
• VALID_XML WITH SCHEMA COLLECTION schema_collection_name: The message body must contain XML that complies with a schema in the specified collection. schema_collection_name must be the name of an existing XML schema collection.    

Let’s have an example of creating a message type:

CREATE MESSAGE TYPE My_First_Msg 
VALIDATION = WELL_FORMED_XML 
GO

You can run the following query to view the message types in the database where you have created the message them:

SELECT * FROM sys.service_message_types

Although Service Broker does not require that both sides of the conversation use the same validation, it is better to have it this way to help troubleshooting. 

If you have high message volume, each message passing through the XML parser can adversely affect the performance. So, you can avoid using XML validations in such case.

Contracts

Service Broker contracts are database objects which define which message type or types will be used in any conversation. Service Broker contracts define two different service roles: the initiator and the target. The initiator of the conversation starts the conversation by sending a message to the target. The contract that the conversation uses defines which service role can send messages of what message type.

To create a contract we use the Create Contract command. The syntax is:

CREATE CONTRACT contract_name
   [ AUTHORIZATION owner_name ]
      (  {   { message_type_name | [ DEFAULT ] }
          SENT BY { INITIATOR | TARGET | ANY } 
       } [ ,...n] ) 
[ ; ]

                                                             

The arguments and their definitions are detailed below:

AUTHORIZATION: Has exactly the same meaning as in creating a message type

message_type_name: is the name of the message that is to be included as part of the contract.

SENT BY: specifies which endpoint can send the particular type of message.

INITIATOR: specifies that only initiator can send the messages of the specified message type.

TARGET: specifies that only target can send the messages of the specified message type.

ANY: specifies that the messages of the specified type can be sent by either initiator or target.

[DEFAULT]: specifies that this contract supports messages of the default message type. By default, all databases contain a message type named DEFAULT.

Let’s continue creating these objects to be used in our messaging application,

--Either the initiator or the target may send the same message type

CREATE CONTRACT My_First_Contract 
(My_First_Msg SENT BY ANY) 
GO 

A contract does not specify the ordering of messages. Service Broker requires the first message of a conversation to be sent by the initiator. After the first message, there are no ordering requirements in the conversation dialog.

More than one contract can use the same message type(s).

The message type and direction cannot be changed once a contract is defined. If you have to change the message type, you must drop and recreate the contract.

Queue

As I mentioned earlier that Service Broker is an asynchronous messaging infrastructure. Lets dive a bit deeper into this now. In asynchronous messaging, you send a message and start doing something else, without caring a bit about the receiver. The processing of the sent message depends solely on the receiver’s mood. He might process it now or later without casting any effect on what you are doing on your end. But, the message needs to be stored somewhere between the time you sent it and the time it will be processed. This magical place where your messages vanish just as you send them is called the queue.

You can imagine the queue as a table (actually SQL Server does implement queue via a hidden table in your database). You cannot directly manipulate ( insert, update or delete) this table though. To see the messages in the queue SQL Server provides you with a read-only view on this table which you can query. When a sender sends a message, it is put to the sender queue. The transport layer moves that message reliably to the destination queue. The receiver queue can now pull the message whenever it feels like. The message from the sender queue is deleted only after it is successfully moved to the receiver queue. So, there is no question of any message being lost in transit.

There is one temporary queue on every SQL Server instance, called the transmission_queue. When a message is sent over a network, Service Broker puts the message in this queue, on the initiator server. Then, the Service Broker sends the message over the network and marks it as waiting for acknowledgement in the transmission_queue. When the message is received in the target queue, Service Broker sends an acknowledgement back to the initiator. When this acknowledgement is received, the message is deleted from the transmission_queue. This process is called the dialog. Note that you will only see messages in transmission_queue when there is some message in transit. You can query the transmission_queue using the following select statement:

SELECT * FROM sys.transmission_queue      

To create a queue we use the Create Queue command. The syntax is:

CREATE QUEUE queue_name
   [ WITH
     [ STATUS = { ON | OFF }  [ , ] ]
     [ RETENTION = { ON | OFF } [ , ] ] 
     [ ACTIVATION (
         [ STATUS = { ON | OFF } , ] 
           PROCEDURE_NAME = <procedure> ,
           MAX_QUEUE_READERS = max_readers , 
           EXECUTE AS { SELF | 'user_name' | OWNER } 
            ) [ , ] ]
     [ POISON_MESSAGE_HANDLING (
       [ STATUS = { ON | OFF } )
    ]
     [ ON { filegroup | [ DEFAULT ] } ]
[ ; ]
<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
        queue_name
} 
<procedure> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
        stored_procedure_name
} 
 

The arguments and their definitions are detailed below:

STATUS: specifies whether the queue is available or unavailable for use after creation. It is a good practice to set the queue off so the application cannot use it. Once the setup is done completely you can turn it on using the alter queue statement.

RETENTION: When OFF (default) the message is deleted from the queue as soon as the transaction that receives the message commits. When set to ON, the messages remain in the queue until the conversation ends. While it may seem harmless to keep it ON, doing so increases the size of the queue and may affect performance of the system as a whole. As a guideline, you should set it ON only when   you have a business need to keep the messages around after processing

ACTIVATION: I will deal with this is the next section as it deserves detailed discussion

POISON_MESSAGE_HANDLING: specifies whether poison message handling is enabled for the queue. The default is ON. If set to ON, the queue will be disabled after five consecutive transaction rollbacks. Setting it to OFF allows to have a custom poison message handling system in place.

ON { filegroup | [ DEFAULT ] } : specifies the SQL Server filegroup where the queue will be created. If the volume of messages is going to be high in your application, it might be better to keep the queue on a separate filegroup. 

--We will be creating a queue each for the initiator and the target

CREATE QUEUE MY_First_Queue_Init 
CREATE QUEUE My_First_Queue_Target 
GO 

Activation

Service Broker Activation is the mechanism that enables the applications to scale dynamically based on the message traffic. Activation uses Service Broker to start an application when there is work for the program to do.

There are two types of activation: internal and external. Internal activation works with SQL Server stored procedures and the procedure is directly activated. External activation works with SQL Server event that indicates that the program should start another queue reader.

Service Broker activation works in two steps. First, Service Broker determines whether activation is necessary. Then, service Broker determines whether activation occurs.

Let’s get back to the create queue syntax and have a look at the activation clause and related arguments:

ACTIVATION (
         [ STATUS = { ON | OFF } , ] 
           PROCEDURE_NAME = <procedure> ,
           MAX_QUEUE_READERS = max_readers , 
           EXECUTE AS { SELF | 'user_name' | OWNER } 

            ) [ , ]

STATUS: If this is ON, Service Broker activates the stored procedure specified in the PROCEDURE_NAME argument. You can use this argument to stop activation of the procedure by setting this argument to OFF. The default value is ON for this argument.

PROCEDURE_NAME = <procedure>: Specifies the name of the procedure you want to be executed when the message arrives at the receiver queue. This value must be a SQL Server identifier.

MAX_QUEUE_READERS: specifies the maximum number of instances of the procedure that the Service Broker starts at the same time. Its value can have any value between 0 and 32,767. While you may feel everything right to set a high value to this parameter but you need to have a more careful analysis of your system. While a high value will definitely make your Service Broker application very quick to respond, but it would also add a lot of stress on your servers. So, give some time and thought to balance your application requirements and your server health.

EXECUTE AS: specifies the account under which the procedure will be run at the time of activation.

Service

Let’s think of our system as a postal service. To send a letter you would write an address on the envelope. In Service Broker world, this address is the service. A target service represents an address that accepts requests for the tasks identified by the contracts that the service specifies. An initiating service represents a return address for a conversation with a target service. Each service uses a queue to store messages. Messages sent to the service are delivered to the queue.

To create a service we use the Create Service command. The syntax is:

CREATE SERVICE service_name

[ AUTHORIZATION owner_name ]

ON QUEUE [ schema_name. ]queue_name

[ ( contract_name | [DEFAULT] [ , . . . n ] ) ]

The arguments and their definitions are detailed below:

AUTHORIZATION: this is the same as described for messages and queues.

queue_name: this is the name of the queue that holds the messages received from this service.

contract_name: this specifies the contract for which the service may be the target.

Let’s create the initiator and target services now,

CREATE SERVICE My_First_Service_Init 

ON QUEUE My_First_Queue_Init 
(My_First_Contract) 

CREATE SERVICE My_First_Service_Target 
ON QUEUE My_First_Queue_Target 
(My_First_Contract) 
GO 

Dialogs (Conversations)

So as we have the basic infrastructure in place for our SQL Server postal services, lets get ready to send and receive a couple of messages. Before that just a brief description of what conversations are in Service Brokers. A conversation is the SQL Server primitive over which messages are sent. A dialog is just a special type of conversation where we have a two-way conversation between two parties (future versions of SQL Server will also support monologs).  

To send messages you need to start a dialog. A dialog defines the exchange of messages between two services and specifies the contract that will be used for the messages in the dialog. Once created, you will get a “dialog handle”, a unique guid identifier for the initiator. This identifier will be used whenever the initiator wants to send any message or wants to receive any message from the target.

In order to start conversation between the services, we need to start a dialog.

DECLARE @Con_Id UNIQUEIDENTIFIER 

BEGIN DIALOG CONVERSATION @Con_Id
   FROM SERVICE My_First_Service_Init 
   TO SERVICE 'My_First_Service_Target' 
   ON CONTRACT My_First_Contract 
   WITH ENCRYPTION=OFF 

--Keep this guid

SELECT @Con_Id 
GO

Sending Messages

So we are all set to start talking with our friend sitting on the target service. To send a message, we need few things though. We need a message type to validate what we are sending, we need our side of the conversation handle and of course, we need a message. As I feel that we have all of those ingredients of our recipe with us, we better start talking!

DECLARE @con_Id UNIQUEIDENTIFIER 
--Insert the GUID from the last section 
SET @con_Id = '00ABCBC9-D049-DC11-BE14-001641E42AD0' 

-- Send a message
;SEND ON CONVERSATION @con_Id  
MESSAGE TYPE My_First_Msg 
('<Hello_Zombies />') 
GO 

Now that we have sent a message, let’s see if this actually works. To check whether the target received our  message we need to check the target queue.

SELECT * 

FROM My_First_Queue_Target 

So now you should be looking at a single row of data. The various columns summarize the properties of the conversation the two queues are having.

Receiving a message

Now that we have sent a message to the target queue and the message has safely reached its destination, we need to receive it too( think of it as a post delivered to your mailbox by the postal services, you need to pick it up from the mailbox finally). Note that once you pick the message up from the target queue, it will be removed from there (known as one-point delivery). We will be extracting the type of the message, conversation handle and the message body in the following code:

DECLARE  
    @handle UNIQUEIDENTIFIER, 
    @type sysname, 
    @body varbinary(MAX) 

;RECEIVE TOP(1)  
    @handle = conversation_handle, 
    @top = message_type_name, 
    @body = message_body 
FROM Simple_Queue_Target 

-- Don’t lose the handle as it will be used to reply
IF @type = 'My_First_Msg' 
BEGIN 
    SELECT  
        CONVERT(XML, @body), 
        @handle 
END 
ELSE 
BEGIN 
    RAISERROR( 
        'Unknown message type',  
        16, 1) 
END 
GO 

Sending a message back

Sending a message back to the initiator is similar to sending a message from the initiator. Note that we are using the conversation handle to send messages within the same conversation.

DECLARE @con_Id UNIQUEIDENTIFIER 
--Insert the handle from the previous section 
SET @con_Id = 'E1A1BFCF-D049-DC11-BE14-001641E42AD0' 

;SEND ON CONVERSATION @con_Id  
MESSAGE TYPE My_First_Msg 
('<Hello_Earth/>') 
GO

Ending the conversation

When we are done with sending or receiving messages, we should always end the conversation. Else the dialog will remain active and keep listening for new messages. Idle conversations use space on the database and can create problems if it is a busy database.

DECLARE @con_Id UNIQUEIDENTIFIER 
--Insert the handle from the previous section 
SET @con_Id = 'E1A1BFCF-D049-DC11-BE14-001641E42AD0' 

END CONVERSATION @con_Id

Conclusion

So, this was my attempt to simplify Service Brokers. In my next article I will use Service Brokers to develop a more complex application with cross-database communication and much more.