Now that the TechEd 2013 presentations are up online on Channel9 you can check out Brian Mitchell’s session What’s New for Columnstore Indexes and Batch Mode Processing. Brian does a great job at presenting the new updatable clustered columnstore indexes and the enhancements done to the vectorized query execution (aka. batch mode). Besides the TechEd presentation there is also another excellent resource available online right now for your education on the topic: the SIGMOD 2013 paper Enhancements to SQL Server Column Stores. Besides the obvious updatability, this paper cites some more improvements that are available in clustered columnstores:

• Improved Index Build
• Sampling Support
• Bookmark Support
• Schema modification support
• Support for short strings
• Mixed Execution Mode
• Hash Join support
• Improvements in Bitmap filters
• Archival support

It should be no surprise to anyone studying columnar storage that the updatable clustered columnstores coming with the next version of SQL Server are based on deltastores. I talked before about the SQL Server 2012 Columnstore internals and I explained why the highly compressed format that makes columnar storage so fast it also makes it basically impossible to update in-place. The technique of having a ‘deltastore’ which stores updates and, during scans, merge the updates with the columnar data is not new and is employed by several of the columnar storage solution vendors.

Deltastores

Deltastores are ordinary B-Trees that store uncompressed row groups of the clustered columnstore

Columnstores introduce a new unit of organization, a row-group. A row-group is a logical unit that groups up to 2²⁰ rows (about 1 million rows). In SQL Server 2012 the row-groups where implicit and there was catalog view to show them. As you can see in Brian’s presentation SQL Server 14 adds a new catalog view: sys.column_store_row_groups. This catalog view show the state of each row group for all columnstores (including non-clustered ones). Updatable clustered columnstores can show the row groups in COMPRESSED state or in OPEN/CLOSED state. The OPEN/CLOSED row groups are deltastores (yes, there could be multiple deltastores per columnstore, see the above mentioned SIGMOD 2013 paper). OPEN row groups are ready to accept more inserts while CLOSED row groups have filled up and are awaiting compression. The structure of a deltastore is explained in the SIGMOD paper:

A delta store contains the same columns as the corresponding column store index. The B-tree key is a unique integer row ID generated by the system (column stores do not have unique keys).

If you wonder what a unique integer row ID generated by the system actually is, remember how uniqueifier columns work. Deltastores are managed entirely by the engine, there is no DDL to control the creation and deletion of deltastores. The engine creates a new deltastore whenever it needs one to handle inserts, closes them when full (have 1 million rows) and a background process called the Tuple Mover compresses this closed deltastores into columnar storage format.

When handling deltastores the columnar storage advantages are dimmed. Deltastores are row mode storage so the entire row has to be read, not only the column(s) of interest. Segment elimination does not occur for deltastores since the deltastores do not have metadata about min and max values contained inside each column. Parallel scans will distribute the deltastores among the threads so that multiple deltastores are scanned in parallel, but there is no attempt for parallelism inside a single deltastores. At maximum size of 1 million rows they’re simply too small to justify the engineering complications of handling parallelism inside the deltastores. All this is explained in the SIGMOD paper:

Parallel scans assign each delta store to a single thread of execution. A single delta store is too small to justify scanning in parallel but multiple delta stores can be scanned in parallel. Scanning delta stores is slower than scanning data in columnar format because complete records have to be read and not just the columns needed by the query.

Tuple Mover

The background Tuple Mover process is responsible to compressing full deltastores. The Tuple Mover is an online operation, it does not prevent data reads from the deltastores being compressed. This is described in the SIGMOD paper:

The Tuple Mover reads one closed delta store at a time and starts building the corresponding compressed segments. During this time scans continue to see and read the delta store. When the Tuple Mover has finished compressing the delta store, the newly created segments are made visible and the delta store is made invisible in a single, atomic operation. New scans will see and scan the compressed format. The Tuple Mover then waits for all scans still operating in the delta store to drain after which the delta store is removed.

Concurrent deletes or updates are blocked while the Tuple Mover compresses a deltastore. Concurrent Inserts are not blocked by the Tuple Mover. As for the ‘background process’ part of the Tuple Mover the closest analogy is the Ghost Cleanup process. Veterans know that Ghost Cleanup is never tuned right for my job, is always either too aggressive for some users or too slow for others. Will Tuple Mover suffer from the same problems? I don’t expect it to, primarily because the unit of work is big. It takes time to accumulate 1 million rows in single row by row inserts.

The removal of compressed deltastores is very efficient as is basically a deallocation (as efficient as a DROP). This is important so that the Tuple Mover does not generate exaggerate logging during compression.

Delete Bitmaps

The deleted bitmap is another B-Tree associated with the clustered columnstore. There is only one deleted bitmap for the entire columnstore, it covers all the row-groups (all segments). Only compressed segments use a deleted bitmap. The DELETE operation is in effect an insert into the deleted bitmap, the row-group and tuple number of the deleted row is inserted into the deleted bitmap. Scans (reads) honor the deleted bitmap by filtering out any row (tuple) marked as deleted. It is recommendable that clustered columnstore indexes that have seen a large number of deletes to be rebuild in order to restore the ‘health’ of their segments by removing the deleted rows. UPDATE operations on clustered columnstores are always split updates, meaning the Query Optimizer will create a plan that contains a delete and an insert for any UPDATE.

Delete bitmaps do not cover deltastores. As B-Trees, the deltastores support direct delete so they implement the delete by removing the deleted row.

Bulk Insert

Large bulk insert operations do not insert rows into delta stores but convert batches of rows directly into columnar format. The operation buffers rows until a sufficient number of rows has accumulated, converts them into columnar format, and writes the resulting segments and dictionaries to disk. This is very efficient; it reduces the IO requirements and immediately produces the columnar format needed for fast scans. The downside is the large memory space needed to buffer rows.

For clustered columnstores bulk insert performance is critical, as is the bread and butter of the data warehousing ETL scenarios. You are going to have to give attention to your ETL pipeline and drive to achieve the optimal directly compressed format. This requires the bulk insert to be able to upload close to 1 million rows per partition in each batch. If the bulk insert uses too small batches then the result will be sub-optimal deltastores instead of the optimized compressed segments. With SSIS you will have to pay attention to the data flow buffer size as the default 10MB is way too small for achieving efficient columnstore bulk inserts. Of course there will be cases when there simply isn’t enough data to upload and in such cases the bulk insert will result in a deltastore instead of compressed columnstores. The deltastores will be left OPEN and subsequent bulk insert operations will reuse them and fill them up, close them and leave them for the Tuple Mover to compress them. While achieving the directly compressed format during bulk insert is desirable, you should not stretch your ETL and business logic out of the way just to achieve it. Going through the intermediate deltastore will remedy itself automatically once sufficient data accumulates.

INSERT … SELECT … targeting clustered columnstore tables is a bulk insert operation

Enjoy! SELECT … INTO has always been a bulk (and minimally logged) operation, but that can only create a row-mode heap. With INSERT … SELECT being now optimized for clustered columnstore indexes to use the bulk insert API, your ETL pipeline can construct the switch-in data directly into columnar storage format in one single pass, w/o having to resort to a rebuild. This is, again, described in the SIGMOD paper.

Trickle Insert

Trickle inserts are all inserts that do not come through the bulk insert API. INSERT INTO ... VALUES ... statements are trickle inserts. Trickle inserts are handled always by a deltastore and they can never create directly compressed data. UPDATE statements (as well as MERGE) result in split updates (delete and insert) and will insert in trickle mode.

Improved Index Build

Clustered columnstore index build is smart. It solve several problems in new ways:

Relevant Dictionaries

Columnar storage columns use a global dictionary, shared by all segments, and local dictionaries shared by specific segments. The more relevant the global dictionary (the more actual data values are covered by it) the better the data compression achieved, as the secondary dictionaries are smaller or even not needed. In SQL Server 2012 the global dictionary was just the first dictionary built, so it could contain skewed data resulting in poor relevance. With clustered columnstore index build the entire data is first sampled ina first stage, global dictionaries are built for all columns that requires them, and then the build proper starts. This creates much better global dictionaries and result in significant storage (compression) improvement.

Minimize blocking

This problem is not specific to clustered columnstore indexes: during an offline index rebuild, there is no reason to block reads. Yes, there is a risk of deadlock at the end of the index rebuild, but there are ways to solve that problem. With clustered columnstore indexes offline rebuild operations are semi-online, meaning reads are allowed but updates are blocked.

Workload Variation

Columnstore index build is very memory intensive. Traditional execution model determines the DOP at beginning of execution and then the query executes with the given DOP. With the improved columnstore build the actual execution DOP varies as the build progresses and the build process can actively and voluntarily reduce it’s DOP (by ‘parking’ execution threads) in order to adjust to low memory conditions.

Sampling Support

Required by the aforementioned two-stage index build in order to create relevant global dictionaries. Similar to how heap and B-Tree sampling selects entire pages to sample, columnstores can select row groups (segments) to sample.

Bookmark Support

Required to implement split updates. The heap bookmark is a physical locator (file_id:page_id:slot_id), the B-Tree bookmark is the actual key value and the clustered columnstore bookmark is the (row_group_id:tuple_id) pair. In deltastores the tuple_id is the value of the uniquifier column so the bookmark is located efficiently with a seek operation. Having bookmarks also frees the optimizer to explore additional options like eager spool.

Schema modification support

Add column, alter column, drop column are supported by clustered columnstore indexes.

Support for short strings

Short strings, like US state abbreviations, are frequent in fact tables and previously only dictionary encoding was available for them, which is inefficient. Now short strings can be encoded by value, w/o requiring a dictionary.

Mixed Execution Mode

Queries can now execute in a mixture of batch-mode and row-mode stages. Special adapter operators can exchange rows into batches and vice-versa. This gives the optimizer freedom to mix batch-mode with unsupported operators w/o having to resort to reverting the entire query to row-mode.

Hash Join and Bitmap filters improvements

The hash join is the preferred join of data warehousing workloads, dominated by large data sets and aggregations. The improved batch-mode hash join handles inner, outer, semi and anti-semi joins, ie. the entire spectrum of possible join operators (don’t confuse them with the join syntax). I recommend going over the relevant chapters on the SIGMOD paper for this topic for details.

Archival support

Simply put, add another layer of compression using XPress8 (a variant of LZ77) over the already compressed segments and dictionaries. Recommended for cold data, can be applied per partition and can give an additional up to 66% reduction in size (YMMV).

SQL Server 2012 installation has the potential to leave an msiexec.exe installer process running after the installation finishes, as described in Windows Installer starts repeatedly after you install SQL Server 2012 SP1:

After you install SQL Server 2012 SP1 on a computer, the Windows Installer (Msiexec.exe) process is repeatedly started to repair certain assemblies. Additionally, the following events are logged in the Application log:
EventId: 1004
Source: MsiInstaller
Description: Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_Ans’, Component ‘{0CECE655-2A0F-4593-AF4B-EFC31D622982}’ failed. The resource”does not exist.

EventId: 1001
Source: MsiInstaller
Description: Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_Ans’ failed during request for component ‘{6E985C15-8B6D-413D-B456-4F624D9C11C2}’

When this issue occurs, you experience high CPU usage.
Cause

This issue occurs because the SQL Server 2012 components reference mismatched assemblies. This behavior causes native image generation to fail repeatedly on certain assemblies. Therefore, a repair operation is initiated on the installer package.

But the this problem has a much sinister side effect: it causes growth of the HKLM\Software registry hive. Except for the System hive, all the other registry hives are still restricted in size to a max of 2GB, see Registry Storage Space:

Views of the registry files are mapped in paged pool memory…The maximum size of a registry hive is 2 GB, except for the system hive.

As the runaway msiexec process bloats the Software registry hive your system may approach the maximum hive size and, even before that maximum is reached, the large hive will consume more and more of the very precious kernel paged pool memory. The system may start to exhibit erratic behavior, complaining about low ‘resources’, closing connections and other symptoms. For an example of how this erratic bahior may manifest, read Why the registry size can cause problems with your SQL 2012 AlwaysOn/Failover Cluster setup.

If you installed SQL Server 2012 SP1 recently follow the steps in KB2793634 for a fix

The biggest problem with the registry bloat erratic behavior is that it occurs long after the SQL Server 2012 SP1 installation and is quite difficult, even for expert users, to trace back the causality of the server erratic behavior to the SP1 installation. If you are uncertain if this issue is affecting you, run dir %SystemRoot%\system32\config and check the size of the SOFTWARE file, which is the storage of this registry hive. If you are indeed dealing with a bloated registry hive, visit KB2498915: How to Compress “Bloated” Registry Hives:

1) Boot from a WinPE disk.
2) Open regedit while booted in WinPe, load the bloated hive under HLKM. (e.g. HKLM\Bloated)
3) Once the bloated hive has been loaded, export the loaded hive as a “Registry Hive” file with a unique name. (e.g. %windir%\system32\config\compressedhive)
a) You can use dir from a command line to verify the old and new sizes of the registry hives.
4) Unload the bloated hive from regedit. (If you get an error here, close the registry editor. Then reopen the registry editor and try again.)
5) Rename the hives so that you will boot with the compressed hive.
e.g.
c:\windows\system32\config\ren software software.old
c:\windows\system32\config\ren compressedhive software

Update: At the time of writing this the SQL Server SP1 download page recommends installing the KB2793634. Kind folk that have been in the trenches and had to deal with this problem have also give me more feedback about the symptoms and solutions. I’ve been told that if the registry is already bloated due to the msiexec issue then applying the KB2793634 will not be enough. The fix only prevents further auto-restarts of msiexec, but it does not compress the registry. The following registry keys *may* be already huge:

• HKLM\Software\Wow6432Node\Microsoft\.NETFramework\v2.0.50.27\NGENService
• HKLM\SOFTWARE\Microsoft\.NETFramework\v2.0.50727\NGENService
• HKLM\Software\Wow6432Node\Microsoft\.NETFramework\v4.0.30319\NGENService
• HKLM\SOFTWARE\Microsoft\.NETFramework\v4.0.30319\NGENService

Symptoms of registry hive bloat include, but are not limited to:

• Cluster service is crashing and “The Group Policy Client service failed the logon. Insufficient system resources exist to complete the requested service” are logged into the event viewer log.
• It is impossible to perform additional installations, or you can do this just shortly after rebooting the server.
• Users with domain accounts are unable to logon on the machine. When they log in a temporary profile is loaded.
• In Server Manager the Roles and Features are not listed anymore.
• Many actions are prevented with the error message ” Insufficient system resources exist to complete the requested service”.

The recommended action is to open a support case and contact CSS. If you are brave, can’t afford CSS support contracts, and you are convinced that the problems you’re experiencing is due to the SQL Server 2012 SP1 registry bload, you may try the following at your own risk:

1. Install the KB2793634 hot fix, as per the procedure described in the KB article.
2. From cmd prompt run %SystemRoot%\Microsoft.Net\Framework\v4.0.30319\ngen.exe executequeueditems and %SystemRoot%\Microsoft.Net\Framework64\v4.0.30319\ngen.exe executequeueditems
3. Compress the registry hive using a WinPE disk or from the Windows recovery mode (press F8 at the boot screen). Note that the Windows system on which you attempt to compress the hive must have the KB973817 installed: The Reg.exe utility does not compress a registry key when the utility saves a registry key to a hive file on a computer that is running Windows Server 2008, Windows Vista, Windows 7 or Windows Server 2008 R2.
4. After the compression procedure is complete, from a cmd prompt run SCF /SCANNOW. See KB929833 for more details.

In the case when the registry hive bloat prevents even the installation of the SP1 hotfix you may attempt increasing the hive size to 4GB, see RegistrySizeLimit. Editing this particular registry key to a wrong value can result in your system blue-screening during boot due to BAD_SYSTEM_CONFIG_INFO kernel panic. Each boot. Use at your own risk. The correct value to put is 0xFFFFFFFF.

If you plan to upgrade an RTM SQL Server 2012 instance to SP1 apply the slipstream SP1+CU2

If you have an RTM instance of SQL Server 2012 and plan to upgrade it to SP1 it is recommended to create a slipstream SP1+CU2 instalation and apply this instead. See Product Updates in SQL Server 2012 Installation, in SQL Server 2012 the slipstream functionality has been renamed to Product Updates:

The Product Update feature replaces the Slipstream functionality that was available in SQL Server 2008 PCU1. Therefore the command-line parameters, /PCUSource and /CUSource, associated with Slipstream functionality should no longer be used. The parameters will continue to work, but may be removed in a future release of SQL Server Setup. The /UpdateSource parameter combines the functionality of the Slipstream parameters.

Frankly I think the term ‘slipstream’ was less ambiguous than ‘product update’ but then who am I to complain…

SQL Server 2012 SP1 has shipped a great enhancement to XML: Selective XML Indexes. When properly used these indexes can speed up the searching of XML columns tremendously, at little disk/size cost:

The selective XML index feature lets you promote only certain paths from the XML documents to index. At index creation time, these paths are evaluated, and the nodes that they point to are shredded and stored inside a relational table in SQL Server. This feature uses an efficient mapping algorithm developed by Microsoft Research in collaboration with the SQL Server product team. This algorithm maps the XML nodes to a single relational table, and achieves exceptional performance while requiring only modest storage space.

However, at the time of this post, the documentation on MSDN omits a crucial requirement: the database has to be enabled to support these new indexes. Because is an on-disk format change shipped in a service pack release the engine cannot use the new selective XML indexes unless explicitly allowed. And the database version must change to a version that the RTM SQL Server 2012 does not recognize so that the database is not accidentally attached/restored on an RTM instance of SQL Sever 2012 that would not comprehend the new Selective XML Index objects and would panic (undefined behavior). To enable Selective XML Indexes in the database you must run sp_db_selective_xml_index:

Enables and disables Selective XML Index functionality on a SQL Server database. If called without any parameters, the stored procedure returns 1 if the Selective XML Index is enabled on a particular database.

EXECUTE sys.sp_db_selective_xml_index
@db_name = N'AdventureWorks2012'
, @action = N'true';
GO

Be aware that once upgraded to support Selective XML indexes this database can no longer be attached or restored on an RTM instance of SQL Server 2012. This applies to log shipping, Database Mirroring and AlwaysOn relationships, which will break when this upgrade is performed. If all the partners in the log shipping, DBM session or AG are also upgraded to SQL Server 2012 SP1 you can re-enable the relationship after you enabled Selective XML Indexes.

The SMO object model for SQL Server ServiceQueue does allow one to enable or disable a queue, but the property that modifies the queue status is not intuitive, it is IsEnqueueEnabled:

Gets or sets the Boolean property that specifies whether the queue is enabled.

This property matches the catalog view column is_enqueue_enabled in sys.service_queues but bears little resemblance to the T-SQL statement used to enable or disable a queue: ALTER QUEUE ... WITH STATUS = {ON|OFF}

For example the following SMO code snippet:

            Server server = new Server("...");
            Database db = server.Databases["msdb"];
            ServiceQueue sq = new ServiceQueue(db.ServiceBroker, "foo");
            sq.Create();
            sq.IsEnqueueEnabled = false;
            sq.Alter();
            sq.IsEnqueueEnabled = true;
            sq.Alter();

generates the following T-SQL:

CREATE QUEUE [dbo].[foo];
ALTER QUEUE [dbo].[foo]  WITH STATUS = OFF ...;
ALTER QUEUE [dbo].[foo] WITH STATUS = ON ...;

With the SQL Server 2012 SP1 CU2 release a new important feature was added: ability to back up and restore a database straight from Azure Blob storage:

This feature released in SQL Server 2012 SP1 CU2, enables SQL Server backup and restore directly to the Windows Azure Blob service. This feature can be used to backup SQL Server databases on an on-premises instance or an instance of SQL Server running a hosted environment such as Windows Azure Virtual Machine. Backup to cloud offers benefits such as availability, limitless geo-replicated off-site storage, and ease of migration of data to and from the cloud.

The syntax for the new feature is straight forward. You must first create a credential for accessing your Azure Blob storage:

SQL Server requires Windows Azure account name and access key authentication to be stored in a SQL Server Credential. This information is used to authenticate to the Windows Azure account when it performs backup or restore operations.

CREATE CREDENTIAL mycredential WITH IDENTITY = 'mystorageaccount'
       ,SECRET = '' ;

BACKUP DATABASE AdventureWorks2012
      TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/db.bak'
      WITH CREDENTIAL = 'mycredential'
     ,STATS = 5;

RESTORE DATABASE AdventureWorks2012
     FROM URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/db.bak'
     WITH CREDENTIAL = 'mycredential'

Full backups, differential backups, log backups, filegroup backups, compressed backups are all supported. The only notable restrictions is that is not allowed to backup to two locations simultaneously. Here is the complete list of limitations:

• The maximum backup size supported is 1 TB.
• Backup to or restoring from the Windows Azure Blob storage service by using SQL Server Management Studio Backup or Restore wizard is not currently enabled
• Creating a logical device name is not supported. So adding URL as a backup device using sp_dumpdevice or through SQL Server Management Studio is not supported
• Appending to existing backup blobs is not supported. Backups to an existing Blob can only be overwritten by using the WITH FORMAT option
• Backup to multiple blobs in a single backup operation is not supported
• Specifying a block size with BACKUP is not supported.
• Specifying a block size for restores might be required in certain scenarios
• Specifying MAXTRANSFERSIZE is not supported.
• Specifying backupset options – RETAINDAYS and EXPIREDATE are not supported.
• SQL Server has a maximum limit of 259 characters for a backup device name. The BACKUP TO URL consumes 36 characters for the required elements used to specify the URL – ‘https://.blob.core.windows.net//.bak’, leaving 223 characters for account, container, and blob names put together.

I recommend going over Backup and Restore to Azure Blob Best Practices.

A recent inquiry from one of our front line CSS engineers had me look into how case sensitive collations decide the sort order. Consider a simple question like How should the values 'a 1', 'a 2', 'A 1' and 'A 2' sort?

create table [test] (
	[col] varchar(10)
		collate Latin1_General_CS_AS);
go

insert into [test] ([col]) values
	('a 1'),
	('a 2'),
	('A 1'),
	('A 2');
go

select [col]
from [test]
order by [col];

Here are two possible outputs:

Which one is correct? A programmer will chose the first order: 'a 1', 'a 2', 'A 1', 'A 2'. Because if one would implement a string comparison routine it would compare character by character until a difference is encountered, and 'a' sorts ahead of 'A'. But this answer is wrong. The correct sort order is 'a 1', 'A 1', 'a 2', 'A 2'! And if you ran the query in SQL Server you certainly got the second output. But look again at the sort order and focus on just the first character:

By default, the algorithm makes use of three fully-customizable levels. For the Latin script, these levels correspond roughly to: alphabetic ordering, diacritic ordering, case ordering

So, in a case sensitive collation, is 'a' ahead or after 'A' in the sort order? The images shows them actually interleaved, is 'a', 'A', 'a', 'A'. What’s going on? The answer is that collation sort order is a little more nuanced that just comparing characters until a difference is encountered. This is described in the Unicode Technical Standard #10: UNICODE COLLATION ALGORITHM. And yes, the same algorithm is applied for non-Unicode types (VARCHAR) too. The algorithm actually gives different weight to character differences and case differences, a difference in alphabetic order is more important than one in case order. To compare the sort order of two strings the algorithm is more like the following:

• Compare every character in case insensitive, accent insensitive manner. If a difference is found, this decides the sort order. If no difference is found, continue.
• Compare every character in case insensitive, accent sensitive manner. If a difference is found, this decides the sort order. If no difference is found, continue.
• Compare every character in case sensitive manner (we already know from the step above there is no accent difference). If a difference is found, this decides the sort order. If no difference is found the strings are equal.

Needless to say the real algorithm does not need to traverse the strings 3 times, but the logic is equivalent to above. And remember that when the strings have different lengths then the comparison expands the shorter string with spaces and compares up to the length of the longest string. Combined with the case sensitivity rules this gives to a somewhat surprising result when using an inequality in a WHERE clause:

select [col]
from [test]
where [col] > 'A'
order by [col];

That’s right, we got back all 4 rows, including those that start with 'a'. This surprises some, but is the correct result. 'a 1' should be in the result, even though 'a' is < 'A'. If you follow the algorithm above: first we expand the shorter string with spaces, so the comparison is between 'a 1' and 'A  '. Then we do the first pass comparison, which is only alphabetic order, case insensitive and accent insensitive, character by character: 'a' and 'A' are equal, ' ' and ' ' are equal, but '1' is > ' '. The comparison stops, we found a alphabetic order difference so 'a 1' > 'A  ', the row qualifies and is included in the result. Ditto for 'a 2'.

The typical SQL Server activation procedure is contains a WHILE (1=1) loop and exit conditions based on checking @@ROWCOUNT. Error handling is done via a BEGIN TRY ... BEGIN CATCH block. This pattern is present in many Service Broker articles on the web, including this web site, in books and in Microsoft samples:

create procedure [<procedure name>]
as
declare @dialog_handle uniqueidentifier
 , @message_type_name sysname
 , @message_body varbinary(max);
set nocount on;

while(1=1)
begin
 begin transaction;
 begin try;
  receive top(1)
   @dialog_handle = conversation_handle
   , @message_type_name = message_type_name
   , @message_body = message_body
  from [<queue name>];
  if @@rowcount = 0
  begin
   rollback;
   break;
  end
  if @message_type_name = N'<my message type>'
  begin
   -- process the message here
                        ...
  end
  else if @message_type_name = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
     or @message_type_name = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
  begin
   end conversation @dialog_handle;
  end
  commit transaction;
 end try
 begin catch
  declare @error_number int = ERROR_NUMBER()
   , @error_message nvarchar(4000) = ERROR_MESSAGE()
   , @xact_state int = XACT_STATE();
  if @xact_state = -1 or @xact_state = 1
  begin
   rollback;
  end
  -- log the error here
               ....
 end catch
end
go

This patter though contains a problem: it will handle very poorly a disabled queue, and hence it will handle very poorly poison messages.

Error 9617 The service queue “…” is currently disabled

Read the rest of this entry »

I noticed that my database log file has grown to 200Gb. I tried to shrink it but is still 200Gb. How can I shrink the log and reduce the file size?

The problem is that even after you discover about DBCC SHRINKFILE and attempt to reduce the log size, the command seems not to work at all and leaves the log at the same size as before. What is happening?

If you look back at What is an LSN: Log Sequence Number you will see that LSNs are basically pointers (offsets) inside the log file. There is one level of indirection (the VLF sequence number) and then the rest of the LSN is basically an offset inside the Virtual Log File (the VLF). The log is always defined by the two LSNs: the head of the log (where new log records will be placed) and the tail of the log (what is the oldest log record of interest). Generating log activity (ie. any updates in the database) advance the head of the log LSN number. The tail of the log advances when the database log is being backed up (this is a simplification, more on it later).

Read the rest of this entry »

Columnar storage has established itself as the de-facto option for Business Intelligence (BI) storage. The traditional row-oriented storage of RDBMS was designed for fast single-row oriented OLTP workloads and it has problems handling the large volume range oriented analytical processing that characterizes BI workloads. But what is columnar storage and, more specifically, how does SQL Server 2012 implement columnar storage with the new COLUMNSTORE indexes?

Read the rest of this entry »

Few days ago I noticed that my StackOverflow profile shows 995 consecutive visited days. So naturally I started thinking about what does it mean to come back every day for a thousand days in a row. Looking back at the post I wrote almost 3 years ago to the day: stackoverflow.com: how to execute well on a good idea I can say that not much has changed: StackOverflow (and now the entire StackExchange network) is first and foremost a great community. The technical execution and the social nurturing of the site makes for a low friction environment that invites and rewards contribution, and it keeps getting better and better.