Monday, 27 October 2014

SQL Server 2014 new In-memory Tables

I have summarised basic concepts of In-Memory OLTP from various Microsoft articles which will help you to start up with the basics,

SQL Server 2014 new In-memory Tables:-

Microsoft has come up new concept called In-Memory OLTP with the introduction of SQL Server 2014 for performance optimization on OLTP database servers. The traditional SQL Server stores the tables in the disk and SQL OS moves the data pages from the disk to the memory (SQL Server Buffer) depending based on the query executed, most of the times the frequently accessed tables pages in the memory are paged to disk to accommodate other requested data pages resulting in frequent paging and high PAGIOLATCH waits. With the cost of hardware reducing over the years In-Memory OLTP gives us the option to store the frequently used table in memory instead of disk. In-Memory architecture is fully integrated with SQL Server and the Memory Optimized tables are fully transactional, durable, and are accessed using Transact-SQL in the same way as disk-based tables. 

In-Memory Architecture

Legacy SQL server did have similar function called DBCC PINTABLE which pins the data pages of the table to memory and these pages does not flush to the disks, but this was a SQL Server internal function meaning the pages are pinned to SQL Server Buffer cache which most of times creates a memory crunch for other data which needs processing which resulted in more of a problem than resolution this function was depreciated in SQL Server. In-Memory OLTP architecture unlike DBCC PINTABLE has its own memory space for storing memory optimized tables and indexes and don’t use Buffer cache which can be still used effective for disk based table operation. SQL server can interact with memory optimized table and a disk based table in same transaction meaning you can have both memory-optimized tables and disk-based tables in the same database, and query across both types of tables.

Transactional Concurrency:-

One of major advantages of the memory optimized table is locks on the table are very minimal irrespective of the isolation level of the transaction. Take for example in READ SNAPSHOT ISOLATION on a disk based table does not block a SELECT operation during a data modification due to row versioning of the data in tempdb database but this still blocks any process which tries to update/delete the data simultaneously but in Memory optimized architecture there are no locking if two transactions attempt to update the same row, rather it is managed by a conflict management. memory-optimized tables use multi versioning meaning each row has different version instead of single row version used in snapshot isolation, Also unlike snapshot isolation which stores the row version in tempdb, memory optimized tables row version are stored in memory. This architecture allows even higher isolation levels to have transactional concurrency. As a part of conflict management SQL server detect conflicts between concurrent transactions, If a conflict is detected, the transaction is terminated and the client needs to retry.


Memory optimized Tables can be configured as both fully durable(SCHEMA_AND_DATA) and non-durable(SCHEMA_ONLY). Memory optimized tables configured fully durable(SCHEMA_AND_DATA) can be recovered  without any data loss on an SQL Server restart whereas Tables configured with SCHEMA_ONLY durability will be able to recover only the table metadata on a SQL server restart, these tables can be typically your staging tables used in ETL process which can be flushed on database restart like tempdb tables.

Like Disk-based tables all changes are logged to a transaction log for durable memory optimized tables and the SQL server writes the transaction to the disk. Now this where it can be misleading on why the data is written to disk when the table is in memory, Memory-optimized tables is fully integrated with the same log stream used by disk-based tables, to recover the table point in time and to allow operations like T-Log, backup and restore without any additional steps, Memory optimized table maintains a copy of table and data on disk on a different data structure stored as free form data rows with the help of two files called data and delta files. The data and delta files are located in one or more containers (using the same mechanism used for FILESTREAM data). These containers are mapped to a new type of filegroup, called a memory-optimized filegroup. On a SQL Server restart memory optimized tables are flushed back into memory from the data and delta files for fully durable table.


Create a database  with a memory optimized filegroup and container

Create a Durable memory optimized table
CREATE TABLE dbo.durabletable_memory 
( Col1 int PRIMARY KEY,
  Col2 char(200) NONCLUSTERED HASH (Id) WITH (BUCKET_COUNT = 1024) 
Memory Optimization Indexes

Memory optimized tables supports indexes, Memory-optimized indexes exist only in memory and index operation are not logged into transaction logs.

There are two types of memory-optimized indexes

Non-clustered Hash Index
 Non clustered Index

Hash index architecture is different from traditional index and it uses a data structure called buckets to store index and data.
Each memory-optimized table must have at least one index. Note that each PRIMARY KEY constraint implicitly creates an index. Therefore, if a table has a primary key, it has an index. A primary key is a requirement for a durable memory-optimized table.


In-Memory OLTP will improve performance best in OLTP with short-running transactions, but to gain the performance improvement we need to select the right table to be optimized otherwise it can cause huge performance issues, being a new component memory optimized OLTP has lot of function limitation which may be improved in future versions. For example cross database transaction, replication, mirroring, database snapshots is not supported and there are also various limitations on operation you can perform on these tables. But if a right table is configured to use this feature Microsoft suggests we may have have 5-20 times performance improvement which is a huge leap.

Wednesday, 8 October 2014

How to create a SSRS DR/Copy Report Server Database across different servers

Imagine you have installed and initialized SSRS in your Primary and DR Environment and To Mirror the DR environment to the Primary SSRS follow the below steps,

  1. Copy the ReportServer and ReportServerTempDB to the DR Server

Note:- If you just do the database restore and try to access the SSRS you get the error “The report server installation is not initialized. (rsReportServerNotActivated)”, You need to follow the below steps to configure the DR SSRS with the existing database.

2.Backup Encryption Key for the Primary SSRS

3.  Copy the Encryption Key File to DR SSRS Server, In my case say C:\Test\ssrsencryptkey.snk

4.  Run the following Query in ReportServer database of the DR server
'DELETE FROM [Dbo].[Keys] WHERE client > -1'

5.  Now search for Program Files folder for “RSKeymgmt.exe” in DR Server

6.  Execute the "RSKeymgmt.exe" from command Prompt of DR Server in Admin context with the below syntax, 
RSKeymgmt.exe -a -f "C:\Test\ssrsencryptkey.snk" -p "<passwordof theencryptionkey>"

Once you  have completed these steps you access the Reports web page for the DR SSRS it should match the primary server. Remember I have not configured any real time DR for the ReportServer and ReportServerTempDB databases in the above example, You have to set up Logshipping/Mirroring for the SSRS databases for complete DR Solution. The above steps merely explains how to configure in case of failover.The same set of steps can be used if you want to copy SSRS across different servers. Hope the article helps.

Wednesday, 6 August 2014

SQL Database Supect Mode due to MSDTC Transaction

A database may go into suspect mode for various reason most of them are related to corruption of disk or data but SQL server may also put a database in a suspect mode if it’s not able to recover the database properly on restart
We had one such scenario recently, On a restart of planned outage one of the database went in to Suspect mode and we saw the following error in the SQL error log,

2014-08-01 10:37:31.760 spid5s       Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2014-08-01 10:39:13.480 spid5s       SQL Server detected a DTC/KTM in-doubt transaction with UOW  {14EBFD4C-0955-4C25-91B1-C418EE182E64}.Please resolve it following the guideline for Troubleshooting DTC Transactions.
2014-08-01 10:39:13.480 spid5s       Error: 3437, Severity: 21, State: 3.
2014-08-01 10:39:13.480 spid5s       An error occurred while recovering database 'DBName'. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (0:75299367). Fix MS DTC, and run recovery again.
2014-08-01 10:39:13.480 spid5s       Error: 3414, Severity: 21, State: 2.
2014-08-01 10:39:13.480 spid5s       An error occurred during recovery, preventing the database 'DBNAME' (6:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

We already know the application using the database uses MSDTC for various sources and any unexpected restart put an In-doubt transaction on DTC. Basically In-Doubt transactions are transaction which the DTC services cannot decide to abort or resolve by itself but since the UOW for the transaction exists
in SQL, SQLOS does not differentiate it aa transaction in In-doubt status and kill it and the transaction will be marked as active till is resolved or aborted from DTC. This is similar to the topic which I wrote earlier in the blog regarding “-2 SPID Blocking”.

when the database tries to recover a DB with UOW of in-doubt transaction on restart it could not rollback or roll forward the transaction so SQL puts database eventually in suspect mode failing to recover completely. You go Component Services->Computers->My Computer->Distributed transaction Coordinator->Local DTC->Transaction List for standalone servers or  Component Services->Computers->My Computer->Distributed transaction Coordinator->Clustered DTC->Transaction List for a cluster instance, Right Click on all transaction marked In-Doubt either commit or abort the transaction depending on your requirements. And restart the SQL Services which should recover the database clean.

But the clean way of doing a restart of a SQL with DTC transactions is to switch of the application before the SQL restart which will avoid this issue in the first place. Hope this article helps!

Friday, 27 June 2014


Find the example TSQL to use UPDATE statement with REPLACE function,

UPDATE [AdventureWorks2008R2].[Person].[Person] 
SET FirstName = REPLACE(FirstName,'sam','jim') Where FirstName = 'sam'

SSIS Out of Memory/Buffer Manager errors

Often I get queries from my developers regarding “out of memory exception” or Buffer Manager errors during data flow task, the frequent query they ask is “I am running the package in the SQL server box itself and if the SQL server is granted say 128 GB of memory why are we getting memory issues.
Valid question but is your SSIS Package actually using the memory allocated to SQL Server?

To understand better on how memory is used during SSIS execution you find interesting details in below article,

To summarise the above query SSIS uses two components run-time engines(To control the package flow) and data flow engine(To control the data flow tasks)

The Data flow engine uses buffer to move  and work with the data, Meaning the data is actually physically moved to your memory or page file and part of the memory used for this is the memory allocated outside of SQL Server and not SQL server buffer manager .This is why we get the memory exceptions even though we run the package on SQL server as SQL server is more aggressive with memory allocation and willy occupy most of the memory allocated to it. SSIS has to fight for memory allocated outside of SQL server which is usually very less on a dedicated SQL box.

To control the buffer size and number of rows in each buffer you can use the DefaultMaxBufferSize  and DefaultMaxBufferRows property in the dataflow task. The default size for buffer is 10 MB and default number of rows in a buffer is 10000.

Microsoft recommends the following  “Tweak the values for DefaultMaxBufferRows and DefaultMaxBufferSize to get as many records into a buffer as possible. Setting these values too low causes SSIS to create many small buffers instead of fewer but larger buffers, which is a great scenario if you have enough memory“.

In case you could not avoid lack of memory resource, SSIS can page the buffers in the disk, this event usually slows down the process. But you can control the location where these buffers are paged in the disk using the property BufferTempStoragePath. The default value for this property is the TEMP/TMP system variable location.

Monday, 14 April 2014

Monitor and Alert SQL Stack dumps

As many of know not all stack dumps raise an error in SQL Server and may go unnoticed for days, SQL stack dumps can sometimes cause database corruption and its always better to analyse the server immediately after a stack dump. Since there are no errors raised we cannot configure normal SQL Alert to report the SQL Stack dumps, I created a PowerShell script which can also be scheduled as a SQL Agent job to monitor the SQL Stack dumps, I have used Chad Millers invoke-sqlcmd2 just for flexibility in the future, you can use native invoke-sqlcmd without any issue. 

If you want to schedule this script as SQL Agent job remember to comment the clear-host and the invoke-sqlcmd2 function in the below script and try to use invoke-sqlcmd instead.Also I have scripted this to check the stack dumps for last one hour as I am running the SQL agent every hour in my environment, please change accordingly.

# Script to Monitor Stack Dump in SQL Server
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 17/02/2014

 [String]$sqlinstance_name = 'sqlinstancename'
##################################################Function Invoke-sqcmd##########################################################################
#Script refered from
Runs a T-SQL script. 
Runs a T-SQL script. Invoke-Sqlcmd2 only returns message output, such as the output of PRINT statements when -verbose parameter is specified 
    You cannot pipe objects to Invoke-Sqlcmd2 
Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid = 1" 
This example connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query. 
2010-08-12 21:21:03.593 
Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt" 
This example reads a file containing T-SQL statements, runs the file, and writes the output to another file. 
Invoke-Sqlcmd2  -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose 
This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command. 
VERBOSE: hello world 
Version History 
v1.0   - Chad Miller - Initial release 
v1.1   - Chad Miller - Fixed Issue with connection closing 
v1.2   - Chad Miller - Added inputfile, SQL auth support, connectiontimeout and output message handling. Updated help documentation 
v1.3   - Chad Miller - Added As parameter to control DataSet, DataTable or array of DataRow Output type 
function Invoke-Sqlcmd2 
    [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance, 
    [Parameter(Position=1, Mandatory=$false)] [string]$Database, 
    [Parameter(Position=2, Mandatory=$false)] [string]$Query, 
    [Parameter(Position=3, Mandatory=$false)] [string]$Username, 
    [Parameter(Position=4, Mandatory=$false)] [string]$Password, 
    [Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600, 
    [Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15, 
    [Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile, 
    [Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow" 
    if ($InputFile) 
        $filePath = $(resolve-path $InputFile).path 
        $Query =  [System.IO.File]::ReadAllText("$filePath") 
    $conn=new-object System.Data.SqlClient.SQLConnection 
    if ($Username) 
    { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout } 
    { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout } 
    #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller 
    if ($PSBoundParameters.Verbose) 
        $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"} 
    $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn) 
    $ds=New-Object system.Data.DataSet 
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd) 
    switch ($As) 
        'DataSet'   { Write-Output ($ds) } 
        'DataTable' { Write-Output ($ds.Tables) } 
        'DataRow'   { Write-Output ($ds.Tables[0]) } 
##################################################END: Function Invoke-sqcmd##########################################################################
$Errorlog = @()
$Errorlog = Invoke-Sqlcmd2 -ServerInstance $sqlinstance_name -Database 'master' -Query 'sp_readerrorlog 1'

ForEach($line in $Errorlog)
 if($line.text -like '*BEGIN STACK DUMP*')
  if($line.LogDate -gt (Get-Date).AddHours(-1)) #Change According to your requirement
   $mailsql = "EXEC msdb.dbo.sp_send_dbmail
      @recipients = '',
      @copy_recipients = '',
      @from_address =  '', 
      @body = 'SQLDumps has been reported in the last one hour for the SQL instance "+$sqlinstance_name+" Please Investigate-"+$line.logDate+":"+$line.text+"',
       @subject = '*******ERROR:SQL Dumps Reported********'"
   Invoke-Sqlcmd2 -ServerInstance $sqlinstance_name -Database 'msdb' -Query $mailsql

Friday, 14 March 2014

PowerShell to list the packages stored in Integration Services SQL2008/SQL2008R2

PowerShell to list the packages stored in Integration Services SQL2008/SQL2008R2; I will come up with a SQL 2012 version soon.

# Get SSIS Package List from a Integration Services Server
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 14/02/2014

$SSISServerName = "Enter SSIS Servername"

 add-type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Import-Module SQLPS –DisableNameChecking -ErrorAction SilentlyContinue
$FnChildFolders = @()
$FnBaseFolderPath = $null
$SubFnChildFolder = @()
$FnChildFolders = @()
$FolderName = $null
$ChkFolderName = $null
$ChildFolderElements = @()
$Element = @()
$SubChkFolder = $null
$BaseFolderPath = $null
$SSISServerName = $null
$SSISServer = @()
$BaseFolder = @()
$ChildFolders = @()
$BaseFolderPakages = @()
$SubChildFolder = @()
$pkgfolderpath = $null
$subpkgfolderpath = $null

Function GetPackageFromFolder($FnChildFolders,$fnFoldername)
 [String]$FolderName= $FnChildFolders
 Write-Host $fnFoldername
   Write-Host "----------------------------`n"
 $ChildFolderElements = $SSISServer.GetPackageInfos($FolderName,$SSISServerName,$null,$null) 
   if($ChildFolderElements -ne @() -or $ChildFolderElements -ne $null)
 forEach($Element in $ChildFolderElements)
  if($Element.Flags -eq "Folder")
   Write-Host "`n`tSubFolder----------->"$Element.Name
   $subpkgfolderpath = $FolderName+$Element.Name+"\"
   GetPackageFromFolder $subpkgfolderpath $Element.Name
   Write-Host $Element.Name
Write-Host "`n"
$BaseFolderPath = "\\"
$SSISServer =  New-Object Microsoft.SQLServer.DTS.RunTime.Application
$BaseFolder = $SSISServer.GetPackageInfos('\',$SSISServerName,$null,$null) 
$ChildFolders = $BaseFolder|Where{$_.Flags -eq "Folder"}
$BaseFolderPakages = $BaseFolder|Where{$_.Flags -eq "Package"}
Write-Host "\"
Write-Host "----"
Write-Host "`n"
ForEach($SubChildFolder in $ChildFolders)
  $FolderName = $SubChildFolder.Name
  $SubChildFolderpath = $BaseFolderPath+$FolderName+"\"
  GetPackageFromFolder $SubChildFolderpath $FolderName
Copyright © 2014 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.