Partition Level Truncation in SQL Server 2016.

Prior to SQL 2016 to clear out a partition, you have to switch data to an auxiliary table (with identical schema) and truncate the auxiliary table. SQL Server 2016 allows you to perform truncation at partition level, eliminating the overhead involved in maintaining auxiliary table.

The syntax is very simple -

TRUNCATE TABLE dbo.FactProductInventoryPartitioned WITH (PARTITIONS (6,7,8) )

This truncates partitions 6,7 & 8 from the fact table.

This greatly simplifies sliding window implementation for DataWarehouse Loads.

Get More Out of Clustered Column Stored Indexes

To get best performance out of CCI the query should be running Batch mode and Segment Elimination is done in efficient manner.   

Batch Mode Execution

In batch mode execution data is processed in batches of 1000 rows (by CPU). In traditional row mode the data is processed one row at a time.

To verify that query is running in batch mode, look at the execution plan and check for “Actual Execution Mode”, it should indicate “Batch”.  To enable batch mode in SQL 2014, Parallelism should be enabled on the server.

Efficient Segment Elimination

To encourage segment elimination, presort the data and then build the clustered column store index. Presort should be done on column subject to heaviest join or aggregate operation.

To presort, create clustered index on the presort column. Once clustered index is created, create clustered column stored index with Drop_Existing=ON Option.

create clustered index cix_FactInternetSalesCCI on  [dbo].FactInternetSalesCCI(OrderDateKey)

create clustered columnstore index cix_FactInternetSalesCCI on FactInternetSalesCCI with (Drop_Existing = ON)

SSAS DMVs for Monitoring & Querying Meta-Data

-- List of Dimensions.

SELECT

[CATALOG_NAME] AS Database,

[CUBE_NAME] AS [Cube],

[DIMENSION_NAME] AS [Dimension]

FROM

$SYSTEM.MDSCHEMA_DIMENSIONS

ORDER BY DIMENSION_NAME

-- List of Measures, along with MDX Formula for Calculated Measures.

SELECT

[CUBE_NAME] AS [Cube],

[MEASUREGROUP_NAME] AS [MeasueGroup],

[MEASURE_NAME] AS [Measure],

[EXPRESSION] AS [Calculation]

FROM

$SYSTEM.MDSCHEMA_MEASURES

ORDER BY

[MEASURE_NAME]

-- Dimension-MeasureGroup Relation.

SELECT

[CUBE_NAME] AS [Cube],

[MEASUREGROUP_NAME] AS MeasureGroup,

[DIMENSION_UNIQUE_NAME] AS Dimension_Real_Name,

[DIMENSION_IS_VISIBLE] AS Dimension_Visible,

[DIMENSION_GRANULARITY] AS Dimension_Granularity

FROM

$SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS

ORDER BY

[DIMENSION_UNIQUE_NAME]

-- Current Sessions along with Resource Usage & Last Executed Command.

-- This can also be used to identify long running queries.

select * from $system.discover_sessions ORDER BY SESSION_LAST_COMMAND_ELAPSED_TIME_MS DESC

-- TO kill a long running query, note the SESSION_SPID from the above query and execute the following XMLA command

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

       <SPID>341157</SPID>

</Cancel>

-- Current Connections.

select * from $system.discover_connections

-- Current Commands. For Currently executing command, Command_End_Time will be blank.

select * from $system.discover_commands order by COMMAND_END_TIME

Ten Reasons to Use SSDT Database Projects.

1.       Schema Compare

Find differences between 2 databases or between database and database project.

In Visual Studio

Go to View -> SQL Server Object Explorer.

Add SQL Server.

Once Server is added, Select a database and choose Schema Compare.

2.     Create a database project. 

Right click on a database and select Create New Project. 

Once the project is created, it can be checked in source control tool (GITS or TFS).

3.       Automated Deployment.

The database project can be published to target database. 

Right click on the database project and select publish. This synchronizes the schema of target database with database project. Any existing data in the target database is retained.

5. Deployment Script Generation.

You can generate a deployment script and examine it before publishing to target database.

Right Click on Database Project, Select Publish and Generate Script. 

The script once generated can be deployed using SSMS.

The Advanced button gives you additional deployment options like Take Backup Before Publish etc.

6.  Snapshot & DACPAC.

 DACPAC is a single database file that contains entire database schema.  When you do not have access to production server, you can generate DACPAC and hand it over to DBA. The DACPAC can be deployed using SSMS "Upgrade Data Tier Application Task".

To create DACPAC right click database project and select "Snapshot Project". This creates DACPAC in snapshots folder of project.

When you do not have source control, DACPAC can be used to capture point in time snapshot or major/minor versions.

7.       Build Time Validation 

Visual studio includes a local debug database, when database project is built, it is deployed to local database, if there are errors (invalid table references for example), they will show up during build time as errors.

8. Automated Testing.

9. Continuous Database Integration.

Assigning SSIS Parent Package Variables to Child Packages in Project Deployment Mode

It’s very common in an ETL Process to pass variable values form parent package to child package. e.g Parent Package may generate Extract Window Begin, End Dates, ETL Execution ID etc. and pass it down to all child packages.

SSIS 2008 and earlier provided Package Configurations to set child package variables from parent package. In SSIS 2012 and later if you are using project deployment mode, you can’t use Package Configuration any more. The following are two ways of passing variables from parent package to child package.   

Execute Package Task Parameter Bindings.

Define a package level parameter in Child Package.

Use Execute Package Task Parameter Bindings in Master Package to bind master package variable to child package parameter.

The parameter in child package can be used directly or assigned to a child package variable and used. 

Using Script Task

The parent package variables are available to child package at run time only (not at design time). To use this behavior –

1. Define a variable in child package.

e.g. if Parent package variable is startDate. The child package variable can be cStartDate.

2. Create a script task in Child Package.

3. Configure parent package variable as read only in script task.

  

4. Inside the script task assign the parent package variable value to child package variable.

Dts.Variables["cStartDate"].Value = Dts.Variables["startDate"].Value;

The child package variable is now set with parent package value.

The disadvantage with this method is that child package cannot be run standalone (for trouble shooting, unit testing etc). The script task would have to be disabled to make the child package run when not called from parent.

SSIS Catalog Useful Queries.

Some useful queries for troubleshooting and monitoring performance of SSIS Packages running in Project Deployment Model.

-- Get All executions Packages.

SELECT * FROM catalog.executions AS E

-- Get Currently Executing Package.

SELECT * FROM catalog.executions AS E

WHERE E.end_time IS NULL

-- Get Error Messages from latest execution.

 SELECT  *

       FROM    SSISDB.catalog.event_messages em

       WHERE   em.operation_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions)

           AND event_name LIKE '%OnError%'

-- Get Exeuction duration for each object

select  *

from catalog.executable_statistics

where execution_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions) -- latest execution.

order by start_time

Using SSAS Formula Engine Cache with Non-Deterministic Functions

If you have complex MDX calculations which take long time to execute, you might be able to exploit formula engine (FE) cache and get good performance.

The following diagram from  “Microsoft SQL Server Analysis Services Multidimensional Performance and Operations Guide” gives overview of query processing.

If calculation results are available in formula engine cache, the values are returned immediately.

If calculation results are available in formula engine cache, the values are returned immediately.

The use on Non-Deterministic functions (e.g. Now() ),  causes FE cache to be not used. Instead, storage engine cache is used instead and calculations are re-evaluated.

As a work around, you can create a static named set using the Non-Deterministic function and use the named set in the calculation inside MDX script.

// Create Static Named Set.

CREATE SET CurrentCube.[Last Completed Month TY] as

(StrToMember("[Date of Service].[Calendar].[DateID].&[" + Format(DateAdd("d",-Day(Now()),Now()), "yyyyMMdd") + "]"));

// Use the named set in calculation.

CREATE MEMBER CURRENTCUBE.measures.[office visits new - ty]

 AS (

      [Measures].[Office Visits - New],

      [Date of Service Comparison].[Aggregation].[Year To Date] ,

      [Last Completed Month TY].Item(0)

),

VISIBLE = 1 ;  

The above calculation uses a date-tool dimension called “Date of Service Comparison” with a member “Year To Date”

// Using date-tool dimension.

SCOPE (

    [Date of Service].[Calendar].MEMBERS,

      [Date of Service].[DateID].MEMBERS );                                                      

   

    ///////////////////////////////////////////////////////////////////////////////////////

    [Date of Service Comparison].[Aggregation].[Year To Date]

          = Aggregate(

                     { [Date of Service Comparison].[Aggregation].DefaultMember } *

                       PeriodsToDate(

                                    [Date of Service].[Calendar].[Year Number],

                                    [Date of Service].[Calendar].CurrentMember

                     ) ); 

                    

END SCOPE;

Note: To use FE cache the calculations should be done in MDX Script in the cube, If the Calculations are done in MDX Query (using WITH Clause), they are re-evaluated every time.

To get the calculation results into FE cache, execute an MDX query which uses the calculation (after cube is processed).

SELECT measures.[My Complex Calculated Measure] FROM MYCUBE