RSS

Tag Archives: sql server

SQL Server Concurrency Effects

Lost Update

Happens when one transaction update overwrites another, causing the update to be lost.

Dirty Reads

Reading uncommitted records.

Phantom Reads

Reading while another transaction is adding new record, result in different number of rows being returned.

Repeatable Reads

When the same query to read is executed, it will return the same result.

Reference

MSDN

Advertisements
 
Leave a comment

Posted by on May 22, 2018 in General

 

Tags: , , , ,

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

 
Leave a comment

Posted by on May 16, 2018 in General

 

Tags: , , , , ,

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

 
Leave a comment

Posted by on May 7, 2018 in General

 

Tags: , , , , , , ,

Database ACID Principal

ACID – Atomicity, Consistency, Isolation, Durability

Atomicity

Requires that each transaction be “all or nothing”.

Consistency

Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers.

Isolation

Ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed sequentially.
A lower isolation level increases the ability to access the same data at the same time, but increases the number of concurrency effects (such as dirty reads or lost updates) users might encounter.

Durability

Once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. Store in non-volatile memory.

 

References

Wikipedia

 
Leave a comment

Posted by on May 2, 2018 in General

 

Tags: , ,

SQL Logical Order of Operations

Following is SQL statement’s Logical Order of Operations (not particular to SQL Server):

  • FROM
  • WHERE
  • GROUP BY
  • Aggregations (COUNT, MAX, etc)
  • HAVING
  • WINDOW
  • SELECT
  • DISTINCT
  • UNION, INTERSECT, EXCEPT
  • ORDER BY
  • OFFSET
  • LIMIT, FETCH, TOP

That is why, running following SQL statement in SQL Server:

SELECT FirstName, LastName, COUNT(*)
FROM dbo.User
GROUP BY FirstName

Will throw:

Column ‘dbo.User.LastName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

GROUP BY is run first and grouped User’s columns by FirstName therefore LastName columns and all other columns are not visible by SELECT clause.

Another not-so-obvious example:

SELECT FirstName, COUNT(*)
FROM dbo.User
WHERE COUNT(*) > 0
GROUP BY FirstName

Refer to logical order above, when WHERE clause is executed, COUNT(*) > 0 is not yet evaluated and thus SQL Server throw exception.

Read more here.

 
Leave a comment

Posted by on May 2, 2018 in General

 

Tags: , , , , ,

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.

elapsed-time-of-each-operation-in-sql-server-execution-plan

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

 
Leave a comment

Posted by on April 13, 2018 in General

 

Tags: , , ,

SQL Server Instances You May Not Know You Have

There are 3 SQL Server database instance in most local machines. These are SQL Server Express instances that mainly useful for developers so they don’t have to install any SQL Server instance to get a project up and running. They are:

  • \SQLEXPRESS or .\SQLEXPRESS
    For example: “stack247\SQLEXPRESS”, or simply “.\SQLEXPRESS”.
    This is older version of SQL Server Express, 2008 to be precise. It’s also installed when you install Visual Studio 2010.
  • (LocalDB)\v11.0 or (LocalDB)\v11.1
    Comes with SQL Server 2012 or Visual Studio 2012. This is a newer version of SQL Server Express, or as the folks at SQL Express team call it, an improved SQL Express. The official name is SQL Express LocalDB
  • (LocalDB)\Projects
    Mainly used as default database by SQL Data Tools to host sandbox databases for your Database projects. Just like SQL Express LocalDB, it also comes with SQL Server 2012 or Visual Studio 2012.
  • (localdb)\mssqllocaldb
    SQL Server 2014 uses this as local database. This is also a default database where Azure Storage Emulator first initialize its database.
 
1 Comment

Posted by on December 18, 2014 in General

 

Tags: ,

 
%d bloggers like this: