Optimize the Inbox Accounting Database
Applies To: Windows Server 2012, Windows Server 2012 R2
You can use this topic to reconfigure your DirectAccess Inbox Accounting database to improve database performance.
The Inbox Accounting feature utilizes a local database that is installed on the DirectAccess server. If you are using the Inbox Accounting database with your Remote Access deployment, it is possible that the database is consuming too many CPU cycles and too much memory, diminishing database performance.
This topic provides the solution of adding a new index to a table in the accounting database to help improve the operations performance. This is accomplished by reducing the number of disk read and write operations that are conducted by several stored procedures that are called by the Remote Access Management Service during logging operations.
If you have a DirectAccess server and you are using Inbox Accounting, you can investigate database performance by reviewing the following symptoms against your server's performance.
On the DirectAccess server, the process SQLSERVR.EXE is consuming a steady level of CPU cycles, on most occasions the majority of the CPU time for a particular core.
The memory usage of the process SVCHOST.EXE hosting the RAMGMTSVC service is growing to very high levels and only settling back down during off-hours.
Reports lag behind when querying for recent dates, and data from the last few days is missing from the reports.
You can use the following procedures to resolve these issues. In addition, you must perform these procedures on all DirectAccess servers in your deployment.
Membership in Administrators, or equivalent, is the minimum required to perform these procedures.
Note
You can perform the following procedures manually or by creating one or more Windows PowerShell scripts (*.ps1) that perform the same tasks. If you chose to use one or more scripts, ensure that your execution policy on each server allows the scripts to run. For example, to configure Execution policy to allow scripts to run from an elevated Windows PowerShell console, open Windows PowerShell and run the following command. Set-ExecutionPolicy RemoteSigned For more information on the Set-ExecutionPolicy Windows PowerShell command, see Set-ExecutionPolicy.
To Backup the Inbox Accounting Database
Select a disk location where you want to store your database backup, and then, if necessary, change the following information in the code below to include your database path and file name: C:\Windows\DirectAccess\db\RaAcctDb.bak
Open Windows PowerShell with Administrator privileges.
Copy and paste each of the following commands into the Windows PowerShell console, and press ENTER after each command.
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection $connection.ConnectionString = 'Server=np:\\.\pipe\Microsoft##WID\tsql\query;Database=RaAcctDb;Trusted_Connection=True;' $command = $connection.CreateCommand() $command.CommandText = " BACKUP database RaAcctDb TO DISK='C:\Windows\DirectAccess\db\RaAcctDb.bak' WITH DESCRIPTION = 'Full backup before optimisation changes'" $connection.Open() $command.ExecuteNonQuery() $connection.close()After the $command.ExecuteNonQuery() is run, Windows PowerShell displays a value of -1.
To Apply Optimizations to the Inbox Accounting Database
Open Windows PowerShell with Administrator privileges.
Copy and paste each of the following commands into the Windows PowerShell console, and press ENTER after each command.
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection $connection.ConnectionString = 'Server=np:\\.\pipe\Microsoft##WID\tsql\query;Database=RaAcctDb;Trusted_Connection=True;' $command = $connection.CreateCommand() $command.CommandText = "CREATE INDEX IdxSessionTblState ON [RaAcctDb].[dbo].[SessionTable] ([SessionState]) INCLUDE ([ConnectionId])" $connection.Open() $command.ExecuteNonQuery() $connection.close()After the $command.ExecuteNonQuery() is run, Windows PowerShell displays a value of -1.
You can use the following procedure to verify the previous procedure.
To Verify Inbox Accounting Database Configuration
Open Windows PowerShell with Administrator privileges.
Copy and paste each of the following commands into the Windows PowerShell console, and then press ENTER.
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection $connection.ConnectionString = 'Server=np:\\.\pipe\Microsoft##WID\tsql\query;Database=RaAcctDb;Trusted_Connection=True;' $command = $connection.CreateCommand() $command.CommandText = "SELECT name from sys.indexes where name like 'IdxSessionTblState'" $adapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter $command $dataset = New-Object -TypeName System.Data.DataSet $adapter.Fill($dataset) $dataset.Tables[0]After you run these commands, Windows PowerShell displays the following output.
name ---- IdxSessionTblState
You can use the following procedure to optionally remove the optimizations that you made to the Inbox Accounting database using the previous procedures.
To Remove Optimizations to the Inbox Accounting Database (Optional)
Open Windows PowerShell with Administrator privileges.
Copy and paste each of the following commands into the Windows PowerShell console, and then press ENTER.
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection $connection.ConnectionString = 'Server=np:\\.\pipe\Microsoft##WID\tsql\query;Database=RaAcctDb;Trusted_Connection=True;' $command = $connection.CreateCommand() $command.CommandText = "DROP INDEX IdxSessionTblState ON [RaAcctDb].[dbo].[SessionTable]" $connection.Open() $command.ExecuteNonQuery() $connection.close()After the $command.ExecuteNonQuery() is run, Windows PowerShell displays a value of -1.
Note
To resolve other database server performance issues, ensure that you install the following hotfixes, which address potential memory pressure problems that are unrelated to the solution provided in this topic. Windows Server® 2012 R2 KB 3063853. Clients are stuck in a "Connecting" state when they try to connect to a DirectAccess server that is running Windows Server 2012 R2. Windows Server 2012 R2 and Windows Server® 2012 KB 2895930. Remote Access Management leaks memory when a VPN or Direct Access connection is used in Windows Server 2012 R2 and Windows Server® 2012.
You can also review the following list for additional updates.
- KB 2883952. Recommended hotfixes and updates for DirectAccess in Windows Server 2012 R2 and Windows Server 2012.