Skip to main content
Skip table of contents

Mitigating SQL Performance Issues

There are a couple of issues that can occur within SQL that may cause some server-side performance, which can create performance issues within GlobalSearch.

Problem: SQL Server Eating Up All Server RAM

SQL will use as much RAM as it thinks it needs, regardless of the memory consumption of other applications on the server. This can lead to a SQL Server Instance causing a server to become unstable or have performance spikes. You can mitigate this issue by stipulating an upper threshold of memory usage for the SQL Server.

Solution: Limit SQL Server Usage

  1. Open SQL Server Management Studio (SSMS)

  2. Right click the database object in the object explorer and click "Properties"

  3. Select the "Memory" page and set "Maximum server memory (in MB):" to your maximum desired usage

  4. Hit "OK" and restart your SQL Server

The following screenshot demonstrates these steps:

Configure memory in SSMS

Problem: SQL Databases are Constantly Opening and Closing, filling up Event Viewer with Notifications

Oftentimes GlobalSearch databases are set to utilize "Autoclose" which will close the SQL database when it is not in use. This can cause issues with performance both on the SQL side, needlessly fill up your Event Viewer with verbose data, and in some cases, cause other system issues.

Solution: Disable Auto Close

  1. Open SQL Server Management Studio (SSMS)

  2. Expand "Databases" in Object Explorer

  3. For each of your production databases and GlobalSearch System databases, do the following:

    1. Right click the database and click "Properties"

    2. Open the "Options: page

    3. Under the "Other Options" Section locate "Auto Close" under the "Automatic" Section (you may have to scroll upwards to find this section)

    4. Set "Auto Close" to False

    5. Hit "OK"

The following screenshot demonstrates these steps:

The following SQL script is equivalent to the above steps:
Be sure that you update the [SmartSearch] to reflect the database you want to disable Auto Close on.

SQL
USE [master]
GO
ALTER DATABASE [SmartSearch] SET AUTO_CLOSE OFF WITH NO_WAIT
GO

Problem: Newly created GlobalSearch databases are slower than normal

When a new GlobalSearch database is created it in turn creates a SQL database of the same name. It stores the connection string for this new SQL database in SSMaster.dbo.ssConnectionStrings. By default the newly created connection strings will use Intergrated Security for authentication. This can sometimes cause issues depending on the server environment. This often comes up for the education VM or for Demo instances that are not tied to a domain/other issues that cause Windows Auth slowness.

Solution: Change connection string from “Integrated Security” to SQL Authentication

The best way to update these connection strings is with the use of SSMS, simply right click on the ssConnectionStrings table and select “Edit Top 200 Rows”;

From there all you will have to do is update the connection strings to replace Integrated Security=True; with User Id=sqlauthuser;Password=password . See the example below;

Original “Integrated Security” connection string

CODE
Data Source=(local)\SQLEXPRESS;Initial Catalog=BrewHaven;Integrated Security=True;MultipleActiveResultSets=true;

Updated “SQL Authentication” connection string

CODE
Data Source=(local)\SQLEXPRESS;Initial Catalog=BrewHaven;User Id=globalsearch;Password=globalsearch;MultipleActiveResultSets=true;

Note that you will have to ensure that SQL Authentication users are both enabled and you have created a user with the appropriate SQL permissions to the SQL database in question.




JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.