SQL 2014 SP1 released

Microsoft has released Service Pack 1 for SQL Server 2014 , 14 May 2015.

Why do you need it?

The most obvious reason is, it is free!

Secondly, there are some key fixes and improvements included in this release. Below are some of the key highlights for me:

You can find the download here.

Posted in SQL Server 2014 | Tagged | Leave a comment

Partition Switching for Big Data Loads – Partition Switch In

This is the third in a series of blogs about partition switching for SQL Server 2014. This blog will focus on the actual Alter Table … Switch statement spoken about in the first blog.

A number of steps has to be followed in order to perform the Alter Table … Switch statement. The Staging Fact table should be on the same File Group as the Fact table and the structure and indexes of the tables must match. A default constraint should also be added to the Staging Fact table because the Switch statements validates the data in the Staging table using this constraint.

In our solution both the Staging_Fact and Fact table have a clustered column store index. Before inserting data into the Staging_Fact the table is dropped and created in the correct File Group and the Column Store index created. We decided to have one File Group per month. The following code snippets is to drop and create the table on the correct File Group

  • Declare @FileGroup varchar(100) = ‘FileGroupName’ + Convert(varchar,Convert(int,substring(convert(varchar(8),@Date),5,2)))
    Declare @SQL varchar(max) = ”
    If Exists(Select name From Sys.tables Where OBJECT_ID = Object_Id(‘dbo.Staging_Fact’))
    Drop TABLE dbo.Staging_Fact;Set @Sql = ‘CREATE TABLE [dbo].[Staging_Fact]([Column1] [int] NULL,
    [Date] [int] NOT NULL) On ‘ + @FileGroup

    Exec (@SQL)

    If Not Exist (Select i.name From sys.indexes i
    JOIN sys.tables t
    ON (i.type_desc = ‘CLUSTERED COLUMNSTORE’)
    WHERE t.name = ‘Staging_Fact’
    AND i.name = ‘CCI_Staging_Fact’)

    Begin
    Create Clustered Columnstore Index CCI_Staging_Fact
    On [dbo].[Staging_Fact]
    End

Now we can do the code to do the partition switch into the Fact table. The following series of code snippets will perform the switch in.

Retrieve the specific partitition number

  • Declare @PartitionNumber Int = (Select Top 1 $partition.pfFunction(@Date) From [dbo].[Fact_Table])

Drop the constraint if it already exist on the Staging_Fact table

  • If Exists (Select * From Information_Schema.Table_CONSTRAINTS
    Where CONSTRAINT_TYPE=’CHECK’
    AND TABLE_SCHEMA=’dbo’
    AND TABLE_NAME = ‘Staging_Fact’ AND CONSTRAINT_NAME = ‘CK_Staging_Fact’)
    Begin
    Exec(‘Alter Table [dbo].[Staging_Fact] Drop Constraint [CK_Staging_Fact]’)
    End

Create the constraint on the Staging_Fact table

  • Declare @SQL varchar(2000) = ”
    Set @SQL = ”Set @SQL = ‘Alter Table [dbo].[Staging_Fact] With Check Add Constraint [CK_Staging_Fact] Check ([Date] =’ + Convert(varchar,@Date) + ‘)’ ;     Exec (@SQL)

And then, the Alter Table … Switch you have been reading about 🙂 The next statement will switch the data to the Fact table on the specified partition

  • Alter Table [dbo].[Staging_Fact]
    Switch To [dbo].[Fact_Table] Partition @PartitionNumber

A partition switch out should also be done if data already exist on the partition you want to insert data into. The switch out will remove the data from the specified partition. The last blog in this series will explain this in more detail.

Posted in SQL Server 2008, SQL Server 2014 | Tagged , , , , ,

SQL Indexing

Thanks to the web today there are really fantastic information available for every SQL Server professional (trust me 15 years back it wasn’t the case). One such key topic is Indexing.

Instead of re-inventing the wheel I want to give a great thanks to David Durant from SQL Server Central for writing this wonderful series of posts about SQL Server Indexing. Following this series you will learn all you need from the basics to best practices. Soon you too can have a DB humming like a speed train with happy (customer) faces.

Posted in SQL Server 2014 | Tagged | Leave a comment

Useful Tips in using BIML in BidsHelper

This is a follow up post on the first post my colleague and owner (Louw Fouche) of sabigdata, wrote around BIML and generating SSIS packages. This article will focus on some of the insights we gained using BIML for Source to Staging SSIS package generation and give some use full tips. At the end of the article there will be url links to articles which will get you started in using BIML if you are not familiar with the language, concepts and syntax.

In the project we are involved in we needed to import +- 60 tables from four source databases into the staging area. We created a meta data table containing all the tables, column names, data type and lengths of all the columns. The below bullet points are some of the insights we gained.

  • In the BIML script there are C# code to retrieve the meta data from the database and do some work with the dataset. Using a C# Console application made the debugging of the code much easier and I would definitely recommend it.
  • There are some functionality available in the Visual Studio 2010 BIDSHelper version which is not available in the Visual Studio 2013 BIDSHelper version. Package parameters, Project Parameters and Project Connection Managers does not generate in VS 2013. As a workaround the packages were created in Visual Studio 2010 then deployed to the SQL 2014 SSIS.Catalog. You then create a VS 2013 SSIS project, choose Integration Services Catalog and import the SSIS packages into the development environment.

SSIS1     By using this workaround you then have the SSIS Packages in the Visual Studio 2013.

SSIS2

  • We created a separate .BIML file containing some C# variables that is used by all the other BIML files. This is useful when you need to do the source to staging packages for more than one database. The BIML scripts are more generic and you only need to change some C# variables and then generate packages for different sources.

Using BIML saved us a lot of time on the project plan and you can you can easily add more functionality and quality to your SSIS packages.

Follow these links to get started using BIML :

Posted in SQL Server 2012, SQL Server 2014 | Tagged , , , | Leave a comment

Stop SSIS Package execution in SSIS.Catalog

This article is about explaining how to cancel or stop a job that was executed directly from the SSIS.Catalog.

I executed a SSIS Package directly from the SSIS.Catalog where I subsequently had to cancel the process. My research lead to the following two options. You can choose which one will work for your situation.

Option 1

Information about the packages are stored in the SSISDB. To view the package information you can query the Catalog.Executions view in the SSISDB.

The following query will return all currently running packages in the SSIS.Catalog

select * from catalog.executions Where end_time is null

You need to use the execution_id found in the result set to stop the package using an internal SSISDB stored procedure.

All_Executions_Query_Results

To determine if you have the correct execution_id run the All_Executions report in the SSIS.Catalog, identify the correct ssis package that is executing and click on the Overview hyperlink.

All_Executions

The drill down report will give you the execution_id of the currently running process.

All_Executios_Overview

The following T-SQL statement will stop the execution of the SSIS package.

Exec catalog.stop_operation  @operation_id =  11

Option 2

When you right click on the SSISDB in the Integration Services Catalogs click on Active Operations. A new window will open with a Stop button where you can cancel the specific ssis package that is executing.

ActiveOperations

Also see the Microsoft documentation related to this topic.

Posted in SQL Server 2012, SQL Server 2014 | Tagged , , , , , , | 1 Comment

SSMS Solutions

Have you ever looked in amazement at your SQL guru who can whip up in a flash a fancy TSQL script to run?

Today I will share with you a super handy feature in SQL Server Management Studio (SSMS) called Solutions. Quite similar to Visual Studio solutions, you can create a solution(s) in SSMS with projects. Inside the projects you can then store all your favorite scripts.

Sounds great; so let me show you how in 5 minutes.

  1. OpenSSMS and press CTR+Shift+N.
      Now you will see this screen.

SQL Server Management Server Project

  1. Add a name.
  2. Hit ok.
  3. Back in SSMS you will notice Solution Explorer open with your new project added.
  4. Right click on “Queries”
  5. Select “New Query”

SSMS Project New Query

Tadaaaa!

Congratulations on your first SSMS Project with a query.

Once you close SSMS it will prompt to save your solution and any open queries. Once confirmed it will save your scripts for use next time.

Now all you need to do is start build your own little pearls of wisdom and amaze the next future guru.

Posted in SQL Server 2014 | Tagged , | Leave a comment

SQL Server data warehouse performance settings

This is the checklist you need to ensure your SQL Server environment is optimally setup for Data Warehouse loads. I have compiled it based on experience and the FastTrack data warehouse reference architecture.

  • Confirm that Windows Server 2012 Application Server role is enabled to ensure proper .NET framework installation and defaults.
  • Enable the Lock Pages in Memory Option.
  • Enable -E trace option to SQL Server. This improves sequential disk access.
  • Enable -T1117 option to SQL Server. This trace flag ensures even growth of all files in a file group in the case that autogrow is enabled.
  • Enable -T610 trace flag on SQL Server. This will aid utilization of minimally logged operations for bulk inserts.
  • DB file growth is preallocated rather than autogrow except tempdb.
  • Set Max SQL Server Memory. Should be <= 92% of total physical memory.
  • Set page compression on large tables. Size recommended >= 1 million records.

Please note:

These settings will improved SQL performance for data warehouse loads but will negatively affect other OLTP type systems, such as SharePoint or CRM running on the server.

You will need elevated permissions on the Windows Server to perform some of these tasks.

Supporting links: SQL Server FastTrack Reference Architecture Guide

 

Posted in SQL Server 2012, SQL Server 2014 | Tagged , , , | Leave a comment