SQL SERVER – DMV to Identify Incremental Statistics – Performance improvements in SQL Server 2014 – Part 3

This is the third part of the series Incremental Statistics. Here is the index of the complete series.


In earlier two parts we have seen what is incremental statistics and its simple example. In this blog post we will be discussing about DMV, which will list all the statistics which are enabled for Incremental Updates.

SELECT  OBJECT_NAME(sys.stats.OBJECT_ID) AS TableName,
sys.columns.name AS ColumnName,
sys.stats.name AS StatisticsName
FROM   sys.stats
INNER JOIN sys.stats_columns ON sys.stats.OBJECT_ID = sys.stats_columns.OBJECT_ID
AND sys.stats.stats_id = sys.stats_columns.stats_id
INNER JOIN sys.columns ON sys.stats.OBJECT_ID = sys.columns.OBJECT_ID
AND sys.stats_columns.column_id = sys.columns.column_id
WHERE   sys.stats.is_incremental = 1

If you run above script in the example displayed, in part 1 and part 2 you will get resultset as following.

When you execute the above script, it will list all the statistics in your database which are enabled for Incremental Update. The script is very simple and effective. If you have any further improved script, I request you to post in the comment section and I will post that on blog with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Simple Example of Incremental Statistics – Performance improvements in SQL Server 2014 – Part 2

This is the second part of the series Incremental Statistics. Here is the index of the complete series.


In part 1 we have understood what is incremental statistics and now in this second part we will see a simple example of incremental statistics. This blog post is heavily inspired from my friend Balmukund’s must read blog post. If you have partitioned table and lots of data, this feature can be specifically very useful.

Prerequisite

Here are two things you must know before you start with the demonstrations.

AdventureWorks – For the demonstration purpose I have installed AdventureWorks 2012 as an AdventureWorks 2014 in this demonstration.

Partitions – You should know how partition works with databases.

Setup Script

Here is the setup script for creating Partition Function, Scheme, and the Table. We will populate the table based on the SalesOrderDetails table from AdventureWorks.

-- Use Database
USE AdventureWorks2014
GO
-- Create Partition Function
CREATE PARTITION FUNCTION IncrStatFn (INT) AS RANGE LEFT FOR VALUES
(44000, 54000, 64000, 74000)
GO
-- Create Partition Scheme
CREATE PARTITION SCHEME IncrStatSch AS PARTITION [IncrStatFn] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
GO
-- Create Table Incremental_Statistics
CREATE TABLE [IncrStatTab](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL)
ON IncrStatSch(SalesOrderID)
GO
-- Populate Table
INSERT INTO [IncrStatTab]([SalesOrderID], [SalesOrderDetailID],
[CarrierTrackingNumber], [OrderQty], [ProductID],
[SpecialOfferID], [UnitPrice],   [UnitPriceDiscount], [ModifiedDate])
SELECT     [SalesOrderID], [SalesOrderDetailID],
[CarrierTrackingNumber], [OrderQty], [ProductID],
[SpecialOfferID], [UnitPrice],   [UnitPriceDiscount], [ModifiedDate]
FROM       [Sales].[SalesOrderDetail]
WHERE      SalesOrderID < 54000
GO

Check Details

Now we will check details in the partition table IncrStatSch.

-- Check the partition
SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('IncrStatTab')
GO

You will notice that only a few of the partition are filled up with data and remaining all the partitions are empty.

Now we will create statistics on the Table on the column SalesOrderID.

However, here we will keep adding one more keyword which is INCREMENTAL = ON. Please note this is the new keyword and feature added in SQL Server 2014. It did not exist in earlier versions.

-- Create Statistics
CREATE STATISTICS IncrStat
ON [IncrStatTab] (SalesOrderID)
WITH FULLSCAN, INCREMENTAL = ON
GO

Now we have successfully created statistics let us check the statistical histogram of the table.

Now let us once again populate the table with more data. This time the data are entered into a different partition than earlier populated partition.

-- Populate Table
INSERT INTO [IncrStatTab]([SalesOrderID], [SalesOrderDetailID],
[CarrierTrackingNumber], [OrderQty], [ProductID],
[SpecialOfferID], [UnitPrice],   [UnitPriceDiscount], [ModifiedDate])
SELECT     [SalesOrderID], [SalesOrderDetailID],
[CarrierTrackingNumber], [OrderQty], [ProductID],
[SpecialOfferID], [UnitPrice],   [UnitPriceDiscount], [ModifiedDate]
FROM       [Sales].[SalesOrderDetail]
WHERE      SalesOrderID > 54000
GO

Let us check the status of the partition once again with following script.

-- Check the partition
SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('IncrStatTab')
GO

Statistics Update

Now here has the new feature come into action. Previously, if we have to update the statistics, we will have to FULLSCAN the entire table irrespective of which partition got the data.

However, in SQL Server 2014 we can just specify which partition we want to update in terms of Statistics. Here is the script for the same.

-- Update Statistics Manually
UPDATE STATISTICS IncrStatTab (IncrStat)
WITH RESAMPLE ON PARTITIONS(3, 4)
GO

Now let us check the statistics once again.

-- Show Statistics
DBCC SHOW_STATISTICS('IncrStatTab', IncrStat)
WITH HISTOGRAM
GO

Upon examining statistics histogram, you will notice that now the distribution has changed and there is way more rows in the histogram.

Summary

The new feature of Incremental Statistics is indeed a boon for the scenario where there are partitions and statistics needs to be updated frequently on the partitions. In earlier version to update statistics one has to do FULLSCAN on the entire table which was wasting too many resources. With the new feature in SQL Server 2014, now only those partitions which are significantly changed can be specified in the script to update statistics.

Cleanup

You can clean up the database by executing following scripts.

-- Clean up
DROP TABLE [IncrStatTab]
DROP PARTITION SCHEME [IncrStatSch]
DROP PARTITION FUNCTION [IncrStatFn]
GO

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – What is Incremental Statistics? – Performance improvements in SQL Server 2014 – Part 1

This is the first part of the series Incremental Statistics. Here is the index of the complete series.


Statistics are considered one of the most important aspects of SQL Server Performance Tuning. You might have often heard the phrase, with related to performance tuning.

“Update Statistics before you take any other steps to tune performance”.

Honestly, I have said above statement many times and many times, I have personally updated statistics before I start to do any performance tuning exercise. You may agree or disagree to the point, but there is no denial that Statistics play an extremely vital role in the performance tuning.

SQL Server 2014 has a new feature called Incremental Statistics. I have been playing with this feature for quite a while and I find that very interesting. After spending some time with this feature, I decided to write about this subject over here.

New in SQL Server 2014 – Incremental Statistics

Well, it seems like lots of people wants to start using SQL Server 2014′s new feature of Incremetnal Statistics. However, let us understand what actually this feature does and how it can help. I will try to simplify this feature first before I start working on the demo code.

Code for all versions of SQL Server

Here is the code which you can execute on all versions of SQL Server and it will update the statistics of your table. The keyword which you should pay attention is WITH FULLSCAN. It will scan the entire table and build brand new statistics for you which your SQL Server Performance Tuning engine can use for better estimation of your execution plan.

UPDATE STATISTICS TableName(StatisticsName) WITH FULLSCAN

Who should learn about this? Why?

If you are using partitions in your database, you should consider about implementing this feature. Otherwise, this feature is pretty much not applicable to you. Well, if you are using single partition and your table data is in a single place, you still have to update your statistics the same way you have been doing.

If you are using multiple partitions, this may be a very useful feature for you. In most cases, users have multiple partitions because they have lots of data in their table. Each partition will have data which belongs to itself. Now it is very common that each partition are populated separately in SQL Server.

Real World Example

For example, if your table contains data which is related to sales, you will have plenty of entries in your table. It will be a good idea to divide the partition into multiple filegroups for example, you can divide this table into 3 semesters or 4 quarters or even 12 months. Let us assume that we have divided our table into 12 different partitions. Now for the month of January, our first partition will be populated and for the month of February our second partition will be populated. Now assume, that you have plenty of the data in your first and second partition. Now the month of March has just started and your third partition has started to populate. Due to some reason, if you want to update your statistics, what will you do?

In SQL Server 2012 and earlier version

You will just use the code of WITH FULLSCAN and update the entire table. That means even though you have only data in third partition you will still update the entire table. This will be VERY resource intensive process as you will be updating the statistics of the partition 1 and 2 where data has not changed at all.

In SQL Server 2014

You will just update the partition of Partition 3. There is a special syntax where you can now specify which partition you want to update now. The impact of this is that it is smartly merging the new data with old statistics and update the entire statistics without doing FULLSCAN of your entire table. This has a huge impact on performance.

Remember that the new feature in SQL Server 2014 does not change anything besides the capability to update a single partition. However, there is one feature which is indeed attractive. Previously, when table data were changed 20% at that time, statistics update were triggered. However, now the same threshold is applicable to a single partition. That means if your partition faces 20% data, change it will also trigger partition level statistics update which, when merged to your final statistics will give you better performance.

In summary

If you are not using a partition, this feature is not applicable to you.

If you are using a partition, this feature can be very helpful to you.

Tomorrow:

We will see working code of SQL Server 2014 Incremental Statistics.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Good Value for Page Life Expectancy – Notes from the Field #026

[Notes from Pinal]: In the past, I have been wrong many times, but I was man enough to accept my mistakes and correct myself. Page Life Expectancy is a very similar subject for me. In the past when I had written a blog post based on Microsoft’s white paper, I was corrected by SQL Experts immediately for my error in judgement and incorrect information. I accepted my mistakes and corrected it. I just shared this story with my good friend Tim Radney and he was very kind to give me guidance on this subject. I asked him if he can write further on this topic and help people understand this complex subject in simple words.

Linchpin People are database coaches and wellness experts for a data driven world. In this 26th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains in a very simple words what should be the good value for Page Life Expectancy (PLE).


When troubleshooting SQL Server performance issues one of the top places to look is memory.  One of the more common methods to check for memory pressure is to check to see the memory counter ‘Page Life Expectancy’ (PLE).  The value returned is represented in seconds.  A very old and dated recommendation is that this value be 300 or higher which is only 5 minutes.  With the standard servers in place today, this value is too low.

What is the proper value to monitor for your server, you may ask, well it really depends on the amount of memory allocated to your instance of SQL.  When working with my clients the value I start with is taking (max memory in GB) / 4 * 300.  This would be the minimum value I would expect to see.  On a server with 32GB allocated to SQL this value would be (32/4)*300 = 2400 seconds or 40 minutes.

For a server with 32 GB of ram, maintaining a PLE value of 2400 or higher is a good value, but what is equally important is to know what the baseline value is for the instance.  What is the average value the server has during certain times during the day?  If you capture this value and alarm/alert when you have big dips then you will be more equipped to get an early detection of a potential issue.  Things like improper indexed queries, large data pulls, etc.

I blogged awhile back and included the query that I run to retrieve the Page Life Expectancy.  http://timradney.com/2013/03/08/what-is-page-life-expectancy-ple-in-sql-server/

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Note: Tim has also written an excellent book on SQL Backup and Recovery, a must have for everyone.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Synchronize Data Exclusively with T-SQL

UPDATE: This review and exercise is based on dbForge Studio.

Database developers often face the task to synchronize users’ data. Currently, to solve this task, there was developed quite a big number of various utilities. In this article, we will review the process of creating the new functionality to synchronize data exclusively with T-SQL.

Let’s split the synchronization process with T-SQL onto the sub-tasks:

  1. Retrieving the table structure of a master database
  2. Preparing data stored in the tables of the master database and saving it at the public locations
  3. Turning off relations and triggers between the tables of a target database for synchronization time
  4. Synchronization of data between tables
  5. Turning on relations and triggers between the tables of the target database

To make the process of creating the synchronization functionality comparatively easy to understand, let’s discuss some restrictions:

  1. Synchronization will be elapsing within one server, syntax will be supported on the level of MS SQL Server 2005 or higher
  2. The schemes between the synchronized databases are identical
  3. Only system data types are supported
  4. It’s possible to synchronize any amount of tables at a time
  5. The structure of tables is not essential

1) Retrieving the table structure of the master database

The list of tables can be obtained with the following query:
SELECT table_name = '[' + s.name + '].[' + t.name + ']'
FROM sys.tables t
JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]

 

It’s important to remember that some system views may contain an excessive amount of connections and calls of system functions, that will negatively affect the run-time performance of the query, such as sys.tables:

SELECT *
FROM sys.objects$ o
LEFT JOIN sys.sysidxstats lob ON lob.id = o.[object_id] AND lob.indid <= 1
LEFT JOIN sys.syssingleobjrefs ds ON ds.depid = o.[object_id] AND ds.class = 8 AND ds.depsubid <= 1
LEFT JOIN sys.syssingleobjrefs rfs ON rfs.depid = o.[object_id] AND rfs.class = 42 AND rfs.depsubid = 0
LEFT JOIN sys.syspalvalues ts ON ts.class = 'LEOP' AND ts.value = o.lock_escalation_option
WHERE o.[type] = 'U '

Therefore, you have to use as simple system views as possible. In our case, you can use sys.objects:

SELECT table_name = '[' + s.name + '].[' + t.name + ']'
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.[type] = 'U '

To make the query not to return excessive rows, we need to preliminary filter out the tables containing no data:

SELECT table_name = '[' + s.name + '].[' + t.name + ']'
FROM (
SELECT
o.[object_id]
, o.[schema_id]
, o.name
FROM sys.objects o
WHERE o.[type] = 'U '
AND EXISTS(
SELECT 1
FROM sys.partitions p
WHERE p.[object_id] = o.[object_id]
AND p.[rows] > 0
)
)
t
JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]

The list of columns containing only system data types can be obtained in the following way:

SELECT
table_name = '[' + s.name + '].[' + o.name + ']'
, column_name = '[' + c.name + ']'
FROM sys.columns c
JOIN sys.objects o ON o.[object_id] = c.[object_id]
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
JOIN sys.types b ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id
WHERE o.[type] = 'U '

Next, to minimize the amount of data that we pass for comparison, it is suggested not to take into account column values, that have default values and can’t be empty. So, the query will look as follows:

SELECT
table_name = '[' + s.name + '].[' + o.name + ']'
, column_name =
CASE WHEN c2.[object_id] IS NULL
THEN '[' + c.name + ']'
ELSE '[' + c.name + '] = NULLIF([' + c.name + '], ' + c2.[definition] + ')'
END
FROM
sys.columns c
JOIN sys.objects o ON o.[object_id] = c.[object_id]
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
JOIN sys.types b ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id
LEFT JOIN sys.default_constraints c2 ON c.default_object_id = c2.[object_id]
AND b.name IN ('int', 'bit', 'char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar')
AND
c.is_nullable = 0
WHERE o.[type] = 'U '
AND c.is_computed = 0

2) Retrieving the data stored in the tables of the master database and saving it in public locations

First of all, we need some interim buffer, into which we will load data from a master database. The tempdb system database is public for any user database on the server, therefore, we can choose it.

Every time synchronization runs, we will check whether the table exists and create it if needed with the following query:

IF OBJECT_ID('tempdb.dbo.MergeTemp', 'U') IS NULL BEGIN
CREATE TABLE
tempdb.dbo.MergeTemp
(
[object_name] NVARCHAR(300) NOT NULL
,
create_date DATETIME NOT NULL DEFAULT GETDATE()
,
data XML NULL
,
CONSTRAINT [PK_MergeTemp] PRIMARY KEY CLUSTERED
(
id ASC, [object_name] ASC
) ON [PRIMARY]
)
END

Then, it’s necessary to decide how to store data from the master database. The main difficulty within this task is that tables may have different structures. The most obvious option is to create a temporary table for each synchronized table and upload data into it. However, such option may be not optimal because we will have to execute many DDL commands.

Therefore, we suggest to solve this task by means of XML, that can describe almost any database entity. The disadvantage of the suggested method is that not quite a correct XML data structure may be generated for some data types. You can retrieve a list of such data types with the following query:

SELECT t.name
FROM sys.types t
WHERE t.user_type_id != t.system_type_id

 

Let’s write a query to create XML with dynamic structure:

DECLARE
@Data NVARCHAR(4000)
,
@ObjectName NVARCHAR(300)
DECLARE [tables] CURSOR READ_ONLY FAST_FORWARD LOCAL FOR
SELECT
[object_name]
, [sql] = '
INSERT INTO tempdb.dbo.MergeTemp([object_name], data)
SELECT '''
+ quota_object_name + ''', (SELECT ' + object_columns + '
FROM '
+ quota_object_name + ' t
FOR XML AUTO, ROOT('''
+ [object_name] + '''))'
FROM (
SELECT
[object_name] = s.name + '.' + t.name
, quota_object_name = '[' + s.name + '].[' + t.name + ']'
, object_columns = STUFF((
SELECT ISNULL(', [' + c.name + '] = NULLIF([' + c.name + '], ' + c2.[definition] + ')',
', [' + c.name + ']')
+
CASE WHEN b.name = 'varbinary'
THEN ' = CAST('''' AS XML).value(''xs:base64Binary(sql:column("'+ c.name +'"))'',''VARCHAR(MAX)'')'
ELSE ''
END
FROM
sys.columns c WITH (NOLOCK)
JOIN sys.types b WITH (NOLOCK) ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id
LEFT JOIN sys.default_constraints c2 ON c.default_object_id = c2.[object_id]
AND b.name IN ('char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar')
WHERE t.[object_id] = c.[object_id]
AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
,
1, 2, '')
FROM (
SELECT
o.[object_id]
, o.[schema_id]
, o.name
FROM sys.objects o WITH (NOLOCK)
WHERE o.[type] = 'U'
AND EXISTS(
SELECT 1
FROM sys.partitions p WITH (NOLOCK)
WHERE p.[object_id] = o.[object_id]
AND p.[rows] > 0
)
AND NOT EXISTS(
SELECT 1
FROM sys.columns c WITH (NOLOCK)
JOIN sys.types t WITH (NOLOCK) ON c.system_type_id = t.system_type_id
WHERE (c.user_type_id != c.system_type_id OR t.name = 'xml')
AND
c.[object_id] = o.[object_id]
)
)
t
JOIN sys.schemas s WITH (NOLOCK) ON t.[schema_id] = s.[schema_id]
) data
OPEN [tables]
FETCH NEXT FROM [tables] INTO @ObjectName, @Data
IF (@@FETCH_STATUS = -1)
RAISERROR('Have nothing to export...', 16, 1)
WHILE @@FETCH_STATUS = 0 BEGIN
EXEC
sys.sp_executesql @Data
FETCH NEXT FROM [tables] INTO @ObjectName, @Data
END
CLOSE
[tables]
DEALLOCATE [tables]

Where, the following query will be generated for each table:

INSERT INTO tempdb.dbo.MergeTemp ([object_name], data)
SELECT <TABLE name>,
(
SELECT <columns list>
FROM <TABLE name> t
FOR XML AUTO, ROOT('<table name>')
)

After such query is executed, an XML with table data will be generated and written into the MergeTemp table.

3) Disabling relations and triggers between the tables of the master database for the time of synchronization

At the next stage, we will insert data into tables, therefore it’s preferably to turn off foreign keys and triggers. If you do not, various errors may appear at the inserting stage.

Here is a script to turn off/on foreign keys for tables:

DECLARE
@FKeysOff NVARCHAR(MAX)
,
@FKeysOn NVARCHAR(MAX)
;
WITH fkeys AS
(
SELECT DISTINCT o.[object_name]
FROM tempdb.dbo.MergeTemp mt
JOIN (
SELECT
o.[object_id]
, [object_name] = '[' + s.name + '].[' + o.name + ']'
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
JOIN sys.foreign_keys fk ON o.[object_id] = fk.parent_object_id
WHERE o.[type] = 'U '
) o ON mt.[object_name] COLLATE DATABASE_DEFAULT = o.[object_name] COLLATE DATABASE_DEFAULT
)
SELECT
@FKeysOff = (
SELECT 'ALTER TABLE ' + fk.[object_name] + ' NOCHECK CONSTRAINT ALL;' + CHAR(13)
FROM fkeys fk
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
,
@FKeysOn = (
SELECT 'ALTER TABLE ' + fk.[object_name] + ' CHECK CONSTRAINT ALL;' + CHAR(13)
FROM fkeys fk
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
EXEC sys.sp_executesql @FKeysOff
EXEC sys.sp_executesql @FKeysOn

This query seems cumbrous and it doesn’t take into account more complicated relations between tables. In case when the database contains relatively few tables, the following query (turning off all table triggers and foreign keys in the database) may be used:

EXEC sys.sp_msforeachtable '
ALTER TABLE ? DISABLE TRIGGER ALL
ALTER TABLE ? NOCHECK CONSTRAINT ALL '

4) Data synchronization between tables

We have already access to data from the master database stored in XML.

With the following query we can find the unique identifier of the database object we are going to synchronize:

SELECT
o.[object_id]
, mt.[object_name]
FROM tempdb.dbo.MergeTemp mt
JOIN (
SELECT
o.[object_id]
, [object_name] = '[' + s.name + '].[' + o.name + ']'
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.[type] = 'U '
) o ON mt.[object_name] COLLATE DATABASE_DEFAULT = o.[object_name] COLLATE DATABASE_DEFAULT

It’s important to note that the object identifier (object_id) is unique in the database and may be changed when the schema is modified (e.g., adding a new column). That’s why, initially, we were wrote the name of the object instead of its identifier. To synchronize tables, we need to parse data from XML.

As an option, for this, you can use the OPENXML construction:

DECLARE @IDoc INT
EXEC
sys.sp_xml_preparedocument @IDoc OUTPUT, @XML
SELECT [<column name>]
FROM OPENXML(@IDoc, '<table name>/t')
WITH ([<column name>] [<column datetype>]
EXEC sys.sp_xml_removedocument @IDoc

 

However, this approach is less effective when parsing significantly large XML structures. Therefore, it was decided to use the construction described below:

Here is an example of a query, that will generate dynamic SQL by XML parsing and synchronization with the target table:

DECLARE
@ObjectID INT
, @ObjName NVARCHAR(300)
,
@SQL NVARCHAR(MAX)
DECLARE package CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
SELECT
o.[object_id]
, mt.[object_name]
FROM tempdb.dbo.MergeTemp mt
JOIN (
SELECT
o.[object_id]
, [object_name] = '[' + s.name + '].[' + o.name + ']'
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.[type] = 'U '
) o ON mt.[object_name] COLLATE DATABASE_DEFAULT = o.[object_name] COLLATE DATABASE_DEFAULT
OPEN package
FETCH NEXT FROM package INTO @ObjectID, @ObjName
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT
@SQL = '
DECLARE @XML XML
SELECT @XML = (
SELECT data
FROM tempdb.dbo.MergeTemp
WHERE [object_name] = '''
+ @ObjName + '''
)
IF EXISTS(
SELECT 1
FROM sys.columns c WITH (NOLOCK)
WHERE c.[object_id] = '
+ CAST(@ObjectID AS VARCHAR(10)) + '
AND c.is_identity = 1
) SET IDENTITY_INSERT '
+ @ObjName + ' ON;
DELETE FROM '
+ @ObjName + '
INSERT INTO '
+ @ObjName + '(' +
STUFF((
SELECT ', [' + c.name + ']'
FROM sys.columns c
WHERE c.[object_id] = @ObjectID
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)'), 1, 2, '') + ')
SELECT '
+ STUFF((
SELECT
', [' + c.name + '] = ' + CASE WHEN c2.name IS NOT NULL THEN 'ISNULL(' ELSE '' END + 'r.c.value(''@' + c.name + ''', ''' + b.name +
CASE
WHEN b.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'varbinary') AND c.max_length = -1 THEN '(max)'
WHEN b.name IN ('char', 'varchar') AND c.max_length!= -1 THEN '(' + CAST(c.max_length AS VARCHAR(10)) + ')'
WHEN b.name IN ('nchar', 'nvarchar') AND c.max_length!= -1 THEN '(' + CAST(c.max_length / 2 AS VARCHAR(10)) + ')'
WHEN b.name = 'decimal' THEN '(' + CAST(c.[precision] AS NVARCHAR(10)) + ',' + CAST(c.[scale] AS VARCHAR(10)) + ')'
ELSE ''
END + ''')' + CASE WHEN c2.name IS NOT NULL THEN ', ' + c2.[definition] + ')' ELSE '' END
FROM
sys.columns c
JOIN sys.types b ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id AND b.name!= 'xml'
LEFT JOIN sys.default_constraints c2 ON c.default_object_id = c2.[object_id]
AND b.name IN ('int', 'bit', 'char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar')
AND
c.is_nullable = 0
WHERE c.[object_id] = @ObjectID
AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
,
1, 2, '') + '
FROM @XML.nodes('''
+ s.name + '.' + o.name + '/t'') r(c)
'
FROM sys.objects o
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.[object_id] = @ObjectID
EXEC sys.sp_executesql @SQL
FETCH NEXT FROM package INTO @ObjectID, @ObjName
END
CLOSE
package
DEALLOCATE package

The following script will build such query for each synchronized table:

DECLARE @XML XML
SELECT
@XML = (
SELECT data
FROM tempdb.dbo.MergeTemp
WHERE [object_name] = '<table name>'
)
IF EXISTS(
SELECT 1
FROM sys.columns c
WHERE c.[object_id] = <table_id>
AND
c.is_identity = 1
) SET IDENTITY_INSERT <TABLE name> ON;
DELETE FROM <TABLE name>
INSERT INTO <TABLE name> (<columns list>)
SELECT [<column name>] = r.c.value('@<column name>', '<column datetype>')
FROM @XML.nodes('<table name>/t') r(c)

Synchronization can be performed in three steps:

  1. Inserting new data
  2. Updating existing data by a key field
  3. Deleting non-existing data in the master database

The provided sequence of actions can be organized through separate operators: INSERT/UPDATE/DELETE; or using a MERGE construction that was implemented in MS SQL Server 2008. However, in this case, SQL code will have quite a complicated structure, since we will have to take into account many factors (e.g., insert data in accordance to the order of the relations between tables). In case if the database has a more simple structure, an easier approach can be used.

If all the tables of the database or a specific isolated schema are synchronized, there is a possibility to directly remove all the records from the tables and insert data from the master database. On one hand, such approach can be the most cost-effective one in terms of implementation, if a database with simple logic is synchronized. On the other hand, this way of synchronization may lead to damages in the integrity and validity of the data in the database.

5) Enabling relations and triggers between the tables of the target database

After inserting data into required tables, it’s necessary to enable foreign keys and triggers in order not to damage the cascade integrity and internal business logic. This can be performed as follows:

EXEC sys.sp_msforeachtable '
ALTER TABLE ? ENABLE TRIGGER ALL
ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL
'

When executing this command, the server will check whether the inserted data is correct. Therefore, if an error arises on this step, the problem may initially due to damaging of the cascade integrity.

The resulting version of the script:

USE AdventureWorks2008R2
SET NOCOUNT ON;
SET XACT_ABORT ON;
RAISERROR('Start...', 0, 1) WITH NOWAIT
DECLARE
@SQL NVARCHAR(MAX)
,
@Time DATETIME
, @Text NVARCHAR(300)
,
@SessionUID UNIQUEIDENTIFIER
SELECT
@SQL = ''
, @Time = GETDATE()
,
@SessionUID = NEWID()
SELECT @Text = CONVERT(NVARCHAR(25), @Time, 114) + ' Creating temporary table...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
IF OBJECT_ID('tempdb.dbo.MergeTemp', 'U') IS NULL BEGIN
CREATE TABLE
tempdb.dbo.MergeTemp
(
id UNIQUEIDENTIFIER NOT NULL
,
[object_name] NVARCHAR(300) NOT NULL
,
create_date DATETIME NOT NULL DEFAULT GETDATE()
,
data XML NULL
,
CONSTRAINT [PK_MergeTemp] PRIMARY KEY CLUSTERED
(
id ASC, [object_name] ASC
) ON [PRIMARY]
)
END
SELECT
@Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Generate SQL queries...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
DECLARE
@Data NVARCHAR(4000)
,
@ObjectName NVARCHAR(300)
DECLARE [tables] CURSOR READ_ONLY FAST_FORWARD LOCAL FOR
SELECT
[object_name]
, [sql] = '
INSERT INTO tempdb.dbo.MergeTemp(id, [object_name], data)
SELECT '''
+ CAST(@SessionUID AS VARCHAR(36)) + ''', ''' + quota_object_name + ''', (SELECT ' + object_columns + '
FROM '
+ quota_object_name + ' t
FOR XML AUTO, ROOT('''
+ [object_name] + '''))'
FROM (
SELECT
[object_name] = s.name + '.' + t.name
, quota_object_name = '[' + s.name + '].[' + t.name + ']'
, object_columns = STUFF((
SELECT ISNULL(', [' + c.name + '] = NULLIF([' + c.name + '], ' + c2.[definition] + ')',
', [' + c.name + ']')
+
CASE WHEN b.name = 'varbinary'
THEN ' = CAST('''' AS XML).value(''xs:base64Binary(sql:column("'+ c.name +'"))'',''VARCHAR(MAX)'')'
ELSE ''
END
FROM
sys.columns c WITH (NOLOCK)
JOIN sys.types b WITH (NOLOCK) ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id
LEFT JOIN sys.default_constraints c2 ON c.default_object_id = c2.[object_id]
AND b.name IN ('char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar')
WHERE t.[object_id] = c.[object_id]
AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
,
1, 2, '')
FROM (
SELECT
o.[object_id]
, o.[schema_id]
, o.name
FROM sys.objects o WITH (NOLOCK)
WHERE o.[type] = 'U'
AND EXISTS(
SELECT 1
FROM sys.partitions p WITH (NOLOCK)
WHERE p.[object_id] = o.[object_id]
AND p.[rows] > 0
)
AND NOT EXISTS(
SELECT 1
FROM sys.columns c WITH (NOLOCK)
JOIN sys.types t WITH (NOLOCK) ON c.system_type_id = t.system_type_id
WHERE (c.user_type_id!= c.system_type_id OR t.name = 'xml')
AND
c.[object_id] = o.[object_id]
)
)
t
JOIN sys.schemas s WITH (NOLOCK) ON t.[schema_id] = s.[schema_id]
-- You can specify sync all db tables or specified schema/tables
-- WHERE s.name = 'Sales'
-- OR o.name IN ('Address', 'AddressType')
) data
OPEN [tables]
FETCH NEXT FROM [tables] INTO @ObjectName, @Data
IF (@@FETCH_STATUS = -1)
RAISERROR('Have nothing to export...', 16, 1)
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT
@Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Generate XML for ' + @ObjectName
RAISERROR(@Text, 0, 1) WITH NOWAIT
EXEC sys.sp_executesql @Data
FETCH NEXT FROM [tables] INTO @ObjectName, @Data
END
CLOSE
[tables]
DEALLOCATE [tables]
PRINT 'ELAPSED TIME: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS NVARCHAR(15)) + 'ms'
RAISERROR('Finish...', 0, 1) WITH NOWAIT
PRINT REPLICATE('-', 80)
USE AdventureWorks2008R2_Live
RAISERROR('Start...', 0, 1) WITH NOWAIT
SELECT @Time = GETDATE()
SELECT @Text = CONVERT(NVARCHAR(25), @Time, 114) + ' Get similar objects in both databases...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
DECLARE @MergeTemp TABLE
(
[object_name] NVARCHAR(300)
,
[object_id] INT
, data XML
)
INSERT INTO @MergeTemp
(
[object_name]
, [object_id]
, daata
)
SELECT
mt.[object_name]
, o.[object_id]
, mt.data
FROM tempdb.dbo.MergeTemp mt
JOIN (
SELECT
o.[object_id]
, [object_name] = '[' + s.name + '].[' + o.name + ']'
FROM sys.objects o WITH (NOLOCK)
JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id]
WHERE o.[type] = 'U'
) o ON mt.[object_name] COLLATE DATABASE_DEFAULT = o.[object_name] COLLATE DATABASE_DEFAULT
WHERE mt.id = @SessionUID
IF NOT EXISTS(
SELECT 1
FROM @MergeTemp
) RAISERROR('Have nothing to import...', 16, 1)
SELECT @Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Turn off foreign keys and triggers...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
EXEC sys.sp_msforeachtable '
ALTER TABLE ? DISABLE TRIGGER ALL
ALTER TABLE ? NOCHECK CONSTRAINT ALL
'
DECLARE
@ObjectID INT
, @ObjName NVARCHAR(300)
DECLARE package CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
SELECT
mt.[object_id]
, mt.[object_name]
FROM @MergeTemp mt
OPEN package
FETCH NEXT FROM package INTO
@ObjectID
, @ObjName
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT
@Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Processing ' + @ObjName + '...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
SELECT @SQL = '
DECLARE @XML XML
SELECT @XML = (
SELECT data
FROM tempdb.dbo.MergeTemp
WHERE [object_name] = '''
+ @ObjName + ''' AND id = ''' + CAST(@SessionUID AS VARCHAR(36)) + '''
)
IF EXISTS(
SELECT 1
FROM sys.columns c WITH (NOLOCK)
WHERE c.[object_id] = '
+ CAST(@ObjectID AS VARCHAR(10)) + '
AND c.is_identity = 1
) SET IDENTITY_INSERT '
+ @ObjName + ' ON;
DELETE FROM '
+ @ObjName + '
INSERT INTO '
+ @ObjName + '(' +
STUFF((
SELECT ', [' + c.name + ']'
FROM sys.columns c WITH (NOLOCK)
WHERE c.[object_id] = @ObjectID
AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)'), 1, 2, '') + ')
SELECT '
+ STUFF((
SELECT
', [' + c.name + '] = ' + CASE WHEN c2.name IS NOT NULL THEN 'ISNULL(' ELSE '' END + 'r.c.value(''@' + c.name + ''', ''' + b.name +
CASE
WHEN b.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'varbinary') AND c.max_length = -1 THEN '(max)'
WHEN b.name IN ('char', 'varchar') AND c.max_length!= -1 THEN '(' + CAST(c.max_length AS VARCHAR(10)) + ')'
WHEN b.name IN ('nchar', 'nvarchar') AND c.max_length!= -1 THEN '(' + CAST(c.max_length / 2 AS VARCHAR(10)) + ')'
WHEN b.name = 'decimal' THEN '(' + CAST(c.[precision] AS NVARCHAR(10)) + ',' + CAST(c.[scale] AS VARCHAR(10)) + ')'
ELSE ''
END + ''')' + CASE WHEN c2.name IS NOT NULL THEN ', ' + c2.[definition] + ')' ELSE '' END
FROM
sys.columns c WITH (NOLOCK)
JOIN sys.types b WITH (NOLOCK) ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id AND b.name!= 'xml'
LEFT JOIN sys.default_constraints c2 WITH (NOLOCK) ON c.default_object_id = c2.[object_id]
AND b.name IN ('bit', 'char', 'varchar', 'nchar', 'nvarchar')
WHERE c.[object_id] = @ObjectID
AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
,
1, 2, '') + '
FROM @XML.nodes('''
+ s.name + '.' + o.name + '/t'') r(c)
'
FROM sys.objects o WITH (NOLOCK)
JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id]
WHERE o.[object_id] = @ObjectID
EXEC sys.sp_executesql @SQL
FETCH NEXT FROM package INTO
@ObjectID
, @ObjName
END
CLOSE
package
DEALLOCATE package
SELECT @Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Turn on foreign keys and triggers...'
RAISERROR(@Text, 0, 1) WITH NOWAIT
EXEC sys.sp_msforeachtable '
ALTER TABLE ? ENABLE TRIGGER ALL
ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL
'
PRINT 'ELAPSED TIME: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS NVARCHAR(15)) + 'ms'
RAISERROR('Finish...', 0, 1) WITH NOWAIT

Conclusion:

As we can see, even for databases with a simple structure, the synchronization process is quite a bigtask that includes many different actions.

As you have noticed early in the article, manual data synchronization is not quite easy task. Thus, to save time and efforts it is highly recommended to use special tools for synchronization. For instance, the following screen-shot demonstrates how you can visualize compared data before synchronization by using comparison feature of dbForge Studio for SQL Server.

The upper grid shows the database objects differences and the bottom grid displays data differences. To synchronize source and target databases you can automatically generate the synchronization script. Then you can view this script, save it, or execute it immediately to update the target database.  So the process can be handled in an easier way.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Faster Application Performance with In-Memory OLTP: Microsoft SQL Server 2014 and SafePeak Dynamic Caching

Today, I’d like to examine how in-memory OLTP engines to vastly improve performance and scalability of SQL Server databases and to accelerate applications.  A very large part of this article focuses specifically on SQL Server 2014 and its new In-Memory OLTP capabilities.

So, let’s dive in.

Achieving fast application response times, high performance and database scalability is critical. Typically, we may have used two approaches looking for performance gains:

  1. Basic application and database read-write code tuning and redesign
  2. Deploying high-spec hardware servers and storage

Both techniques have limitations.  Code re-writes and database redesign cban be lengthy processes, and can introduce bugs into otherwise stable systems.  Bigger hardware and specialized storage technologies can be expensive. And upgrading to larger hardware can also cause increased software licensing costs.

In-Memory to the rescue

In-memory solutions are becoming increasingly popular and software development professionals have started to embed different kinds of In-Memory solutions.  Caching prevents the cached queries from hitting the database at all, thus eliminating the locks, blocks and spinlocks and drastically reducing CPU, Memory and I/O load. The benefit is that storing, accessing and maintaining data IN-MEMORY (instead of on disks) greatly improves application performance and helps Dev and IT teams meet SLA goals.

There are a few different flavors of in-memory technology. Let’s take a look.

Data-Grids (IMDG) and Distributed Caching Solutions

For developers building new applications, a popular approach is to use API/code based Data-Grids (IMDG) and Distributed Caching solutions. Such solutions enable complex parallel processing, event processing and generally help to build highly scalable, high performance applications (such as large social networks’ websites and real-time stock-trading platforms).  But major code development effort is required. Developers are required to not only put (and get) data into In-Memory Cache but also to mimic database transactional logic.  Maintaining this code can get complicated.

For most production databases and applications such major code changes are not a feasible option.

In-Memory Dynamic Caching

In-memory dynamic caching is another approach that is very well suited to accelerating live in-production applications and databases.  In-memory dynamic caching does not require additional code in applications, or modifications to databases.  This key advantage over Data Grids and Distributed Caching solutions (and over the SQL Server 2014 In-Memory OLTP engine, as we will see) makes it much easier technology to deploy. Because there is no requirement to touch app-code, it also means this approach will accelerate 3rd-party applications that use SQL Server, like SharePoint or Dynamics.

With in-memory dynamic caching, all queries and stored procedures are analyzed and managed automatically: their logic and dependent objects (like tables, views, triggers, procedures, etc.) are identified and analyzed.  All read-based queries and procedures leverage the in-memory cache. Write commands are monitored and cache invalidation is applied in real-time.

Because in-memory dynamic caching is the easiest solution to deploy currently, I’ll take a closer look at the leading vendor in this category later in this article. But let’s first take a hard look at in-memory OLTP from SQL Server 2014.

Introduction to SQL Server 2014 In-Memory OLTP

Microsoft’s newly released SQL Server 2014 features the In-Memory OLTP engine (some even define this as a revolution).

I want to examine SQL Server 2014 In-Memory OLTP from different angles: how to start using it, provide directions for migration planning, review closely many of its limitations, discuss SQL 2014 In-Memory OLTP applicability and see where the SQL Server In-Memory OLTP can be an alternative to in-memory dynamic caching, and where it is complimentary.

SQL Server 2014’s biggest feature is definitely its In-Memory transaction processing, or in-memory OLTP, which Microsoft claims make database operations much faster. In-memory database technology for SQL Server has long been in the works under the code name “Hekaton”.

Microsoft built its new In-Memory OLTP query processing engine from scratch, using a new lock-free and latch-free design. This is a key differentiator from alternatives such as pinning tables with DBCC PINTABLE or from putting databases on SSDs. While “DBCC PINTABLE” places a table in SQL Server’s buffer pool and the SSDs provide higher I/O, relational locks and latches still exist.

SQL Server 2014 In-Memory OLTP engine uses a new optimistic multi-version concurrency control mechanism. When a row in a shared buffer is modified, the In-Memory OLTP engine makes an entirely new version of that row and timestamps it. This process is very fast because it’s done entirely in memory. The engine then analyzes and validates any updated rows before committing them. This design is faster and more scalable than the traditional locking mechanism used by SQL Server’s relational database engine because there are no locks or other wait-states that prevent the processor from running at full speed.

In order to start using the SQL 2014 In-Memory OLTP, a database must have certain tables (actually, the file groups used to store tables) declared as memory-optimized. The resulting table can be used as a conventional database table or as a substitute for a temporary table.

Additional speed improvements are made from the gains realized by keeping a table in memory. Reads and writes to an in-memory table only lock on individual rows, and even then, row-level locks are handled in such a way that they don’t cause concurrency issues. Stored procedures can also run in memory for more of a boost by having them compiled to native code.

Excellent performance for DML write commands (Inserts, Update, Delete)

In classic disk tables there is a latch contention: concurrent transactions are attempting to write to the same data page; latches are used to ensure only one transaction at a time can write to a page.

The In-Memory OLTP engine is latch-free; there is no concept of pages. Thus, concurrent transactions do not block each other’s inserts, thus enabling SQL Server to fully utilize the CPU.

Microsoft released benchmarks show the following performance difference for INSERT intensive tables (run on a server with 2 CPUs, with 24 logical cores):

Disk-based tables Memory-optimized tables Performance difference
10,000,000 order inserts using 100 threads, with 100,000 inserts each 1:31:11 0:01:49 50X

Read (SELECT) commands can gain significant benefit as well. The new engine allows creating “Native” pre-compiled Stored Procedures can access data of memory-optimized tables and perform data changes.  With the use of In-memory tables there are significantly less (but not zero) locks, blocks, spinlocks and of course the I/O scans.

Quick start guide to SQL Server 2014 In-Memory

Step #1: Add MEMORY_OPTIMIZED_DATA filegroup to enable in-memory OLTP for your Database:

IF NOT EXISTS (SELECT * FROM sys.data_spaces WHERE TYPE='FX')
ALTER DATABASE CURRENT ADD FILEGROUP [AdventureWorks2012_mod] CONTAINS MEMORY_OPTIMIZED_DATA
GO
IF NOT EXISTS (SELECT * FROM sys.data_spaces ds JOIN sys.database_files df ON ds.data_space_id=df.data_space_id WHERE ds.TYPE='FX')
ALTER DATABASE CURRENT ADD FILE (name='AdventureWorks2012_mod', filename='$(checkpoint_files_location)AdventureWorks2012_mod') TO FILEGROUP [AdventureWorks2012_mod]

Step #2: For memory-optimized tables, automatically map all lower isolation levels (including READ COMMITTED) to SNAPSHOT:
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON

Step #3: Create your specific Table(s) to be Memory_Optimized:

To use In-Memory OLTP, you define a heavily accessed table as memory optimized. You have to define the primary key settings advanced settings when you create the table (can’t be altered later).

Notice the HASH, BUCKET_COUNT and MEMORY_OTIMIZED settings:
CREATE TABLE [Sales].[SalesOrderDetail_inmem](
[SalesOrderID] UNIQUEIDENTIFIER NOT NULL INDEXIX_SalesOrderID HASH WITH (BUCKET_COUNT=1000000),
[SalesOrderDetailID] [int] NOT NULL,
[OrderDate] [datetime2] NOT NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL INDEXIX_ProductID HASH WITH (BUCKET_COUNT=10000000),
/*
...
...
*/
INDEX IX_OrderDate (OrderDate ASC),
CONSTRAINT [imPK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY NONCLUSTERED HASH
(
[SalesOrderID],
[SalesOrderDetailID]
) WITH (BUCKET_COUNT=10000000)

) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)

Step #3 – Update statistics for memory-optimized tables:
UPDATE STATISTICS Sales.SalesOrderDetail_inmem
WITH FULLSCAN, NORECOMPUTE

Step #4 – CREATE stored procedures to be “NATIVE_COMPILATION”:

Stored procedures that only reference Memory_Optimized tables can be natively compiled into machine code for further performance improvements.
CREATE PROCEDURE Sales.usp_InsertSalesOrder_inmem
@SalesOrderID UNIQUEIDENTIFIER OUTPUT,
@DueDate datetime2 NOT NULL,
@OnlineOrderFlag bit NOT NULL,
@Comment NVARCHAR(128) = NULL,
@SalesOrderDetails Sales.SalesOrderDetailType_inmem readonly
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN
ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
/*
Your procedure code
Your procedure code
Your procedure code
*/
END

Migrating to SQL Server 2014 In-Memory OLTP

Migration to In-Memory OLTP has to be performed in a development environment and carefully tested. Your High-Availability design, Databases design, Tables schemas and data, stored procedures, business logic in the database and even application code – all may require many syntax changes to use In-Memory OLTP.

This is not a “click and migrate” process. It requires development cycles, application and database design and code changes.

The right way to use the In-Memory OLTP engine is:

  1. Plan your production database architecture. The In-Memory OLTP is very different and has many limitations in terms of H/A, Mirroring, Replications available functionalities;
  2. Plan carefully your new database (and possibly application) design;
  3. Migrate several specific tables and procedures that are good benefit candidates;
  4. Develop or change your business-logic to fit the new design;
  5. Test and evaluate;
  6. Deploy

To evaluate whether the In-Memory OLTP can improve your database performance, you can use Microsoft new AMR tool (Analysis, Migrate and Report). For helping with actual migration you can use the Memory Optimization Advisor for tables and the Native Compilation Advisor to help porting a stored procedure to a natively compiled stored procedure.

The AMR tool helps identifying the tables and stored procedures that would benefit by moving them into memory and also help performing the actual migration of those database objects. The AMR tool is installed when you select the “Complete” option of “Management Tools”, and is later accessed through SQL Server Management Studio (SSMS) in Reports  –>> Management Data Warehouseà Transaction performance reports tool:

The AMR tool provides reports which tables and procedures can benefit the most from In-Memory OLTP and provide a hint how complex will be the conversion. The reports show either recommendations based on usage, contention and performance. Here is example (graphics may change in the GA release):

After you identify a table that you would like to port to use In-Memory OLTP, you can use the Memory-Optimization Advisor to help you migrate the disk-based database table to In-Memory OLTP. In SSMS Object Explorer, right click the table you want to convert, and select Memory-Optimization Advisor.

Limitations of SQL Server 2014 In-Memory OLTP

The In-Memory addition to SQL Server 2014 does not just expand the main features of the SQL Server, but rather it is a completely new engine. Just like any new software, the SQL Server In-Memory engine has limitations. Many things have to be planned and defined differently.

The below list is not full, but rather represents many major non-supported features for your consideration:

Server and Database limitations

  • REPLICATION is not supported – Officially not supported, although in-memory tables can be defined as Subscribers (but not Publishers)
  • MIRRORING is not supported
  • DATABASE SNAPSHOT is not supported
  • 250GB limit per server – Total data in all loaded in-memory tables cannot exceed 250GB
  • MARS is not supported – Multiple Active Result Sets (MARS) is not supported with natively compiled stored procedures, so your application can’t use MARS connection to talk with the database
  • Change Data Capture (CDC) is not supported
  • DTC (distributed transactions) are not supported
  • RTO (Recovery Time Objective) of your High Availability – the Starting and Recovery time is slower – For every database object (table, stored procedure) SQL Server has to compile and link the corresponding DLL file (that is loaded afterwards into the process space of sqlservr.exe), and this also takes some time. The compilation and linking is also performed when you are restarting your SQL Server, or when you perform a cluster failover.

SCHEMA, KEYS, INDEXes, TRIGGERS  limitations:

  • FIXED Schema – You have to design your in-memory tables with knowledge of your data size. Indexes, statistics, and blocks cannot be changed / applied later.
  • ALTER TABLE for existing disk table is not supported – You can’t alter existing tables to become memory-optimized. You have to create new tables that are memory-optimized.
  • ALTER TABLE for in-memory table is not supported – You cannot add another column to a memory-optimized table in your production.
  • Row size limited to 8060 bytes
  • FOREIGN KEY’s and CHECK CONSTRAINTS are not supported
  • Datatypes:
    • Datetimeoffset, Geography, Hierarchyid, Image, Ntext, Text, Sql_variant, Varchar(max), Xml, User data types (UDTs) – not supported
    • CHAR and VARCHAR – Should be replaced to n(var)char
  • Various not supported TABLE and COLUMN definitions: IDENTITY, ON (file group or partition), Data type [name], Computed Columns, FILESTREAM, SPARSE, ROWGUIDCOL, UNIQUE
  • INDEX limitations: no COLUMNSTORE, CLUSTERED INDEX, no LOB datatypes
  • DDL TRIGGERS and Event Notifications (Server and Database level) – Have to be removed before creating or dropping memory-optimized tables and/or natively compiled procedures
  • LOGON TRIGGERS do not affect memory-optimized tables
  • DML TRIGGERS cannot be defined on memory-optimized tables – You can explicitly use stored procedures to insert, update, or delete data to simulate the effect of DML triggers.

T-SQL non supported operators:

  • Classic: OR, NOT, IN, LIKE, BETWEEN, CONTAINS, PERCENT, DISTINCT, NEXT VALUE FOR
  • UNION’s are  not supported
  • MIN, MAX – limited to non strings
  • LEFT / RIGHT / FULL OUTER JOIN – Outer joins return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions – not supported (only Inner Joins are supported).
  • FOR XML, FOR BROWSE
  • Dynamic SQL (EXECUTE, EXEC) not supported
  • CURSORs are not supported
  • Sub-Queries are not supported

Transactions, Cross Database queries and

  • BEGIN, COMIT, ROLLBACK are not supported – “Atomic Blocs” are an alternative
  • Cross Database queries and transactions are limited – You cannot access another database from the same transaction or the same query that also accesses a memory-optimized table. You can create Table Variables, create two transactions: 1) insert the data from the remote table into the variable; 2) Insert the data into the local memory-optimized table from the variable.
  • MERGE – A useful feature with performing insert, update, or delete operations on a target table based on the results of a join with a source table.

Functions, Views and Stored Procedures

  • User-defined functions cannot be used in natively compiled stored procedures
  • No In-Memory (“Native”) Functions
  • VIEWs – Views cannot be accessed from natively compiled stored procedures.
  • Disk-based tables cannot be accessed from natively compiled stored procedures. 

T-SQL window functions are not supported at all. Examples:

  • ROW_NUMBER()
  • RANK()
  • OVER (PARTITION BY) or OVER (PARTITION BY)

First conclusions about SQL Server 2014 In-Memory OLTP

Microsoft took a huge step with the SQL Server 2014 In-Memory OLTP, allowing developers to create high-speed and more scalable applications and databases. Embedding the new In-Memory OLTP engine inside SQL Server allows combining both classic disk-based tables and procedures together with the In-Memory tables and natively compiled stored procedures. This gives you amazing tools to create high speed enterprise and web-scale applications.

But the In-Memory OLTP has many limitations. It lacks support for many classically used T-SQL operators and functionalities, and many complications exist to migrate existing apps. The new In-Memory engine is not automatic to deploy, nor plug-and-play friendly. It’s designed to be carefully defined, developed and tested. Migration of existing applications to benefit In-Memory OLTP is possible but only in cases where you are able to develop and maintain the needed changes in the database and the application.

The SQL Server 2014 In-Memory OLTP is a great framework that is really focused on building new applications or new application-parts that will benefit from the In-Memory engine. For new apps it is also not a silver bullet solution to fit any problem, challenge or need, but rather a mission-specific tool.

In-Memory Dynamic Caching: An SQL Server 2014 In-Memory OLTP alternative or complimentary?

We have reviewed briefly the main in-memory data caching alternatives (data-grids, distributed caching, and dynamic caching).  Of these, dynamic caching can be the easiest to deploy. Our examination of SQL Server 2014 In-Memory OLTP shows it to be a very interesting solution but one that has various limitations making it tough to use for existing production applications.  So the obvious question is this: how do Dynamic Caching and the new SQL Server In-Memory OLTP engine compare?  Are they complimentary? What’s best to use today?

So we can ground the conversation in reality, let’s compare the leader in dynamic caching for SQL Server, SafePeak, with SQL Server 2014 In-Memory OLTP.

SafePeak In-Memory Dynamic Caching

SafePeak is a query-level automated caching middleware for SQL Server based operational applications. SafePeak’s software solution combines three ideas:

  1. Result-based data caching for repetitive queries and stored procedures;
  2. In-memory databases that automatically keep transaction data integrity; and
  3. A solution that can fit immediately to production applications (including closed third party apps) with no code changes and minimal efforts for deployment.

SafePeak can accelerate the performance of applications and databases by In-Memory Caching of results from repetitive SQL queries and SQL stored procedures, while maintaining 100% ACID compliance and data integrity. SafePeak answers queries in microseconds (<0.001sec). SafePeak result-based dynamic caching prevents cached queries from hitting the database at all, thus eliminating the locks, blocks and spinlocks and drastically reducing usage and of I/O, CPU and Memory.

The SafePeak solution is software only. It doesn’t require any specialized hardware. And it doesn’t require any changes to apps or databases to deploy (unlike data grids).   It’s based on “smart auto-learning”, where all queries and stored procedures are analyzed automatically. It identifies and analyzes logic and dependent objects (like tables, views, triggers, procedures, etc.) for you.  And as applications evolve, so too does cached data.

Critically, it has advanced data integrity and eviction processes.  SafePeak will determine if an incoming query is an update, insert, alter or any other request that may cause a change in the database. In this case, SafePeak dissects the request and decides which tables in the database may be affected by its execution. It then looks at the query results stored in the Cache Manager and evicts all results that have any connection to the affected database tables.

Besides the auto-pilot mechanism, SafePeak’s management dashboard allows you to tune and refine caching effectiveness.

For example, with a good understanding of the application, a user can configure caching rules for certain SQL queries, stored procedures or even tables and views to have reduced cache invalidation sensitivity to table updates, in order to keep the data in cache. Consider a use-case like “Get details of order #123” command, where the result does not change with entrance of new orders.

Another example is a “cache warm-up” mechanism, in which you create a list of queries that are scheduled to enter SafePeak’s Cache.

SafePeak and SQL Server 2014 In-Memory OLTP

Now that we’ve looked at SafePeak’s software for dynamic caching, the following chart lets us quickly compare it to SQL Server 2014 In-Memory OLTP.

Attribute SQL Server 2014In-Memory OLTP SafePeakIn-Memory Dynamic Caching
Data In-Memory All table data Hot Data only
Type of data in memory Raw tables data Result-sets(of Queries and Procedures)
RAM requirements High ** All table data is loaded to memory Medium *
* Only Hot and Computed data is in-memory
Middleware No Yes
Response time from memory Micro to milliseconds Microseconds
Fast speed for “first query call” Yes No *
* Possible via cache warm-up
Read:Write ratio affects the Read efficiency performance No Yes
READ queries acceleration High High
WRITE queries acceleration High Low
No Locks on tables Yes No* Locks are reduced but only due to less load on the server
SQL Server Support 2014 only 2005, 2008, 2012(2014 to be supported during 2014)
Full T-SQL and Schema support No, has many limitations Yes
Replication, Mirroring support No Yes
Database / Application Migration requirements Complex None
Time to migrate and deploy for existing production application Long Short
Fits 3rd party applications (SharePoint, Dynamics…) No Yes

The initial comparison shows that SafePeak In-Memory Dynamic Caching is a better fit for general cases of production and custom applications (including OLTP and Reporting applications), especially where there are significantly more reads than writes.  The SafePeak technology also accelerates 3rd-party applications like Microsoft SharePoint, Microsoft Dynamics, or BMC Remedy for example.

SafePeak focuses on caching Reads, but has also a limited in-direct acceleration effect on Writes.

As a by-product, SafePeak also can improve the scalability of SLQ Server databases, thanks to two situations:

  1. Reads no longer tax database I/O, CPU or memory resources – which is especially helpful to databases that are serving “chatty” applications;
  2. Databases have freed-up resources now available to serve more users and more transaction workloads.

The SQL In-Memory OLTP engine is focused on loading specific tables into memory, creating specific queries and procedures calls for them and getting great performance on those tables. However many changes may be required to the database and the application. Performance and scalability improvements for very specific tasks, like Writes or Reads that are always-in-memory, can be much higher than SafePeak, but these improvements come with time and development costs. Also, SQL Server In-Memory OLTP engine does not answer all complex load and performance challenges.

The best answer probably is that both SafePeak Caching and the Microsoft In-Memory OLTP can be either chosen for different tasks or complement each other:

  • Where SafePeak “virtualizes” and accelerates the hot-data from all most-accessed tables, views and procedures, and
  • Where the SQL Server In-Memory OLTP boosts performance of a few very intensive write-and-read tables.

This has been a long article, but I hope it helps you better understand how In-Memory Data Cache solutions can address your SQL Server database performance and scalability.

If you’re curious to evaluate SafePeak software you can download free software from their website (www.safepeak.com/download).  It comes with technical training and free assistance support, which I always recommended taking advantage of.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Optimizing Three Important Resources – CPU, Memory and Disk

For any DBA there are three most important resources – CPU, Memory and Disk. Here is a real life story just happened recently which discusses CPU, Memory & Disk along with a new manager and frustrated employee. Well, thanks to Dell Software’s Spotlight on SQL Server Enteprise,  the story has a happy ending.  Trust me, you will enjoy this mini story, so continue reading.

Beginning of the Story

Recently, I was traveling to Delhi to speak at a popular annual conference. Due to a lenghy commute, I decided to arrive one day early to catch up with an old friend. My friend and I had plans to have dinner together as we had not seen each other for quite a while. However, when I called up my friend at 4 PM, he said he will be not able to join me and encouraged me to go to dinner alone. Trust me – I was here early to catch up with my friend, so dinner was not the priority. I immediately asked the usual question WHY? He replied that he has a new boss that is giving him a hard time with the database server.

After listening to his situation for about 10 minutes, I asked him if I can join him in his office to help him understand what is going on with his servers. If I can be any help and resolve the problem, maybe we can still catch up for the dinner, if not, well it’s still fun to work with a new database server and solve its problem. I arrived shortly at my friend’s office and he guided me to his cubicle.

My friend was recently moved to a new team in his organization where he was responsible of managing a critical server, but this server was suffering from a slow performance  issue. To make the matter worse, his team had a new manager who had just joined two days prior. In this world, there are many great managers (I have been rather fortunate), but there are always  people with “room for improvement”. His manager was only two days into his role, but due to this tense situation, he got aggravated at my friend and asked him to solve a problem before the end of the day. Additionally, he asked him to prepare the details related three important resources – CPU, Memory and Disk (with a presentation to match).

The Resources - CPU, Memory and Disk

For any system, there are three major resources that need to be accounted for – CPU, Memory and Disk . It is extremely crucial to know how each of these resources are used properly and optimally in any system. If due to any reason, any one of the resources is over used or under used, it can create a negative effect on the performance of the entire server. Here my friend was challenged to solve this problem very quickly –  as well as was asked to create reports on the subject. Well, this was indeed not easy for him to do in such a short period of the time.

Spotlight on SQL Server to the Rescue

Honestly, I knew this was not going great. I asked my friend that there is no way he can do everything in such a short period of the time. He needs to now depend on third party tools which are built for performance analysis and tuning. Sometimes it makes sense to leave the expert’s work to experts. There is simple no need to re-engineer the entire wheel. I suggested he download Dell Software’s Spotlight on SQL Server Enterprise and install it on his production server. The installation is pretty straight forward and right after it was installed, it brought up following screen. There are four sections in the Home Page sections.

  1. Sessions (Active sessions)
  2. SQL processes (CPU usages)
  3. SQL Memory (Buffer Cache)
  4. Disk Storage (Log File and Indexes)

When you look at each of the sections you can find various information very clearly and easily. It is absolutely impossible to explain each section in words, hence, I have created this 150 seconds video which explains how each of the sections works. Trust me, it is very easy to use tool.

Finally – Dinner Time

Well, my friend immediately installed Spotlight on SQL Server Enterprise on his server and was able to discover various issues in no time. In our case we had issues with blocked processes as well as slow disk. With the help of the following two areas on the home screen, we were able to quickly resolve the problem on his production server. Once the problem was solved, his manager was presented with various screenshots and graphs from the tool itself  – he was rather delighted to see all the tabular information,  as well as in a graphical way. Around 8 PM we left the office for dinner.

Today

If you wonder what is the status of my friend’s organization today, here is the quick note.

Action Item for my friend – He needs to pay for our dinner next time.

Action Item for you – Download Dell Spotlight on SQL Server Enterprise today and let me know what you think?

Reference: Pinal Dave (http://blog.SQLAuthority.com)