Creating In Memory OLTP Tables

This is second article on In Memory table.
Lets see how to create In Memory tables.

Lets create a new database.

Index in In Memory OLTP Tables.

In Memory OLTP is a new revolution in OLTP for SQL Server users.
The feature supports extremely fast transaction but as every feature we have to also
Handling in memory table is quite different compare to normal tables and we have to be ready for it.

sys.dm_exec_query_profiles - SQL 2014 , new way to troubleshot queries

SQ: server 2014 has many silent features which are very unique and can help a lot to developers
one of it is sys.dm_exec_query_profiles

This DMV gives real time query execution details while query is running.
This is very helpful for troubleshooting queries.
The counters are per operator per thread. The data is serialized into the showplan xml when query finishes.

SELECT INTO with Parallelism

Lets see one more feature of SQL Server 2014

Its SELECT INTO with parallelism

Select into supports parallelism and due to this time for inserting data into table is reduced

System tables for columnstore indexes - sys.column_store_row_groups

System tables for columnstore indexes.

Columnstore index is a new feature and will be used heavily once 2014 is live.
We also have to be familiar with lots of stuffs related to it.

Columnstore Indexes and sp_spaceused

SQL Server 2014 supports clustered columnstore index.
Storage structure and updating/deleting data is quite different compare to normal tables.
It has concepts of deltastore which we will see in future articles.
Here I want to notice you behavior of clustered columnstore index while reporting table size.
Lets see an example for it

Archive data compression using Columnstore_Archive

As we all are aware about clustered columnstore index and compression that we can achive by it
In reporting system we generally query recent data more compared to older data.
So it can be acceptable for us in many cases if we can get more compression at the cost of slower query.

Columnstore Indexes- Identification and space usage

How to find tables with columnstore indexes

As we know that SQL 2012 has added feature of Nonclustered Columnstore Index. SQL 2014 onwards it will support Clustered Columnstore Indexes also.

As databases are growing we also need to track which tables has clustered and nonclustered columnstore indexes.

System tables for buffer pool extension

Buffer Pool extension is new feature in SQL 2014
We have two system tables to get information for buffer pool extension

1. sys.dm_os_buffer_pool_extension_configuration

Buffer Pool Extension SQL 2014

We all are aware of Buffer Pool and its importance while querying database.
When ever there is request for data , data and index pages are read from disk into the buffer pool
Also from buffer pool modified pages are written to disk during checkpoints. These pages are then read again from disk.All this random I/O activities putting pressure on disk.In general to resolve this issues we have to add RAM or high performance disks. All this leads to cost escalation and more Disk and Ram cause hardware failure.

USE OF XACT_ABORT

With use of XACT_ABORT we can manage either entire transaction will rollback or
only statement which raises error

When XACT_ABORT is ON entire transaction will rollback in case of run time error

Database Audit - Example with Select statement.

SQL SERVER audit is a server feature and its used to audit server actions.
Its available in SQL 2008 and later editions.
Its using SQL Server extended events to audit server activity.
Its providing much more granularity and we can use it to audit database related events also.

How to change default databse backup location in SQL Server

We all are aware of backup in SQL Server and how important it is. But very few have paid attention at backup location. When we take backup SQL Server show folder which is assigned for default backup.
But as its in C drive , almost majority will change location while taking backup.
So its better if we change default backup location to new address.

Running sum simplified in SQL 2012

Running sum is a regular requirement in query for developer and many times developer has to run number  of complex  queries or self joins to get running sum
But now since SQL  2012 its very easy and funny and simplified.

Partial Backup

Partial Backup

We can use partial backup  while handling  large databases
Using partial backup we can take backup of only read write enabled filegroups instead of
all filegroups which are read_only.

Copy only backup option

copy_only option for backup

Its a useful feature but very few are aware of this option .

As we all are aware of backups and its different types

We are aware of backup strategies which include backup chain of full,differential and log backups

Filtered index

Filtered index

Filtered index is a feature added in SQL Server 2008 and serves a great purpose for some of the requirements
As a general we all know that index improves search performance if INDEX is created properly
But some cases where we font requires data from entire column but only some filtered values
Even for this we have to create index for data entire column.
Whenever we query this column it has to read index for all values  column.