News

From time to time I discover new features or possibilities with SQL Server. The information will be shared here.

SQL Server 2012 Configuration Manager

With SQL Server 2012 Microsoft has made some minor but lovely changes to the Configuration Manager. As I mentioned in "Why don't my trace flag work", misconfigiration of SQL Server startup parameters may eventually prevent SQL Server from starting. With SQL Server 2012, this type of error has been mitigated by adding a Startup Parameters tab to SQL Server Configuration Manager, where you can add and remove startup parameters for your SQL Server instance.

A few words on BISM Tabular

BISM (Business Intelligence Semantic Model) Tabular is a completely new feature in SQL Server 2012, well worth a glance or two. We're pretty used to cubes in SSAS, and although there were some dramatic changes in SSAS 2005, the concept has pretty much been the same. This is NOT true for BISM Tabular, where all the data is stored unaggregated in tables in memory. You might think this would have a substantial overhead, not to mention being a great limitation due to the amount of memory in the server. As a matter of fact, this is not so, for several reasons. First of all, the data is actually compressed, and reports suggest a compressed data size from 1/5 to 1/400 of the uncompressed data, depending on the data types and the distribution of values. If we can achieve "only" 1/50, a 64GB server can handle some 3TB of uncompressed data, which is not bad at all.

So, what are the drawacks, there has to be some, right? According to Marco Russo and Alberto Ferrari at SQL Pass Rally Nordic, it may be easier to handle complex data models in a cube than BISM Tabular. Furhtermore, you will need to learn a new programming lanugage, DAX, to query BISM Tabular. However, if you're used to programming, another language is not all that difficult to handle. You have great benefits though, as DAX is performing way better at certain queries. If you have ever tried to use complex logic like linkmember and parallelperiod in MDX, you know these may have a completely awful performance, while the same often can be done way more efficiently in DAX. I just can't wait to dive further into it.

Finally, I had the chance to ask them about hardware for BISM Tabular, especially since it did not seem to fit very well into a cluster with other resources. With BISM Tabular, SSAS have to be able to allocate memory for all the data, so we can easily understand that it won't be a good candidate for a cluster unless you have an way more memory than you need, or it is an active/passive cluster. And even if you have a lot of memory, you may still experience "issues". BISM Tabular is not NUMA aware, so you will have suboptimal performance in a NUMA system. Furthermore, it does not scale very well over 4 cores, and as it is extremely memory intensive, you should have as fast memory as possible. So, what we're basically saying is a single quad core CPU with as high clock frequency as possible, and fast memory. This is normally not what I see in a regular cluster, where the focus tends to focus on the number of cores and amount of memory. As of today, Intel Xeon X5677 seems to be a brilliant CPU for BISM tabular, with its 4 cores, 1333MHz DDR3 bus speed, 3.46GHz clock frequency and 12MB L3 cahce.

SQL Server 2008 SP3 is available

Microsoft has now released SQL Server 2008 SP3

Free ebook: SQL Server Hardware Choises Made Easy

Glenn Berry and Redage offer a free ebook: SQL Server Hardware Choises Made Easy. Visit RedGate for more information.

Facebook and Twitter

You can follow me on Facebook and Twitter as well. Some short comments on findings, and information about upcoming events etc will be published here, but not on MSSQL.no. You can also use these two channels to get in contact with me.

Online rebuild of indexes with LOB columns in SQL Server "Denali"

Prior to SQL Server "Denali", any attempt to perform an online rebuild of an index containing a LOB column would give the following error:

Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index '<index name>' because the index 
contains column '<column name>' of data type text, ntext, image, varchar(max), 
nvarchar(max), varbinary(max) or xml. For non-clustered index the  column could be an 
include column of the index, for clustered index it could be any column of the table. 
In case of drop_existing the column could be part of new or old index. The operation 
must be performed offline.

This limitation has been removed in SQL Server "Denali", which is especially great for clustered tables containing lob columns.

Free ebook: Data Quality and Master Data Management with Microsoft SQL Server 2008 R2

Dejan Sarka and Davide Mauri offer a free ebook: Data Quality and Master Data Management with Microsoft SQL Server 2008 R2. Visit SoldiQ for more information.

T-SQL iif() in SQL Server "Denali"

I guess most of my readers are familiar with the case statement in T-SQL. Beginning with CTP3 of SQL Server Denali we've got a new conditional statement in SQL Server, iif. You may recognize iif from Reporting Services where it has been used for years now. Now, let's say you want to return a list of all indexes with a column saying if it is filtered or not, as yes or no respectively. In SQL Server 2008 R2 and prior editions you would execute a statement like this:

select
  name,
  case
    when has_filter = 0 then 'No'
    else 'Yes'
  end as Filter
from
  sys.indexes

With SQL Server Denali CTP3, this statement can be written in a much shorter form:

select
  name,
  iif(has_filter = 0,'No','Yes') as Filter
from
  sys.indexes

I totally agree, technically speaking this is not a great "innovation". It will however let us spend less time writing our code, and so far during my tests it genereates exactly the same execution plan as a case statement.

Download code.

Offset and Fetch Next in SQL Server "Denali"

Starting with Microsoft SQL Server "Denali", you can specify which rows to return by using offset and fetch (next|first) rows in combination with an order by clause, as described in the SQL:2008 standard. It should be noted however, that the standard also describes the use of fetcdh (next|first) rows as an alternative to top, that could be used without group by as well, but this is not implemented as of Denali CTP1.

So, what change does this new feature do? Let us test the new syntax against windowing functions, which were introduced in SQL Server 2005 and let us do the same thing. In my example below, I use these two techniques to list rows 11 through 20 from sys.objects orderd by object_id.

select 
  * 
from 
  sys.objects 
order by 
  object_id
offset 10 rows
fetch next 10 rows only;
go

;with RowNumbered as ( 
  select 
    *,
    ROW_NUMBER() over (order by object_id) as Rowno
  from
    sys.objects
) select 
  * 
from 
  RowNumbered 
where 
  RowNo between 11 and 20;
go

If we look at the execution plan, we'll see that the new syntax only have approximately 1/3 of the cost of the old syntax, which indicates a significiant performance improvement.

Download code.

SQL Server code-named "Denali"

Microsoft SQL Server code-named "Denali" CTP 1 is available. Visit Microsoft for more information.

Free ebook: Introducing Microsoft SQL Server 2008 R2

Microsoft Press offers a free ebook: Introducing Microsoft SQL Server 2008 R2. Visit Microsoft Press for more information.

SQL Server 2008 R2 offers SCSU support

SQL Server 2008 R2 has added support for SCSU, that is Standard Compression Scheme for Unicode. To have the data stored in this format, row compession must be enabled for the table. SCSI allows for up to 50 percenct compression of unicode data, which also is the typical for western languages as German and English. Other languages, as Chinese and Japanese, will typically not compress that well.

The support for SCSU is great for all database solutions where most of the text is in english, but there are some occurences of characters that require unicode. In this case, we can use unicode (nvarchar) and still use approximately the same amount of space as we would with varchar. SCSU is only applied to in-row data, and never nvarchar(max).

SCSU is implemented in SQL Server 2008 R2 Enterprise Edition and above. For more information, see Books Online and The Unicode Consortium.