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

The following screenshot demonstrates these steps:

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

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.

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

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

Updated “SQL Authentication” connection string

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.

Related articles