Cumulative Update 1 for SQL Server 2012 SP2

Microsoft has released Cumulative Update 1 for SQL Server 2012 Service Pack 2 and it is available for downloading.

Here is the link for downloading: http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2976982&kbln=en-us

Here is the link for the list of bugs fixed:http://support.microsoft.com/kb/2976982

Read this for understanding versions and service packs released for all SQL Server versions:http://dinesql.blogspot.com/2014/01/versions-and-service-packs-of-sql.html

Service Pack 2 is available for SQL Server 2012

Microsoft has released SQL Server 2012 Service Pack 2 and it is available for downloading.

Here is the link for downloading: http://www.microsoft.com/en-us/download/details.aspx?id=43340

Here is the link for the list of bugs fixed: http://support.microsoft.com/KB/2958429

Read this for understanding versions and service packs released for all SQL Server versions: http://dinesql.blogspot.com/2014/01/versions-and-service-packs-of-sql.html

Cumulative Update packages for SQL Server 2008 R2, 2012, and 2014

Cumulative update #12 is available for SQL Server 2008 R2 SP2. Refer the following link for downloading it and understanding the fixes done.

http://support.microsoft.com/kb/2938478

Cumulative update #9 is available for SQL Server 2012 SP1. Refer the following link for downloading it and understanding the fixes done.

http://support.microsoft.com/kb/2931078

Cumulative update #1 is available for SQL Server 2014. Refer the following link for downloading it and understanding the fixes done.

http://support.microsoft.com/kb/2931693

For more info on SQL Server versions and service packs, refer:http://dinesql.blogspot.com/2014/01/versions-and-service-packs-of-sql.html

Quick Note on SQL Server 2012 Licensing Cost

When it comes to purchasing Microsoft SQL Server, I know for a fact, many finds difficulty on selecting the edition and calculating the cost. Here is a quick note I made on it, it was based on a discussion had for selecting the edition and licensing it.

	Enterprise	Business Intelligence	Standard
Options available	Core-based only	Server + CALs only	Core-based or Server + CALs
Price	USD 6,874 per core (four-core minimum per socket)	Server – USD 8,592 CAL – USD 209	USD 1,793 per core (four-core minimum per socket) - Server – USD 898 CAL – USD 209

Again, calculating the price for core-based is not just core price into number of cores. It is based on something called Core Factor. Refer this table to determine the core factor for your processor;

Processor Type	Core Factor
All processors not mentioned below	1
AMD Processors 31XX, 32XX, 41XX, 42XX, 61XX, 62XX Series Processors with 6 or more cores	0.75
Single-Core Processors	4
Dual-Core Processors	2

Number of core licenses to be purchased is based on formula: Number of cores * Core Factor. Here is an example for calculating core-based cost for Enterprise Edition;

1. 2 Intel Xeon 6-core processors

Total physical cores – 12
Core factor – 1
Core licenses required = 12 x 1 = 12
Cost for Enterprise Edition = 12 x USD 6,874 = USD 82,488

What is Columnstore Index - SS SLUG Aug 2013 – Demo V

If you have worked with large databases (or data warehouses), you have already seen enough of scenarios where you do not get much benefits out of indexes, particularly with large data tables. SQL Server 2012 ColumnStore index structure was specifically introduced for addressing this issue.

This new structure is based on Microsoft Vertipaq technology and it goes as a non-clustered index. The different between this and traditional indexing is the storage. The indexes we have worked so far store data in a row-wise structure where as this stores data in a column-wise structure. Another different which make this unusable with OLTP databases is, structure becomes Read-Only.

 

How this exactly stores data was discussed in the user group meeting. Here are set of images that explains how SQL Server organizes data for ColumnStore indexes.

The first image shows a table that contains millions of records with hundreds of columns. If you execute a command for creating a ColumnStore non-clustered index, the first step of SQL Server is grouping records (image 2). Then it creates segments for each column in each group. Third image shows how 16 segments are created. SQL Server uses Dictionary Compression for compressing these segments. These segments will be stored in LOBs and become unit of transfer between the disk and the memory.

Let’s see how it can be used and the performance benefit. This sample code uses ContosoRetailDW and it can be downloaded at: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=18279.

Have a look on following query. The query accesses three tables (one is very large) and performs set of aggregations.

USE [ContosoRetailDW]

GO

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT 

    p.ProductName

    , d.CalendarYear

    , d. CalendarMonth

    , COUNT(*) TotalOrders

    , SUM(f.SalesQuantity) Quantity

    , SUM(SalesAmount) Amount

    , SUM(CASE WHEN f.DateKey = '2009-12-31 00:00:00.000' THEN SalesAmount ELSE 0 END) AmountToday

FROM dbo.FactSales f

    INNER JOIN dbo.DimDate d

        ON d.Datekey = f.DateKey

    INNER JOIN dbo.DimProduct p

        ON p.ProductKey = f.ProductKey

GROUP BY

    p.ProductName

    , d.CalendarYear

    , d. CalendarMonth

ORDER BY 1, 2, 3

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

It has read around 19,000 pages and has taken 40 seconds.

Now, let’s create a ColumnStore index on the table. The recommended way is, include all columns when creating the index.

CREATE COLUMNSTORE INDEX IX_FactSales_CStore ON dbo.FactSales (

        SalesKey, DateKey, channelKey, StoreKey, ProductKey, PromotionKey, CurrencyKey

        , UnitCost, UnitPrice, SalesQuantity, ReturnQuantity, ReturnAmount, DiscountQuantity

        , DiscountAmount, TotalCost, SalesAmount,ETLLoadID, LoadDate, UpdateDate)

This might takes minutes based on the number of records in the table. Once it is created, run the SELECT again and see the result.

As you see, number of pages that have been read is around 8,000 and time taken is 2 seconds. It is a significant improvement. However the biggest issue with this is, now table has become a read-only table. However this can be applied in partition level and partitions that contain history data can be benefited from this.

This link: http://technet.microsoft.com/en-us/library/gg492088.aspx gives all info regarding ColumnStore indexes. Have a look on it for understanding restrictions, limitations and scenarios you can use it.

SQL Server 2012 Posters

These posters have been published few months back, if you have not downloaded them yet, here are the links;

	SQL Server 2012 System Views Map http://www.microsoft.com/en-us/download/details.aspx?id=39083 The Microsoft SQL Server 2012 System Views Map shows the key system views included in SQL Server 2012, and the relationships between them.
	Microsoft Business Intelligence at a Glance Poster http://www.microsoft.com/en-us/download/details.aspx?id=35586 Provides an overview of Microsoft's Business Intelligence technologies in Office, SQL Server, and BI services in Windows Azure.

SQL Server 2012 Cumulative Update #3 is available

The cumulative update #3 for SQL Server 2012 is available for downloading now at: http://support.microsoft.com/kb/2723749. Make sure you apply this to test environment first and check before applying to live environment.