SQL Server 2014 SP2 - Clone Database

Looks like there is a new management feature available for SQL Server 2014 SP2 called "clone database".  According to Microsoft the goal is to create a database that contains the schema of all the objects and the current statistics from a specified source.

The main use of this command is to provide customer support a clone of your database so they can investigate performance issues related to the query optimizer.  I am assuming this could be used internally as well to get a copy of a production and restore this database to a support server for troubleshooting as well.  This might eliminate the need to take a native database backup of a large production database thus reducing the overall time, space, and complexity needed to troubleshoot certain issues in your production environment.  Keep in mind there are certain restrictions and you need to be a member of the sysadmin fixed server role.

• DBCC CLONEDATABASE uses an internal database snapshot of the source database for the transactional consistency that is needed to perform the copy. This prevents blocking and concurrency problems when these commands are executed. If a snapshot cannot be created, DBCC CLONEDATABASE will fail.

See the online KB article https://support.microsoft.com/en-us/kb/3177838

I will be testing with some local databases to see how I can effectively leverage this snapshot but it looks promising and unless  you happened to look at the SQL Server 2014 SP2 release notes you might have missed it.

Setting Min and Max SQL Server Memory Dynamically

Based off a standard configuration the goal is to set the min and max memory values via TSql script code.

You need to create the sp_configure statements as nvarchar variable because the syntax is incorrect when trying to execute them as part of a logic check


 DECLARE @totalmemory BIGINT
 DECLARE @max_memorysqlstmt NVARCHAR(500)
 DECLARE @min_memorysqlstmt NVARCHAR(500)
 SET @totalmemory = (SELECT CAST(physical_memory_kb as bigint) / POWER(1024, 2) FROM sys.dm_os_sys_info)
 IF @totalmemory > = 256
 BEGIN
PRINT 'Setting SQL Server memory to 256GB'
SET @min_memorysqlstmt = 'sp_configure ''min server memory'', 256000;'
SET @max_memorysqlstmt = 'sp_configure ''max server memory'', 256000;'
 END
 ELSE
 BEGIN
PRINT 'Setting SQL Server memory to 10GB'
SET @min_memorysqlstmt = 'sp_configure ''min server memory'', 10240;'
SET @max_memorysqlstmt = 'sp_configure ''max server memory'', 10240;'
 END
 EXECUTE sp_executesql @max_memorysqlstmt
  EXECUTE sp_executesql @min_memorysqlstmt
 RECONFIGURE;