I ran into an odd problem with one of the Windows systems at a customer that was running with a very high CPU load. The system is running Windows Server 2012R2 with the DirectAccess role enabled. Upon further investigation it seemed that the SQL Server process that is running the Windows Internal Database for DirectAccess was to blame here:
I did some digging around on the web and found this blog post by Richard Hicks that described the exact problem I was experiencing on the DirectAccess servers:
DirectAccess SQL Server High CPU Usage – Richard Hicks/
In his blog post Richard used the GUI to manually create an additional index to reduce the CPU load on the DirectAccess server. Because I was having this issue on multiple servers I decided to create a PowerShell module based on his instructions. I have finalized the PowerShell module and made it publicly available: FixDaDatabase
The module comes with three cmdlets:
- Get-DaDatabaseIndexStatus
- Add-DaDatabaseIndex
- Remove-DaDatabaseIndex
To get started with the module either download the module from GitHub, the TechNet Script Gallery or the PowerShell Gallery. In order to install the module directly from the PowerShell gallery run the following command:
1 | Install-Module -Name FixDaDatabase -Verbose |
First we run Get-DaDatabaseIndexStatus to establish if an Index is already present on the system:
1 | Get-DaDatabaseIndexStatus |
Since the additional Index is not available the Add-DaDatabaseIndex cmdlet can be used to create the Index:
1 | Add-DaDatabaseIndex |
To verify the results of this cmdlet the Get-DaDatabaseIndexStatus cmdlet can be used:
1 | Get-DaDatabaseIndexStatus -Verbose |
After creating the additional Index the CPU load of the system dropped down significantly within a minute:
The module is available at the following locations:
- PowerShell Gallery – FixDaDatabase
- TechNet Script Gallery – FixDaDatabase
- GitHub – JaapBrasser – FixDaDatabase
If you have any questions or suggestions for this module feel free to leave a comment below or submit a pull request on GitHub.
All the links mentioned in this article are available below:
