Page Comparison

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 user 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

Problem

The following screenshot demonstrates these steps:

...

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

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

Solution

...

: Disable Auto Close

Problem

After an Upgrade, System Performance is Much Slower

Some clients may notice some system slowness issues after an upgrade. It may be due to a SQL Upgrade Script not properly running at time of upgrade. This is typically due to an install with insufficient SQL Permissions. Luckily, running the upgrade scripts is easy and can be done within minutes.

Solution

Run Upgrade Scripts

...

...

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;

Related articles

...