Using SQL Server in Memory Tables

Owned by Aravind Naik (Unlicensed)
Last updated: Apr 23, 2024 by Mayuresh Balaji Kamble (Unlicensed)Version comment
1 min read

Using SQL Server 2016 and above in the Memory Tables for SummitAI on-premise instances, the performance of the Application can be significantly improved.

Prerequisites for SQL Server in Memory Tables

Memory tables are supported from SQL Server 2016 and above.

Configuring SummitAI Application for Using SQL Server in Memory Table

Generate the in-memory database in SQL server. Refer to Script for generating in memory session database.

Add the following settings to use SQL server in memory tables as session provider. Refer to sample web.config  For SQL server in memory

<sessionState mode="Custom"  customProvider="AzureSessionProvider" allowCustomSqlDatabase="true" sqlConnectionString="Server=tcp:serverIP;Database=AspState;User ID=userId;Password=password;">        

                           <providers>          

                                  <add useInMemoryTable="true" retryInterval="100"  maxRetryNumber="5" name="AzureSessionProvider" type="Azure.Utilities.SqlSessionStateStore, Azure.Utilities" writeExceptionsToEventLog="false" />        

                           </providers>
   
                     </sessionState>

Sample Web.Config

For SQL Server in Memory

Sample SQL Server In memory - Web.config

Script for Generating in Memory Session Database

aspstate_sql2016_with_retry.sql