Columnstore Index

• New in SQL Server 2012.
• Stored by columns, a column-based index, instead of row-based like in traditional index. For example, if row-based index is consisted of Firstname and Lastname columns, the column-based index would have 2 different indexes: Firstname in its own index and Lastname in its own index.
• The index is compressed, allowing high performance.
• The compressed data is stored in-memory, reducing needs to read off the disks.
• Compression ratio is generally high because the same data type in a column.
• Generally a better choice for wide table with many columns, as commonly found in data warehouse tables.
• Clustered and non-clustered index.
• Can be combined with row-based index.

Reference
Microsoft Docs

Advertisements

SQL Server Isolation Level

Definition

Isolation is the “I” in ACID principal which rules the transactional state and its concurrency. The higher the isolation level, the lower the concurrency effects. And vice versa.

ISO Standard Isolation

The ISO standard defines 4 different isolation levels, from lowest to highest:
– Read uncommitted
– Read committed
– Repeatable read
– Serializable, where transactions are completely isolated from one another.

SQL Server Database Isolation

SQL Server Database supports all levels of isolation defined by ISO standard. In addition, it adds another isolation levels: the Snapshot isolation level (ALLOW_SNAPSHOT_ISOLATION).

SQL Server Database also add another implementation of Read Committed isolation level: Read Committed Snapshot Isolation or RCSI (READ_COMMITTED_SNAPSHOT). The original Read Committed implementation is referred to RC.

In Azure SQL Database, the default setting for Read Committed is RCSI (both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are set to ON).

In on-premise SQL Server, the default is RC (both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are set to OFF). But can be set to use RCSI by setting them to ON.

These two isolation levels, Snapshot isolation and RCSI, use row versioning that is maintained in tempdb.

Code

To check database settings for Snapshot isolation:

SELECT name, snapshot_isolation_state, is_read_committed_snapshot_on FROM sys.databases

To check current connection’s transaction isolation level:

SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions

To set transaction isolation level for current connection. Transaction isolation level is per session / connection.

SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }

Isolation Level and Concurrency Effects Matrix

Isolation Level	Dirty Read	Lost Update	Non Repeatable Read	Phantom
Read uncommitted	Yes	Yes	Yes	Yes
Read committed	No	Yes	Yes	Yes
Repeatable read	No	No	No	Yes
Snapshot	No	No	No	No
Serializable	No	No	No	No

Performance

In a load test, performance is significantly higher in RCSI, but it requires a lot higher throughput in tempdb (some 50x larger I/O). So planning on tempdb scaling is very important.

Reference
Microsoft Docs
Technet Blog
MSDN Blog

Elapsed Time of Each Operation in SQL Server Execution Plan

In SQL Server Execution Plan, you can see actual time it takes to run each operation. To do this: right click on the operation > Properties. In the Properties window, there should be Actual Time Statistics that will show elapsed time in millisecond.

Little Kendra explains in more details: https://littlekendra.com/2016/12/20/actual-time-statistics-in-execution-plans-elapsed-cpu-time-and-more/

Indexer-Supported Collection in C#

Indexer is a faster way to search a list / collection in C#, no looping is necessary.

Classes that support indexer:

• Array []
• List<T>
• Dictionary<Key, Value>
• ILookup<Key, Value>

Classes that don’t support indexer:

• IEnumerable<T>
• HashSet<T>
• ICollection<T>

For classes that don’t support indexer, there’s ElementAt LINQ extension method.

ASP.Net MVC 4 Bundles Ignore List

.min JavaScript Files

It all started when I tried to bundle a .min JavaScript file in my ASP.Net MVC 4 application. MVC just completely ignore any .min files I have in BundleConfig.cs.

Obviously, it doesn’t work. OK, it’s not so obvious when it happened to me.

Ignore List

Turn out, ASP.Net MVC 4 Bundles has ignore list. You guess it, the .min extension is included in the ignore list. To override the ignore list:

public static void SetIgnorePatterns(IgnoreList ignoreList)
{
    if (ignoreList == null)
        throw new ArgumentNullException("ignoreList");

    ignoreList.Ignore("*.intellisense.js");
    ignoreList.Ignore("*-vsdoc.js");
    ignoreList.Ignore("*.debug.js", OptimizationMode.WhenEnabled);
    //ignoreList.Ignore("*.min.js", OptimizationMode.WhenDisabled);
    ignoreList.Ignore("*.min.css", OptimizationMode.WhenDisabled);
}

public static void RegisterBundles(BundleCollection bundles)
{
    bundles.IgnoreList.Clear();
    SetIgnorePatterns(bundles.IgnoreList);

    // Continue with your code ...
}

That’s it, the .min JavaScript file will now be included in bundling.

Alternatively, you can also rename the JavaScript to remove its .min extension to be included in bundling process. This approach doesn’t require modification of bundle’s Ignore List.

While we are on the subject, it’s important to note that Bundle and Minification are different process. ASP.Net site explains it as:

Bundling

Bundling is a new feature in ASP.NET 4.5 that makes it easy to combine or bundle multiple files into a single file. You can create CSS, JavaScript and other bundles. Fewer files means fewer HTTP requests and that can improve first page load  performance.

Minification

Minification performs a variety of different code optimizations to scripts or css, such as removing unnecessary white space and comments and shortening variable names to one character. Consider the following JavaScript function.

Source: MVC 4 Bundling and Minification

How to Turn Off WS-SecureConversation in WCF

Since WS-SecureConversation is heavy, increase complexity and have some performance implications (when used with short list session), we might be better off turn this feature off. In WS2007FederationHttpBinding, WS-SecureConversation is enabled by default.

To turn of this feature, change establishSecurityContext attribute to false in your config file (web.config / app.config). This also applies to Message security mode.

<ws2007FederationHttpBinding>
    <binding>
        <security mode="TransportWithMessageCredential">
            <message establishSecurityContext="false" />
        </security>
    </binding>
</ws2007FederationHttpBinding>

Make sure you turn off on both the client and the service. Otherwise, you will encounter Unsecured or Incorrectly Secured Fault Was Received From The Other Party error.

This applies to WCF with .Net 4.5

Do Not Use Database Trigger

Or rather, use database trigger carefully.

Trigger is convenient and easy to write, but wrong implementation of database trigger can cause serious performance issue.

Basic rule is to avoid database trigger at all cost, find alternative way to achieve same result. Below is a video from Pluralsight showing usage of a database trigger that performs poorly. The slow performance caused by overhead that comes with database trigger, in this case the operation reads more table that necessary. When alternative approach is used (setting default constraint on a column), the difference is significant.

Try it for yourself.

-- Create our demo
CREATE TABLE [dbo].[charge_demo](
    [charge_no] [dbo].[numeric_id] NOT NULL PRIMARY KEY CLUSTERED,
    [charge_no] [dbo].[numeric_id] NOT NULL,
    [provider_no] [dbo].[numeric_id] NOT NULL,
    [category_no] [dbo].[numeric_id] NOT NULL,
    [charge_dt] [datetime] NOT NULL,
    [charge_amt] [money] NOT NULL,
    [statement_no] [dbo].[numeric_id] NOT NULL,
    [charge_code] [dbo].[status_code] NOT NULL,
    [insert_dt] [datetime] NULL;
GO

-- Create our trigger
CREATE TRIGGER [trg_i_charge_demo]
    ON [dbo].[charge_demo]
    AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE [dbo].[charge_demo]
    SET [insert_dt] = GETDATE()
    FROM [dbo].[charge_demo] AS [c]
    INNERT JOIN inserted AS [i] ON
        [c].[charge_no] = [i].[charge_no];
END
GO

Source: Hidden Side Effect of Trigger