Optimize a SQL Server database - HxGN SDx - Update 31 - Installation & Upgrade

Installation and Setup for HxGN SDx Application Server

Language
English (United States)
Product
HxGN SDx
Search by Category
Installation & Upgrade
SmartPlant Foundation / SDx Version
10

You can improve the performance of a SQL Server database by creating memory optimized tables using In-Memory OLTP. Memory optimized tables can improve concurrency, reduce locking, and lower CPU and IO overhead processing. Although performance is always situation-dependent, in most cases system performance and scalability can be improved in a multi-user environment.

This process creates a memory optimized filegroup on disk and allocates the relevant tables to this filegroup. SQL Server disk allocation for memory optimized tables is stored in one or more data/delta file pairs (also referred to as checkpoint file pairs or CFP).

In-Memory OLTP is available in SQL Server 2017 with both the Enterprise and Standard editions. In SQL Server 2016, it is available with the Enterprise edition. It is available with the Standard edition in version 2016 Service Pack 1.

We recommend creating the GRAPH_TMP table in the HxGN SDx database as a memory optimized table and using the SCHEMA_ONLY setting. No data is stored in this table since it is a temporary table.

  1. Delete the existing GRAPH_TMP table in your database.

    When upgrading the software of a HxGN SDx installation, if a GRAPH_TMP table had been created for the previous version of the software, you should delete the old GRAPH_TMP table, create a new table, check performance, and then run the script below.

  2. Run the script below after making the following changes:

    1. Edit the FILENAME option in the third ALTER DATABASE statement to define a drive and path of your choice.

      Do not create the folder yourself on disk. It will be created automatically when the script is executed.

    2. Change YourDatabaseName to the name of your database where this appears in the script.

  3. In SDx Server Manager, view the site properties for the site associated with this database and change the Multiple active result sets to FALSE.

    Failure to complete this step will cause the following error: SPFException: Failed to insert database records; System.InvalidOperationException: Cannot access destination table 'GRAPH_TMP'

SHARED Tip If the DBA notices that GRAPH_TMP is deadlocking, or there are unstable execution plans involving GRAPH_TMP, you can use Enterprise Edition from SQL Server 2019 to turn off this table and use session isolated TMP files instead. For more information, see Configure HxGN SDx to use SQL Server #TMP files.

The following sample script creates a new GRAPH_TMP table that uses memory optimization:

SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;

SET NOCOUNT ON;

--

-- Variables and cursors.

--

DECLARE @lowestPossibleVersion1 AS NVARCHAR(MAX)= '13';

DECLARE @lowestPossibleVersion2 AS NVARCHAR(MAX)= '2216';

DECLARE @version1 AS NVARCHAR(MAX);

DECLARE @version3 AS NVARCHAR(MAX);

DECLARE @lobjStartTime DATETIME;

DECLARE @full_path NVARCHAR(MAX);

DECLARE @new_path NVARCHAR(MAX);

DECLARE @DBName NVARCHAR(MAX);

DECLARE @SQLCommand NVARCHAR(MAX);

--

-- Set variable properties

--

SELECT @full_path = physical_name

FROM sys.database_files

WHERE [type_desc] = 'ROWS';

SET @new_path = LEFT(@full_path, LEN(@full_path) - CHARINDEX('\', REVERSE(@full_path), 1) + 1) + DB_NAME() + 'inmemory';

SELECT @version1 = LEFT(CONVERT(VARCHAR(MAX), SERVERPROPERTY('productversion')), 2);

SELECT @version3 = RIGHT(LEFT(CONVERT(VARCHAR(MAX), SERVERPROPERTY('productversion')), 9), 4);

PRINT(FORMAT(SYSDATETIME(), 'yyyy/MM/dd-HH:mm:ss:fff') + ': Executing "DI-156970-GraphTmpInMemory.tsql" ...');

BEGIN TRY

--

--Check the SQL version first

--

IF(@version1 > @lowestPossibleVersion1

OR (@version1 = @lowestPossibleVersion1

AND @version3 >= @lowestPossibleVersion2))

BEGIN

PRINT('Version is higher than 13.0.2216.0 your version is: ' + @@version);

--

-- Check if graph tmp is already memory optimized

--

IF(EXISTS

(

SELECT name,

is_memory_optimized

FROM sys.tables

WHERE type = 'u'

AND name = 'Graph_Tmp'

AND is_memory_optimized = 1

))

BEGIN

PRINT 'Graph tmp is already memory optimized exiting';

END;

ELSE

BEGIN

--

-- Remove the existing table

--

IF(EXISTS

(

SELECT *

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'GRAPH_TMP'

))

BEGIN

PRINT 'Removing Existing Graph_Tmp table';

DROP TABLE GRAPH_TMP;

END;

--

-- Set the database options to allow in memory table

--

PRINT 'SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON';

SET @SQLCommand = 'ALTER DATABASE [' + DB_NAME() + '] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;';

EXECUTE (@SQLCommand);

PRINT 'ADD FILEGROUP MEMORY CONTAINS MEMORY_OPTIMIZED_DATA';

SET @SQLCommand = 'ALTER DATABASE [' + DB_NAME() + '] ADD FILEGROUP MEMORY CONTAINS MEMORY_OPTIMIZED_DATA;';

EXECUTE (@SQLCommand);

PRINT 'ALTER DATABASE [' + DB_NAME() + '] ADD FILE( NAME = ''' + DB_NAME() + 'inmemory' + ''' , FILENAME = ''' + @new_path + ''') TO FILEGROUP MEMORY;';

SET @SQLCommand = 'ALTER DATABASE [' + DB_NAME() + '] ADD FILE( NAME = ''' + DB_NAME() + 'inmemory' + ''' , FILENAME = ''' + @new_path + ''') TO FILEGROUP MEMORY;';

EXECUTE (@SQLCommand);

--

-- Create the new in memory graph tmp

--

PRINT 'Create new in memory graph tmp';

CREATE TABLE [dbo].[GRAPH_TMP]

([SRCEDGE] [NVARCHAR](50) COLLATE Latin1_General_CI_AS NULL,

[SOURCEOBJUID] [NVARCHAR](128) COLLATE Latin1_General_CI_AS NULL,

[SOURCEOBJDOMAIN] [NVARCHAR](50) COLLATE Latin1_General_CI_AS NULL,

[RELUID] [NVARCHAR](128) COLLATE Latin1_General_CI_AS NULL,

[RELDOMAIN] [NVARCHAR](50) COLLATE Latin1_General_CI_AS NULL,

[OBJUID] [NVARCHAR](128) COLLATE Latin1_General_CI_AS NULL,

[OBJDOMAIN] [NVARCHAR](50) COLLATE Latin1_General_CI_AS NULL,

[OBID] [NVARCHAR](50) COLLATE Latin1_General_CI_AS NULL,

[EDGECONFIGS] [NVARCHAR](200) COLLATE Latin1_General_CI_AS NULL,

[ROW_ID] [UNIQUEIDENTIFIER] NOT NULL

DEFAULT(NEWID()),

CONSTRAINT [PK_GRAPH_TMP] PRIMARY KEY NONCLUSTERED([ROW_ID] ASC)

) WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

END;

END;

ELSE

BEGIN

PRINT('Cannot install in memory table. SQL server is too old min version 13.0.2216.0 your version is: ' + @@version);

END;

END TRY

For more information on In-Memory OLTP, refer to the Microsoft SQL Server documentation. For example, see https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/introduction-to-memory-optimized-tables.